We help IT Professionals succeed at work.

load the entire range into a Variant - how?

on
SumVLooksEval1.xlsmI want to give effect to this:

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.

The following code is modified to do the Evaluation once only, but how to apply the rest I do not understand.

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

Dim ArrayStr As Range
Dim Found As Variant

For Each ArrayStr In RangeToSum

Found = Evaluate(ArrayStr.Value)

If Not IsError(Application.VLookup(Lookupvalue, Found, 1, False)) Then SumVlookups = SumVlookups + Application.VLookup(Lookupvalue, Found, 2, False)

Next ArrayStr

End Function
``````

this is a follow on from here.

Anthony
Comment
Watch Question

View Solution Only

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Like so:

``````Dim varData
varData = RangeToSum.Value
``````

then iterate that array.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Revised function would be:
``````Public Function SumVlookups(ByVal Lookupvalue As Double, ByVal RangeToSum As Range) As Double
Dim Found            As Variant
Dim varData
Dim varVal
Dim n                As Long

varData = RangeToSum.Value

For n = LBound(varData, 1) To UBound(varData, 1)
Found = Evaluate(varData(n, 1))
If Not IsError(Found) Then
varVal = Application.VLookup(Lookupvalue, Found, 2, False)
If Not IsError(varVal) Then
If IsNumeric(varVal) Then SumVlookups = SumVlookups + varVal
End If
End If
Next n

End Function
``````
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Out of curiosity, why are you using array strings in cells? Seems to make the use of Excel fairly pointless. ;)
Chartered Accountant
CERTIFIED EXPERT

Commented:
are you using some shorthand in there? vardata will default to variant
and vardata =  : is that shorthand for Set vardata = RangeToSum etc or maybe Let ?

so we have RangeToSum As Range which is an instance of the Range Class (i.e. an object)
and then a variable called vardata is (set to) that object; same way as I am reading about collections of all sorts being given names to make them much easier to use in code from the point of view of humans reading and writing it. that right?

code now looks like this (singles now also changed to doubles):

``````Public Function SumVlookups(ByVal Lookupvalue As Double, ByVal RangeToSum As Range) As Double

Dim ArrayStr As Range
Dim Found As Variant
Dim varData As Variant

varData = RangeToSum.Value

For Each ArrayStr In varData

Found = Evaluate(ArrayStr.Value)

If Not IsError(Application.VLookup(Lookupvalue, Found, 1, False)) Then SumVlookups = SumVlookups + Application.VLookup(Lookupvalue, Found, 2, False)

Next ArrayStr

End Function
``````

aha, varData .. that's Hungarian notation?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
"are you using some shorthand in there? vardata will default to variant "
Correct. If you don't specify a type, it is Variant.

"and vardata =  : is that shorthand for Set vardata = RangeToSum etc or maybe Let ?"

Let (which is outdated and nobody uses it anymore except in Property Let statements) - if you used Set it would fail because Value does not return an object.
Chartered Accountant
CERTIFIED EXPERT

Commented:
Out of curiosity, why are you using array strings in cells? Seems to make the use of Excel fairly pointless. ;)

too busy laughing here to reply! :-)

tell you in a minute.

ok, at least you haven't trotted out the more common "Excel is not intended to be used like that!" or "you can't do that" and so on. Dare I say very sharp.

Imagine you have two lists (database tables if you like) and you need a very simple solution to a many to many link between the two.

That would be a junction table.

These array constants are that junction table.

Why not use another sheet? Because it would kill the presentation to users (my accounting/tax clients) in daily use: next to them I am an expert, I come to EE to be kept humble. :-)

Why not use Access? Because this is for Excel/Spreadsheet users.
I have bought Access and even a PC to develop all this, but first I want to get the basics working in Excel. Walk before I run you might say.

And finally, I wrote an application that worked like this twenty years ago but I could never get the arithmetic working, only the storage of the junction data; same problem back then, a separate table would not work in the user work process - and anyway I was working in 2d and only 5,000 rows, space was crucial.

The effect is that it keeps the spreadsheet with a very simple appearance (if you understand book-keeping that is), hiding the complexity and impossible bits away in a UDF. Well... a couple of UDFs, which in VBA terms are very simple.

Anthony
Chartered Accountant
CERTIFIED EXPERT

Commented:
back to code:

your rewrite masks errors in col 2 ? I want those errors to manifest

will my For Each not work ?  edit: hum.. it gets value errors all the way through.
Chartered Accountant
CERTIFIED EXPERT

Commented:
This takes me to the next level in my learning process. Thank you.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
" Because it would kill the presentation to users"

How do you want errors in column 2 manifested, and what qualifies as an error?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
>>"ok, at least you haven't trotted out the more common "Excel is not intended to be used like that!" or "you can't do that" and so on"

I get bored saying that too many times in a day. :)

Besides this seems more like a case of "well, you can do that but you probably shouldn't"...
Chartered Accountant
CERTIFIED EXPERT

Commented:
why not?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
The age old principle of keep it simple. The entire program is built around arrays of cells, so why not use them? (goes back to my earlier question about why a separate sheet would kill the presentation, I suspect).
Chartered Accountant
CERTIFIED EXPERT

