What's the most efficient way of multiplying columns within an array in VBA?-CodePudding

I am trying to create two sets of arrays arr and arr_two. I am generating random numbers in arr and it has 2000 rows and 20 columns. As the second step, I'd like to multiply the first two columns in arr and capture that as the first column in arr_two, multiply first three and capture that in the second column of arr_two and the 19th column of arr_two would be the product of all the twenty columns of arr. I'd like to kno how to go about creating this arr_two. I've created arr as follows:

Sub arraytest()

    Dim arr(2000, 20) As Variant
    Dim arr_two(2000, 20) As Variant
    
    For i = 0 To 2000
        For j = 0 To 20        
        arr(i, j) = WorksheetFunction.Sum(WorksheetFunction.Norm_Inv(Rnd(), 0, 1) / 100, 1)                    
        Next j                
    Next i

End Sub

CodePudding user response：

Multiplying Array Columns

Sub ArrayTest()

Dim tT As Double: tT = Timer

    Const rCount As Long = 2000 ' the same
    Const scCount As Long = 20 ' different
    
    ' Populate the source array ('sData').
    
    Dim sData() As Double: ReDim sData(1 To rCount, 1 To scCount)
    
    Dim r As Long
    Dim sc As Long
    
    For r = 1 To rCount ' by rows
        For sc = 1 To scCount
            With WorksheetFunction
                On Error Resume Next ' 'Norm_Inv' may raise an error
                    sData(r, sc) = .Sum(.Norm_Inv(Rnd(), 0, 1) / 100, 1)
                On Error GoTo 0
            End With
        Next sc
    Next r
    
Dim sT As Double: sT = Timer - tT
    
    ' Populate the destination array ('dData').
    
    Dim dcCount As Long: dcCount = scCount - 1 ' one less column
    Dim dData() As Double: ReDim dData(1 To rCount, 1 To dcCount)
    
    Dim nsc As Long ' next
    Dim dc As Long ' current
    Dim pdc As Long ' previous
    
    ' First column:
    For r = 1 To rCount ' current source * next source
        dData(r, 1) = sData(r, 1) * sData(r, 2)
    Next r
    
    ' Remainder of columns:
    For dc = 2 To dcCount ' by columns
        nsc = dc   1
        pdc = dc - 1
        For r = 1 To rCount ' previous destination * next source
            dData(r, dc) = dData(r, pdc) * sData(r, nsc)
        Next r
    Next dc

tT = Timer - tT
Debug.Print "Source time      = " & sT
Debug.Print "Destination time = " & tT - sT
Debug.Print "Total time       = " & tT

    ' Write to a worksheet.

'    With Sheet1
'        .UsedRange.Clear
'        With .Range("A1").Resize(, dcCount)
'            .Value = Application.Transpose( _
'                .Worksheet.Evaluate("""Col ""&ROW(1:" & dcCount & ")"))
'            .Offset(1).Resize(rCount).Value = dData
'            .EntireColumn.AutoFit
'        End With
'    End With

End Sub

Results

Source Time      = 0,4609375
Destination Time = 0,01513671875
Total Time       = 0,47607421875

The results for 200k rows show the efficiency better:

Source time      = 48,181884765625
Destination time = 0,3291015625
Total time       = 48,510986328125