Solved

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

Posted on 2012-03-11
3
438 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:Anthony Mellor
  • 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:Anthony Mellor
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

840 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