VBA - Proper method to initialize an array with string?

Question

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.

@Sorceri I did review that link and it is the link that I put at the top of my question. I looked specifically at @Andez's comment in the top answer where he seems to indicate the Dim MyArray() as String... MyArray = Array(..., but that is still not working for me. What am I missing? — ScottyJ
– ScottyJ, Commented Dec 31, 2014 at 22:26
Here is doc. to Array function : msdn.microsoft.com/en-us/library/aa262675%28v=vs.60%29.aspx. It returns a Variant containing an array so it will work only with Variant. — gembird
– gembird, Commented Dec 31, 2014 at 23:00

Alex K. · Accepted Answer · 2015-01-01 14:55:48Z

4

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

answered Jan 1, 2015 at 14:55

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ScottyJ Over a year ago

@Alex K. Thank you for the suggestion. I would not have thought (and actually had not heard of) to use a helper function. For my purposes, Serenity's answer helped me the most, but I certainly am grateful for your willingness to help me. Thank you.

ThunderFrame Over a year ago

It's also useful to have helper function(s) that can convert an existing array to an array of a specific type. Eg. Convert Variant Array to String Array, or Byte Array to Long Array.

Serenity · Accepted Answer · 2014-12-31 23:35:06Z

3

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.

answered Dec 31, 2014 at 23:35

Serenity

1161 bronze badge

1 Comment

ScottyJ Over a year ago

Thank you. This answer addressed my misunderstanding that Array() returns a variant (somehow, I missed that point before now I'm clued in). It also provides the a solution that most closely resembles the syntax I was trying to use. I appreciate your help.

Maciej Los · Accepted Answer · 2015-01-01 15:15:29Z

1

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

answered Jan 1, 2015 at 15:15

Maciej Los

8,6211 gold badge22 silver badges36 bronze badges

1 Comment

ScottyJ Over a year ago

Thank you for the response. I like your suggested answer, especially the Char(64 +i) suggestion. I accepted @Serenity's answer because it helped me understand my misconception most clearly, but I appreciate your help. Thank you.

Collectives™ on Stack Overflow

VBA - Proper method to initialize an array with string?

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related