Question - Constant to Array function

[Jaafar Tribak]

Hi dear forum members.

Consider the following:

Const MyConst = "A" & "B" & "C" & "D"

Sub Test()
    Dim Myarray() As Variant
    
    Myarray = Array(MyConst)
    Debug.Print Myarray(0); Myarray(1); Myarray(2); Myarray(3)
End Sub

Of course, the above code won't work but you get the idea ... What I am looking for is to be able to populate the array MyArray with the 4 letters A,B,C,D based on the Const MyConst just like you woud using the following:

Myarray = Array("A", "B", "C", "D")

I have tried a few string manipulations/Evaluate etc ... but with no sucess... Does anybody have an idea how/if this could be done ?

Thank you.

 

[RoryA]

Use Split?

Const MyConst = "A,B,C,D"
myArray = Split(MyConst, ",")

 

[Jaafar Tribak]

Use Split?

Const MyConst = "A,B,C,D"
myArray = Split(MyConst, ",")

That was nice and simple - How could I have missed that ! lol
I spent an hour trying all kinds of string manimpulations but never thought of using Split.
Thanks RoryA.

 

[RoryA]

Glad to help. It was probably the lack of API calls that threw you off...

 

[Jaafar Tribak]

Glad to help. It was probably the lack of API calls that threw you off...

 

[Norie]

You could also do it like this.

Const MyConst = "ABCD"
Dim MyConstUni As Variant
Dim MyArray As Variant

    MyConstUni = StrConv(MyConst, vbUnicode)
    
    MyArray = Split(Left(MyConstUni, Len(MyConstUni) - 1), Chr(0))

 

[Jaafar Tribak]

You could also do it like this.

Const MyConst = "ABCD"
Dim MyConstUni As Variant
Dim MyArray As Variant

    MyConstUni = StrConv(MyConst, vbUnicode)
    
    MyArray = Split(Left(MyConstUni, Len(MyConstUni) - 1), Chr(0))

That also worked ... Thank you Norie.