Array Base

[mdmackillop]

In creating a UDF in answer to a query for a quadratic equation solution, I came up with

Function Quads(Vals As Variant)
    a = Vals(1)
    b = Vals(2)
    c = Vals(3)
    Quad1 = -b + (b ^ 2 - 4 * a * c) / 2 * a
    Quad2 = -b - (b ^ 2 - 4 * a * c) / 2 * a
    Quads = Quad1 & " or " & Quad2
End Function

I expected to use a = Vals(0), b = Vals(1) and c = Vals(2)
There is no Option Base declared, so why does the array index start at 1?
MD

[mdmackillop]

Thanks John for the discrete PM.
For other eagle eyed persons, I posted this in "another place"

I have two excuses
1. I followed the question: -b +/- [(b^2 - 4*a*c)]/(2a)]
2. It's 35 years since I last calculated one and I can't remember what it does!

MD

[johnske]

Hi MD,

This sub will give the correct roots for a quadratic equation, I'm sure you can adapt this and put it into array form yourself

Note that a simple sub like this won't give complex roots, but I can easily modify it to do this if required

Sub QuadraticEqn()
a = InputBox("A?") '//a <> 0 !!
b = InputBox("B?")
c = InputBox("C?")
If (b ^ 2 - 4 * a * c) < 0 Then
MsgBox "Cannot calculate, complex or imaginary number"
Exit Sub
End If
x1 = (-b + Sqr(b ^ 2 - 4 * a * c)) / (2 * a)
x2 = (-b - Sqr(b ^ 2 - 4 * a * c)) / (2 * a)
MsgBox x1 & " OR " & x2
End Sub

[mdmackillop]

Thanks John,
I revised it to a UDF as follows, and I leave the questioner to resolve any other error handling issues.

Function Quad(Vals As Variant)
      a = Vals(1)
      b = Vals(2)
      c = Vals(3)
      If (b ^ 2 - 4 * a * c) < 0 Then
            Quad = "Cannot calculate, complex or imaginary number"
            Exit Function
      End If
      x1 = -b + Sqr(b ^ 2 - 4 * a * c) / (2 * a)
      x2 = -b - Sqr(b ^ 2 - 4 * a * c) / (2 * a)
      Quad = x1 & " OR " & x2
End Function

[johnske]

Hi MD,

Just for the sake of completeness, I modified my previous to include complex and imaginary numbers (code below)

Option Explicit
 
Sub QuadraticEqn()
Dim a, b, c, x1, x2, RealValue, ImaginaryPart As Integer
a = InputBox("A?")
If a = 0 Then
MsgBox "The roots are indeterminate (infinite)"
Exit Sub
End If
b = InputBox("B?")
c = InputBox("C?")
If (b ^ 2 - 4 * a * c) < 0 Then
ImaginaryPart = Sqr(Abs(b ^ 2 - 4 * a * c)) / (2 * a)
RealValue = -b / (2 * a)
MsgBox "The equation has complex roots, these are:" & vbLf & _
"i) " & RealValue & " + " & ImaginaryPart & " i" & " AND " & _
"ii) " & RealValue & " - " & ImaginaryPart & " i"
Exit Sub
End If
x1 = (-b + Sqr(b ^ 2 - 4 * a * c)) / (2 * a)
x2 = (-b - Sqr(b ^ 2 - 4 * a * c)) / (2 * a)
MsgBox "The equation has real roots, they are:" & vbLf & _
"i) " & x1 & " AND " & "ii) " & x2
End Sub

[johnske]

OOPS!! Replace " e" with " i" above - will edit it now

[Jacob Hilderbrand]

The -b gets divided by 2a as well.

x1 = (-b + Sqr(b ^ 2 - 4 * a * c)) / (2 * a)

[mdmackillop]

Thanks Jacob

[johnske]

The -b gets divided by 2a as well.

x1 = (-b + Sqr(b ^ 2 - 4 * a * c)) / (2 * a)

Sorry, ANOTHER OOPS!! (thanx)...Shows ya shouldn't rush these things (I got it rite in the comlex part tho) - will edit it now

[johnske]

OK, checked the previous with some "real" numbers, and for some 'VBA reason' there was always an integer (1, 2, 3, ...) returned as the value for (b ^ 2 - 4*a*c) when it's written in complete as Sqr(Abs(b ^ 2 - 4*a*c))/(2*a), it HAS to be written as "Numba = (b ^ 2 - 4*a*c)" to return the correct value (just DON'T ask me why the previous can't be used - mathematically, it's the same thing )...

