VBA Evaluate Range and VLOOKUP

[DocAElstein]

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
	A	B	C	D
1	Produnt
2	Name
3	Chocolate-europe aroma	4		Choc
4	Chocolate-Cookies	0		Choc
5	Banana-Chocolate-Split	10		Bana
6	Limette-Ksekuchen	16		Lime
7	Erdbeere-Quark	8		Erdb
8	Erdbeere-Mix	0		Erdb
9	Jamaica Sun	6		Jama
10	Waldbeeren	0		Wald
11
12
13
14	LOOKUP Table
15	Product Name
16	Haselnu-Walnu-aromatisiert
17	Tiramisu		2
18	Chocolate-colonial blend
19	Chocolate-europe aroma		4
20	Chocolate-Cookies
21	Jamaica Sun		6
22	Himbeere-Joghurt
23	Erdbeere-Quark		8
24	Erdbeere-Mix
25	Banana-Chocolate-Split		10
26	Waldbeeren
27	Kirsche		12
28	Kirsche-grner Apfel
29	Kirsche-Ananas		14
30	Stracciatella
31	Limette-Ksekuchen		16
32	grner Apfel-Quark
33	Blutorange-Quark
Sheet1

Cell Formulas
Range	Formula
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

[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
	A	B	C	D	E
1	Produnt
2	Name
3	Chocolate-europe aroma	4		Choc	Choc
4	Chocolate-Cookies	0		Choc	Choc
5	Banana-Chocolate-Split	10		Bana	Bana
6	Limette-Ksekuchen	16		Lime	Lime
7	Erdbeere-Quark	8		Erdb	Erdb
8	Erdbeere-Mix	0		Erdb	Erdb
9	Jamaica Sun	6		Jama	Jama
10	Waldbeeren	0		Wald	Wald
11
12
Sheet1

Which again is wot I expect.


Now I apply this 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
	A	B	C	D	E
1	Produnt
2	Name
3	Chocolate-europe aroma	4	4	Choc	Choc
4	Chocolate-Cookies	0	4	Choc	Choc
5	Banana-Chocolate-Split	10	4	Bana	Bana
6	Limette-Ksekuchen	16	4	Lime	Lime
7	Erdbeere-Quark	8	4	Erdb	Erdb
8	Erdbeere-Mix	0	4	Erdb	Erdb
9	Jamaica Sun	6	4	Jama	Jama
10	Waldbeeren	0	4	Wald	Wald
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

 

[DocAElstein]

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

[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

 

[RoryA]

I confess I stopped reading this a long way back, but why is my function Pubic??

 

[shg]

Curious. One would think those should be Privates.

 

[RoryA]

Quite. Or perhaps because it's close to being the dog's b*******s? (does that translate across the water?)

 

[shg]

I'm going to need help with that. But be careful you don't get thrown out on your asterisks.

 

[RoryA]

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)

 

[shg]

Ah. "Because he can."

 

[DocAElstein]

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