Switch between Uni- and Multi-Dimensional arrays inside generic Subroutine RRS feed

  • Question

  • Hi,

    I am trying to write a perform some common operations on arrays like sort, remove duplicates etc. NOW,

    1. Processing Calls A,  D and F are working fine
    2. Processing call
    3. Call C runs into 'Subscript Out of Range' with UniDimProc, Which I can understand, because the Array needs width
    4. But Call C runs into same error with MultiDim: This perplexes me, because we were always told that if we don't declare a width, the default width is Zero, so I would expect Call E = Call A, and Call F = Call B. But UniArrZero behaves similar to MultiArr

    MY Qns are:

    1. Why is this happening?
    2. Is there an alternative construct possible that can process both Uni- & Multi-Dimensional arrays

    My code looks like below (Code lines where "SubScript" issue occurs are marked #Conflict):

    Sub MultiDimProc(arr) Dim Width As Long Dim Dummy As String Dummy = Chr(255) Width = UBound(arr, 2) 'Determine Width of the array #Conflict For i = LBound(arr) To UBound(arr) If arr(i, 0) = "" Then arr(i, 0) = Dummy 'Pass a dummy value #Conflict next i End Sub Sub UniDimProc(arr) Dim Width As Long Dim Dummy As String Dummy = Chr(255) For i = LBound(arr) To UBound(arr) If arr(i) = "" Then arr(i) = Dummy 'Pass a dummy value #Conflict next i End Sub Sub ConsumeCommonFns() DIM UniArr as variant DIM MultiArr as Variant    DIM UniArrZero as variant

    redim UniArr(10) Redim MultiArr(10, 5)    redim UniArrZero(10, 0)

    Call UniDimProc(UniArr) 'Processing Call A Call MultiDimArr(UniArr) 'Processing Call B Call UniDimProc(MultiArr) 'Processing Call C Call MultiDimArr(MultiArr) 'Processing Call D

       Call UniDimProc(UniArrZero)     'Processing Call E
       Call MultiDimArr(UniArrZero)    'Processing Call F End Sub

    Tuesday, June 9, 2020 5:52 AM

All replies

  • For a one-dimensional array, the 'width' is not 0. The second dimension simply doesn't exist.

    You could perform a check for the existence of the second dimension:

    Sub ArrProc(arr)
        Dim i As Long
        Dim b As Long
        Dim Dummy As String
        Dim f As Boolean
        Dummy = Chr(255)
        On Error Resume Next
        b = LBound(arr, 2)
        f = (Err = 0)
        On Error GoTo 0
        If f Then
            For i = LBound(arr) To UBound(arr)
                If arr(i, b) = "" Then arr(i, b) = Dummy
            Next i
            For i = LBound(arr) To UBound(arr)
                If arr(i) = "" Then arr(i) = Dummy
            Next i
        End If
    End Sub
    Sub ConsumeCommonFns()
        Dim UniArr As Variant
        Dim MultiArr As Variant
        Dim UniArrZero As Variant
        ReDim UniArr(10)
        ReDim MultiArr(10, 5)
        ReDim UniArrZero(10, 0)
        Call ArrProc(UniArr)     'Processing Call A
        Call ArrProc(MultiArr)   'Processing Call C
        Call ArrProc(UniArrZero) 'Processing Call E
    End Sub

    ArrProc will work for one-dimensional and two-dimensional arrays, and fail for higher-dimensional arrays.

    I changed the code slightly to work even if the lower bound of the second dimension is > 0.

    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Tuesday, June 9, 2020 10:54 AM