• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Populating a VBA array via evaluating a formula

jeffreyweir

jeffreyweir

Active Member
Dear Seniors (tehehehe)

Say we have this in B16:

"Dear Seniors"

Is there any way we can call this from VBA:

=MID(B16,ROW(OFFSET(A1,,,LEN(B16))),1)

...in a way that populates a VBA array with this:

{"D";"e";"a";"r";" ";"S";"e";"n";"i";"o";"r";"s"}


If I try var = [MID(B16,ROW(OFFSET(A1,,,LEN(B16))),1)] then I just get a var with "D" in it.


Note that var = [ROW(OFFSET(A1,,,LEN(B16)))] populates an array to var, but put this in a MID function and you only get the first element.
 
Hi Jeff


I did this long hand with each element of the array populated ar(0) = D, Ar(1) = ";" ar(2) = "e"


Which was a bit long winded but I suspect that is the way you want it. I rested on the following which pushes the data into the variant;


ar = {"D;e;a;r; ;S;e;n;i;o;r;s"}


Not sure if that is what you want but here it is.

[pre]
Code: 
Sub AddSC()
Dim str As String
Dim ar As Variant

str = StrConv([B16], vbUnicode)
ar = Replace(Left(str, Len(str) - 1), vbNullChar, ";")
End Sub
[/pre]

In my attempts I did have the same problem of only the first element showing up so if you have not cracked it maybe the above will assist in some way. The above does not have the space character " " in between words. That may be one of the deal breakers.


Anyways take care


Smallman
 
Hi Jeff,


Yes, for example you can wrap it in
Code: 
TRANSPOSE()


var = [TRANSPOSE(MID(B16,ROW(OFFSET(A1,,,LEN(B16))),1))]


If you're using VBA anyway, then writing a function using native VBA methods will be more flexible, more robust and probably faster than this.
 
Thanks, Smallmann and Colin.


I wonder why this needs to be wrapped in TRANSPOSE to work?


Yep, reading the formula into VBA then iterating through the string so I can write the individual letters to an array is probably faster. But var = [TRANSPOSE(MID(B16,ROW(OFFSET(A1,,,LEN(B16))),1))] is as simple as I am lazy!


Thanks again, lads.
 
Perhaps because MID itself is designed to return a single result from the array.


e.g.

Code: 
vA = [IF(MID(B16,ROW(OFFSET(A1,,,LEN(B16))),1)<>"~",MID(B16,ROW(OFFSET(A1,,,LEN(B16))),1),"")]


will also give the same result as does Colin's code which is shorter. It looks longer but it is just your formula wrapped in IF condition.
 
Hi Jeff,


I think there are 2 key points to
Code: 
TRANSPOSE():

(1) As Shrivallabha pointed out, it is a context setter for the [code]MID() function.

(2) 1D arrays in VBA are horizontal. [code]TRANSPOSE() converts the vertical row array to a horizontal one, so the output of the evaluation is a 1D array. Shrivallabha's [code]IF() alternative will return a 2D array (1 column by 11 rows). Of course you can change it to use [code]COLUMN()
instead:

[pre]vA = [IF(MID(B16,COLUMN(OFFSET(A1,,,,LEN(B16))),1)<>"~",MID(B16,COLUMN(OFFSET(A1,,,,LEN(B16))),1),"")][/code][/pre]
As I mentioned earlier, for me this is largely academic. Range.Value2[/code] to get the string then pass it into a function which uses StrConv()[/code] and Split()[/code] to get the character array would be the first route I'd explore. Comparing the two might make for a good blog post... :)
 
I am sure the blog post will be interesting as there are few cases where I have seen user opting for Native Excel function in VBA via Worksheetfunction. / application. route.


I have not used StrConv before. So I am interested to know how you would do it. Here's what I worked out:

[pre]
Code: 
Option Explicit
Sub Test()
Dim vA As Variant
Dim i As Long
vA = ReturnArray(Range("A1").Value)
For i = LBound(vA) To UBound(vA)
Debug.Print vA(i)
Next i
End Sub

Public Function ReturnArray(strValue As String) As Variant
ReturnArray = Split(StrConv(strValue, vbUnicode), Chr(0))
End Function
[/pre]

Obviously it can be used as direct one liner.
 
That looks pretty good. :)


One thing I picked up on is you need to strip the last character off the unicode string before you split it. Here's my first effort which has a few other small tweaks:

[pre]
Code: 
Sub Test2()

Dim strCharacters() As String

strCharacters = CharArr(Range("A2").Value2)

End Sub

Public Function CharArr(ByRef strValue As String) As String()

Dim strUnicode As String

If LenB(strValue) > 0 Then
strUnicode = StrConv(strValue, vbUnicode)
CharArr = Split(Left$(strUnicode, Len(strUnicode) - 1), vbNullChar)
End If

End Function
[/pre]
 
Thanks for that tip. Much appreciated.


Is there any specific reason for going to Value2 than typical Value ? I have read an article on Value2 but couldn't make out the reason here.


Wonder where is StrConv used? Someone somewhere must be finding good use of it. For normal case conversions, I have used Lcase and the likes so never really had to resort to this one. Of course now I know at least one usage ;).
 
Thanks Colin


I should have used Split rather than Replace, apply the change and we have lift off. Thanks mate.

[pre]
Code: 
Sub AddSC2()
Dim str As String
Dim ar As Variant

str = StrConv([B16], vbUnicode)
ar = Split(Left(str, Len(str) - 1), vbNullChar)
End Sub
[/pre]

Smallman
 
Hi Narayan,


I think that was the article I read some time back. FastExcel [Charles Williams] write ups are very good and assiduously built. His comments on formula volatility are really good and I try to read them once in a while.


If you look at his conclusions then one of them says:

.Value2 is faster than .value with numbers (no significant difference with text)
Click to expand...

And since we are dealing with texts I could not make out the reason so said it in my reply. It could be that Colin uses .Value2 normally like I use .Value :). So asked again [which comes to me more naturally than answering ;)].
 
Hi Shrivallabha,


You're on the money again. :)


In my work, I always use
Code: 
Value2 rather than [code]Value, unless I am concerned about date/currency. Maybe there are some other edge cases when I would prefer [code]Value but I can't think of any off the top of my head. As noted, [code]Value2 is faster than Value
with numbers.


When I post online, I'm inconsistent with which one I use. People sometimes get confused by Value2[/code] so I tend to post with Value[/code] unless I think the original poster has enough VBA know-how not be confused by Value2[/code].
 
You must log in or register to reply here.
Back
Top