bizarre "invalid procedure call" error

[PatFinegan]

Sub Test1 below generates an "invalid procedure call or argument" erro
(#5) in Excel 97 and Excel 2003 on all of my PCs. Sub Test2 does not
Can anyone replicate this? If so, can anyone explain the problem, o
provide a workaround other than writing the variables to cells an
performing the calculations there? Thanks in advance.

Sub Test1()
Dim a As Double, b As Double, c As Double
a = -0.1
b = 0.05
c = a ^ b
End Sub

Sub Test2()
Dim c as Double
c = -0.1 ^ 0.05
End Su

 

[PatFinegan]

Just to clarify, the problem arises whether or not the answer is a
imaginary number. So, for example, -1 = -1 ^ (1/3) involves n
imaginary numbers, but will generate a runtime error when I assign -
and 1/3 to separate variables.

Sub test()
Dim a As Double, b As Double, c As Double
c = -1 ^ (1 / 3) 'Performs fine.
a = -1
b = 1 / 3
c = a ^ b 'Crashes.
End Su

 

[Don Guillett]

Can't tell you why but the problem was with the -
This worked

Sub Test1()
Dim a As Double, b As Double, c As Double
a = 0.1
b = 0.05
c = -a ^ b
MsgBox c
End Sub

 

[kkknie]

Not sure how do a FRACTIONAL exponent for any negative number without
getting an imaginary result. What the code you posted is doing is the
following:

c = -1 ^ (1 / 3) 'Performs fine.

First take 1/3 to get 0.333333...
Then raise 1 to the 0.333333 to get 1
Then negate it.

You can duplicate the error if you change your formula to read:

c = (-1) ^ (1 / 3) 'Error

K

 

[Nick Hodge]

Pat

Replicate...yes...reason no.... It appears to be the - sign in the
variable. remove the sign and all is well. Negate it later and all is well
too

Sub Test1()
Dim a As Double, b As Double, c As Double
a = 0.1
b = 0.05
c = -(a ^ b)
Debug.Print c
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[PatFinegan]

Thanks, kkknie, but a fractional root whose reciprocal is an odd intege
(as opposed to an even integer) should not generate an error on
negative number.

For example: -8 = -2 ^ 3
Therefore: -2 = -8 ^ (1/3)

I added an error handler to my second post, showing how Excel handle
the equation correctly on a worksheet, but not in VBA

 

[PatFinegan]

Thanks, Nick, for the simple workaround. Strange discrepency betwee
Excel and VBA, though

 

[Tom Ogilvy]

it appears to be a precedence/order of operations problem. If you put
parens around -0.1 you get the same problem:

Sub Test2()
Dim c As Double
c = (-0.1) ^ 0.05
End Sub


Exponention appears to have a higher precedence than negation.

 

[Tom Ogilvy]

From help for precedence

Arithmetic
Exponentiation (^)
Negation (-)
Multiplication and division (*, /)
Integer division (\)
Modulus arithmetic (Mod)
Addition and subtraction (+, -)
String concatenation (&)



--
Regards,
Tom Ogilvy

it appears to be a precedence/order of operations problem. If you put
parens around -0.1 you get the same problem:

Sub Test2()
Dim c As Double
c = (-0.1) ^ 0.05
End Sub


Exponention appears to have a higher precedence than negation.

 

[Tom Ogilvy]

if you put -1 (A1) in one cell and .05 (a2) in another

=A1^A2

raises an error as well.

=-1^0.5 raises an error as well

---------------------------------------

In a worksheet, the order of operations is different:

: (colon)
(single space)

, (comma)
Reference operators
- Negation (as in -1)
% Percent
^ Exponentiation
* and / Multiplication and division
+ and - Addition and subtraction
& Connects two strings of text (concatenation)
= < > <= >= <> Comparison

 

[PatFinegan]

Dear Tom,

Thanks for pointing out the difference between orders of operation i
VBA and Excel.

However, to clarify, my first post c= -1^.5 should indeed hav
generated an error because the square root of a negative number i
imaginary. I immediately regretted posting it because it cluttered tw
very distinct issues -- the handling of imaginary numbers and
computational inconsistency between Excel and VBA.

That's why I added a second equation, c = -1 ^ (1/3), because the cub
root of a negative number is not imaginary and should therefore no
result in an error. But as I wrote, the equation worked fine in a
Excel spreadsheet but bombed in VBA. It is that latter computationa
discrepency that I find troubling, since the equation is definitel
"solvable". Sorry if that wasn't clear

 

[Tom Ogilvy]

Just because the equation is solvable doesn't mean it doesn't deal in the
realm of imaginary numbers. Since only a subset of roots of negative real
numbers would result in non-imaginary numbers, I think it is clear that the
authors of VBA have chosen to error on such a construct. While this may be
inconsistent with Excel, there is no reason to expect them to be the same in
this gray area - VBA is a separate product that has been coupled with Excel.
They don't share common code.