Formula not being called from personal.xls
 

Hi there folks.
I am trying to add a formula to my personal.xls file for use in all
workbooks.
Here it is:-

' Interpolate values

Function ITP(tgtwt, lwt1, Lwt2, yield1, Yield2)
diffa = (Lwt2 - lwt1)
diffb = (tgtwt - lwt1)
facta = diffb / diffa
diffc = Yield2 - yield1
factb = diffc * facta
ITP = yield1 + factb
End Function

Nice and simple.
It works a treat within the personal.xls file but when I open a fresh
workbook it doesn't! I get a #NAME error.
If I then unhide personal.xls and try it in the personal file it's
fine.

It's really confusing me and any help would really be appreciated!

It's Excel 2003 running on Windows XP (SP2)

Thanks very much,

Wullie

Precede the macro name with Personal.xls!

--
HTH

Bob Phillips

wrote in message
oups.com...
Hi there folks.
I am trying to add a formula to my personal.xls file for use in all
workbooks.
Here it is:-

' Interpolate values

Function ITP(tgtwt, lwt1, Lwt2, yield1, Yield2)
diffa = (Lwt2 - lwt1)
diffb = (tgtwt - lwt1)
facta = diffb / diffa
diffc = Yield2 - yield1
factb = diffc * facta
ITP = yield1 + factb
End Function

Nice and simple.
It works a treat within the personal.xls file but when I open a fresh
workbook it doesn't! I get a #NAME error.
If I then unhide personal.xls and try it in the personal file it's
fine.

It's really confusing me and any help would really be appreciated!

It's Excel 2003 running on Windows XP (SP2)

Thanks very much,

Wullie

It works a treat within the personal.xls file but when I open a fresh
workbook it doesn't! I get a #NAME error.
...any help would really be appreciated!

Hi. Just to add. Another debugging technique is to go to your worksheet,
and hit the "Insert Function" button ("Fx") located next to the formula bar.
From the Category list, select "User Defined" By inserting your formula
from here, you will notice that your formula is preceded by "Personal.xls!".
If you don't see your function at all in this list, it may indicate another
problem. It usually means you placed the formula on a worksheet module, so
it won't run anyway.

Just to add...Since your formula works, I would assume you do not have the
option "Require Variable Declaration" turned on.
It's "usually" a good technique to turn this on. If interested, go to the
vba editor, and do Tools | Options | Editor tab | and check on "Require
Variable Declaration"

The disadvantage of course is that your formula is a little longer since you
have to use Dim statements...

Function ITP(tgtwt, lwt1, Lwt2, yield1, Yield2)

Dim diffa
Dim diffb
Dim facta
Dim diffc
....etc
diffa = (Lwt2 - lwt1)
diffb = (tgtwt - lwt1)
....etc

You could write it like this, with the last variable in the formula
purposely misspelled. The program will now catch the misspelling.

Function ITP(tgtwt, lwt1, Lwt2, yield1, Yield2)
ITP = yield1 + ((tgtwt - lwt1) * (Yield2 - yield1)) / (Lwt2 - lwt1l)
'<--
End Function


Just another option, but this may be a little slower...

Function ITP(tgtwt, lwt1, Lwt2, yield1, Yield2)
With WorksheetFunction
ITP = .Forecast(tgtwt, Array(yield1, Yield2), Array(lwt1, Lwt2))
End With
End Function

--
Dana DeLouis
Win XP & Office 2003


wrote in message
oups.com...
Hi there folks.
I am trying to add a formula to my personal.xls file for use in all
workbooks.
Here it is:-

' Interpolate values

Function ITP(tgtwt, lwt1, Lwt2, yield1, Yield2)
diffa = (Lwt2 - lwt1)
diffb = (tgtwt - lwt1)
facta = diffb / diffa
diffc = Yield2 - yield1
factb = diffc * facta
ITP = yield1 + factb
End Function

Nice and simple.
It works a treat within the personal.xls file but when I open a fresh
workbook it doesn't! I get a #NAME error.
If I then unhide personal.xls and try it in the personal file it's
fine.

It's really confusing me and any help would really be appreciated!

It's Excel 2003 running on Windows XP (SP2)

Thanks very much,

Wullie

Bob Phillips wrote:
Precede the macro name with Personal.xls!

--
HTH

Bob Phillips


Hi Bob,
That works really well but I still have a question.
Why do I now need to prefix with personal.xls!?
I'm 100% sure I never had to do that when running on Windows 2000. It's
only since I upgraded to WIndows XP.
Is it another upgrade "quirk"?

Thanks for all the help guys. It is very much appreciated!

Wullie

It's always been that way that I recall, although I must admit to never
having Windows 2000.

--
HTH

Bob Phillips

wrote in message
oups.com...

Bob Phillips wrote:
Precede the macro name with Personal.xls!

--
HTH

Bob Phillips


Hi Bob,
That works really well but I still have a question.
Why do I now need to prefix with personal.xls!?
I'm 100% sure I never had to do that when running on Windows 2000. It's
only since I upgraded to WIndows XP.
Is it another upgrade "quirk"?

Thanks for all the help guys. It is very much appreciated!

Wullie

If you saved your personal.xl* file as an addin (*.xla), then you wouldn't need
to specify the workbook name--the personal.xla file would still have to be open,
though.

Or if you added a reference to personal.xls (.xls) to the other workbook's
project, you wouldn't need the workbook name, either.

(But if you didn't do either, I'd bet a faulty memory <bg.)

wrote:

Bob Phillips wrote:
Precede the macro name with Personal.xls!

--
HTH

Bob Phillips

Hi Bob,
That works really well but I still have a question.
Why do I now need to prefix with personal.xls!?
I'm 100% sure I never had to do that when running on Windows 2000. It's
only since I upgraded to WIndows XP.
Is it another upgrade "quirk"?

Thanks for all the help guys. It is very much appreciated!

Wullie

--

Dave Peterson