Application.VLookup(Lookupvalue, Evaluate(ArrayStr.Value), 2, False) - improvements at all?

SumVLooks.xlsmWhat this does is sum a bunch of numbers each looked up in col 2 of a series of array constants stored as strings and brought in to use by Evaluate at run time.

These array constants look like this (test data) {1,2;3,4;5,6} the col 2 numbers can be anything money related, the col 1 numbers are document refs, probably numeric, no decimals.

The UDF is intended to check that errors in the evaluate are skipped and errors caused by failure to lookup the lookup value are also skipped, but NOT to skip errors that arise when both the Evaluated Array Constant and the Lookup Value have been found not to error. In other words errors in the column 2 amount ARE desired as results for investigation.

The IF is single line, NOT block, reads better as said single line.

I have managed to avoid all Variants in this version, though the price is doing the Evaluate twice.

Anything left to improve?
I am now thinking about speed if applied to a few thousand cells in a column.

Public Function SumVlookups(ByVal Lookupvalue As Single, ByVal RangeToSum As Range) As Single

    Dim ArrayStr As Range               
       
    For Each ArrayStr In RangeToSum
       
       If Not IsError(Application.VLookup(Lookupvalue, Evaluate(ArrayStr.Value), 1, False)) Then SumVlookups = SumVlookups + Application.VLookup(Lookupvalue, Evaluate(ArrayStr.Value), 2, False)
              
      Next ArrayStr
 
End Function

Open in new window


Anthony
LVL 9
Anthony MellorChartered AccountantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Why avoid Variants when they would save you doing both calculations twice for every item?
Anthony MellorChartered AccountantAuthor Commented:
I guess that is really my question, which is faster, using a variant or evaluating twice?

I see much written about avoiding the use of variants because of the additional processing they involve.

The evaluate when valid will always return an array constant {1,2;3,4;5,6} (contents vary); I gather from another question that I could type this to say Single but I get value errors when I do, even though the entries are all numeric - is that what you would expect or should I be posting an example?
Rory ArchibaldCommented:
I would never use a Single - I'd use a Double, since that is what Excel uses natively.

I would also use Variants here since it will always save you having to do two calculations and possibly three. Further, I would load the entire range into a Variant to start with rather than reading cell by cell. It should be considerably faster with thousands of cells.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.