excel - Splitting a Multi-dimensional array VBA

Question

So I'm trying to write a function that can do Dot Product calculations on arrays. I want to use the SumProduct worksheet function to do this. I demonstrated what I'm trying to do on the worksheet with the attached image:

I want to do this using VBA, and for each column of W. The issue is that W is a two dimensional array and I need to split each column into its own array. I'm not sure how to do this efficiently.

Any thoughts on how I can split W column wise?

Edit: I know that you can loop through W, and assign each column to temporary array for calculations. However, I'm curious if this can be done without a loop. I'm writing a Neural Network so performance is important. (VBA is the only language I have at the office, otherwise I'd use Python.)

Edit: Thank you for the assist friends. Based on the Application.Index() suggestion I went ahead and tried to implement it.

Dim w() As Double: ReDim w(3, 3)
For i = 0 To UBound(w)
    For ii_ = 0 To UBound(w, 2)
        w(i, ii) = 0.01
    Next
Next

Dim a() As Double: ReDim a(3)
For i = 0 To UBound(a)
    a(i) = 0.03
Next

Dim b() As Double: ReDim b(3)
For i = 0 To UBound(b)
    b(i) = 0.03
Next

'assign each column of w to L 
Dim L() As Double: ReDim L(UBound(a))
For i = 0 To UBound(w, 2)
    L(i) = Application.Index(w, 0, 1)
Next

I am getting a type mismatch error on Application.Index(). The idea is to use L() like so:

'linear activation
Dim Z() as Double: redim Z(ubound(a))
for i = 0 to ubound(Z)
    Z(i) = Worksheetfunction.SumProduct(L, a) + b(i)
next

Haven't gotten far enough to test this out though. Any thoughts on how to get .Index() to work?

Answer 1

Trying to be close to your original code and assuming the array b elements to represent single values added as sort of constant during each new calculation:

• declare arrays as variant
• and as 1-based 2-dimensional as it facilitates operations together with Application.Index (which result in 1-based arrays)

Dim w: ReDim w(1 To 4, 1 To 4)
Dim i As Long, ii As Long
For i = LBound(w) To UBound(w)
    For ii = LBound(w, 2) To UBound(w, 2)
        w(i, ii) = 0.01 * i + ii
    Next
Next

Dim a: ReDim a(1 To 4, 1 To 1)
For i = LBound(a) To UBound(a)
    a(i, 1) = 0.03
Next

Dim b: ReDim b(1 To 4, 1 To 1)
For i = LBound(b) To UBound(b)
    b(i, 1) = 0.03
Next

'assign each column of w to L
Dim L As Variant: ReDim L(1 To UBound(a))
For i = LBound(w, 2) To UBound(w, 2)
     L(i) = Application.Index(w, 0, i)
     Debug.Print "[" & i & "] ", _
         WorksheetFunction.SumProduct(L(i), a) + b(i, 1)
Next

Anyway personally I'd prefer pure array calculations multiplying factors via a function.