using a function

1. using a function

The problem in a nutshell is as follows:

Sub Macro2()
Dim xxx As Double
xxx = 0.05
Call NewFuntio(xxx)
End Sub

Sub NewFuntio(temp)
Dim t1,t2 As Double
t1 = (temp + 1) ^ (1 / 12)
t2 = 1.05^(1/12)
End Sub

t1 = 1.00407412390531
t2 = 1.00407412378365

Why is the function giving the wrong answer?

Thanks

2. Re: using a function

Jeff,

t1 and t2 are identical for me - perhaps try dimensioning better....

Sub Macro2()
Dim xxx As Double
xxx = 0.05
Call NewFuntio(xxx)
End Sub

Sub NewFuntio(temp)
Dim t1 As Double
Dim t2 As Double
t1 = (temp + 1) ^ (1 / 12)
t2 = 1.05 ^ (1 / 12)
MsgBox t1 & Chr(10) & t2

End Sub

HTH,
Bernie
MS Excel MVP

"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:D65AC26E-2128-45E2-A5C1-50AE97658B8D@microsoft.com...
>
> The problem in a nutshell is as follows:
>
> Sub Macro2()
> Dim xxx As Double
> xxx = 0.05
> Call NewFuntio(xxx)
> End Sub
>
> Sub NewFuntio(temp)
> Dim t1,t2 As Double
> t1 = (temp + 1) ^ (1 / 12)
> t2 = 1.05^(1/12)
> End Sub
>
> t1 = 1.00407412390531
> t2 = 1.00407412378365
>
> t1 is the wrong answer and t2 is the correct answer.
>
> Why is the function giving the wrong answer?
>
> Thanks
>

3. Re: using a function

When you declare two variables like that

Dim t1, t2 as Double

you are not declaring two doubles as you think, but one variant, one double.

Use

Sub Macro2()
Dim xxx As Double
xxx = 0.05
Call NewFuntio(xxx)
End Sub

Sub NewFuntio(temp)
Dim t1 As Double, t2 As Double
t1 = (temp + 1) ^ (1 / 12)
t2 = 1.05 ^ (1 / 12)
End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:D65AC26E-2128-45E2-A5C1-50AE97658B8D@microsoft.com...
>
> The problem in a nutshell is as follows:
>
> Sub Macro2()
> Dim xxx As Double
> xxx = 0.05
> Call NewFuntio(xxx)
> End Sub
>
> Sub NewFuntio(temp)
> Dim t1,t2 As Double
> t1 = (temp + 1) ^ (1 / 12)
> t2 = 1.05^(1/12)
> End Sub
>
> t1 = 1.00407412390531
> t2 = 1.00407412378365
>
> t1 is the wrong answer and t2 is the correct answer.
>
> Why is the function giving the wrong answer?
>
> Thanks
>

4. RE: using a function

"Jeff" wrote:
> t1 = 1.00407412390531
> t2 = 1.00407412378365
> t1 is the wrong answer and t2 is the correct answer.
> Why is the function giving the wrong answer?

First, you should identify the version of Excel that you are using. I see
no difference with my version of Excel, Office Excel 2003, at least when I
use msgbox to look at t1 and t2.

> Sub NewFuntio(temp)
> Dim t1,t2 As Double
> t1 = (temp + 1) ^ (1 / 12)
> t2 = 1.05^(1/12)

Try changing the "dim" declaration to:

dim t1 as double, t2 as double

which I suspect is your intent.

As you have written, t1 is declared a variant, not a double. I suspect that
in your version of Excel, variants that contain floating point numbers are
treated as "single", double. I was unable to duplicate your results exactly
by forcing t1 to be type single, but that might be because I am not being
careful with the type of intermediate computations. Nonetheless, I do get a
difference when I store t1 into a double (t3).

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1