VBA Evaluate Method 255 character limit

[DocAElstein]

Re: VBA Evaluate Function 255 character limit

. Hi,
. There are many Threads on the above theme and I have done my best to review them all. Apologies if I have missed any relevant to the particular problem I am having…

. I am aware of the string argument limit of 255 for the VBA Evaluate Function. I wrote codes similar to this shortened example as part of answering some other threads yesterday…

…. ( The code uses an “Evaluate Range one – liner” to concatenate the text in the rows of a single column ( Column A here ) into one cell ( cell C6 here ). In this code the actual string Argument to be used in the VBA Evaluate Function is produced and placed in a variable by a loop to eliminate having to type in manually a long argument for a large number of rows )

. For demonstration and experimenting I also write out to cell B2 the character length of the in loop produced Evaluate string argument and also write out to cell C2 that actual Evaluate string argument

. Here is a typical screenshot after running the code, here I set the number of rows to 33, as by experimenting I found 33 is about the limit for a simple character in the cells in column 1 along with a space for delimiter. Indeed the code crashes if I increase to 34, as the character length of Evaluate Argument String becomes 258.

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

1​

[/td][td]Character Count[/td][td]String[/td][/tr]

[tr][td]

2​

[/td][td]

1​

[/td][td]

250​

[/td][td]A1&" "&A2&" "&A3&" "&A4&" "&A5&" "&A6&" "&A7&" "&A8&" "&A9&" "&A10&" "&A11&" "&A12&" "&A13&" "&A14&" "&A15&" "&A16&" "&A17&" "&A18&" "&A19&" "&A20&" "&A21&" "&A22&" "&A23&" "&A24&" "&A25&" "&A26&" "&A27&" "&A28&" "&A29&" "&A30&" "&A31&" "&A32&" "&A33[/td][/tr]

[tr][td]

3​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

1​

[/td][td]

65​

[/td][td]1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1[/td][/tr]

[tr][td]

7​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Limit255[/td][/tr][/table]

( Here the code:

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=darkgreen]'[/color]
[color=blue]Sub[/color] Limit255() '
[color=blue]Dim[/color] wks [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks = ThisWorkbook.Worksheets("Limit255") [color=darkgreen]'Sheet Info[/color]
wks.Range("B2:C6").ClearContents 'Clear contents in outout Range
[color=blue]Dim[/color] lr [color=blue]As[/color] Long: [color=blue]Let[/color] lr = 33 [color=darkgreen]'[/color]
[color=blue]Dim[/color] rngE [color=blue]As[/color] Range: [color=blue]Set[/color] rngE = wks.Range("A1:A" & lr & "") 'Input Range
 
[color=darkgreen]'Build string for Evalute "One liner"[/color]
 [color=blue]Dim[/color] r [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'Variable for row[/color]
 [color=blue]Dim[/color] EvalArgStr [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'[/color]
    [color=blue]For[/color] r = 1 [color=blue]To[/color] lr - 1 'lr Step 1
    [color=blue]Let[/color] EvalArgStr = EvalArgStr & "" & wks.Range("A" & r & "").Address & "" & "&"" ""&" [color=darkgreen]'Concatenate lines with space between[/color]
    [color=blue]Next[/color] r
 [color=blue]Let[/color] EvalArgStr = EvalArgStr & "" & wks.Range("A" & lr & "").Address & "" [color=darkgreen]'Concatenate last cell value withoiut space[/color]
 [color=blue]Let[/color] EvalArgStr = Replace(EvalArgStr, "$", "") [color=darkgreen]'Remove "$" in string to shorten it a bit[/color]
 
 
[color=darkgreen]'Check sizes of Evaluate string argument[/color]
[color=blue]Let[/color] wks.Range("C2").Value = EvalArgStr [color=darkgreen]'Output Evaluate Argument String[/color]
[color=blue]Let[/color] wks.Range("B2").Value = Len(EvalArgStr) [color=darkgreen]'Output character length of Evaluate Argument String[/color]
 
[color=darkgreen]'Check size of the evaluated String[/color]
[color=blue]Let[/color] wks.Range("C6").Value = Evaluate(EvalArgStr) [color=darkgreen]'Output results of "Evaluate Range one - liner" to Cell C6[/color]
[color=blue]Let[/color] wks.Range("B6").Value = Len(wks.Range("C6").Value) [color=darkgreen]'Output character length in cell C6[/color]
    [color=darkgreen]'Let wks.Range("B6").Value = Len(Evaluate(EvalArgStr))[/color]
 
[color=blue]End[/color] [color=blue]Sub[/color] [color=darkgreen]'Limit255[/color]

….
. The results above I expected.

…..
. However, if I increase the characters in the cells in column A, say by increasing to about 6 – 7 characters, then I get this result.

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

1234567​

[/td][td]Character Count[/td][td]String[/td][/tr]

[tr][td]

2​

[/td][td]

1234567​

[/td][td]

250​

[/td][td]A1&" "&A2&" "&A3&" "&A4&" "&A5&" "&A6&" "&A7&" "&A8&" "&A9&" "&A10&" "&A11&" "&A12&" "&A13&" "&A14&" "&A15&" "&A16&" "&A17&" "&A18&" "&A19&" "&A20&" "&A21&" "&A22&" "&A23&" "&A24&" "&A25&" "&A26&" "&A27&" "&A28&" "&A29&" "&A30&" "&A31&" "&A32&" "&A33[/td][/tr]

[tr][td]

3​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

1234567​

[/td][td]

255​

[/td][td]1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 1234567 123456 123456 123456 123456 123456 123456 123456 123456[/td][/tr]

[tr][td]

7​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

18​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

19​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

20​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

21​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

22​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

23​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

24​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

25​

[/td][td]

1234567​

[/td][td][/td][td][/td][/tr]

[tr][td]

26​

[/td][td]

123456​

[/td][td][/td][td][/td][/tr]

[tr][td]

27​

[/td][td]

123456​

[/td][td][/td][td][/td][/tr]

[tr][td]

28​

[/td][td]

123456​

[/td][td][/td][td][/td][/tr]

[tr][td]

29​

[/td][td]

123456​

[/td][td][/td][td][/td][/tr]

[tr][td]

30​

[/td][td]

123456​

[/td][td][/td][td][/td][/tr]

[tr][td]

31​

[/td][td]

123456​

[/td][td][/td][td][/td][/tr]

[tr][td]

32​

[/td][td]

123456​

[/td][td][/td][td][/td][/tr]

[tr][td]

33​

[/td][td]

123456​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Limit255[/td][/tr][/table]


.. increasing further by another character, crashes at the “Evaluate One liner” when the evaluated string exceeds 255 characters.

. So, finally the question:-

. Is this telling me that , not only is
. a) the string argument within the evaluate function limited to 255 characters, but that also
. b) a string evaluated by the Evaluate Function is limited to 255 characters.
. I am not really a computer person. Maybe someone in the know could try to explain in simple English ( or German ! ) what is actually going on here and giving me this limit. Possibly due to how VBA is working .a) and .b) are one and the same thing……?????

