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

 

[offthelip]

you don't need a function to solve those two equations, two simple equations will do, see this explanation:
AX+BY=C

DX + EY=F

Multiply the second equation B and divide it by E

(B/E)*DX + (B/E)*EY=(B/E)*F

(BDX/E)+ BY=BF/E

Subtract first equation from the second

(BDX/E-AX=(BF/E)-C

Multiply both sides by E

BDX-AXE=BF-CE

Simplify the RHS

(BD-AE)X=(BF-CE)

Gives :

X=(BF-CE)/(BD-AE)

To find Y ( we now know X) we change the first equation

BY=C-AX

So Y = (C-AX)/B

 

[elliott10]

you don't need a function to solve those two equations, two simple equations will do, see this explanation:
AX+BY=C

DX + EY=F

Multiply the second equation B and divide it by E

(B/E)*DX + (B/E)*EY=(B/E)*F

(BDX/E)+ BY=BF/E

Subtract first equation from the second

(BDX/E-AX=(BF/E)-C

Multiply both sides by E

BDX-AXE=BF-CE

Simplify the RHS

(BD-AE)X=(BF-CE)

Gives :

X=(BF-CE)/(BD-AE)

To find Y ( we now know X) we change the first equation

BY=C-AX

So Y = (C-AX)/B

How does this look in a code though?

 

[offthelip]

try this, ( note I load the whole range into a variant array just to speed it up, just a habit)

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)

X = (B * F - C * E) / (B * D - A * E)
Y = (C - A * X) / B
MsgBox "X equals " & X & " and Y = " & Y
End Sub

 

[elliott10]

try this, ( note I load the whole range into a variant array just to speed it up, just a habit)

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)

X = (B * F - C * E) / (B * D - A * E)
Y = (C - A * X) / B
MsgBox "X equals " & X & " and Y = " & Y
End Sub

I appreciate your response. However when I copied and pasted this, it said there was an overflow with the X= line

 

[kweaver]

If you just use formulas in the spreadsheet, mathematically you can solve the simultaneous equations by:

Book1
	A	B	C	D
1		5	-2	16
2		7	6	-4
3
4		5X + -2 = 16
5		7X + 6Y = -4
6
7	Determinant	0.022727273
8	Inverse	6	2
9		-7	5
10	X =	2
11	Y =	-3
12
13	X =	2
14	Y =	-3
Sheet1

Cell Formulas
Range	Formula
B4	B4	=B1&"X + "&C1& " = "& D1
B5	B5	=B2&"X + "&C2&"Y = "& D2
C8, B9	C8	=-C1
C9	C9	=B1
B7	B7	=1/MDETERM((B1:C2))
B8	B8	=C2
B10:B11	B10	{=$B$7*MMULT(B8:C9,D1:D2)}
B13:B14	B13	{=MMULT(MINVERSE(B1:C2),D1:D2)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[kweaver]

I forgot the Y in the writing of the equation:

Book1
	A	B	C	D
1		5	-2	16
2		7	6	-4
3
4		5X + -2Y = 16
5		7X + 6Y = -4
6
7	Determinant	0.022727273
8	Inverse	6	2
9		-7	5
10	X =	2
11	Y =	-3
12
13	X =	2	One Step
14	Y =	-3
15
Sheet1

Cell Formulas
Range	Formula
B4	B4	=B1&"X + "&C1& "Y = "& D1
B5	B5	=B2&"X + "&C2&"Y = "& D2
C8, B9	C8	=-C1
C9	C9	=B1
B7	B7	=1/MDETERM((B1:C2))
B8	B8	=C2
B10:B11	B10	{=$B$7*MMULT(B8:C9,D1:D2)}
B13:B14	B13	{=MMULT(MINVERSE(B1:C2),D1:D2)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[offthelip]

I appreciate your response. However when I copied and pasted this, it said there was an overflow with the X= line

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

 

[offthelip]

if you want to do it with equations in much simpler way than suggested above put these two equations into cells C1 and C2
=IFERROR( (A2 * A6 - A3 * A5) / (A2 * A4 - A1 * A5),"No solution")
= IFERROR((A3 - A1 *C1) / A2,"No solution")

 

[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

what is inarr?