2 D arrays (matrix) calculation within a VBA function

[paznativ]

Hi all
i have a function that takes the user input, and generates 3 equations with 3 variables (this is an academic work about wastewater analysis).
i need to use the inverse and multification functions (Minverse and MMult) in the VBA code.
to do so i need to populate a matrix of 3x3 and an array of 1x3 with data from variables, inverse the matrix, multiply the array with the inverted matrix and export the results as a 1x3 array.
i'm stuck with my limited knowledge of VBA...
can someone help with a simple code, showing me how to populate an array, use the functions and export it out?
thanks!

Paz

 

[shg]

Most native functions are available to VBA:

Option Explicit

Sub paz()
  Dim avA           As Variant
  Dim avAI          As Variant
  Dim avB           As Variant
  Dim avC           As Variant

  ' initialize with literals
  avA = [{1,3,1;3,2,2;1,1,1}]
  avB = [{1,2,3}]
  
  With WorksheetFunction
    avAI = .MInverse(avA)
    avC = .MMult(avB, avAI)
  End With

  Range("A1:C3").Value2 = avA
  Range("E1:G3").Value2 = avAI
  Range("E5:G5").Value2 = avB
  Range("E7:G7").Value2 = avC
End Sub

 

[paznativ]

Thanks!!
but, there's always a but, i need to input variables into the matrix. not literals.
how can i do that?

 

[offthelip]

You can load them from cells on the worksheet like this:

Sub paz()
  Dim avA           As Variant
  Dim avAI          As Variant
  Dim avB           As Variant
  Dim avC           As Variant

  ' initialize with literals
  'avA = [{1,3,1;3,2,2;1,1,1}]
  'avB = [{1,2,3}]
' initialise from the worksheet 
  avA = Range("A1:C3").Value2
  avB = Range("E5:G5").Value2
 
  With WorksheetFunction
    avAI = .MInverse(avA)
    avC = .MMult(avB, avAI)
  End With

  Range("A1:C3").Value2 = avA
  Range("E1:G3").Value2 = avAI
  Range("E5:G5").Value2 = avB
  Range("E7:G7").Value2 = avC
End Sub