9

Hoping there is a quick answer to this question....

I have an array and I want to populate it with a list of arguments.

Sub EasyArrayInput()
Dim myArr() as variant
myArr = ("string1", "string2", "string3")
End Sub

I am well aware of how to loop through and populate with a for/next or do/while, but it would be nice to be able to populate an array when the values wont change without using a hardcoded method.

Sub UsualMethodThatIDontWantToDo()
Dim myArr(1 to 3) as variant
myArr(1) =  "string1"
myArr(2) =  "string2"
myArr(3) =  "string3"
End Sub

Is there anyway to do it in a method similar to the first code snippet? I would prefer to do it that way. I apologize if this question has been asked/answered, but I'm not quite sure what the method I am asking about is called.

Thanks in advance!

Edit: Solution

The code snippet below (from the link that chancea sent) will create an array that is a variant and exaclty what I wanted. 

Sub EasyArrayInput()
Dim myArr() as variant
myArr = Array("string1", "string2", "string3")
End Sub

The next code snippet looks to be useful for if you only have strings and don't want to initialize a variant:

Sub EasyArrayInput()
Dim myArr() as String
myArr = Split("String1,String2,String3", ",")
End Sub
Community
  • 1
  • 1
JSS
  • 203
  • 1
  • 2
  • 11
  • 2
    This is a duplicate of http://stackoverflow.com/questions/19369132/declare-and-initialize-string-array-in-vba – chancea Feb 19 '15 at 16:04
  • It's really not the same as that question. – Rory Feb 19 '15 at 16:16
  • Thanks chancea, I wasn't quite sure what I needed to look up but found the solution from your link. I have added the solution in my question. Really appreciate how quickly you were able to help! – JSS Feb 19 '15 at 16:17

2 Answers2

10

How about?

Sub EasyArrayInput()
    Dim myArr() As Variant

    myArr = Array("string1", "string2", "string3")
End Sub
basodre
  • 5,720
  • 1
  • 15
  • 23
2

Assuming you have some sort of numeric sequence, you can do something like this:

Dim myArray()
myArray = [TRANSPOSE(INDEX("string"&ROW(1:10),))]

but frankly I think a loop is clearer.

Rory
  • 32,730
  • 5
  • 32
  • 35
  • That would work and I would use it, but the code that I am writing will be transferred from workbook to workbook with different scopes/sheets. I appreciate your help though! – JSS Feb 19 '15 at 16:18
  • I'm not sure I see the relevance, but OK. – Rory Feb 19 '15 at 16:19