Getting 'SubScript Out of Range' Error in VBA when calling class Method

Question

I'm going to try to lay out this issue in as much detail as possible. Apparently I can't post images, as I am a New Member...So I will try to describe my situation as good as I possibly can.

So, I am working with a custom class called "Shifts". I have this class declared and set up in a Class Module in VBA. I declare an instance of the "Shifts" class inside a normal Module and call it "Shift".

Dim Shift As New Shifts

My "Shifts" class has 4 variables (String Arrays):

Private ShiftMembers() As String
Private ShiftCallSigns() As String
Private ShiftAssignments() As String
Private ShiftStatuses() As String

I have written a Sub within the class called "Clear" to clear the data in these variables (via ReDim):

Public Sub Clear()
    ReDim ShiftMembers(-1) As String
    ReDim ShiftCallSigns(-1) As String
    ReDim ShiftAssignments(-1) As String
    ReDim ShiftStatuses(-1) As String
End Sub

Now, when I call the Clear Sub of the "Shifts" class (declared as "Shift"):

Shift.Clear 'This is called from within the Module.

i get Subscript out of range (Error 9). My class is declared at the very top of the module, outside of any methods or functions. The Clear() sub within my class is declared Public. I don't understand why I can't seem to access my Clear Sub properly.

Any help would be greatly appreciated!

-Rob

David Zemens David Zemens · Accepted Answer · 2015-01-30T01:16:44

I don't know if you can use ReDim that way. From the documentation, the subscripts argument is bound by the Option Base statement:

subscripts

Required

Dimensions of an array variable; up to 60 multiple dimensions may be declared. The subscripts argument uses the following syntax: [lower To] upper [,[lower To] upper] . . . When not explicitly stated in lower, the lower bound of an array is controlled by the Option Base statement. The lower bound is zero if no Option Base statement is present.

The Option Base statement must be either 1 or 0, and -1 is out of bounds, so the error makes sense.

In order to clear your arrays use the Erase command instead of ReDim.

I put this in my class module (I use the _Initialize procedure just to make one of the array's non-empty, in order to verify that Erase did its job):

Private ShiftMembers() As String
Private ShiftCallSigns() As String
Private ShiftAssignments() As String
Private ShiftStatuses() As String
Sub Class_Initialize()
    ReDim ShiftMembers(1 To 2)
End Sub

Public Sub Clear()
    Erase ShiftMembers
    Erase ShiftCallSigns
    Erase ShiftAssignments
    Erase ShiftStatuses

End Sub

I use the following to test:

Sub t()
Dim s As New Shift

s.Clear

End Sub

Getting 'SubScript Out of Range' Error in VBA when calling class Method

1 Answers