3

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.

Community
  • 1
  • 1
ScottyJ
  • 945
  • 11
  • 16
  • review http://stackoverflow.com/questions/19369132/declare-and-initialize-string-array-in-vba – Sorceri Dec 31 '14 at 22:18
  • @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 Dec 31 '14 at 22:26
  • Here is doc. to Array function : http://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. – Daniel Dušek Dec 31 '14 at 23:00
  • @wackojacko1997 What dee said, needs to be variant. – Sorceri Dec 31 '14 at 23:10

3 Answers3

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.

Serenity
  • 116
  • 1
  • 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. – ScottyJ Jan 02 '15 at 01:30
3

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
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • @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. – ScottyJ Jan 02 '15 at 01:37
  • 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. – ThunderFrame Dec 29 '16 at 01:11
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

Maciej Los
  • 8,468
  • 1
  • 20
  • 35
  • 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. – ScottyJ Jan 02 '15 at 01:33