VBA Evaluate Range and VLOOKUP

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Hi,
As a beginner I may be going a bit above my head!!

After studying and participating at some considerable length in Threads and links to do with using the Evaluate function to speed things up, I thought I understood it. Here are some of those links and Threads. (www.excelfox.com/forum/f22/concatenating-balls-1891/ VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan )

. So … the following simplified example File (XL2007 .xlsm)
https://app.box.com/s/pr78mhna00advvhsrmvi
has a Spreadsheet LEFT Function and a Spreadsheet VLOOKUP Function

The results look good! (That is to say wot I expect!) :-



Book1
ABCD
1Produnt
2Name
3Chocolate-europe aroma4Choc
4Chocolate-Cookies0Choc
5Banana-Chocolate-Split10Bana
6Limette-Ksekuchen16Lime
7Erdbeere-Quark8Erdb
8Erdbeere-Mix0Erdb
9Jamaica Sun6Jama
10Waldbeeren0Wald
11
12
13
14LOOKUP Table
15Product Name
16Haselnu-Walnu-aromatisiert
17Tiramisu2
18Chocolate-colonial blend
19Chocolate-europe aroma4
20Chocolate-Cookies
21Jamaica Sun6
22Himbeere-Joghurt
23Erdbeere-Quark8
24Erdbeere-Mix
25Banana-Chocolate-Split10
26Waldbeeren
27Kirsche12
28Kirsche-grner Apfel
29Kirsche-Ananas14
30Stracciatella
31Limette-Ksekuchen16
32grner Apfel-Quark
33Blutorange-Quark
Sheet1
Cell Formulas
RangeFormula
B3=VLOOKUP(A3,$A$16:$C$33,3,FALSE)
B4=VLOOKUP(A4,$A$16:$C$33,3,FALSE)
B5=VLOOKUP(A5,$A$16:$C$33,3,FALSE)
B6=VLOOKUP(A6,$A$16:$C$33,3,FALSE)
B7=VLOOKUP(A7,$A$16:$C$33,3,FALSE)
B8=VLOOKUP(A8,$A$16:$C$33,3,FALSE)
B9=VLOOKUP(A9,$A$16:$C$33,3,FALSE)
B10=VLOOKUP(A10,$A$16:$C$33,3,FALSE)
D3=LEFT(A3,4)
D4=LEFT(A4,4)
D5=LEFT(A5,4)
D6=LEFT(A6,4)
D7=LEFT(A7,4)
D8=LEFT(A8,4)
D9=LEFT(A9,4)
D10=LEFT(A10,4)




I apply this code

