VBA Evaluate Method: “full “ Evaluate(“__”)(1) “works” ; “shorthand” [__](1) doesn't.
Hi
I am simplifying some long code lines by replacing the “full” Evaluate Command
Evaluate(“__”)
with its “shorthand” Command
[__]
Mostly this is going fine. Evem with some very complicated and long code line sections. But I hit a problem when a ( 1 Dimensional ) Array is returned and I try to “pick” of one element.
THE ISSUE HERE is getting here “shorthand” Command to work in the same way that the “full” Command does. ( I am not interested at this stage in alternative ways to get the required ( column here ) information, thanks ! )
_...................................
Consider a simple Demo.
I have a simple Range
Using Excel 2007 32 bit
Row\Col |
B |
C |
4 |
16 |
26 |
5 |
18 |
28 |
6 |
20 |
30 |
I am using the “full” Evaluate Command in various code lines to get at things like the start column, column count , the indicies, etc.. of the Range.
I also show the attempts at replacing some of the "Full" Evaluate command parts with the "shorthand" Evaluate equvalent part..., - ( At least I thought to was was equivalent, in the way I am applying it, at least )
Line 11 in the following code is refusing to compile. This is my main problem currently.
The error returned roughly translated to English is:
Complie Error:
“Wrong number of arguments or invalid assignment to a Property”
_........................................
_ I can see no reason why this will not work
11 ______ [column(B4:C6)](1)
When this does:
10 ______ Evaluate("column(B4:C6)")(1)
_.......................
_ It is further puzzling, as my workaround clearly works
13 Dim vTemp As Variant
14 Let vTemp = [column(B4:C6)]_____' Returns 1 Dimensional Array {2, 3}
15 Let Cs = vTemp(1)______________' Returns first Array Element, = 2
Unfortunately this workaround is not of use to me as I need / want to replace the “Full” bit with the “shorthand” bit in the same line.
_.........
Can anyone spread any light on this, seeming, Anomaly... ( I am currently successfully substituting the “working” “shorthands” into some very complex and long code lines with no problem. Just this one Problem is holding me back )
Thank you
Alan
Bookmarks