Commented:
funny you should say that, my old prog was call keep it short and simple.

here's what i added above but you beat me to it with a  reply:

" Because it would kill the presentation to users"

Sure: it means an additional table that has nothing to do with book-keeping, in Access (i.e. any database) the additional table would not be generally seen, it would be a background item, so I am applying the same idea in Excel. Exactly how to do that (the implementation)
is what I am exploring, trying to keep it very simple by hiding the complexity in one column and a UDF instead of the expected additional sheet/table. edit I should add that the column already exists in book-keeping terms, it is sometimes called the Folio column and contains the cross reference to the other ledger (i.e. another list), so where in the old days we would hand write multiple references in one "cell" this is that being created in computer/excel speak.

Using the name space would work, but don't fancy managing that.
Using external text file would work too, but this seems to be better than that because there's no additional file.
Really all I am doing is using vlookup and doing a bit of summing(index etc, but because Excel does not have a formula to do the Evaluate I have to use a macro,whether xlm (which I like) or vba. The various text manipulation solutions may attract more of my attention if I find the vba solution is ultimately too slow - or put in a switch so it only recalculates only when told.

How do you want errors in column 2 manifested, and what qualifies as an error?

The 7 Excel sheet errors, as would be expected to be seen by a user. This identifies money amounts being entered as something inappropriate in the string stored array constant in col 2.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Here's a revised function - it simply returns a #VALUE error if the relevant value in column 2 of the array is not numeric.
Chartered Accountant
CERTIFIED EXPERT

Commented:
nothing attached :-)

if we are designing our errors, anything beyond two decimal places is an error.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Minor issue!

``````Public Function SumVlookups(ByVal Lookupvalue As Double, ByVal RangeToSum As Range) As Double
Dim Found            As Variant
Dim varData
Dim varVal
Dim varValue
Dim n                As Long

varData = RangeToSum.Value

For n = LBound(varData, 1) To UBound(varData, 1)
Found = Evaluate(varData(n, 1))
If Not IsError(Found) Then
' check for match on lookup value
varVal = Application.VLookup(Lookupvalue, Found, 1, False)

If Not IsError(varVal) Then
' return relvant value from second column
varValue = Application.VLookup(Lookupvalue, Found, 2, False)
' make sure it's a number
If IsNumeric(varValue) Then
SumVlookups = SumVlookups + varValue
Else
SumVlookups = CVErr(xlErrValue)
Exit For
End If
End If
End If
Next n

End Function
``````
Chartered Accountant
CERTIFIED EXPERT

Commented:
good grief.. I have been wondering to what extent If statements consume processing time, now seems like a good time to ask?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
They consume no more processing time than any other code really and can save you time since you only process relevant lines.

I'm not really sure why that merits a 'good grief'. If all you want is short code, stick with what you had.
Chartered Accountant
CERTIFIED EXPERT

Commented:
the "good grief" is me being impressed by your mental agility that goes in to so many ifs (i'm a beginner remember, perhaps more easily impressed)

I'm running it now against 1,000 lookups in 1000 strings and it's very slow, is that to be expected?  or is my machine playing tricks on me.. it's possible.

edit: I'll reboot and see.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Ahh, the dangers of the written word... :)

Was the original much faster?
Chartered Accountant
CERTIFIED EXPERT

Commented:
zackly.
yes very much, just rebooted now and armed with sandwich, so that might help, the original recalced in a moment, like a hesitation, whereas the new one started giving me % done of the calculation. I think I should upload example of each.. just a minute or two.

ok, I can't, takes so long to recalc.

The test is {1,2;3,4;5,6} copied in to a2 to a1000

and the numbers 1 - 6 in c1 to c6, repeated in blocks of 6 all the way down to row 1000

and the UDF entered beside every number 1 - 6 using the numbers 1 - 6 as lookups all the way down again to row 1000. so that's 1000 udfs looking up 1000 string arrays constants and adding up the lookup values from each.

that is all the same as the layout in the op, just all copied down 1000 rows.

So can I load the string arrays and then apply my original process? When I tried it i got a value error - that's my second bit of code above.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Well. I just tested your original function against that same set up and it was no faster than the other version I just posted. This seems to speed it up a bit by adding a check to see if the lookup data is present before evaluating anything:
``````Public Function SumVlookups(ByVal Lookupvalue As Double, ByVal RangeToSum As Range) As Double
Dim Found            As Variant
Dim varData
Dim varVal
Dim varValue
Dim n                As Long

varData = RangeToSum.Value

For n = LBound(varData, 1) To UBound(varData, 1)
If InStr(varData(n, 1), Lookupvalue & ",") > 0 Then
Found = Evaluate(varData(n, 1))
If Not IsError(Found) Then
' check for match on lookup value
varVal = Application.VLookup(Lookupvalue, Found, 1, False)

If Not IsError(varVal) Then
' return relvant value from second column
varValue = Application.VLookup(Lookupvalue, Found, 2, False)
' make sure it's a number
If IsNumeric(varValue) Then
SumVlookups = SumVlookups + varValue
Else
SumVlookups = CVErr(xlErrValue)
Exit For
End If
End If
End If
End If
Next n

End Function
``````
Chartered Accountant
CERTIFIED EXPERT

Commented: