Solved

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

Posted on 2012-03-11
3
436 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

863 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

26 Experts available now in Live!

Get 1:1 Help Now