"Subscript out of range" error in VBA using array of strings

Question

I learned C# and VB.Net in school, but not VBA.

I am trying to automate a pipe-delimited .csv export in Access. The user selects a ship date from a combobox and clicks "export", and the application needs to do the rest. The problem is, it is exporting the dates in short date format (m/d/yyyy) no matter how I have them formatted in Access; the client's required format is mmddyyyy.

I tried appending the date to a text field in another table but Access won't let me do that. So I placed a textbox on the form and typed the date in the correct format and used that to populate the table to be exported, and that works.

What I am trying to do now is write a subroutine to populate the textbox in the correct format whenever the user changes the combobox. I keep getting "Runtime Error 9: Subscript out of range" on the following line: If Len(dateParts(0)) = 2 Then

Here is the code I have so far:

Private Sub tbxShipDate_Change()
    Dim strShipDate As String
    Dim strTextDate As String
    Dim dateParts() As String

    strShipDate = Me.tbxShipDate.Value

    If Len(strShipDate) = 10 Then
        strTextDate = strShipDate
        strTextDate = Replace(strTextDate, "/", "")
    Else
        dateParts = Split(strShipDate, "/")

        'check month format
        If Len(dateParts(0)) = 2 Then
            strTextDate = dateParts(0)
        Else
            strTextDate = "0" & dateParts(0)
        End If

        'check day format
        If Len(dateParts(1)) = 2 Then
            strTextDate = strTextDate & dateParts(1)
        Else
            strTextDate = strTextDate & "0" & dateParts(1)
        End If

        'add year
        strTextDate = strTextDate & dateParts(2)
    End If

    Me.tbxTextDate.Value = strTextDate

End Sub

Do you have Option Base 1 set at the top of the module? you can set a breakpoint at the line and check the locals window to find out what dateParts() is holding... — Marshall

Chris Chris · Accepted Answer · 2013-05-01T19:46:51

I think you will need to ReDim your array to the correct length before using it, like this:

ReDim dateParts(3)

before you can use it.

"Subscript out of range" error in VBA using array of strings

1 Answers