PDA

View Full Version : [SOLVED] Array Base



mdmackillop
11-09-2004, 04:04 PM
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? :confused:
MD

mdmackillop
11-09-2004, 04:30 PM
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
11-09-2004, 05:26 PM
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 :bink:


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
11-09-2004, 05:45 PM
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
11-09-2004, 06:13 PM
Hi MD,

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



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
11-09-2004, 06:26 PM
OOPS!! Replace " e" with " i" above - will edit it now :bink:

Jacob Hilderbrand
11-09-2004, 06:47 PM
The -b gets divided by 2a as well.

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

mdmackillop
11-09-2004, 06:49 PM
Thanks Jacob

johnske
11-09-2004, 06:54 PM
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 :bink:

johnske
11-09-2004, 07:35 PM
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 :confused: :confused: )... :bink:


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
11-09-2004, 10:26 PM
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 :bink:



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
11-09-2004, 11:10 PM
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
11-09-2004, 11:14 PM
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
11-09-2004, 11:24 PM
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... :bink: i.e.


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

Jacob Hilderbrand
11-09-2004, 11:27 PM
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
11-09-2004, 11:39 PM
And here it is as a function:

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

johnske
11-10-2004, 12:13 AM
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 :bink:

Jacob Hilderbrand
11-10-2004, 01:16 AM
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
11-10-2004, 03:12 AM
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 :bink:

Jacob Hilderbrand
11-10-2004, 03:29 AM
x = -c/b (not -b/c)You're right. That's what I get for not solving the equation by hand.

johnske
11-10-2004, 04:13 AM
BTW jacob, thanx for pointing out that:

Dim x, y, z as Integer is NOT the same as...
Dim x as Integer, Dim y as Integer, Dim z as Integer

(Although I see that with VB.NET they are now the same) :bink:

Jacob Hilderbrand
11-10-2004, 04:38 AM
You would think they should be the same. I mean you only have to write Dim once so why not the data type?

johnske
11-10-2004, 04:51 AM
I always thort that if you wrote (on the same line)

Dim x as Integer, y as Integer, z as Integer why not (instead of repeatedly typing as Integer, as Integer, as Integer,...) shorten it to

Dim x, y, z as Integer

(and, to tell the truth, this is the first time I've got wrong results doing this so I've continued to do it up till now)...Oh well, live and learn :bink:

mdmackillop
11-10-2004, 05:49 AM
Hi John,
You could always use Dim x!, y!, z!
MD

johnske
11-10-2004, 06:43 AM
Hi John,
You could always use Dim x!, y!, z!
MDSo what does the exclamation mark stand for? - As integer? - If so how do you declare (say) a string in shorthand notation? :bink:

mdmackillop
11-10-2004, 06:45 AM
By the way,
I found an answer to my original question.:roll:
MD

mdmackillop
11-10-2004, 06:47 AM
Sorry John, I think I'm wrong with the "!", but "$" works for text, so there must be some other shortcuts out there.
I picked it up from someone elses code. I don't know more than these, or where they can be found.
MD

johnske
11-10-2004, 06:52 AM
Dim x$
I picked it up from someone elses code. I don't know more than these, or where they can be found.
MDMD: Hmmm, very interesting...I'll have to do a search to find out more (I'm a one finger typist, so ANYthing to save typing... :D )

mdmackillop
11-10-2004, 06:53 AM
John, see my edited reply above :blush
x% seems to return an integer value. (trial and error method)

Jacob Hilderbrand
11-10-2004, 07:35 AM
String $
Long &
Double #
Single !
Integer %

johnske
11-10-2004, 07:39 AM
String $
Long &
Double #
Single !
Integer %COOL!! - (Couldn't find anything with a search) :bink:

Ken Puls
11-10-2004, 08:29 AM
Hey guys,

With regards to type declarations, you might find this link (http://mindprod.com/unmain.html) (orginally posted by NateO at Mr.Excel) quite amusing. Specifically, check out the section on "How To Write Unmaintainable Code Tricks In Offbeat Languages"


:D

mdmackillop
11-10-2004, 09:26 AM
You'll find the following if you look at the types noted under the Dimension section of the VBA help.

The ampersand (&) type-declaration character represents a Long.
The percent sign (%) type-declaration character represents an Integer.
The at sign (@) type-declaration character represents Currency.
The exclamation point (!) type-declaration character represents a Single.
The number sign (#) type-declaration character represents the Double.
The dollar sign ($) type-declaration character represents a String.

See also DefType statements

' Variable names beginning with A through K default to Integer.
DefInt A-K
' Variable names beginning with L through Z default to String.
DefStr L-Z
CalcVar = 4 ' Initialize Integer.

mdmackillop
11-10-2004, 11:21 AM
Hi John,
As a fellow OFT, you might find the zip file here useful, if you do much repetitive stuff in Word. http://www.vbaexpress.com/forum/showthread.php?goto=newpost&t=1274
It's still WIP and any suggestions welcome.
MD