In my last post, I created an array from formula text by using VBA’s Evaluate method, in order to roll my own FormulaArray function that displays the array returned by a formula, for documentation purposes.
In the course of this, I’ve discovered something a bit weird about how this method evaluates the arrays returned by a MID function.
Let’s use this snippet:
Sub EvaluateThis()
Dim var As Variant
var = ActiveSheet.Evaluate(ActiveCell.Formula)
End Sub
First, let’s look at how it handles an array generated by the COLUMN() function:
Now watch what happens when we use that array to split apart a string using the MID() function:
So if you push F9, you get an array, but if you use the Evaluate method you don’t…you just get the first letter. Is this weird, or am I missing the point?
@Jeff This is quite common. F9 and evaluate formula feature don’t produce the same results in most array formulas.
Also the evaluate formula feature In some cases you also see different results when you array enter as compared to when you don’t.
The desired result with:
sn = Evaluate("Index(" & Mid(Cells(19, 1).Formula, 2) & ",)")@sam: ahh…I see now that other formulas are indeed affected also. Cheers for that.
@snb: great workaround. Thanks!
For illustration purposes only:
Sub M_snb()sn = [index(mid("Atomize me",column(1:10),1),)]
MsgBox Join(sn), , UBound(sn)
sn = [index(mid("Atomize me",transpose(row(1:10)),1),)]
MsgBox Join(sn), , UBound(sn)
sn = [transpose(mid("Atomize me",row(1:10),1))]
MsgBox Join(sn), , UBound(sn)
End Sub
NB. the size of the resulting array when using column(1:10) is alarming.
Most of the time you can use an 'if(cond,true,false)'-construction too to return an array.
In this case I wasn't successful.
@snb,
> sn = [index(mid(“Atomize me”,column(1:10),1),)]
> NB. the size of the resulting array when using column(1:10) is alarming.
Try changing this part of your code line…
•
column(1:10)to this...
•
column(A:J)and see if the array size is more to your liking.
@snb,
By the way, we can add one more example method to your sample macro which uses native built-in VBA functions rather than calling out to Excel’s Evaluate “engine”…
Sub M_snb_extra()sn = Split(Replace(Trim(Replace(StrConv("Atomize me", vbUnicode), Chr(0), " ")), " ", " "))
MsgBox Join(sn), , UBound(sn)
End Sub
Note, though, that the upper bound is 9 now, not 10... that is because the Split function always returns a zero-based array (even if "Option Base 1" is used) whereas Evaluate always returns a one-based array (even if "Option Base 0" is used).
Here is the last code I posted using a much simpler construction for the expression assigned to the ‘sn’ variable…
Sub M_snb_extra()sn = Split(Left(StrConv("Atomize me", vbUnicode), LenB("Atomize me") - 1), Chr(0))
MsgBox Join(sn), , UBound(sn)
End Sub
Although it would be more logical to introduce a variable to put the text in so that the quoted string is not repeated twice within the same statement...
Sub M_snb_extra()Text = "Atomize me"
sn = Split(Left(StrConv(Text, vbUnicode), LenB(Text) - 1), Chr(0))
MsgBox Join(sn), , UBound(sn)
End Sub
@RR
Can you explain why column(1:10)column(A:J) ?
This thread is devoted to the method ‘evaluate’ and its peculiarities. So I restricted my illustrations to that theme.
In response to your non-evaluate alternative: why not using ?
Sub M_snb_extra()sn = Split(StrConv("Atomize me", 64), Chr(0))
MsgBox Join(sn), , UBound(sn)
End Sub
In the first sentence between column(1:10) and column(A:J) I used the operator for ‘is not equal to’.
Wordpress or whatever chose to remove that operator. < > ?
@snb,
Evaluate use Excel formula rules… it does not recognize 1:10 as 10 columns, rather, Column(1:10) is the number of columns in the full rows 1 thru 10… 16000+ such columns.
As for your non-Evaluate suggestion… there is an extra blank element at the end of the array because StrConv produces two bytes per character (for the “normal” non-Unicode characters)… the ASCII value followed by a Chr(0) character… that last trailing Chr(0) character is what produces the empty array element at the end. You may not have seen it when you responded, but I posted a third message with a simpler code line for the assignment to the ‘sn’ variable.
@RR
I hadn’t seen your last suggestion.
But I’d say:
Sub M_snb_extra()sn = Split(StrConv("Atomize me", 64), Chr(0))
MsgBox Trim(Join(sn)), , UBound(sn)
End Sub
@snb,
> But I’d say:
>
> Sub M_snb_extra()
> sn = Split(StrConv(“Atomize me”, 64), Chr(0))
> MsgBox Trim(Join(sn)), , UBound(sn)
> End Sub
That might be alright if outputting to the MessageBox were the desired outcome, but it is not… the MessageBox is there to show that the array was constructed correctly. The problem with doing the way you have shown is that you might need to remember (depending on what you plan to do with the array or it LBoune/UBound numbers) that you have to account for that extra element (even though it is the empty string) in future code you write for the program or when you revisit the code in six months for maintenance purposes. Personally, I think constructing the array so that it contains what it is intended to contain, and nothing more, is the better way to go, so my recommendation (assuming a non-Evaluate method was acceptable) would be to use method I used in the last code I posted.