Compile Error: Expected Array - VBA

Question

Could anyone please explain what is the reason for this error? The code looks totally fine to me.

Public n As Long, i As Long, prodCode As String
Private Sub newArray()

    n = wsProducts.Range("A1", wsProducts.Range("A1").End(xlDown)).Rows.Count

    ReDim prodCode(n)

    For i = 1 To n
        prodCode(i) = wsProducts.Range("A1").Offset(i, 0)
    Next i

End Sub

You have declared prodCode as a string, but not an array of strings — YowE3K
– YowE3K, Commented Feb 16, 2017 at 18:53
BTW - If you declare prodCode() As Variant (or, in this case, even as prodCode As Variant) you can then get rid of the ReDim statement by replacing the loop with prodCode = Application.Transpose(wsProducts.Range("A1:A" & n)) — YowE3K
– YowE3K, Commented Feb 16, 2017 at 18:59

YowE3K · Accepted Answer · 2017-02-16 19:12:56Z

2

You have declared prodCode as a string, but not as an array of strings.

You need to use prodCode() As String if you wish to use that variable as an array.

Note: You can also speed your code up by using a Variant array, which allows you then to "load" the array in one transfer operation:

Public n As Long, prodCode() As Variant
Private Sub newArray()

    n = wsProducts.Range("A1", wsProducts.Range("A1").End(xlDown)).Rows.Count
    'P.S. That statement would simplify to 
    'n = wsProducts.Range("A1").End(xlDown).Row

    prodCode = Application.Transpose(wsProducts.Range("A1:A" & n)

End Sub

Application.Transpose has been used to change the shape of the array from being a 1 To n, 1 To 1 two-dimensional array to being a 1 To n one-dimensional array.

answered Feb 16, 2017 at 19:12

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compile Error: Expected Array - VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related