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

Question

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

IMHO looping arrays is pretty efficient. I'd say just do that. And take advantage of previous results where you can to minimize the number of multiplications. — chris neilsen
– chris neilsen, Commented Oct 14, 2022 at 20:58

VBasic2008 · Accepted Answer · 2022-10-14 23:46:11Z

2

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

edited Oct 14, 2022 at 23:46

answered Oct 14, 2022 at 22:17

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dal Over a year ago

Thanks this is great and a very neat piece of code. However, for the dData, third column should be a product of first three columns of sData and fourth should be first 4 ... so on and the 19th column of dData product of all the twenty coulmns of sData. This one is computing current * next.

VBasic2008 Over a year ago

Sorry, I have modified the code many times and it should work now. Previously it was just multiplying 2 adjacent source columns. There are now two loops that write: one for the first column and one for the remaining columns. 1.) It multiplies the first two source columns writing to the 1st destination column. 2.) It multiplies the 1st destination and the 3rd source columns writing to the 2nd destination column. 3.) It multiplies the 2nd destination column and the 4th source columns writing to the 3rd destination column...etc.

VBasic2008 Over a year ago

If you do d1=s1*s2 then instead of d2=s1*s2*s3 you can do d2=d1*s3. Similarly, instead of d3=s1*s2*s3*s4 you can do d3=d2*s4 and instead of d4=s1*s2*s3*s4*s5 you can do d4=d3*s5. That is what chris neilsen was telling with "minimize the number of multiplications".

Collectives™ on Stack Overflow

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

1 Answer 1

Multiplying Array Columns

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Multiplying Array Columns

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related