How to I dynamically resize an array in VBA? I keep getting subscript and index issues

Question

Dim codesArray() As Variant
  ...
If WorksheetExists(workSheetName) Then
  ...
Else
     ReDim Preserve codesArray(UBound(codesArray) + 1) ' Error subscript 
     codesArray(UBound(codesArray)) = cell.Value
End If

I am not sure why I am getting an index out of range error.

Basically everytime the worksheet name is not found, I want to add it to the Array and then eventually let the user know the names of the worksheets that are missing.

So the best employee would have 0 missing worksheets. The worst would be missing them all.

John Coleman · Accepted Answer · 2017-08-04 02:48:28Z

5

The problem is with UBound(codesArray). You can't call UBound() on an array which hasn't yet been dimensioned.

You can use error handling to cover this case. Abstracted to a potentially useful sub:

Sub ExtendArray(A As Variant, Optional NewPlaces = 1, Optional LBase = 0)
    On Error GoTo err_handler
    ReDim Preserve A(LBound(A) To UBound(A) + NewPlaces)
    Exit Sub
err_handler:
    ReDim A(LBase To LBase + NewPlaces - 1)
End Sub

answered Aug 4, 2017 at 2:48

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

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

Comments

Dirk Reichel · Accepted Answer · 2017-08-04 04:36:10Z

2

As already said var() will error out at bound checking. To do this without any errors you can use a code like this:

Dim codesArray
  ...
If WorksheetExists(workSheetName) Then
  ...
Else
  If IsArray(codesArray) Then
    ReDim Preserve codesArray(UBound(codesArray) + 1)
  Else
    ReDim codesArray(0)
  End If
  codesArray(UBound(codesArray)) = cell.Value
End If

or like this:

Dim codesArray
  ...
If WorksheetExists(workSheetName) Then
  ...
Else
  If IsArray(codesArray) Then
    ReDim Preserve codesArray(UBound(codesArray) + 1)
    codesArray(UBound(codesArray)) = cell.Value
  Else
    codesArray = Array(cell.Value)
  End If
End If

The good thing about this is the avoidance of any error-changing-code. So if there is a different reason for an error, it won't be suppressed. Also it should be self-explaining.... if you still have any questions left, just ask ;)

answered Aug 4, 2017 at 4:36

Dirk Reichel

7,9991 gold badge18 silver badges31 bronze badges

2 Comments

GettingStarted Over a year ago

I used the second one and I'm still getting the same error. Subscript out of range. I think you're right about the array being uninitialized.

Dirk Reichel Over a year ago

@softwareisfun have you changed your Dim codesArray() As Variant to Dim codesArray ???

Dy.Lee · Accepted Answer · 2017-08-04 12:36:25Z

0

Use variable k .

Dim codesArray() As Variant
Dim k As Long
  ...
If WorksheetExists(workSheetName) Then
  ...
Else
     ReDim Preserve codesArray(k) ' Error subscript
     codesArray(k) = Cell.Value
     k = k + 1
End If

Upper code Array number begin at 0.

Bellow code Array number begin at 1. Lbound(codeArray) is 1.

Dim codesArray() As Variant
Dim k As Long
  ...
If WorksheetExists(workSheetName) Then
  ...
Else
    k = k + 1
     ReDim Preserve codesArray(1 To k) ' Error subscript
     codesArray(k) = Cell.Value

End If

edited Aug 4, 2017 at 12:36

answered Aug 4, 2017 at 3:00

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

2 Comments

Dy.Lee Over a year ago

@softwareisfun, when we first enter the loop, k is 0. the array size start from 1, but array number is 0. codeArray(0), codeArray(1), codeArray(2),... ,codeArray(n)

Dy.Lee Over a year ago

@softwareisfun, upper code Lbound(codeArray) is 0.

Darren Bartrup-Cook · Accepted Answer · 2017-08-04 12:59:14Z

It may be better to use a collection rather than an array - no need to resize then.

Sub Test()

    Dim vSheetNames As Variant
    Dim sht As Variant
    Dim colMissing As Collection
    Dim vItem As Variant
    Dim sMissingString As String
    Dim lLastComma As Long

    vSheetNames = Array("Sheet1") ', "Sheet2", "Sheet3", "Sheet4", "Sheet5"

    'Build a collection of missing sheets.
    Set colMissing = New Collection
    For Each sht In vSheetNames
        If Not WorkSheetExists(CStr(sht)) Then
            colMissing.Add sht
        End If
    Next sht

    If colMissing.Count = 0 Then
        MsgBox "All sheets are present", vbOKOnly + vbInformation
    Else
        'Build the message string.
        For Each vItem In colMissing
            sMissingString = sMissingString & vItem & ", "
        Next vItem

        'Remove the last comma.
        sMissingString = Left(sMissingString, Len(sMissingString) - 2)

        'Replace last comma with the word 'and'.
        lLastComma = InStrRev(sMissingString, ",")
        If lLastComma > 0 Then
            sMissingString = Left(sMissingString, lLastComma - 1) & " and" & Mid(sMissingString, lLastComma + 1)
        End If

        MsgBox IIf(colMissing.Count = 1, "This sheet is ", "These sheets are ") & "missing: " & vbCr & _
            sMissingString, vbOKOnly + vbInformation

    End If

End Sub

Public Function WorkSheetExists(SheetName As String, Optional WrkBk As Workbook) As Boolean
    Dim wrkSht As Worksheet

    If WrkBk Is Nothing Then
        Set WrkBk = ThisWorkbook
    End If

    On Error Resume Next
        Set wrkSht = WrkBk.Worksheets(SheetName)
        WorkSheetExists = (Err.Number = 0)
        Set wrkSht = Nothing
    On Error GoTo 0
End Function

Collectives™ on Stack Overflow

How to I dynamically resize an array in VBA? I keep getting subscript and index issues

4 Answers 4

Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related