VBA Evaluate Method 255 character limit

DocAElstein

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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:

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
 
Last edited:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
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.
 
Upvote 0
Rick Rothstein said:
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.
Click to expand...
Hi Rick,

I think as per DocAElstein's example, the limit also applies to the output string.
 
Upvote 0
circledchicken said:
Hi Rick,

I think as per DocAElstein's example, the limit also applies to the output string.
Click to expand...
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.
 
Last edited:
Upvote 0
circledchicken said:
Hi Rick,

I think as per DocAElstein's example, the limit also applies to the output string.
Click to expand...
Hi circledchicken,
yep, I did mean that, and as per A Poulsom's answer Post #2 and Rick’s in Post # 5
Rick Rothstein said:
…..yes, there is a 255 character limit to the output to a single cell as well. …..
Click to expand...
….It seems it does,

… It’s very helpful all the Profi Confirmation and input.
Thanks
Alan
 
Upvote 0
Rick Rothstein said:
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), .........
Click to expand...

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.
 
Upvote 0
Rick Rothstein said:
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.
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Excel VBA Module Name Length Limit
Replies
6
Views
767
fred3
F
A
  • Solved
VBA Email Hyperlink to Workaround 255 Character Limit, With Conditions
2
Replies
12
Views
2K
FormR
FormR
zookeepertx
  • Solved
Trying to decipher a short bit of code in a macro
Replies
7
Views
613
Alex Blakenburg
A
P
  • Question Question
VBA to perform Text Diff (but with >2500 Char per cell)
Replies
0
Views
239
potatoooooooo
P
M
  • Solved
VBA Evaluate UDF that can process range of cells/array
Replies
5
Views
1K
lrobbo314
lrobbo314

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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