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?

If you are going the route of worksheetfunctions, you can use INDEX(). — JvdV
– JvdV, Commented Jan 7, 2021 at 14:42
What that "W" is? Is it the row where it appears in your picture? What multi-dimensional array are you referring to? — FaneDuru
– FaneDuru, Commented Jan 7, 2021 at 14:58
Any thoughts on how to get .Index to work? I tried using it in my code and I get a 'type mismatch error'. I put my code in the post above. — lazylama
– lazylama, Commented Jan 12, 2021 at 13:28

T.M. · Accepted Answer · 2021-01-12 20:21:07Z

0

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.

answered Jan 12, 2021 at 20:21

T.M.

10k3 gold badges40 silver badges61 bronze badges

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

1 Comment

T.M. Over a year ago

@lazylama FYI Posted a solution close to OP a week ago - feel free to accept by ticking the green checkmark and/or to upvote if helpful.

Collectives™ on Stack Overflow

Splitting a Multi-dimensional array VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related