Matrix / Array Operations just in VBA (not worksheets)

[Munster]

I googled and googled but could not find exactly what I'm looking for: I'm trying to do matrix operations (add, subtract, multiply, etc) but only in VBA. All the examples I've seen are how to use VBA to enter the formulas into a worksheet and the calculations are done there.

Currently my code reads in a bunch of data to an array. What I want to do next is basically summed up in this array formula from a worksheet that gives me the correct calculation:{=SUM(($D$1:$ZZ$1000-B3)^2)}. But I'm not sure how to do this just in vba - I really don't want to copy data out of the array to a worksheet, do array ops and the copy back. The arrays I'm working with are 2D if that make any difference.

PS I am aware of MMulti for multiplication of two matrix but not sure how to multiply everything in a matrix by a constant.

Thanks in advance.

 

[Munster]

{=SUM(($D$1:$ZZ$1000-B3)^2)}

For some reason not able to edit posts...so I'll do a reply.
Just to clarify - the range reference $D$1:$ZZ$1000 would be replaced with an array reference in VBA.

 

[Kyle123]

You won't have much luck natively with this, it's not baked into the language.

You may have some luck here: https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html

 

[Munster]

That looks like it will work. You would think that array calcs would be available in VB but I guess I'm going to have to do some "manual" calcs. Thanks.

 

[Rick Rothstein]

For your stated goal of getting a function that will sum up the squares of each element in an array, you can use this function...

Function ArraySumSquares(Arr As Variant) As Double
  Dim V As Variant
  For Each V In Arr
    ArraySumSquares = ArraySumSquares + V * V
  Next
End Function

For possible future use, here is a subroutine (not a function) that will accept an array as its first argument, a numeric constant as its second argument and a quoted operator indicating what operation on each array element to do with that constant. The operator for this third argument are "+", "-", "*", "/" and "^".

Sub ArrayByConstant(Arr As Variant, Constant As Double, Operator As String)
  Dim R As Long, C As Long
  If IsArray(Arr) Then
    For R = 1 To UBound(Arr, 1)
      For C = 1 To UBound(Arr, 2)
        Select Case Operator
          Case "+":          Arr(R, C) = Arr(R, C) + Constant
          Case "-":          Arr(R, C) = Arr(R, C) - Constant
          Case "*":          Arr(R, C) = Arr(R, C) * Constant
          Case "/":          Arr(R, C) = Arr(R, C) / Constant
          Case "^":          Arr(R, C) = Arr(R, C) ^ Constant
        End Select
      Next
    Next
  End If
End Sub

And here is a macro do demonstrate the use of this subroutine...

Sub Test()
  
[B][COLOR="#008000"]  ' Declare a variable to be our array
[/COLOR][/B]  Dim Munster As Variant
  
[B][COLOR="#008000"]  ' Munster is the array name, here I am loading
  ' it up from a worksheet range, but you can use
  ' any method to load in its elements that you want
[/COLOR][/B]  Munster = Range("A1:C5")
    
 [B][COLOR="#008000"] ' Call the subrouting and pass it some arguments
[/COLOR][/B]  ArrayByConstant Munster, 5, "*"
  
[B][COLOR="#008000"]  ' At this point every element in the Munster array
  ' was changed by applying the operator and
  ' constant to it. Here I output the array to a range
  ' to show you this.
[/COLOR][/B]  Range("E1:G5") = Munster
  
End Sub

 

[Munster]

Arr(R, C) = Arr(R, C) + Constant

Yes! That's what I've been looking for. I knew it had to be easy. Thanks Rick and kudos on the mini sub ArrayByConstant.