Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Initializing Arrays with a single statement

This is a discussion on Initializing Arrays with a single statement within the Excel Questions forums, part of the Question Forums category; I have need an array filled with constants to run a macro of mine. Is there any way to assign ...

  1. #1
    New Member
    Join Date
    Jul 2002
    Location
    Cleveland
    Posts
    23

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

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

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339

    Default

    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.

  4. #4
    New Member
    Join Date
    Jul 2002
    Location
    Cleveland
    Posts
    23

    Default

    Thanks Guys!

  5. #5
    New Member
    Join Date
    Jun 2012
    Location
    Singapore
    Posts
    5

    Default Re: Initializing Arrays with a single statement

    Quote Originally Posted by Juan Pablo González View Post
    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?

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,727

    Default Re: Initializing Arrays with a single statement

    Quote Originally Posted by boyshawn View Post
    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 by Rick Rothstein; Jun 26th, 2012 at 11:29 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  7. #7
    New Member
    Join Date
    Jun 2012
    Location
    Singapore
    Posts
    5

    Default Re: Initializing Arrays with a single statement

    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.

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,727

    Default Re: Initializing Arrays with a single statement

    Quote Originally Posted by boyshawn View Post
    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.
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  9. #9
    New Member
    Join Date
    Jun 2012
    Location
    Singapore
    Posts
    5

    Default Re: Initializing Arrays with a single statement

    Quote Originally Posted by Rick Rothstein View Post
    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.

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,727

    Default Re: Initializing Arrays with a single statement

    Quote Originally Posted by boyshawn View Post
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com