VBA Error: How do I define and initialize an Array in VBA

Question

I copied this code from Microsoft, but it does not work.

Dim Pixels(1 To 3) As Integer Pixels = Array(1, 2, 3)

When I excute it, I get an error on the '1' in the Array Statement saying "Invalid outside procedure".

I am running the code from an initialization module and would like a quick way to initizlize "Pixels" and was hoping I could use "Array".

Thanks for your help. Mac

All executable statements in VBA must be within a procedure scope (Sub, Function, Property [Get|Let|Set]). Dim isn't an executable statement, it's legal at module-level, only in the module's (declarations) section (at the top). You need to put the Pixels = Array(1, 2, 3) assignment in a procedure in the same module as the declaration (could be another module if it was Public Pixels(1 To 3) As Integer). — Mathieu Guindon
– Mathieu Guindon, Commented May 22, 2019 at 4:43
Do Dim Pixels() As Variant or Dim Pixels As Variant instead of Dim Pixels(1 To 3) As Integer to make possible the result of Array() function be assigned to the variable. — omegastripes
– omegastripes, Commented May 22, 2019 at 6:07
@omegastripes, that goes against advice from Cody Gray at stackoverflow.com/questions/5691330 : "Generally, you should declare variables of a specific type, rather than Variant." — DIV
– DIV, Commented Jul 10 at 3:50

Skin · Accepted Answer · 2019-05-22 05:35:55Z

Some example methods here ...

Public Sub DefineArray()
    Dim i As Long

    ' ---------------------------------------------------------------
    ' Using the Array function
    ' ---------------------------------------------------------------
    Dim arrTest1 As Variant

    arrTest1 = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

    For i = 0 To UBound(arrTest1)
        Debug.Print arrTest1(i)
    Next

    ' ---------------------------------------------------------------
    ' ReDim Preserve
    ' ---------------------------------------------------------------
    Dim arrTest2() As String

    For i = 0 To 10
        ReDim Preserve arrTest2(i)
        arrTest2(i) = i
    Next

    For i = 0 To UBound(arrTest2)
        Debug.Print arrTest2(i)
    Next

    ' ---------------------------------------------------------------
    ' Fixed at declaration
    ' ---------------------------------------------------------------
    Dim arrTest3(10) As String

    For i = 0 To UBound(arrTest3)
        arrTest3(i) = i
    Next

    For i = 0 To UBound(arrTest3)
        Debug.Print arrTest3(i)
    Next

    ' ---------------------------------------------------------------
    ' Using a function that returns an array, e.g. Split
    ' ---------------------------------------------------------------
    Dim strTest As String, arrTest4 As Variant

    strTest = "This is a test"
    arrTest4 = Split(strTest, " ")

    For i = 0 To UBound(arrTest4)
        Debug.Print arrTest4(i)
    Next

    ' ---------------------------------------------------------------
    ' From a range
    ' ---------------------------------------------------------------
    Dim arrTest5 As Variant, lngRow As Long, lngCol As Long

    arrTest5 = Sheet1.Range("A1:K10").Value

    For lngRow = LBound(arrTest5, 1) To UBound(arrTest5, 1)
        For lngCol = LBound(arrTest5, 2) To UBound(arrTest5, 2)
            Debug.Print arrTest5(lngRow, lngCol)
        Next
    Next
End Sub

Hopefully that gives you a good cross section and as per the comment, make sure it's contained within a function or procedure (sub). See my example.

Can you help me with this question? I tried everything I knew but could not solve. stackoverflow.com/q/56207459/8631622 @Skin

jonrizz · Accepted Answer · 2019-05-22 22:31:23Z

1

You may try this sample below.

Dim Pixcels(2) As Integer

Pixcels(0) = 1

Pixcels(1) = 2

Pixcels(2) = 3

edited May 22, 2019 at 22:31

answered May 22, 2019 at 4:17

jonrizz

944 bronze badges

Collectives™ on Stack Overflow

VBA Error: How do I define and initialize an Array in VBA

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related