. Hallo.
Good Day to you.
.......
As a general improvement / advancement I would like to see would be to increase the “strange” limit of 255 characters in the
VBA Evaluate Method
=Evaluate( StringArgument )
, which ( I find strange ) applies both to the StringArgument and the Output string
http://www.mrexcel.com/forum/excel-...ons-evaluate-method-255-character-limit.html?
.. maybe there is some fundamental limit here and it is inappropriate for a “simple “ version Update. ??
......................
Any comments? To aid, -
. If I may a short clarification:
. If I understand correctly, the simplest explanation of the
VBA Evaluate Method is that it will act on its string argument as if it were a
Spreadsheet formula in a cell. So like
=Evaluate(“ Put in here what would be in the spreadsheet cell ” )
...
..
But it has the extra advantage over the Spreadsheet that you can include some VBA Code in that argument to help build up that string argument...
=Evaluate(“ Put in here what would be in the spreadsheet cell ” “ & Bit of VBA Code & “ ” )
.... so really powerful, or would be if not having that character limit.
.
. Wot I mean .. consider this code Sub AlanEvaluate() where I attempt to mimic the
VBA Evaluate Method. It makes a very long string formula, pastes that into the cell and then retrieves the
spreadsheet evaluated answer, ( a bit more detail to the code is given in the above Thread reference. )
. The equivalent
VBA Evaluate Method , line , at line number 120 fails due to the 255 limit ( in this example I am talking about the 255 Limit to the string actually seen by VBA in the =
Evaluate( ) code line )
Code:
Rich (BB code):
Sub AlanGotEvaluate()
10 Dim wks As Worksheet: Set wks = ThisWorkbook.Worksheets("Limit255") 'Sheet Info
20 wks.Range("B2:D6").ClearContents 'Clear contents in output Range
30 Dim lr As Long: Let lr = 34 '34 chosen to make the evalute String a bit longer than 255 Characters and cause VBA Evalute Method to fail. ( 33 Works )
40 Dim rngE As Range: Set rngE = wks.Range("A1:A" & lr & "") 'Input Range
50
60 'Build string for Evalute "One liner" which is just a bit too long for Evalute Method String Argument ( 258 )
70 Dim r As Long 'Variable for row
80 Dim EvalArgStr As String '
90 For r = 1 To lr - 1 'lr Step 1
100 Let EvalArgStr = EvalArgStr & "" & wks.Range("A" & r & "").Address & "" & "&"" ""&" 'Concatenate lines with space between
110 Next r
120 Let EvalArgStr = EvalArgStr & "" & wks.Range("A" & lr & "").Address & "" 'Concatenate last cell value without space
130 Let EvalArgStr = Replace(EvalArgStr, "$", "") 'Remove "$" in string to shorten it a bit
140 Let EvalArgStr = "=" & EvalArgStr 'Include the = so as not to rely on the Implicit Default, which makes substitution in Spreadsheet easier.
150
160 'Output size of Evaluate string argument, and Output Evaluate Argument String as Text
170 Let wks.Range("B2").Value = Len(EvalArgStr) 'Output character length of Evaluate Argument String
180 Let wks.Range("C2").Value = " " & EvalArgStr 'Output Evaluate Argument String ( Add a space so that it comes out as a Text String )
190
200 'Output result of VBA Evalute Method and Check size of the evaluated String THESE LINES WILL ERROR
210 ' Let wks.Range("C6").Value = Evaluate(EvalArgStr) 'Output results of "Evaluate Range one - liner" to Cell C6
220 ' Let wks.Range("B6").Value = Len(wks.Range("C6").Value) 'Output character length in cell C6
230 'Let wks.Range("B6").Value = Len(Evaluate(EvalArgStr))
240
250 ' "Alan Evaluate" Put string Formula into cell and retrieve Spreadsheet Evaluate result.
260 Let wks.Range("D2").Value = EvalArgStr 'Output Evaluate Argument as Formula
270 Dim AlanGotEvaluate As String: Let AlanGotEvaluate = wks.Range("D2").Value 'Retrieve Spreadsheet Evaluated Value
280 Let wks.Range("D6").Value = wks.Range("D2").Value 'Paste out Spreadsheet Evaluated Value
End Sub
Code works on this Spreadsheet
Using Excel 2007
| Row\Col | A | B | C | D |
|---|
1 | 1 | Character Count | String | Formula |
2 | 1 | | | |
3 | 1 | | | |
4 | 1 | | | |
5 | 1 | | | |
6 | 1 | | | |
7 | 1 | | | |
8 | 1 | | | |
9 | 1 | | | |
10 | 1 | | | |
11 | 1 | | | |
12 | 1 | | | |
13 | 1 | | | |
14 | 1 | | | |
15 | 1 | | | |
16 | 1 | | | |
17 | 1 | | | |
18 | 1 | | | |
19 | 1 | | | |
20 | 1 | | | |
21 | 1 | | | |
22 | 1 | | | |
23 | 1 | | | |
24 | 1 | | | |
25 | 1 | | | |
26 | 1 | | | |
27 | 1 | | | |
28 | 1 | | | |
29 | 1 | | | |
30 | 1 | | | |
31 | 1 | | | |
32 | 1 | | | |
33 | 1 | | | |
34 | 1 | | | |
35 | | | | |
.....................................
Alan..
P.s.
File with test codes in
https://app.box.com/s/i8h2llddp9nweb17oopdcy6xhr735rtu