Option Explicit

Sub QuadraticEqn()
Dim a, b, c, x1, x2, RealValue, ImaginaryPart, Numba
a = InputBox("A = ?")
If a = 0 Then
MsgBox "The roots are indeterminate (infinite)"
Exit Sub
End If
b = InputBox("B = ?")
c = InputBox("C = ?")
Numba = (b ^ 2 - 4 * a * c)
If Numba < 0 Then
ImaginaryPart = Sqr(Abs(Numba)) / (2 * a)
RealValue = -b / (2 * a)
MsgBox "The equation has complex roots, these are:" & vbLf & _
"x = " & RealValue & " + " & ImaginaryPart & " i" & " (AND)" & vbLf & _
"x = " & RealValue & " - " & ImaginaryPart & " i"
Exit Sub
End If
x1 = (-b + Sqr(Numba)) / (2 * a)
x2 = (-b - Sqr(Numba)) / (2 * a)
MsgBox "The equation has real roots, they are:" & vbLf & _
"x = " & x1 & " (AND)" & vbLf & _
"x = " & x2
End Sub

(Edited for clarity of results output {appearance} + there are overflow errors for large numbers if a "type" of variable is specifiied)

[johnske]

Have sorted the problem as to why (b^2 - 4*a*c) didn't work - the problem only arose when I went to Option Explicit and had to declare the variables - I declared them as the wrong "type" (they should be declared just as "Variant"). My apologies to all viewing the thread, the following gives the correct answers now

Option Explicit
 
Sub QuadraticEqn()
Dim a, b, c, x1, x2, RealValue, ImaginaryPart As Variant
a = InputBox("A = ?")
If a = 0 Then
MsgBox "The roots are indeterminate (infinite)"
Exit Sub
End If
b = InputBox("B = ?")
c = InputBox("C = ?")
If (b ^ 2 - 4 * a * c) < 0 Then
ImaginaryPart = Sqr(Abs(b ^ 2 - 4 * a * c)) / (2 * a)
RealValue = -b / (2 * a)
MsgBox "The equation has complex roots, these are:" & vbLf & _
"x = " & RealValue & " + " & ImaginaryPart & " i" & " (AND)" & vbLf & _
"x = " & RealValue & " - " & ImaginaryPart & " i"
Exit Sub
End If
x1 = (-b + Sqr(b ^ 2 - 4 * a * c)) / (2 * a)
x2 = (-b - Sqr(b ^ 2 - 4 * a * c)) / (2 * a)
MsgBox "The equation has real roots, they are:" & vbLf & _
"x = " & x1 & " (AND)" & vbLf & _
"x = " & x2
End Sub

[Jacob Hilderbrand]

Two Things:

1) Why Variant? Why not Double?

2)

 Dim a, b, c, x1, x2, RealValue, ImaginaryPart As Variant

In this case only ImaginaryPart is defined by the data type you specify. All the other variables would just be Variant by default. Since you are declaring as Variant it doesn't matter since the other variable would still be Variant. But if you did this:

Dim a, b, c, x1, x2, RealValue, ImaginaryPart As Double

only ImaginaryPart would be Double.

[Jacob Hilderbrand]

Oh also:

If a = 0 Then 
        MsgBox "The roots are indeterminate (infinite)" 
        Exit Sub 
End If

Not true. If A = 0 then there is one root = (-c/b)

[johnske]

Two Things:

1) Why Variant? Why not Double?

2)

Dim a, b, c, x1, x2, RealValue, ImaginaryPart As Variant

In this case only ImaginaryPart is defined by the data type you specify. All the other variables would just be Variant by default. Since you are declaring as Variant it doesn't matter since the other variable would still be Variant. But if you did this:

Dim a, b, c, x1, x2, RealValue, ImaginaryPart As Double

only ImaginaryPart would be Double.

I could be wrong, but I think I tried "double", "long", and "integer" and they all bombed out (overflow) when large values for a, b, and c were entered - and yes, it could be left blank... i.e.

Dim a, b, c, x1, x2, RealValue, ImaginaryPart

[Jacob Hilderbrand]

Try this:

Option Explicit
 
