+ Reply to Thread
Results 1 to 5 of 5

Using a VBA UDF in an array formula

Hybrid View

  1. #1
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,436

    Re: Using a VBA UDF in an array formula

    Since I don't have access to the add-in file, I copied the code (you should edit your post to put the code in code tags -- this forum can be strict about those kind of things) into a module and ran it from the file (I assume the code here is the same as is in the add-in). When I ran the UDF in this way, the UDF fails. I added a stop statement to the top of the UDF, and discovered that it aborts on the TempArray(I,J)=ActiveSheet.Range(... statement. Looking at the variables just before that statement, I see that StrE and StrD are both empty, so this statement fails.

    I'm not sure what this statement is trying to do. I would have expected this statement to be looking at the arrays/ranges that were passed to the UDF -- not trying to go back to the spreadsheet for additional information. Before we can worry about how the UDF output is interacting with the MMULT() functions, we need to get a valid output from the UDF, which, I'm hoping, just means figuring out what that statement is supposed to be doing and then correcting it.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  2. #2
    Registered User
    Join Date
    08-31-2017
    Location
    Spokane Washington
    MS-Off Ver
    14 and up. All components
    Posts
    4

    Re: Using a VBA UDF in an array formula

    My apologies. As Mr. Shorty pointed out, there were two lines missing from my code. I have modified the file and inserted the missing statements. I have attached this macro enabled file which includes the corrected macro and a short subroutine called TestUDF that can be called from the developer tab to run the macro.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Array summing inventory of a specific part (array formula) EXCEL 2007
    By fdirosa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-07-2018, 12:51 PM
  2. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  3. Can I make the row lookup array/range part in an array formula variable?
    By OLLY-7 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-18-2014, 09:06 AM
  4. Array formula + Array formula with criteria that lookups a Table
    By anrichards22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-16-2013, 11:41 AM
  5. Replace hard coded array in formula with link to input array
    By David Brown in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2011, 07:45 AM
  6. Converting 3x10 array to a 1X30 array to run a Match formula
    By NBVC in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-08-2009, 07:45 AM
  7. Replies: 0
    Last Post: 07-27-2005, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1