Solved

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

Posted on 2012-03-11
3
430 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:anthonymellorfca
  • 2
3 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37708733
Why avoid Variants when they would save you doing both calculations twice for every item?
0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37708939
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?
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 37708951
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now