How come function won't work from personal.xls? 2

[KellyStee]

I created a simple function that works fine if the function's code is in a module of the workbook. However, I need to be able to use it in all workbooks, so I moved the code to module 1 of personal.xls, but there the function doesn't work. It's as if the function doesn't even exist. What am I doing wrong? This is probably a stupid issue I'm having; I never have fully grasped the concept of objects and modules and where code should actually go. . .(

Thanks in advance!

 

[Zathras]

Have you qualified any references with "ThisWorkbook"? If so, that would be a problem since in the context of personal.xls, ThisWorkbook refers to personal.xls.

If not, perhaps you can post the code here so someone can take a look at it...

 

[KellyStee]

No references. . .it really is the easiest function (I think, but what do I know?).
Function Charge(C, F, G)
Select Case G
Case Is < 5
Charge = C / 50 + F / 12 - F / 5
Case Else
Charge = C / 50 + F / 12 - 0.8
End Select
End Function

 

[Zathras]

See thread68-503916 and follow the steps outlined by PeterMoran.

If you are on a network, there are other issues to consider. Is there already a personal.xls on the network? If so, can you modify it? If not, is it ok to create one?

I am on a network here. There is no personal.xls on the network, so I created one using Peter's record macro technique and then moved it to my local hard drive. I then went into Tools/Options/General and pointed the Alternate startup file location to that folder. Seems to work just fine. When I start Excel, the personal.xls is also loaded and hidden. Side benefit - no security prompting for macros.

 

[KellyStee]

UGH! I have a personal.xls file on my hard drive saved to C:\Documents and Settings\[username]\Application Data\Microsoft\Excel\XLSTART that is loaded when I opened Excel. On a whim, I noticed in another post that maybe it should be saved in another spot (C:\Program Files\Microsoft Office\Office\XLStart), so I saved it there and put the path in the Alternate Startup File field as you mentioned, but to no avail.
I read through Peter's message and followed all the steps there, but no luck with that either. Am I right in that in order to save a FUNCTION to personal.xls, I click on the plus sign beside the name VBAProject(PERSONAL.XLS) and double-clink on Module1 under the Modules subfolder, and copy my function code there? Or should I be sticking it somewhere else?

 

[Zathras]

Sounds like you're doing it right.

In Excel, if you select Insert/Function... from the menu can you scroll down to &quot;User Defined&quot; in the &quot;Function Category&quot; box?

Are you trying to type

   =Charge(...

directly in the cell instead of

   =PERSONAL.XLS!Charge(...

?

 

[KellyStee]

oooooooooooooooooooooooo!! BINGO!! I didn't know you could insert the function from the drop-down menu. That was the problem: I needed to type =personal.xls!charge(. . .
When the macro was embedded directly into the workbook, I just had to type = charge(. . .

THANKS!!

 

[KellyStee]

BTW, is there a way not to have to type the personal.xls! each time?? that's so annoying!

 

[Zathras]

AFAIK, no.

Easiest way is to copy/paste (after entering the first one).

Shift-F3 is a keyboard way to get the Insert/Function dialog but that's not much help either, unfortunately.

 

[zarney]

>In Excel, if you select Insert/Function... from the menu >can you scroll down to "User Defined" in the "Function >Category" box?

No!

Trying to learn here...I created a simple function in the Sheet1 code window in the VBA editor named multipply (with two pp's):

Function Multipply(a, b)
Multipply = a * b
End Function

But when I select Insert/Function there is no "User Defined" listed in the "Function Category" list. If I enter =Multipply(2,2) directly into a cell I get #Name? when I click on the error icon it indicates Invalid Name Error.

I am using 2003. I have a couple of addins, if I go to Tools/Add-Ins and make an addin available (Simplify for Excel) the "User Defined" choice appears in the Insert/Function list. But my multipply function is still not there. I am puzzled!

Any suggestions appreciated! Thank you.

 

[zarney]

I should have mentioned that the code is NOT in personal.xls, it is in book1/sheet1 the courrent workbook/sheet

 

[PHV]

Create your function in a brand new standard code module instead of a sheet code window:
menu Insert -> Module

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[zarney]

Thank you very much PHV, works like a charm!