Modify an existing function to handle arrays of different sizes / structures

Question

I am having problems processing some arrays in VBA, or, more specifically, I am having problems with the efficiency of using some existing routines / methods to work with arrays of different sizes / sizes.

Arrays are retrieved from the COM object, and when they arrive in a predictable, consistent structure, on the basis of which the procedure returns the array (s), we have problems getting all the functions to return data to the same structure .

So, I am dealing with various structures, sometimes with a 2D array, but sometimes with a 1D array, where each element of the array is an option / array.

For example, if I have an existing function that expects a 2D structure of type arr(0,0) , I need to change it to also accept a 1D array, where each element is of type Variant (structured as arr(0)(0) )

What am i doing now

I turn off errors and check the Ubound of the second dimension, knowing that this will throw an error if it is a 1D array. Then I can 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 it might be most effective in this case.

• I also don't like relying on Excel.Application.Transpose , but not finding 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 delete the array because I need to work with 0-base arrays.

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

It’s more convenient for me to fix my code in another place, I’m just wondering if this is a good approach, which I can then standardize as a function and call from other modules where this is a potential error, or is there another way to do this more efficiently.

Additional information and screenshots

I work exclusively with 1- and 2-d arrays. But sometimes I get a 1D array in which each element is also a Variant type. This gives me the opportunity, because I hope that I can change some functions and methods that I use for the 2d array to work with the "array array" structure.

Expected 2D Array

Problem array of options

Answer 1

While your code is working, I think that everything is in order. If anything, you might want to transfer some of the functions inside your routine to their own functions so that they can be reused.

Chip Pearson has an available function on his array site that will give you the number of array dimensions that 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