Initializing Arrays with a single statement

[cryosis]

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

 

[Juan Pablo González]

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")

 

[Mudface]

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.

 

[cryosis]

Thanks Guys!

 

[boyshawn]

MyArray = Array("Value", "Value2", "Value3")

Hi

This solution only work with variant array and not string array. Is there a similiar work around for string array too?

 

[Rick Rothstein]

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...

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".

 

[boyshawn]

Thank you Rick Rothstein for the help!

However, I am writig a function like this:

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.

 

[Rick Rothstein]

Thank you Rick Rothstein for the help!

However, I am writig a function like this:

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.

 

[boyshawn]

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.

 

[Rick Rothstein]

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...

Function Is_In_Array(item As Variant, referenceArray() As Variant) As Boolean

to this...

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...

If VarType(referenceArray) < vbArray Then Exit Function