VBA Excel: which one is better and faster: using one variant matrix for a range or several arrays fo-CodePudding

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

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.

CodePudding user response：

If there are many columns, it may be noticeably faster to read all the data into a single matrix in one go. There is a large overhead with every transfer of data between Excel and VBA. A larger data transfer is not much slower than a small data transfer, but many data transfers is a lot slower than a single transfer.

Here is an excellent source of detail:

Data transfer between worksheet cells and VBA variables is an expensive operation that should be kept to a minimum. You can considerably increase the performance of your Excel application by passing arrays of data to the worksheet, and vice versa, in a single operation rather than one cell at a time. If you need to do extensive calculations on data in VBA, you should transfer all the values from the worksheet to an array, do the calculations on the array, and then, possibly, write the array back to the worksheet. This keeps the number of times data is transferred between the worksheet and VBA to a minimum. It is far more efficient to transfer one array of 100 values to the worksheet than to transfer 100 items at a time.

http://www.cpearson.com/excel/ArraysAndRanges.aspx

CodePudding user response：

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