Multiply 1D-array by 1D-array or Constant - VBA

At risk of being of topic, I decided to share some code, Q&A-style. If the general opinion is such that this would be off-topic I'll be happy to delete if need be.

_Background:

I've been wondering if it was possible to return a 1D-array from multiplying another 1D-array by either a constant value or a third 1D-array (of the same size) without iteration.

So the process I'm looking for would look like:

Multiply by Constant > Derive {3,6,9} directly from {1,2,3}*3
Multiply by array > Derive {3,8,15} directly from {1,2,3}*{3,4,5}

_{Sample Code:}

I have seen questions regarding this topic, but I've not yet seen an answer that would do this without iteration. The closest I've seen is from @SiddharthRout, on an external forum.

But usually one would opt for iteration:

Multiply by constant

Sub Test()
    
    Dim arr1 As Variant: arr1 = Array(1,2,3)
    Dim y As Long, x As Long: x = 3 'Our constant
    
    For y = LBound(arr1) To UBound(arr1)
        arr1(y) = arr1(y) * x
    Next y
    
    End Sub

Multiply by array

Sub Test()

Dim arr1 As Variant: arr1 = Array(1, 2, 3)
Dim arr2 As Variant: arr2 = Array(3, 4, 5)
Dim y As Long

For y = LBound(arr1) To UBound(arr1)
    arr1(y) = arr1(y) * arr2(y)
Next y

End Sub

_Question:

How could you retrieve a 1D-array from multiplying another 1D-array by any constant or another (equally sized) 1D-array without iteration?

Solution

As of what I found was that the key to the answer would lay in MMULT, returning an array from multiplying rows*columns.

_{Multiply 1D-Array by Constant}

Sub Multiply_1D_byConstant()

Dim arr1 As Variant: arr1 = Array(1, 4, 3, 5, 10, 15, 13, 11, 6, 9)

With Application

    Dim x As Long: x = 3 'Our constant
    Dim y As Long: y = UBound(arr1) + 1

    Dim arr2 As Variant: arr2 = .Evaluate("TRANSPOSE(ROW(" & x + 1 & ":" & x + y + 1 & ")-ROW(1:" & y + 1 & "))")
    Dim arr3 As Variant: arr3 = .Evaluate("TRANSPOSE(ROW(1:" & y & "))")
    Dim arr4 As Variant: arr4 = .Index(.MMult(.Transpose(arr1), arr2), arr3, 1)

End With

End Sub

Here, .Evaluate will quickly return a 1D-array n times our constant, n being Ubound(arr1)+1. In the above case: {3,3,3,3,3,3,3,3,3,3}

We than .Transpose arr1 within our .MMult(.Transpose(arr1), arr2) which will return a 2D-array. Because we would need to iterate that, we rather cut into the array to extract a 1D-array by .Index. The result of the above would be:

{3, 12, 9, 15, 30, 45, 39, 33, 18, 27}

To visualize how this works: .MMult will return a 2D-array from above example like so:

Then, because we basically give .Index an array like {1,2,3,4,5,6,7,8,9,10}, but in a dynamic way, for rows and just a 1 for the first column, .Index will slice a 1D-array out of this 2D-array:

_{Multiply 1D-Array by 1D-Array}

This would work kind of the same. Let's imagine the below:

Sub Multiply_1D_by1D()

Dim arr1 As Variant: arr1 = Array(1, 4, 3, 5, 10, 15, 13, 11, 6, 9)
Dim arr2 As Variant: arr2 = Array(2, 1, 4, 1, 2, 3, 2, 5, 2, 1)

With Application

    Dim y As Long: y = UBound(arr1) + 1
    Dim arr3 As Variant: arr3 = .Evaluate("TRANSPOSE(ROW(1:" & y & "))")
    Dim arr4 As Variant: arr4 = .Index(.MMult(.Transpose(arr1), arr2), arr3, arr3)

End With

End Sub

This time we don't tell .Index to extract the same, constant, first column from the result of .MMult, but we give it the same array of values as the rows. These values need to be a 1D-array so therefor we use the .Evaluate to return the array dynamically. So the above returns a 1D-array like:

{2, 4, 12, 5, 20, 45, 26, 55, 12, 9}

To visualize how this works: .MMult will return a 2D-array from above example like so:

Then, because we basically give .Index two arrays like {1,2,3,4,5,6,7,8,9,10}, but in a dynamic way, .Index will slice a 1D-array out of this 2D-array:

In this same fashion you can slice out any 1D-array from a 2D-array using .Index as long as you both specify the rows and columns parameter with a valid 1D-array. I hope this will be helpfull to anyone.