Change a single-dimension array into a multi-dimensional array in VBA for Access

Use ReDim:

Public Function DivideArray()

    Dim strDaysTimes    As String
    Dim arrDaysTimes()  As String
    Dim DaysTimes()     As String
    Dim Index           As Integer
    
    strDaysTimes = InputBox("What days and times do you want to schedule meetings for? (write as 6c,7b)", "Enter Days and Times")
    
    arrDaysTimes() = Split(strDaysTimes, ",")
    ReDim DaysTimes(UBound(arrDaysTimes) - LBound(arrDaysTimes) + 1, 0 To 1)
    
    For Index = LBound(arrDaysTimes) To UBound(arrDaysTimes)
        DaysTimes(Index, 0) = Left(LTrim(arrDaysTimes(Index)), 1)
        DaysTimes(Index, 1) = Right(RTrim(arrDaysTimes(Index)), 1)
    Next
    
    For Index = LBound(arrDaysTimes) To UBound(arrDaysTimes)
        Debug.Print DaysTimes(Index, 0), DaysTimes(Index, 1)
    Next

End Function

Input example:

a7, b8, c9

Output:

a             7
b             8
c             9

Just for the sake of the art an alternative to @Gustav 's approach with the bonus that it returns token lengths greater than 1, too.

Furthermore it profits from the fact that the Val() function is able to return

• a) the starting numeric value from an input string and
• b) the closing string by a split via the above numeric value as delimiter.

Public Function tokenize(ByVal s As String)
    Dim arr() As String
    arr() = Split(Trim(s), ",")
    
    Dim tmp() As String
    ReDim tmp(0 To UBound(arr) - LBound(arr), 0 To 1)
    
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        Dim num: num = Val(arr(i))
        tmp(i, 0) = num
        tmp(i, 1) = Split(arr(i), num)(1)
    Next
    tokenize = tmp
End Function

Example call

Sub testTokenize()
'0. Get input string (e.g. "6c,7b")
    Dim strDaysTimes As String
    strDaysTimes = InputBox( _
        "What days and times do you want to schedule meetings for? (write as 6c,7b)", _
        "Enter Days and Times", _ 
        "6c,7b")
'1. Call help function
    Dim results As Variant
    results = tokenize(strDaysTimes)    ' << function tokenize()
'2. Show results in VB Editor's immediate window    
    Dim i As Long
    For i = LBound(results) To UBound(results)
        Debug.Print results(i, 0), results(i, 1) 
    Next
End Sub

The following code will help you get there.

The GetDaysAndTimes function will return a Jagged array (i.e. an array of arrays). This means that to get the Day and Time of Item 3 you would use ArrayName(2)(0) and ArrayName(2)(1) where arrayname is the name of the array you are using (arrayDaysTimes?)

The function SplitAlphaNumString allows users to enter codes such as AB23.

Option Explicit

' This function takes the string returned by your input box
Public Function GetDaysAndTimes(ByRef ipString As String) As Variant

    Dim myItems As Variant
    myItems = VBA.Split(ipString, ",")
    
    Dim myDayTimes As Variant
    
    Dim myindex As Long
    For myindex = LBound(myItems) To UBound(myItems)
       myDayTimes(myindex) = SplitAlphaNumString(myItems(myindex))
    Next

    GetDaysAndTimes = myDayTimes
    
End Function

Public Function SplitAlphaNumString(ByVal ipString As String) As Variant

    Dim myindex As Long
    For myindex = 1 To VBA.Len(ipString)
       
        If VBA.Asc(VBA.Mid(ipString, myindex, 1)) < 58 Then
        
            Dim myAlphas As String
            myAlphas = VBA.Mid(ipString, 1, myindex - 1)
            
            Dim myNums As String
            myNums = VBA.Mid(ipString, myindex)
            
            SplitAlphaNumString = Array(myAlphas, myNums)
            Exit Function
            
        End If
        
    Next
            
End Function

Sub Test()

    Dim myArray As Variant
    myArray = SplitAlphaNumString("D5")
    Debug.Print myArray(0), myArray(1)
    
End Sub