Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell reference in a formula is called | Excel Discussion (Misc queries) | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
dragging a formula | Excel Discussion (Misc queries) | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions |