VBA Spreadsheet to Array (and vice-versa)

_vba_util_array.readme

----------------------------------
VBA Excel Spreadsheet to Array (and back)
----------------------------------

Func's to push a spreadsheet into a VBA array for processing,
then dump an array back to a spreadsheet.

Purpose ...

Had a coding interview challenge where I needed to take a year's
worth of sample data, and model it out over many other years.
Speed was a priority in the challenge. Iterating through arrays
is faster then iterating through the Excel spreadsheet object model,
so I needed some methods to dump the data from a spreadsheet
into an array, then dump array into spreadsheet.

I googled up if anyone already solved this problem before reinventing
the wheel. I found a few entries on stackexchange that did a bit of
the work. So, I used them, and credited. Other code I hammered out
to bring it all together.

vba_util_array.bas

'----------------------------------
' misc utility routines for arrays
'----------------------------------

Option Explicit

'----------------------------------
' dump array out to excel sheet.
' this was my original solution,
' but then I found a better way
' at stackexchange below.
'----------------------------------

Public Sub OutputArray_OLD(arrayToOutput() As Variant, rng As Excel.Range)

    Dim r, c    As Integer
    Dim lastRow As Integer
    Dim lastCol As Integer

    ' ubound dimensions are 1-based,
    ' so 1 = row, 2 = col
    lastRow = UBound(arrayToOutput, 1)
    lastCol = UBound(arrayToOutput, 2)

    For r = 0 To lastRow
        For c = 0 To lastCol
            rng.Offset(r, c).Value = arrayToOutput(r, c)
        Next
    Next

End Sub

'----------------------------------
' a more elegant array-to-sheet sub
' takes in an array & range to spit it out to,
' resizes the range to the ubounds of the array
' then sets the range to the array values
'
' pulled from here...
' https://stackoverflow.com/questions/6063672/excel-vba-function-to-print-an-array-to-the-workbook
'----------------------------------

Public Sub OutputArray(arr() As Variant, rngUpperLeftAnchor As Excel.Range, Optional zeroBasedArray = True)
    
    Dim rng As Excel.Range
    Dim lastRow As Long
    Dim lastCol As Long

    Set rng = rngUpperLeftAnchor
    lastRow = UBound(arr, 1)
    lastCol = UBound(arr, 2)

    ' excel ranges are 1-based
    ' so if we're passing in a 0-based
    ' array, we need to bump the row/col
    ' out +1 to keep from truncating
    ' off the last row/col in array

    If zeroBasedArray Then
        lastRow = lastRow + 1
        lastCol = lastCol + 1
    End If

    rng.Resize(lastRow, lastCol) = arr

End Sub

'----------------------------------
' this pushes a range directly into an array,
' but the array is 1-based, not 0-based
' which sort of makes it a pain right now...
' still, might develop this further later on
'----------------------------------

Sub SetArray()

    Dim rng As Excel.Range
    Dim arr() As Variant
    Dim lastRow As Long
    Dim lastCol As Long
    Dim r, c As Long

    Set rng = Sheets("data_input").Range("A1")
    
    arr = rng.CurrentRegion

    lastRow = UBound(arr, 1)
    lastCol = UBound(arr, 2)

    Debug.Print lastRow
    Debug.Print lastCol
'    Debug.Print arr(0, 0)   ' throws back "subscript out of range" error
    Debug.Print arr(1, 1)

End Sub

'----------------------------------
' find row in array where ID shows up.
' designed this to take 1D or 2D arrays
' because VBA can't do overloaded functions
'----------------------------------

Function GetArrayRow(arr() As Variant, id As Variant) As Long

    Dim lastRow As Long     ' ubound of 1st array dimension (rows)
    Dim d As Long           ' # of array dimensions

    d = GetArrayDimensions(arr)
    lastRow = UBound(arr, 1)

    
    For GetArrayRow = 0 To lastRow

        ' 1D
        If d = 1 Then
            If arr(GetArrayRow) = id Then
                GoTo EXIT_FUNC
            End If
        
        ' 2D
        ElseIf d = 2 Then
            If arr(GetArrayRow, 0) = id Then
                GoTo EXIT_FUNC
            End If
        Else
            ' can build this out to support 3D+ arrays later
            ' but for now does what I need it to do
        End If
    Next

    ' if not found, return error number
    GetArrayRow = -1

EXIT_FUNC:

End Function

'----------------------------------
' DEBUG - test 1D & 2D array row pull
'----------------------------------

Sub GetArrayRowTest()

    Dim a() As Variant
    Dim i As Integer
    Dim u As Integer
    
    u = 1000

    ' test 1D array (rows only)
    ReDim a(u)

    For i = 0 To u
        a(i) = CStr(i)
    Next

    Debug.Print GetArrayRow(a, "1199")


    ' test 2D array (rows & cols)
    ReDim a(u, u)

    For i = 0 To u
        a(i, 0) = CStr(i)
    Next

    Debug.Print GetArrayRow(a, "455")
    

End Sub

'----------------------------------
' I want to be able to use one function (GetArrayRow)
' to dig through arrays of any dimensionality
' and return the row matching ID, so, need
' to first determine how many dimensions
' an array has...
'
' in VBA, you do it hackishly by looping
' through array dims until you get an error
' *sigh*
'
' code pulled & modified from here...
' https://stackoverflow.com/questions/6901991/how-to-return-the-number-of-dimensions-of-a-variant-variable-passed-to-it-in-v
'----------------------------------

Function GetArrayDimensions(arr() As Variant) As Long

    Dim l As Long   ' array dimension iterator
    
    On Error GoTo EXIT_FUNC

    For GetArrayDimensions = 1 To 60000
        l = LBound(arr, GetArrayDimensions)
    Next

EXIT_FUNC:
    
    GetArrayDimensions = GetArrayDimensions - 1

End Function