Initializing Arrays with a single statement

cryosis

New Member
Joined
Jul 28, 2002
Messages
23
I have need an array filled with constants to run a macro of mine. Is there any way to assign multiple values to an array in a single statement. It might look something like this :

Dim MyArray as String
MyArray = ["Value", "Value2", "Value3"]

I'm pretty sure you can do it in other languages but I'm not sure about VBA.

Thanks,
Rick
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
On 2002-08-14 12:56, cryosis wrote:
I have need an array filled with constants to run a macro of mine. Is there any way to assign multiple values to an array in a single statement. It might look something like this :

Dim MyArray as String
MyArray = ["Value", "Value2", "Value3"]

I'm pretty sure you can do it in other languages but I'm not sure about VBA.

Thanks,
Rick

MyArray = Array("Value", "Value2", "Value3")
 
Upvote 0
Try

Dim myarray As Variant

myarray = Array("Cat", "Dog", "Rabbit")

It might help to take a look at the Array function help entry in the VBA help files, too. HTH.
 
Upvote 0
This solution only work with variant array and not string array. Is there a similiar work around for string array too?
You can do it this way...

Code:
Dim MyStringArr() As String
MyStringArr = Split("Text1,Text2,Text3", ",")
Note 1: The Split function always returns a zero-based array, even if 'Option Base 1' is in effect.

Note 2: Do not use spaces around the commas to "pretty things up".
 
Last edited:
Upvote 0
Thank you Rick Rothstein for the help!

However, I am writig a function like this:


Code:
Private Sub Test_Ignore_List()
    Dim ignore_list() As String
    
    ignore_list = Split("|ignore1|ignore2|ignore3", "|", , vbTextCompare)
    
    Dim current_list() As String
    ReDim current_list(1 To 1000) As String
    
    current_list(1) = "I am not ignore1"
    current_list(2) = "I am not ignore2"
    current_list(3) = "ignore1"
    
    MsgBox (Is_In_Array(current_list(2), ignore_list))
End Sub

Function Is_In_Array(item As Variant, referenceArray() As Variant) As Boolean
    Dim i As Integer
    
    For i = LBound(referenceArray) To UBound(referenceArray) Step 1
        If (InStr(1, item, referenceArray(i), vbTextCompare)) Then
            Is_In_Array = True
            Exit Function
        End If
    Next i
    
    Is_In_Array = False
End Function

Then there is error is passing the array into the function due to different data type. May I know is there a good way to go around this, while maintaining the flexiblity of the code to accept integer, string and other primitive data-type array.
 
Upvote 0
Thank you Rick Rothstein for the help!

However, I am writig a function like this:


Code:
Private Sub Test_Ignore_List()
    Dim ignore_list() As String
    
    ignore_list = Split("|ignore1|ignore2|ignore3", "|", , vbTextCompare)
    
    Dim current_list() As String
    ReDim current_list(1 To 1000) As String
    
    current_list(1) = "I am not ignore1"
    current_list(2) = "I am not ignore2"
    current_list(3) = "ignore1"
    
    MsgBox (Is_In_Array(current_list(2), ignore_list))
End Sub

Function Is_In_Array(item As Variant, referenceArray() As [B][COLOR=#B22222]Variant[/COLOR][/B]) As Boolean
    Dim i As Integer
    
    For i = LBound(referenceArray) To UBound(referenceArray) Step 1
        If (InStr(1, item, referenceArray(i), vbTextCompare)) Then
            Is_In_Array = True
            Exit Function
        End If
    Next i
    
    Is_In_Array = False
End Function

Then there is error is passing the array into the function due to different data type. May I know is there a good way to go around this, while maintaining the flexiblity of the code to accept integer, string and other primitive data-type array.
Change the declaration for referenceArray (shown in red) from Variant to String so the arrays match. Just so you know, the lower bound for an array created by the Split function is always zero-based no matter if the Option Base is set to 0 or 1, so you can replace the LBound(referenceArray) in your For..Next with 0. Also, as an aside, you do not need to specify "Step 1" in your For..Next loop as this type of loop increments by one unless told to do otherwise.
 
Upvote 0
Change the declaration for referenceArray (shown in red) from Variant to String so the arrays match. Just so you know, the lower bound for an array created by the Split function is always zero-based no matter if the Option Base is set to 0 or 1, so you can replace the LBound(referenceArray) in your For..Next with 0. Also, as an aside, you do not need to specify "Step 1" in your For..Next loop as this type of loop increments by one unless told to do otherwise.

Hi Rick Rothstein, thank you for the suggestion, I understand that this will solve the problem, but it make the function inflexible, if next time I wish to parse a integer array into the function, the function will fail.
 
Upvote 0
Hi Rick Rothstein, thank you for the suggestion, I understand that this will solve the problem, but it make the function inflexible, if next time I wish to parse a integer array into the function, the function will fail.
Okay, the other possibility is to change the function declaration from this...

Code:
Function Is_In_Array(item As Variant, referenceArray() As Variant) As Boolean
to this...

Code:
Function Is_In_Array(item As Variant, referenceArray As Variant) As Boolean
In the first declaration, the function was expecting an array of Variants. By removing the empty parentheses, the argument becomes a normal Variant which can accept pretty much anything, including String, Long, Double, etc. arrays. One thing you might want to do inside the function so that it does not crash is test that an array was actually passed into the function...

Code:
If VarType(referenceArray) < vbArray Then Exit Function
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top