VBA Excel: which one is better and faster: using one variant matrix for a range or several arrays for each column?

Question

I need to use values from several columns based on a row value. Is it better and faster to create variant matrix array and store all columns in that array then search and deploy values from that array, or create an array for each column, considering that there can be several thousand rows and the values are used multiple times?

Example: We have the following data:

If the person joined before 01-Jan-13, I want to deduct due amount from equity. Is it better to declare a variant array like

Dim matrix() as Variant
Dim ws as Worksheet
Dim cols(4) as String: cols(0) = "A': cols(1) = "B": cols(2) = "C": cols(3) = "D"
Dim i as Integer
Dim b as Integer: b = 2 'beginning row
Dim j as Integer: j = 4 'number of lines

Set ws = Worksheets("Sheet1")

For i = 0 to UBound(cols)
  matrix(i) = Range(cols(i) & b & ":" & cols(i) & (b + j)).value2
End if

or

Declare separate four arrays, like

Dim arr1() as String
Dim arr2() as Date
Dim arr3() as Integer
Dim arr4() as Integer

Of course I can directly use data from cells by directly referencing cells as well, but as I use this multi-thousand-row data multiple times it makes more sense to store them in array.

Answer 1

Copy a Range to an Array and Vice Versa

Option Explicit

Sub DeductDue()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    
    ' Reference the data (no headers) range ('rg').
    Dim rg As Range
    With ws.Range("A1").CurrentRegion
        Set rg = .Resize(.Rows.Count - 1).Offset(1)
    End With
    
    ' Write the values from columns 2-4 ('Date' to 'Due') to an array ('sData').
    Dim sData As Variant: sData = rg.Columns(2).Resize(, 3).Value
    
    ' Wrtie the values from column 6 ('Equity') column to an array ('dData').
    Dim dData As Variant: dData = rg.Columns(6).Value
    
    ' Loop through the rows of the arrays and calculate...
    Dim r As Long
    For r = 1 To rg.Rows.Count
        If Year(sData(r, 1)) < 2013 Then
            dData(r, 1) = dData(r, 1) - sData(r, 3)
        End If
    Next r
    
    ' Write the result to a column range, e.g.:
    rg.Columns(6).Value = dData ' overwrite Equity with the deducted values
    
End Sub