Many Thanks
Alan Elston
Bavaria.



P.s. I upload the file I am using for these experiments in case it helps:
https://app.box.com/s/h6lyxjmh84det1j15rhgxz01ectwjyma

 

[Andrew Poulsom]

Yes, those are the limits.

 

[Rick Rothstein]

To put it simply, the argument to the Evaluate function is the text string IT sees (that is, the text string that results if you print out the argument, everything between the parentheses, in the Immediate Window), not the text string YOU see, and it is that text string that has the 255 character limit.

 

[circledchicken]

To put it simply, the argument to the Evaluate function is the text string IT sees (that is, the text string that results if you print out the argument, everything between the parentheses, in the Immediate Window), not the text string YOU see, and it is that text string that has the 255 character limit.

Hi Rick,

I think as per DocAElstein's example, the limit also applies to the output string.

 

[Rick Rothstein]

Hi Rick,

I think as per DocAElstein's example, the limit also applies to the output string.

Ah, I guess I misread his intent... yes, there is a 255 character limit to the output to a single cell as well. That is easy to demonstrated with this line of code (execute it in the Immediate Window)...

Range("A1").Value = Evaluate("REPT(""X"",255)")

The above outputs 255 X's to cell A1... change the 255 to 256 and the output becomes a #VALUE! error instead.

 

[DocAElstein]

Yes, those are the limits.

O.K. , Thanks .

Alan

( Maybe something to consider to suggest in that Thread
http://www.mrexcel.com/forum/lounge-v-2-0/695674-what-i-would-like-see-excel-2016-a.html
… although personally I think Excel is complicated enough and difficult to keep up with. – so many thanks again for sharing your knowledge!!! )

 

[DocAElstein]

Hi Rick,

I think as per DocAElstein's example, the limit also applies to the output string.

Hi circledchicken,
yep, I did mean that, and as per A Poulsom's answer Post #2 and Rick’s in Post # 5

…..yes, there is a 255 character limit to the output to a single cell as well. …..

….It seems it does,

… It’s very helpful all the Profi Confirmation and input.
Thanks
Alan

 

[DocAElstein]

To put it simply, the argument to the Evaluate function is the text string IT sees (that is, the text string that results if you print out the argument, everything between the parentheses, in the Immediate Window), .........

Hi Rick...

.... And that is then also what my code wrote out to cell B2 : - the character length of the in loop produced Evaluate string argument ( which I then and also wrote out to cell C2 in my screen shots in Post #1?

Alan.

 

[DocAElstein]

Ah, I guess I misread his intent... yes, there is a 255 character limit to the output to a single cell as well. That is easy to demonstrated with this line of code (execute it in the Immediate Window)...

Range("A1").Value = Evaluate("REPT(""X"",255)")

The above outputs 255 X's to cell A1... change the 255 to 256 and the output becomes a #VALUE! error instead.

Thanks Rick,
. That is a nice compacter way then mine to demonstrate this additional 255 limit ( the 255 character limit to the output to a single cell of the Evaluate Function / Method ).
. I had not googled confirmation of this particular limit, so thanks again for all your inputs..
Alan