System of equations with unknown variable x, y macro

[elliott10]

Hi. I need to write a macro on VBA that can solve the equations Ax + By = C and Dx + Ey = F. The macro should be run using a button, and the output will be a message box that displays the x and y values. A through F should go in the first column of the spreadsheet, and they are not zero. I can't find very much information online about how to do this. However, I found the answer to this question on another forum, but can someone write comments to break it down? Also, when I ran this, there was an error with the line
"X = WorksheetFunction.MMult(WorksheetFunction.MInverse(A), B)". Thanks.

Option Explicit
Sub systemofequations()
Dim A As Variant
Dim B As Variant
Dim X As Variant

ReDim A(1 To 2, 1 To 2)
ReDim B(1 To 2, 1 To 1)

With Sheet1

A(1, 1) = .Cells(1, 1)
A(1, 2) = .Cells(2, 1)
A(2, 1) = .Cells(4, 1)
A(2, 2) = .Cells(5, 1)

B(1, 1) = .Cells(3, 1)
B(2, 1) = .Cells(6, 1)

X = WorksheetFunction.MMult(WorksheetFunction.MInverse(A), B)

MsgBox "X equals " & X(1, 1) & " and Y = " & X(2, 1)
End With





End Sub

 

[elliott10]

This will happen depending on the values you have for A to F, I suggest you put a check in to check for zero in the denominator:

Sub test()
inarr = Range(Cells(1, 1), Cells(6, 1))
A = inarr(1, 1)
B = inarr(2, 1)
C = inarr(3, 1)
D = inarr(4, 1)
E = inarr(5, 1)
F = inarr(6, 1)
If (B * D <> A * E) And B <> 0 Then
X = (B * F - C * E) / (B * D - A * E)
Y = (C - A * X) / B
MsgBox "X equals " & X & " and Y = " & Y
Else
MsgBox ("No solution")
End If
End Sub

also could you explain the lines:

If (B * D <> A * E) And B <> 0 Then
X = (B * F - C * E) / (B * D - A * E)
Y = (C - A * X) / B

how did you arrive to that? It works and all, but I just don't understand. Thanks

 

[offthelip]

Did you understand my explanation in post #2? if you didn't then you need to brush up on how to solve two linear equations, there are lots of web sites that help with this for example:
http://www.mathcentre.ac.uk/resources/workbooks/mathcentre/web-simultaneous1.pdf
I have annotated my code with comments to explain it

Sub test()
inarr = Range(Cells(1, 1), Cells(6, 1)) ' inarr is a variant array and this statement loads the values in cells A1 to A6 into this variant array
                                         ' inarr(1,1) has the values from A1
                                         ' inarr (2,1) has the values from A2
                                         ' inarr(3,1) has the values from A3 , etc
A = inarr(1, 1)                          ' this statement assigns the value from inarr (1,1) i.e A1 into a new variable which I called A
                                          ' This A value ties up with the A in your equation
B = inarr(2, 1)                           ' this statement assigns the value from inarr (2,1) i.e A2 into a new vavriable which I called B
C = inarr(3, 1)                           ' etc
D = inarr(4, 1)                           'etc
E = inarr(5, 1)
F = inarr(6, 1)
If (B * D <> A * E) And B <> 0 Then   ' if you look at the denominator ( the bit after the slash /)  in the calculation below you can see
                                       ' that if B*D = A*E then the denominator will be zero, so the first part of this checks that the denominator in the first equation is not zero
                                       ' the second check checks that B is not zero because this is the denominator in the second equation.
X = (B * F - C * E) / (B * D - A * E)  ' this is exactly the equation from my post 2 plus a couple of asterisks to turn it into excel VBa
Y = (C - A * X) / B                     ' ditto
MsgBox "X equals " & X & " and Y = " & Y
Else
MsgBox ("No solution")
End If
End Sub