…….
Evaluate is limited to a formula length of ca. 255 characters. If you use the External:=True parameter you are including the full workbook name in the formula string, when all you need is the sheet name. That last is why the original didn't work - Application.Evaluate always evaluates in the context of the active sheet, so if you don't supply sheet references, your ranges all need to be on the same sheet. Worksheet.Evaluate evaluates in the context of the specified sheet whether or not it is active.
……….. one point I had very similar example files to those in this thread working….then my computer just crashed under the strain!
. It may have had something to do with the sheet referencing, - so I will bare your comments in mind……
….my main computer is repaired, so just for anyone following this thread…..
….I am assuming that I must use the External:=True parameter for trying to access an external Look Up Table? ? Any comments to this would be welcome
. That being the case, I have done some extensive tests saving the same Look Up Table File with many different names and sheet names. I share here just a summary.
. The full workbook name is certainly playing a role in getting the two Mega Formulas working.
. For example, going back to my original Example file and playing (changing) with the full File name
… This is about* the limit to get the
second Mega formula to work:
Code:
[color=darkblue]Set[/color] rngLUpValues = Workbooks("ABCDEFGHIJKLMNOPQ").Worksheets("LookUpTable").Range("$A$16:$C$33")
[color=darkblue]Set[/color] rngLUpEntries = Workbooks("ABCDEFGHIJKLMNOPQ").Worksheets("LookUpTable").Range("$A$16:$A$33")
[color=darkblue]Set[/color] rngLUpOffset = Workbooks("ABCDEFGHIJKLMNOPQ").Worksheets("LookUpTable").Range("$A$15")
. This for example will not work for the second Mega Formula:
Code:
[color=darkblue]Set[/color] rngLUpValues = Workbooks("ABCDEFGHIJKLMNOPQR").Worksheets("LookUpTable").Range("$A$16:$C$33")
[color=darkblue]Set[/color] rngLUpEntries = Workbooks("ABCDEFGHIJKLMNOPQR").Worksheets("LookUpTable").Range("$A$16:$A$33")
[color=darkblue]Set[/color] rngLUpOffset = Workbooks("ABCDEFGHIJKLMNOPQR").Worksheets("LookUpTable").Range("$A$15")
( * To tie it down to the exact last character length that will work then as expected you need to increase the length in turn of each file, as I have tediously done!, so for example this will just about work
Code:
[color=darkblue]Set[/color] rngLUpValues = Workbooks("ABCDEFGHIJKLMNOPQR").Worksheets("LookUpTable").Range("$A$16:$C$33")
[color=darkblue]Set[/color] rngLUpEntries = Workbooks("ABCDEFGHIJKLMNOPQ").Worksheets("LookUpTable").Range("$A$16:$A$33")
[color=darkblue]Set[/color] rngLUpOffset = Workbooks("ABCDEFGHIJKLMNOPQ").Worksheets("LookUpTable").Range("$A$15")
)
. Similarly, looking at changing the sheet name length:
.. This will not work for the second Mega formula.
Code:
[color=darkblue]Set[/color] rngLUpValues = Workbooks("ABCDEFGHIJKLMNOPQ").Worksheets("LookUpTableA").Range("$A$16:$C$33")
[color=darkblue]Set[/color] rngLUpEntries = Workbooks("ABCDEFGHIJKLMNOPQ").Worksheets("LookUpTableA").Range("$A$16:$A$33")
[color=darkblue]Set[/color] rngLUpOffset = Workbooks("ABCDEFGHIJKLMNOPQ").Worksheets("LookUpTableA").Range("$A$15")
.. but reducing the Workbook names appropriately results in the following working:
Code:
[color=darkblue]Set[/color] rngLUpValues = Workbooks("ABCDEFGHIJKLMNOP").Worksheets("LookUpTableA").Range("$A$16:$C$33")
[color=darkblue]Set[/color] rngLUpEntries = Workbooks("ABCDEFGHIJKLMNOP").Worksheets("LookUpTableA").Range("$A$16:$A$33")
[color=darkblue]Set[/color] rngLUpOffset = Workbooks("ABCDEFGHIJKLMNOP").Worksheets("LookUpTableA").Range("$A$15")
. I achieved eventually a similar limitation in the first mega Formula, but this was a much larger. This is probably as expected as the evaluate formula has fewer references to files.
. …………………………………………………………………………………….
…….
Evaluate is limited to a formula length of ca. 255 characters. ……..
. This possibly needs a bit more thinking about… taking the full file names that just about worked again
Code:
[color=darkblue]Set[/color] rngLUpValues = Workbooks("ABCDEFGHIJKLMNOPQR").Worksheets("LookUpTable").Range("$A$16:$C$33")
[color=darkblue]Set[/color] rngLUpEntries = Workbooks("ABCDEFGHIJKLMNOPQ").Worksheets("LookUpTable").Range("$A$16:$A$33")
[color=darkblue]Set[/color] rngLUpOffset = Workbooks("ABCDEFGHIJKLMNOPQ").Worksheets("LookUpTable").Range("$A$15")
But modifying it to give some ridiculously long strings in the evaluate string argument.. gives a code such as this…
Code:
[color=darkblue]Sub[/color] Evaluate_INDEXwithMATCHMegaMegaRoryA6()
[color=darkblue]Dim[/color] rngName [color=darkblue]As[/color] Range, rngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValues [color=darkblue]As[/color] Range, rngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntries [color=darkblue]As[/color] Range, rngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffset [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValues = Workbooks("ABCDEFGHIJKLMNOPQ").Worksheets("LookUpTable").Range("$A$16:$C$33")
[color=darkblue]Set[/color] rngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntries = Workbooks("ABCDEFGHIJKLMNOPQR").Worksheets("LookUpTable").Range("$A$16:$A$33")
[color=darkblue]Set[/color] rngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffset = Workbooks("ABCDEFGHIJKLMNOPQ").Worksheets("LookUpTable").Range("$A$15")
[color=darkblue]Set[/color] rngName = ActiveSheet.Range("$A$3:$A$10")
[color=darkblue]Dim[/color] rngJJ [color=darkblue]As[/color] Range, rngLL [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngJJ = ActiveSheet.Range("J3:J10")
[color=darkblue]Set[/color] rngLL = ActiveSheet.Range("L3:L10")
[color=darkblue]Let[/color] rngJJ = ""
[color=darkblue]Let[/color] rngLL = ""
[color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX(" & rngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValues.Address(External:=True) & ",N(IF(1,MATCH(" & rngName.Address & "," & rngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntries.Address(External:=True) & ",0))),3),)")
[color=darkblue]Let[/color] rngLL.Value = Evaluate("INDEX(INDEX(" & rngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValuesrngLUpValues.Address(External:=True) & ",N(IF(1,MMULT(N(TRANSPOSE(" & rngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntries.Address(External:=True) & ")=" & rngName.Address & "),ROW(" & rngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntriesrngLUpEntries.Address(External:=True) & ")-ROW(" & rngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffsetrngLUpOffset.Address(External:=True) & ")))),3),)")
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_INDEXwithMATCHMegaMegaRoryA6()[/color]
Which still works!! So clearly what is being talked about by the “Evaluate Formula length” is something subtle and not a simple case of adding the characters in the evaluate string argument!! (I think??)
. To further demonstrate this point…
. This “Windows” version of a code that did work does not work.
Code:
[color=darkblue]Set[/color] rngLUpValues = Windows("ABCDEFGHIJKLMNOPQ").ActiveSheet.Range("$A$16:$C$33")
[color=darkblue]Set[/color] rngLUpEntries = Windows("ABCDEFGHIJKLMNOPQ").ActiveSheet.Range("$A$16:$A$33")
[color=darkblue]Set[/color] rngLUpOffset = Windows("ABCDEFGHIJKLMNOPQ").ActiveSheet.Range("$A$15")
. In order for this to work the File names must be reduced a bit:
Code:
[color=darkblue]Set[/color] rngLUpValues = Windows("ABCDEFGHIJKLMNOP").ActiveSheet.Range("$A$16:$C$33")
[color=darkblue]Set[/color] rngLUpEntries = Windows("ABCDEFGHIJKLMNOPQ").ActiveSheet.Range("$A$16:$A$33")
[color=darkblue]Set[/color] rngLUpOffset = Windows("ABCDEFGHIJKLMNOP").ActiveSheet.Range("$A$15")
(Note: it was found that the External:=True parameter was still needed to make these codes work)
. here is One final code version, for completeness, which includes the Worksheet in the evaluate string argument to make it longer…
Code:
[color=darkblue]Sub[/color] Evaluate_INDEXwithMATCHMegaMegaRoryA7()
[color=darkblue]Dim[/color] wkstBlc [color=darkblue]As[/color] Worksheet, wkstPro [color=darkblue]As[/color] Worksheet [color=green]'Main File Worksheet, File with entries Worksheet[/color]
[color=darkblue]Dim[/color] Blanco [color=darkblue]As[/color] [color=darkblue]String[/color], LookUpTable [color=darkblue]As[/color] [color=darkblue]String[/color]
[color=darkblue]Let[/color] Blanco = "VBA Evaluate Range VLOOKUP and INDEX witm MATCH.xlsm"
[color=darkblue]Set[/color] wkstBlc = Workbooks(Blanco).Worksheets.Item(5) [color=green]'fifth sheet in main File[/color]
[color=darkblue]Let[/color] LookUpTable = "ABCDEFGHIJKLMNOPQ.xlsx"
[color=darkblue]Set[/color] wkstPro = Workbooks(LookUpTable).Worksheets("LookUpTable") [color=green]'First sheet in File with entries[/color]
[color=darkblue]Dim[/color] rngName [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngName = wkstBlc.Range("A3:A10")
[color=darkblue]Dim[/color] lLastRow [color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'[/color]
lLastRow = 10
[color=darkblue]Dim[/color] rngLL [color=darkblue]As[/color] Range, rngJJ [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngLL = wkstBlc.Range("L3:L" & lLastRow)
[color=darkblue]Set[/color] rngJJ = wkstBlc.Range("J3:J" & lLastRow)
[color=darkblue]Let[/color] rngJJ = ""
[color=darkblue]Let[/color] rngLL = ""
[color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX(" & wkstPro.Range("A16:C33").Address(External:=True) & ",N(IF(1,MATCH(" & rngName.Address & "," & wkstPro.Range("A16:A33").Address(External:=True) & ",0))),3),)")
[color=darkblue]Let[/color] rngLL.Value = Evaluate("INDEX(INDEX(" & wkstPro.Range("A16:C33").Address(External:=True) & ",N(IF(1,MMULT(N(TRANSPOSE(" & wkstPro.Range("A16:A33").Address(External:=True) & ")=" & rngName.Address & "),ROW(" & wkstPro.Range("A16:A33").Address(External:=True) & ")-ROW(" & wkstPro.Range("A15").Address(External:=True) & ")))),3),)")
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_INDEXwithMATCHMegaMegaRoryA7()[/color]
… was still found to have a similar “ABCDEFGHIJKLMNOPQ” length limitation.
…………………………………………….
Some last puzzling results:
. 1) Note that this last code does not reference the active sheet at all. It references the Look Up Values Workbook. As an experiment I changed The name and path of this workbook to something ridiculously long and there were no differences in the results in the “ABCDEFGHIJKLMNOPQ” length limitation
. 2) Another result noticed.. reducing the Full file name by saving, for example directly on a drive, rather than in various sub folders as I had, did not affect the results. (For completeness I increased the full File name of the “ABCDEFGHIJKLMNOPQ” File to something ridiculous to D:\Ab OKT 2013\YouTube Comments Kontakt GuteFrage Forums\OffenFragens\Excel Beispiel Files\EvaluateCuncatenate\ABCDEFGHIJKLMNOPQRSTUVWXYZ\ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ2 …… and the codes still worked!!)
….. So what is meant here by the “Full Workbook name” is not necessarily the Full File Name” !?
. Enough to be thinking about here for now……
Alan
...Hmm.... er... OK. - I Guess you are talking about some intimate internal workings?... and as a result of that there are 255 from the 342 "left over" as it were for wot I have available to use in the formula I am typing in. But I still have to guess or do experiments as i did to get at what the Evaluate is taking based on what I write in the String argument.... I think I sort of get it. 
