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

I have code to ask a user for a series of codes that then creates a single-dimensional array like this:

Dim strDaysTimes As String
Dim arrDaysTimes() As String

strDaysTimes = InputBox("What days and times do you want to schedule meetings for? (write as 6c,7b)", "Enter Days and Times")

arrDaysTimes() = Split(strDaysTimes, ",")

The number of inputs is not defined but the format is. It could be "6c,7b" or "5a,6b,7b".

I want to convert this into a multi-dimensional array that would carry the values like this (one dimension has the number portion and the other has the letter portion):

5 a

6 b

7 b

I know that I need to use a nested For...Next statements to process multidimensional arrays, but I would appreciate any suggestions.

CodePudding user response：

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

CodePudding user response：

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