CODEWITHSUNDEEP
arraysstringvbainitialization
ScottyJ
ScottyJ

Reputation: 1077

VBA - Proper method to initialize an array with string?

I reviewed this question/answer here before writing this: Declare and Initialize String Array in VBA

I am trying to understand the proper way to declare and initialize an array WITHOUT resorting to data type VARIANT.

This is my code, and it does work as it is:

Function MakeLegalFilename(legalname As String) As String

Dim MyArray() As Variant
Dim x As Integer

MyArray = Array("<",">","?","@","%")

For x = LBound(MyArray) To UBound(MyArray)
    legalname = Replace(legalname, MyArray(x), "", 1)
Next x

MakeLegalFilename = legalname

End Function

If I change "Variant" to "String," the code fails at MyArray = Array(... with a runtime error 13 type mismatch.

If I define the array size to match the number of characters in the string (5 total, but array starts at 0):

Dim MyArray(4) As String
MyArray = Array("<",">","?","@","%")

Now I get a compile error at MyArray = Array(... that says "Can't assign to array."

I know that I could declare the array this way and make it work (I tested it this way):

 MyArray(0) = "<"
 MyArray(1) = ">"
 MyArray(2) = "?"
 ...
 MyArray(4) = "%"

But if I am coding in a whole list of characters (say 20), then doing this is cumbersome, and plus, I would like to know why the other way doesn't work, since it suggests I have a fundamental misunderstanding. In it's most basic form, my question really is, why doesn't this:

Dim MyArray(4) As String
MyArray = Array("<",">","?","@","%")

work?

Thank you.

Upvotes: 3

Views: 8244

Answers (3)

Maciej Los
Maciej Los

Reputation: 8591

To initialize static-size string array, use:

Dim MyArray(4) As String
MyArray(0) = "<"
MyArray(1) = ">"
MyArray(2) = "?"
MyArray(3) = "@"
MyArray(4) = "%"

To initialize dynamic-size string array, use:

Dim MyArray() As String
For i = 0 to 10
    Redim Preserve MyArray(i) 'increase the size 
    MyArray(i) = Char(64 + i)
Next

For further information, please see: http://msdn.microsoft.com/en-us/library/wak0wfyt.aspx

Upvotes: 1

Alex K.
Alex K.

Reputation: 175826

Use a helper function:

Dim MyArray() As String
MyArray = StrArray("<", ">", "?", "@", "%")

... 
Public Function StrArray(ParamArray args() As Variant) As String()
   Dim i As Long
   ReDim temp(UBound(args)) As String
   For i = 0 To UBound(args)
       temp(i) = args(i)
   Next
   StrArray = temp
End Function

Upvotes: 4

Serenity
Serenity

Reputation: 116

Array returns a Variant.

So you can't use it if you don't what a variant.

Split can split a string.

Split Function

Description

Returns a zero-based, one-dimensional array containing a specified number of substrings.

Syntax

Split(expression[, delimiter[, limit[, compare]]])

Put comma delimited string into split containing your characters.

Upvotes: 3

Related Questions