+ Reply to Thread
Results 1 to 3 of 3

How to read an array one time only, and use it in a UDF evaluated in multiple cells?

  1. #1
    Registered User
    Join Date
    08-06-2013
    Location
    the boonies
    MS-Off Ver
    Excel 2007
    Posts
    46

    Question How to read an array one time only, and use it in a UDF evaluated in multiple cells?

    This code I'm developing (my first VBA script) essentially works, but given real-world data attached to it, will be inefficient and likely slow.
    There is a reference sheet called "markers" containing 25000 lines (example attached only has 20) that need to be cross-referenced to about 30000 data points. Currently my code would need to read the 25000 lines at each of the 30000 data points getting cross-referenced. But, I'm sure there's a better way.

    Can you show me how to read the reference arrays in worksheet "markers" just one time (from what I've read I think this would need to be done outside of the UDF or with Public scope - not clear to me), while my UDF is called repeatedly and makes use of those arrays to make evaluations in worksheets "A" and "B" (see range A7:A16 in both those workseets).

    Thanks!

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: How to read an array one time only, and use it in a UDF evaluated in multiple cells?

    Here's one alternative:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    08-06-2013
    Location
    the boonies
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: How to read an array one time only, and use it in a UDF evaluated in multiple cells?

    protonLeah,

    Thanks once again for replying to my post. The coding update you provided nicely illustrated how to use public functions, enabling a one-time-only read of a large array for any number of function calls that use the array. This indeed saved a lot of time when scaling up and running the code on my real dataset. Greatly appreciated.

    There were some index mismatching issues lurking in v3(vba), which are resolved in v4 and posted here for completeness. [You may not have caught it because your sheet "B" function-calls reference the Select Case SD = "TsdA", but should reference SD = "TsdB". When the functions are given the intended SD argument, the result blows up]. What I learned was that the array read (at least as implemented here) always results in an index starting with "1" (note we elected to use Option Base 1 for this function). I thought that this code would (as I intended) put data from worksheet "markers" row X into array-element-row X. To resolve this I started doing some more complicated index adjustments, but then realized it was just simpler to let the array read .Range("E1:E25"). instead of starting at E6. So, the first few elements of the array capture whatever garbage I have in the first five header rows, but the array-element-index is now aligned with the "marker" worksheet row numbers, making QC much easier when dealing with all the data.

    I need help with at least one last enhancement to this project, and will post that on another thread.

    This issue SOLVED.

    Cheers!
    G
    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. [SOLVED] SUMIFS with multiple criteria evaluated against array?
    By opheim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-07-2013, 09:03 AM
  2. Replies: 11
    Last Post: 04-14-2013, 12:02 AM
  3. [SOLVED] Read last element of Split array and then discard it and resize the array.
    By staggers47 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-28-2012, 06:36 PM
  4. IF function: extending to read multiple cells/answers
    By balcony in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-25-2011, 02:08 PM
  5. Read Range To Array, Then Pass Array To Function
    By ProbablyNotARealName in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-20-2010, 12:29 PM

Tags for this Thread

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