Sub QuadraticEqn()
Dim a As Double
Dim b As Double
Dim c As Double
Dim x1 As Double
Dim x2 As Double
Dim RealValue As Double
Dim ImaginaryPart As Double
Dim Radical As Double
a = Val(InputBox("A = ?"))
b = Val(InputBox("B = ?"))
c = Val(InputBox("C = ?"))
If a = 0 Then
MsgBox "The equation has one root. Note that this is not a quadratic " & _
"equation; however, the solution is: " & -b / c
Exit Sub
End If
Radical = b ^ 2 - 4 * a * c
If Radical < 0 Then
ImaginaryPart = Sqr(Abs(Radical)) / (2 * a)
RealValue = -b / (2 * a)
MsgBox "The equation has complex roots, these are:" & vbNewLine & _
"x = " & RealValue & " + " & ImaginaryPart & " i" & " (AND)" & vbNewLine & _
"x = " & RealValue & " - " & ImaginaryPart & " i"
Else
x1 = (-b + Sqr(Radical)) / (2 * a)
x2 = (-b - Sqr(Radical)) / (2 * a)
MsgBox "The equation has real roots, they are:" & vbNewLine & _
"x = " & x1 & " (AND)" & vbNewLine & _
"x = " & x2
End If
End Sub

I put in 99999999999999999999 for a, b, and c and it worked fine.

[Jacob Hilderbrand]

And here it is as a function:
[vba]
Option Explicit

Function QuadraticEqn(a As Double, b As Double, c As Double)

Dim x1 As Double
Dim x2 As Double
Dim RealValue As Double
Dim ImaginaryPart As Double
Dim Radical As Double

If a = 0 Then
QuadraticEqn = -b / c
Else
Radical = b ^ 2 - 4 * a * c
If Radical < 0 Then
ImaginaryPart = Sqr(Abs(Radical)) / (2 * a)
RealValue = -b / (2 * a)
QuadraticEqn = RealValue & " + " & ImaginaryPart & " i" & " (Or) " & _
RealValue & " - " & ImaginaryPart & " i"
Else
x1 = (-b + Sqr(Radical)) / (2 * a)
x2 = (-b - Sqr(Radical)) / (2 * a)
QuadraticEqn = x1 & " (Or) " & x2
End If
End If

End Function
[/vba]

[johnske]

Jacob, re your previous...Been trying to work out how to paste an eqn from eqn editor to here, but can't do it...

(kx + j)^2 = k^2*x^2 + 2jkx + J^2....In the quadratic eqn, 'a' refers to K^2 and 'b' refers to 2jk. If a = K^2 = 0, then k is also zero, so 2jkx is zero and the only solution is j = j. However, why wait till after the user has entered all 3 numbers before telling them it's not really a quadratic eqn? Anyway when considered purely as a quadratic eqn the solution is x = infinity (as we're dividing by zero, and x is implicit in the equation).

Gotta go out now

[Jacob Hilderbrand]

Well... If you divide by 2*a and a = 0 then yes the answer is infinity or negative inifity as the case my be; however, since the equation is not quadratic, you cannot use the Quadratic Equation to solve it. So you never divide by 0.

I waited until after all three numbers are inputted because the equation can still be solved with the other coefficients.

Now if a and b are 0 then we have no solutions (i.e., c <> 0) or infinite solutions (i.e., c=0). Of course if a, b, and c = 0 then we really don't have much of an equation.

[johnske]

Hi Jacob,

The procedure was specifically designed to use the quadratic formula to obtain solutions for quadratic equations...so

If the user enters zero for "a" in a quadratic equation, then "b" is automatically zero and the procedure should rightfully terminate there, as the result for this quadratic equation is x = 0/0 = indeterminate.

The procedure should reflect this (correct) result and not allow a user to enter a value for "b" that cannot possibly be the correct value for a quadratic equation. (I know I didn't go all the way with 'VBA' error-handling for this, but terminating for a = 0 was part of the 'mathematical' error-handling procedure)

However, note that if we are to completely disregard the fact that it is not a quadratic equation and allow users to proceed to use the formula to solve for the x in ax^2 + bx + c = 0 then, when a is zero and b is non-zero, the solution for x is, x = -c/b (not -b/c)

Regards,
John

[Jacob Hilderbrand]

x = -c/b (not -b/c)

You're right. That's what I get for not solving the equation by hand.