Code:
[color=darkblue]Sub[/color] Evaluate_Left()[color=darkblue]Dim[/color] rngName [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
[color=darkblue]Dim[/color] rngEE [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngEE = ThisWorkbook.Worksheets("sheet1").Range("E3:E10")
[color=darkblue]Let[/color] rngEE = Evaluate("if(row(3:10),LEFT(" & rngName.Address & ",4))")
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_Left()[/color]


And get the following:-


Book1
ABCDE
1Produnt
2Name
3Chocolate-europe aroma4ChocChoc
4Chocolate-Cookies0ChocChoc
5Banana-Chocolate-Split10BanaBana
6Limette-Ksekuchen16LimeLime
7Erdbeere-Quark8ErdbErdb
8Erdbeere-Mix0ErdbErdb
9Jamaica Sun6JamaJama
10Waldbeeren0WaldWald
11
12
Sheet1


Which again is wot I expect.


Now I apply this code

Code:
[color=darkblue]Sub[/color] Evaluate_VLOOKUP()[color=darkblue]Dim[/color] rngName [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
[color=darkblue]Dim[/color] rngCC [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngCC = ThisWorkbook.Worksheets("sheet1").Range("C3:C10")
[color=darkblue]Let[/color] rngCC = Evaluate("if(row(3:10),VLOOKUP(" & rngName.Address & ",$A$16:$C$33,3,FALSE))")
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_VLOOKUP()[/color]


…but get the following:-



Book1
ABCDE
1Produnt
2Name
3Chocolate-europe aroma44ChocChoc
4Chocolate-Cookies04ChocChoc
5Banana-Chocolate-Split104BanaBana
6Limette-Ksekuchen164LimeLime
7Erdbeere-Quark84ErdbErdb
8Erdbeere-Mix04ErdbErdb
9Jamaica Sun64JamaJama
10Waldbeeren04WaldWald
11
12
Sheet1


. I would have expected that result without The extra If Row() stuff, which I thought overcame this problem.
. can anyone suggest wot is going wrong, or how I obtain the correct results (Using the Evaluate Function for a range)
Thanks
Alan.

P.s. I will also post this Thread Here: Multiple Columns Into Single Column Using Data Text To Column - Page 2
 
...…. Just noticed in parsing while working in a similar Thread
Range Dimensioning, Range and Value Referencing and Referring to Arrays - Page 3
. ….. that I did not include in the last post here post # 90 the Public Function for returning URLs From a range of Hyperlinks. ( This is required for codes HG7_EvaluateRoary and HG_8_RoaryDirectWorksheetFunction ) from the last Post here # 90
. So just for completeness:

Code (Public Function)

Code:
[color=blue]Public[/color] [color=blue]Function[/color] GetURLRoaryA([color=blue]ByVal[/color] cell [color=blue]As[/color] Range, [color=blue]Optional[/color] [color=blue]ByVal[/color] default_value [color=blue]As[/color] [color=blue]Variant[/color]) [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'When an Array is assigned through Let to this function, it returns an Array which then through effectively A "Let One Liner" becomes a modified Range based on the Supplied range and any Additional Arguments. It can be thought as a normal Function working on a input Range. A Particular characteristic here is that the Output is created in a loop which specifically assigns each cell within the range. This probably ensures that VBA in any further workings "Know" or "allows" for an Array and so for example ensures that this Function can be used in Evaluate Function "One liners" without the usual "coercing stuff". I think variant is the only type of Function capable of returning an Array. By val use the value held in the value within the sub (Function) , rather than referencing the allocated source "bucket" of the variable. So any given value to the Variable outside the Sub (Function) are not changed[/color]
[color=lightgreen]'     'Lists the Hyperlink Addresses for a given range[/color]
[color=lightgreen]'     'If cell does not contain a hyperlink, return default_value[/color]
[color=lightgreen]'                                    Dim rCell As Range'...." started out with a simpler version and then decided to redo it to handle ranges with more than one row/column but forgot to remove the variable declaration."[/color]
    [color=blue]Dim[/color] vOut() [color=blue]As[/color] [color=blue]Variant[/color]
    [color=blue]Dim[/color] x [color=blue]As[/color] Long: [color=blue]Dim[/color] y [color=blue]As[/color] Long [color=lightgreen]'We build a collection of output by looping into an Array. So these variables will be used for both Row,Column indicies for the cell Range coming into the [color=blue]Function[/color], as well as the Array Indicies. Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.[/color]
    [color=blue]If[/color] IsMissing(default_value) [color=blue]Then[/color] default_value = vbNullString [color=lightgreen]'I guess this is important to give it an empty cell rather than not yet anythng, as not yet anything may cause strange errors somewhere[/color]
 
    [color=blue]If[/color] cell.Count = 1 [color=blue]Then[/color] [color=lightgreen]'This IF bit has the effect of getting the function just to return a single string in the unique case of only one cell. It will work as well without this extra IF bit. But it is probably just good practice to avoid unecerssary looping ang making of an Array for this simplist case.[/color]
        [color=blue]If[/color] cell.HyperLinks.Count > 0 [color=blue]Then[/color]
            GetURLRoaryA = cell.HyperLinks(1).Address [color=lightgreen]'The 1 in ( ) is just to get the syntax right. It can only be 1 for 1 cell. But for a given range of n cells it could be 1 to n[/color]
                [color=lightgreen]'GetURLRoaryA = GetURLstr(cell(y, x)) 'Extra Possibility, GetURLstr(Onecell) is a simple function simply doing Hyperlinks(1).Address for the given 1 cell[/color]
        [color=blue]Else[/color]
            GetURLRoaryA = default_value [color=lightgreen]'Something to return for No Hyperlink in the (one in thisd case) cell..[/color]
        [color=blue]End[/color] [color=blue]If[/color]
    [color=blue]Else[/color] [color=lightgreen]'For the case of more more than one cell we effectively loop throught each cell and put the required Hyperlink in the appropriate place in the (Re)Dimensioned Array[/color]
        [color=blue]ReDim[/color] vOut(1 [color=blue]To[/color] cell.Rows.Count, 1 [color=blue]To[/color] cell.Columns.Count) [color=lightgreen]'We must give our Array a size, or we cannot add to parts of it. The ReDim rather than Dim is used as Dim only accepts Numbers, not variables as ReDim does.[/color]
        [color=blue]For[/color] y = 1 [color=blue]To[/color] cell.Rows.Count [color=lightgreen]'Using for fun the convention of cell numbers in a spreadsheet..start in the first row..[/color]
            [color=blue]For[/color] x = 1 [color=blue]To[/color] cell.Columns.Count [color=lightgreen]'.... go along the .....[/color]
                [color=blue]If[/color] cell(y, x).HyperLinks.Count > 0 [color=blue]Then[/color] [color=lightgreen]'Always check / allow for no Hyperlink or the next line could error..[/color]
                    vOut(y, x) = cell(y, x).HyperLinks(1).Address [color=lightgreen]'Worth remembering the extra Thing(y, x).Stuff rather than Thing and /n or just Stuff.. a taypical mistake, (ar least by me!!)[/color]
                        [color=lightgreen]'vOut(y, x) = GetURLstr(cell(y, x)) 'Extra Possibility, GetURLstr(Onecell) is a simple function simply doing Hyperlinks(1).Address for the given 1 cell[/color]
                [color=blue]Else[/color]
                    vOut(y, x) = default_value [color=lightgreen]'Something to return for No Hyperlink in cell(y, x)..[/color]
                [color=blue]End[/color] [color=blue]If[/color]
            [color=blue]Next[/color] x [color=lightgreen]'....coulmns in current row....[/color]
        [color=blue]Next[/color] y [color=lightgreen]'....then go to next row down ..and start going along... etc..[/color]
        GetURLRoaryA = vOut [color=lightgreen]'At This point GetURLRoaryA becomes an Array or Rather an Object with a collection of values. By Virtue of a typical let Rng.Value = RoaryLeftPubic(Rng ,   ____) the range will be filled  with the values from the Array. This would be the normal one line allowed exclisively for puutting just values in this given Rng[/color]
 
    [color=blue]End[/color] [color=blue]If[/color]
[color=blue]End[/color] [color=blue]Function[/color] [color=lightgreen]'GetURLRoaryA[/color]
    [color=blue]Public[/color] Function GetURLstr([color=blue]ByVal[/color] Hyplinkcell [color=blue]As[/color] Range) '. By val uses the value held in the value within the sub (Function) , rather than referencing the allocated source "bucket" of the variable. So any given value to the Variable outside the Sub ([color=blue]Function[/color]) are not changed
     [color=lightgreen]'Lists the Hyperlink Address for one Given Cell[/color]
              GetURLstr = Hyplinkcell.HyperLinks(1).Address [color=lightgreen]'The 1 in ( ) is just to get the syntax right. It can only be 1 for 1 cell. But for a given range of n cells it could be 1 to n[/color]
 
    End Function
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
I confess I stopped reading this a long way back, but why is my function Pubic??
 
Upvote 0
Curious. One would think those should be Privates.
 
Upvote 0
Quite. :) Or perhaps because it's close to being the dog's b*******s? (does that translate across the water?)
 
Upvote 0
I'm going to need help with that. But be careful you don't get thrown out on your asterisks.
 
Upvote 0
It's also known as the mutt's nuts? a.k.a. the best thing since sliced bread (important not to confuse these two metaphors)
 
Last edited:
Upvote 0
I confess I stopped reading this a long way back, but why is my function Pubic??

. Apologies for any Typos. Privates and Public etc. No offence intended. But your explanations are appropriate.
. I confess I almost stopped reading this Thread myself a long way back
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top