Array - Subscript out of range in VBA

Question

I am trying to store the values inside an array. I am facing a problem it says subscript out of range.

This is the code,

Sub Trial()

Dim HeaderArray() As Variant

Dim HeaderValue As String

Dim j As Long

Dim i as Long

set wk = Activeworkbook

lastrow_header_Config = Wk.Sheets("Config").Cells(Rows.Count, "W").End(xlUp).Row

j = 1

      For i = 2 To lastrow_header_Config

         HeaderValue = Wk.Sheets("Config").Range("W" & i).Value

             If HeaderValue <> "" Then

              HeaderArray(j - 1) = HeaderValue // Subscript out of range error

             j = j + 1

             End If

      Next

End Sub

What is the mistake I am making. Kindly advise.

Ouch, please read up on indenting your code. Which line errors? — SJR
– SJR, Commented Sep 19, 2017 at 13:56
You do not set the size of the array. You need to Redim HeaderArray(the size you want) — Scott Craner
– Scott Craner, Commented Sep 19, 2017 at 13:56
@Scott Craner, Ya that helped. But its going to be dynamic. So, how do we make it as dynamic. — Student of the Digital World
– Student of the Digital World, Commented Sep 19, 2017 at 13:59
Make it dynamic with ReDim Preserve. Or set size with variable before loop. — Scott Holtzman
– Scott Holtzman, Commented Sep 19, 2017 at 14:01

Scott Craner · Accepted Answer · 2017-09-19 14:00:58Z

4

You need to declare the size of the array before trying to put data in it. Use COUNTA to find the number of cells with data in your range:

Sub Trial()

Dim HeaderArray() As Variant
Dim HeaderValue As String
Dim lastrow_Header_Config As Long
Dim j As Long
Dim i As Long

Set Wk = ActiveWorkbook

lastrow_Header_Config = Wk.Sheets("Config").Cells(Rows.Count, "W").End(xlUp).Row
ReDim HeaderArray(Application.WorksheetFunction.CountA(Wk.Sheets("Config").Range("W2:W" & lastrow_Header_Config))-1) As Variant
j = 0

For i = 2 To lastrow_Header_Config
    HeaderValue = Wk.Sheets("Config").Range("W" & i).Value
    If HeaderValue <> "" Then
        HeaderArray(j) = HeaderValue
        j = j + 1
    End If
Next

End Sub

answered Sep 19, 2017 at 14:00

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

Comments

Glenn G · Accepted Answer · 2017-09-19 14:10:05Z

3

try this and see how it works for you

pay close attention to the ReDim HeaderArray(j) line and the ReDim Preserve HeaderArray(j) lines

Sub Trial()
    Dim HeaderArray() As Variant
    Dim HeaderValue As String
    Dim j As Long
    Dim i As Long
        Set Wk = ActiveWorkbook
        lastrow_header_Config = Wk.Sheets("Config").Cells(Rows.Count, "W").End(xlUp).Row
        j = 1
        ReDim HeaderArray(j)   '<============= initialize your array length
        For i = 2 To lastrow_header_Config
            HeaderValue = Wk.Sheets("Config").Range("W" & i).Value
            If HeaderValue <> "" Then
                ReDim Preserve HeaderArray(j) '<================= adjust your array length to accomodate the additional info
                HeaderArray(j - 1) = HeaderValue '// Subscript out of range error
                j = j + 1
            End If
        Next
End Sub

Also you might want to read up on using the option keyword. Arrays by default have the first data point at index 0 so for example array(1) creates an array that has 1 data point, however to reference that data point you would use array(0). if you wanted the first data point in the array to be referenced using array(1), then you would use the Option Base 1 keyword at the very top of your module.

answered Sep 19, 2017 at 14:10

Glenn G

6651 gold badge12 silver badges29 bronze badges

1 Comment

Scott Craner Over a year ago

Nice Option. FWIW; Redim Preserve is slower than setting the size before hand. But in many instances it is the only option. Another note about it is that in multi dimensional arrays you can only change the last dimensions. The others are set when initialized.

Patrick Honorez · Accepted Answer · 2017-09-19 14:02:39Z

1

On the first pass, j = 1. Therefore you try to set HeaderArray(0) a value, while HeaderArray is probably 1 based.
You can eventually use Option Base 0, or explicitely Redim HeaderArray(0 to 10) (or whatever value you need)

edited Sep 19, 2017 at 14:02

answered Sep 19, 2017 at 13:59

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

5 Comments

Student of the Digital World Over a year ago

But array starts from '0' ? isn't it ?

Patrick Honorez Over a year ago

No they don't. It depends on your option base. Except for Split() which always returns a 0 based array, if I remember well.

Scott Craner Over a year ago

And when loading an array directly from a range it starts at 1: SomeArray = Range("A1:A10").Value will start at 1. @Sid29

Student of the Digital World Over a year ago

Thanks guys :) learned more than what I wanted to, really nice of you :)

Mathieu Guindon Over a year ago

Implicitly-sized arrays are 0-based unless specified otherwise (and yes, except if it's initialized from a Range). Option Base 0 is redundant, it's the default.

Collectives™ on Stack Overflow

Array - Subscript out of range in VBA

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related