Convert string array to variant array, or "Array("111", "222")" to Array("111", "222")

[Rowland Hamilton]

Folks:
I used a method to create an array that is actually a string, and captured account numbers (text values) like this:

strArray = "Array("111", "222", "333")"
[End CODE]

But I want to convert it to a variant like:
[CODE]
vntArray = Array("111", "222", "333")
[End CODE]

so that I can use LBound and UBound code on the array.

How do I do this?

Originally, I tried to use the string, but after getting runtime error type 13 mismatch, I manually copied the debug.print of the array, and redefined it without the leading and trailing quotes, and stopped getting the error.

Also, how do you widen the code windows when you post?

Thanks, Rowland

 

[Rowland Hamilton]

To clarify, I need to remove the leading and trailing quotation marks. - Thank you.

 

[AlphaFrog]

Use the Left, Mid, Right functions to parse the string to something like "111", "222", "333". Then use the Split function with the comma as the delimiter to convert the string into an array.

 

[Rowland Hamilton]

working with dynamically created array, so won't be able to parse that way.

 

[Rick Rothstein]

I used a method to create an array that is actually a string, and captured account numbers (text values) like this:

strArray = [B][COLOR=#a52a2a]"Array("111", "222", "333")"[/COLOR][/B]
[End CODE]
But I want to convert it to a variant like:
[CODE]
vntArray = Array("111", "222", "333")
[End CODE]
[/QUOTE]
Instead of capturing the account numbers in the format you showed us (highlighted in red), can you capture them in this format instead?

strValue = "111,222,333"

Note there are [B]no spaces[/B] after the commas to "neaten" the display. If so, then you can create your variant array this way...

vntArray = Split(strValue, ",")

Note that the array produced by the Split function is [B]always [/B]a zero-based array no matter what the Option Base setting is.

 

[Rowland Hamilton]

Thanks, my original capture method used:

'Create Beginning Segment Of Array
strArray = "Array("

'Format Text In Each Cell To Add As Array Element
For intLoop = 1 To i_CountAccounts

    strArray = strArray & Chr(34) & Range("A" & intLoop).Value & Chr(34) & ", "
Next intLoop

'Add Closing Parenthesis
strArray = strArray & ")"

'Remove Superfluous Comma And Space From Last Entry
strArray = Replace(strArray, ", )", ")")

'Print Array to Immediate window (choose view on VBE menu)
Debug.Print strArray

But I changed it to:

'Create Beginning Segment Of Array
'strArray = "Array("
strValue = ""
'Format Text In Each Cell To Add As Array Element
For intLoop = 1 To i_CountAccounts

    'strArray = strArray & Chr(34) & Range("A" & intLoop).Value & Chr(34) & ", "
     strValue = strValue & Range("A" & intLoop).Value & ","
Next intLoop

'Add Closing Parenthesis to remove last comma
'strArray = strArray & ")"
strValue = strValue & ")"

'Remove Superfluous Comma And Space From Last Entry
'strArray = Replace(strArray, ", )", ")")
 strValue = Replace(strValue, ",)", "")

'Print Array to Immediate window (choose view on VBE menu)
Debug.Print strValue
vntArray = Split(strValue, ",")

 

[Rowland Hamilton]

Still don't know how to stretch those code boxes for more width.

 

[Rowland Hamilton]

How do I make the first capture to be vntArray(1)? The Split Array is capturing vntArray(0) to dynamic last vntArray (in this case 7). I typed Option Base 1 before the sub but that didn't work. I'm working within a wider code that uses named ranges like budget1, budget2, etc, that correspond with the array order starting with 1 not 0.

 

[p45cal]

Will the one line (after i_CountAccounts has been assigned a value):

vntArray = Application.Transpose(Range("A1:A" & i_CountAccounts).Value)

do what you need?

 

[Rick Rothstein]

How do I make the first capture to be vntArray(1)? The Split Array is capturing vntArray(0) to dynamic last vntArray (in this case 7). I typed Option Base 1 before the sub but that didn't work. I'm working within a wider code that uses named ranges like budget1, budget2, etc, that correspond with the array order starting with 1 not 0.

I presume the part I highlighted in red comes about because you are running a loop from X=1 to whatever (where X is assumed to be the loop counter variable)... if so, just use X-1 for the vntArray. The reason is your other arrays start with element 1 whereas vntArray always starts with 0 (no matter what the Option Base setting is)... so the array elements are simply offset by 1 from each other, so just take that into account by subtracting 1 from the loop counter when you address elements in vntArray.