Modify existing function to handle arrays of varying dimensions/structure

Question

I'm having trouble handling some arrays in VBA, or more specifically I'm having trouble trying to make efficient use of some existing subroutines/methods to work on arrays of varying sizes/dimensions.

The arrays are retrieved from a COM object and while they arrive in a predictable, consistent structure based on which routine returns the array(s), we have had trouble getting all of the functions to return the data in the same structure.

So, I am dealing with varying structures, sometimes a 2D array, but sometimes a 1D array where each array item is a variant/array.

For example, if I have an existing function which expects a 2D structure like arr(0,0), I need to modify this to also accept a 1D array, where each item is type Variant (structured like arr(0)(0)).

What I'm Currently Doing

I disable errors, and test the Ubound of second dimension, knowing that will raise an error if it's a 1D array. I can then do a slightly different iteration based on the structure of the array.

• I hate using On Error Resume Next if I can avoid it, but it seems like that may be the most efficient in this case.

• I also don't like relying on Excel.Application.Transpose but don't find any method that can do this natively in PowerPoint.

Example:

Function GetSmallFromBar(counts As Variant, banner As Variant, categories As Variant) As Variant
Dim small As Object
Dim arrSizeErr As Variant
Dim i As Long
Set small = CreateObject("Scripting.Dictionary")

On Error Resume Next
arrSizeErr = UBound(counts, 2)
arrSizeErr = (Err.Number <> 0)
Err.Clear
On Error GoTo 0

'Array is structured like arr(0)(0) instead of arr(0,1)
If arrSizeErr Then
    counts = Excel.Application.Transpose(counts)
    ReDim Preserve counts(0 To UBound(counts) - 1)
    'Modify for unique array structure
    For i = LBound(categories) To UBound(categories)
        If counts(i) < 100 Then
            small(i) = categories(i)
        End If
    Next
    GoTo EarlyExit
End If

'This works for the expected array structure, arr(0,0)
For i = LBound(categories) To UBound(categories)
    If counts(i, 0) < 100 Then
        small(i) = categories(i)
    End If
Next
EarlyExit:
GetSmallFromBar = small.Items()

Set small = Nothing
End Function

Note: I redim the array because I need to work with 0-base arrays.

There are probably a half-dozen places in my code where I run in to something like this, and each one relies on a similar but probably not identical method.

I am comfortable fixing my code elsewhere, I'm just wondering if this is a good approach that I can then standardize as a function and call from other modules where this is a potential error, or whether there is another way to do this more efficiently.

Additional info and screenshots

I am working exclusively with 1- and 2-d arrays. But sometimes I get a 1D array wherein each item is also a Variant type. This gives me fits because I'm hoping I can modify some of the functions and methods that I use on a 2d array to work on the "array of array" structure.

Expected 2D-Array

Problematic Array of Variants

Answer 1

As long as your code currently works I think it's fine. If anything you might want to wrap some of the functions within your subroutine into their own functions so that it's reusable.

Chip Pearson has an available function on his array site that will give you the number of dimensions of the array, which you can then use to determine what you need to do:

Public Function NumberOfArrayDimensions(Arr As Variant) As Integer
Dim Ndx As Integer
Dim Res As Integer
On Error Resume Next
    Do
        Ndx = Ndx + 1
        Res = UBound(Arr, Ndx)
    Loop Until Err.Number <> 0
NumberOfArrayDimensions = Ndx - 1
End Function

Source: Chip Pearson, VBA Arrays