Solved: Parsing string elements in an array

[Digita]

Hi,

I have this code which returns each string element in an array:

Sub Test()
Dim myArray() As Variant
myArray = Array("a", "b", "c")
For Each i In myArray
Debug.Print i
Next
End Sub

Now, if I have 'a,b,c' (no separating space and without the double inverted commas), my amended code below includes a couple of extra lines to insert the inverted commas:

Sub Test2()
Dim instring As String
Dim myArray() As Variant
instring = "a,b,c"
instring = """" & WorksheetFunction.Substitute(instring, ",", """, """) & """"
myArray = Array(instring)
For Each i In myArray
Debug.Print i
Next
End Sub

But the output is not what I expect. Any idea why?

Thanks & regards


kp

[GTO]

What output did you expect?

[Digita]

The output I want is similar to the 1st code. In the immediate window the first code shows:

a
b
c (on 3 separate lines)

The second code shows "a", "b", "c" on the same line.

[GTO]

Hi Digita,

I (unfortunately) have to wake up in but a few hours, and am a horrendously slow typist, so my rather sorrowful explanation is:

'Array()' is a function that basically "builds" an array, of single dimension, and x elements (however many elements you specify by inclusion) for you.

Thus - 'MyArray = Array("A","B","C")' results in a three element array; whereas:

strVar = (a string with however many commas or quote marks)
'MyArray = Array(strVar)' results in a one element array containing the string.

There are many here who could explain this in a far more articulate fashion (xld, where are you?), but hopefully that made sense.

Have a great night or day as the case may be,

Mark

[Digita]

Hi Mark.

Now I understand. I would have to split the string to break it down into each character and load it back to form a 3 element array.

Thank you so much.

Regards


kp

[Slyboots]

Digita,

The most efficient way to convert a string into an array is using the Split function. Here's a sample routine:

Sub DoSplit()
myString = "a,b,c"
myArray = Split(myString, ",")
MsgBox myArray(0)
End Sub

Note that Split always returns a zero-based array.

Cheers,

Stan


Hi Mark.

Now I understand. I would have to split the string to break it down into each character and load it back to form a 3 element array.

Thank you so much.

Regards


kp

[mikerickson]

Your use of WorksheetFunction.Substitute instead of VB's Replace suggest you may be on a Mac.
If so Split is not supported. It is well worth it to put a Split emulator in your Personal Macro Workbook. (A Join emulator is also useful)

[Digita]

Hi guys,

Thank you all for your comments. Your ideas really help me in my project. Really appreciate your input.

Have a great evening.

Best regards


kp