load the entire range into a Variant - how?

Like so:

Dim varData
varData = RangeToSum.Value

Select allOpen in new window

then iterate that array.

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

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

Select allOpen in new window

aha, varData .. that's Hungarian notation?

"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.

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

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.

This takes me to the next level in my learning process. Thank you.

" Because it would kill the presentation to users"

May I ask why?

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

>>"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"...

why not?

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).

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"

May I ask why?


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.

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

nothing attached :-)

if we are designing our errors, anything beyond two decimal places is an error.

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)
        ' load array data
        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

Select allOpen in new window

good grief.. I have been wondering to what extent If statements consume processing time, now seems like a good time to ask?

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.

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.

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

Was the original much faster?

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.

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)
        ' load array data
        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

Select allOpen in new window

next reply is here