none
Multidimensional Array Definition in VBA RRS feed

  • Question

  • I have multidimensional array and I want to set values for it. I have problem to write correct code syntax for it. Receive code error message " COmpile error: Missing end bracket" . I have loooked on web and tried several methods all of them return compile error. In next coe eache row of matrix is separated by ";". Can anyone help?

    Here is code:

    Dim IV(10, 5) As Variant
    
    IV = Array [{1, 2, 7, 14, 113, 193, 218, 228, 248, 249, 249;1, 2, 7, 14, 33, 53, 61, 66, 74, 74, _
         74;1, 2, 7, 14, 33, 38, 44, 48, 53, 60, 61;1, 2, 7, 14, 19, 29, 38, 42, 53, 54, 55; _
         1, 2, 7, 14, 15, 20, 28, 38, 46, 50, 56;1, 2, 20, 26, 32, 35, 37, 39, 40, 40, 40}]

    Friday, July 6, 2018 8:06 AM

Answers

  • You could do this:

        Dim IV As Variant

        IV = Evaluate("{1, 2, 7, 14, 113, 193, 218, 228, 248, 249, 249; " & _
            "1, 2, 7, 14, 33, 53, 61, 66, 74, 74, 74; " & _
            "1, 2, 7, 14, 33, 38, 44, 48, 53, 60, 61; " & _
            "1, 2, 7, 14, 19, 29, 38, 42, 53, 54, 55; " & _
            "1, 2, 7, 14, 15, 20, 28, 38, 46, 50, 56; " & _
            "1, 2, 20, 26, 32, 35, 37, 39, 40, 40, 40}")

    Or this:

        Dim IV As Variant

        IV = Array(Array(1, 2, 7, 14, 113, 193, 218, 228, 248, 249, 249), _
               Array(1, 2, 7, 14, 33, 53, 61, 66, 74, 74, 74), _
               Array(1, 2, 7, 14, 33, 38, 44, 48, 53, 60, 61), _
               Array(1, 2, 7, 14, 19, 29, 38, 42, 53, 54, 55), _
               Array(1, 2, 7, 14, 15, 20, 28, 38, 46, 50, 56), _
               Array(1, 2, 20, 26, 32, 35, 37, 39, 40, 40, 40))

    Note that IV has been defined as a variant, not as an array.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 6, 2018 8:33 AM

All replies

  • You could do this:

        Dim IV As Variant

        IV = Evaluate("{1, 2, 7, 14, 113, 193, 218, 228, 248, 249, 249; " & _
            "1, 2, 7, 14, 33, 53, 61, 66, 74, 74, 74; " & _
            "1, 2, 7, 14, 33, 38, 44, 48, 53, 60, 61; " & _
            "1, 2, 7, 14, 19, 29, 38, 42, 53, 54, 55; " & _
            "1, 2, 7, 14, 15, 20, 28, 38, 46, 50, 56; " & _
            "1, 2, 20, 26, 32, 35, 37, 39, 40, 40, 40}")

    Or this:

        Dim IV As Variant

        IV = Array(Array(1, 2, 7, 14, 113, 193, 218, 228, 248, 249, 249), _
               Array(1, 2, 7, 14, 33, 53, 61, 66, 74, 74, 74), _
               Array(1, 2, 7, 14, 33, 38, 44, 48, 53, 60, 61), _
               Array(1, 2, 7, 14, 19, 29, 38, 42, 53, 54, 55), _
               Array(1, 2, 7, 14, 15, 20, 28, 38, 46, 50, 56), _
               Array(1, 2, 20, 26, 32, 35, 37, 39, 40, 40, 40))

    Note that IV has been defined as a variant, not as an array.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 6, 2018 8:33 AM
  • Thank you for HELP!
    Friday, July 6, 2018 8:54 AM