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.
Anthony
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 FunctionAnthony
Rory Archibald
Why avoid Variants when they would save you doing both calculations twice for every item?
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.