Solved

load the entire range into a Variant - how?

Posted on 2012-03-12
23
399 Views
Last Modified: 2012-03-12
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

Open in new window



this is a follow on from here.

Anthony
0
Comment
Question by:anthonymellorfca
  • 12
  • 11
23 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37709093
Like so:

Dim varData
varData = RangeToSum.Value

Open in new window


then iterate that array.
0
 
LVL 85

Accepted Solution

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

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37709110
Out of curiosity, why are you using array strings in cells? Seems to make the use of Excel fairly pointless. ;)
0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37709121
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

Open in new window



aha, varData .. that's Hungarian notation?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37709131
"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.
0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37709135
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
0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37709239
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.
0
 
LVL 9

Author Closing Comment

by:anthonymellorfca
ID: 37709258
This takes me to the next level in my learning process. Thank you.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37709259
" 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?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37709263
>>"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"...
0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37709390
why not?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37709429
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).
0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37709442
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37709514
Here's a revised function - it simply returns a #VALUE error if the relevant value in column 2 of the array is not numeric.
0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37709541
nothing attached :-)

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

Expert Comment

by:Rory Archibald
ID: 37709585
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

Open in new window

0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37709638
good grief.. I have been wondering to what extent If statements consume processing time, now seems like a good time to ask?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37709667
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.
0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37709694
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37709723
Ahh, the dangers of the written word... :)

Was the original much faster?
0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37709867
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37710023
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

Open in new window

0
 
LVL 9

Author Comment

by:anthonymellorfca
ID: 37710160
next reply is here
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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 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 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.

747 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

11 Experts available now in Live!

Get 1:1 Help Now