User Defined Function Not Available In Other Worksheets

J

Joshua

hello all. i've recently built a function that is used to
manipulate a string (basically it pulls the last name out
of a full name....minus any prefixes and suffixes, etc).

the function works fine, and i'm pleased with it.
however, when i try to use the function in a new excel
spreadsheet it isn't recognized. when i place "getlastname
(a1)" in a cell, all i get is #NAME?.

any ideas as to how i might be able to access the function?

thanks in advance!
 
T

Tom Ogilvy

Move the function to a general/standard module. Don't put it in the code
module associated with any sheet. You should always do this.
 
G

Guest

i figured it out (with help from cpearson.com!
thanks)......

basically, i thought i could just call a user defined
function by typing it into a cell.......well, that's not
right. it needs to be typed in as such:

PERSONAL.xls!getlastname(a1)

i wasn't referring to the worksheet where the function
resided.

hope this helps anyone out there that might find
themselves with a similar problem.
 
A

Akshay

the function works fine, and i'm pleased with it.
however, when i try to use the function in a new excel
Click to expand...
spreadsheet it isn't recognized. when i place "getlastname
Click to expand...
(a1)" in a cell, all i get is #NAME?.
Click to expand...

did you put the function in the worksheet itself? or is it part of a module
in the workbook?

Either way, the code is local to each workbook. In order for it to be
available globally, you should create a XLA library file and stick your code
in modules there and then have it loaded automatically when Excel starts.
Then everything will have access to the functions.

-akshay
 
G

Guest

general/standard module? what do you mean exactly? can
you provide me with a step-by-step?? thanks.....i'm hope
i'm not asking for too much.
 
S

steve

When you go to the VBE and view the project you will see:
A list of your projects (workbooks) if you have go to the View menu
and Select Project Explore. This is a tree view just like your
Explore window.
For each Project there are 4 possible 'folders' (you may not see all)
Microsoft Excel Objects - these are the worksheets plus another
for ThisWorkbook. These are 'class' modules designed to hold
event code for the individual object (workbook or sheet).
Modules - this contains the general/standard code modules. This is
where your regular code should go.
Forms - if you build forms, this is where they will be stored.
Class - this is a special form of code. (I don't know enough about
this stuff and don't expect you to get involved with these
anytime
soon - but some day!!!)
 
G

Guest

unfortunately, i'm just not seeing the light! hahaha.

i have opened up my VBE, and i can see the "tree" that you
referred to......i see a tree like this:

AutoSave.XLS
VBAProject(Import.xls)
VBAProject(PERSONAL.xls)

when i click on VBAProject(Import.xls), which is the what
i'm currently working on, i see the branches that you
referred to including Modules.

i have my code for the user defined function i built
stored there in the Module under VBAProject(Import.xls),
and i can use the function in this particular
project......but i am unable to use it in other projects.

where do i need to move it so that it will be globally
available? thank you. sorry if i'm becoming a pain.
 
S

steve

Ahhhh! So you want to use it in other projects....

In the other projects you either need to preface the function with the
workbook name... or put the function in an Add-In.

To make an add-in.
Start with a new workbook.
Do what you want.
When you save it - Save as Excel Add-In - not as Excel Workbook
You can save it anywhere on your drive. But there is an Add-Ins
folder in Office... (I have add-ins all over the place)

Then go to the Tools menu, select Add-Ins and add it to your list.
Now it will load everytime you open Excel and be available to all
workbooks (without needing a workbook reference).

And the 'pain' you feel is called 'growing 'pain'...
 
J

Josh in Tampa

thank you very much!!! that helped out alot!
-----Original Message-----
Ahhhh! So you want to use it in other projects....

In the other projects you either need to preface the function with the
workbook name... or put the function in an Add-In.

To make an add-in.
Start with a new workbook.
Do what you want.
When you save it - Save as Excel Add-In - not as Excel Workbook
You can save it anywhere on your drive. But there is an Add-Ins
folder in Office... (I have add-ins all over the place)

Then go to the Tools menu, select Add-Ins and add it to your list.
Now it will load everytime you open Excel and be available to all
workbooks (without needing a workbook reference).

And the 'pain' you feel is called 'growing 'pain'...

--
sb



.
Click to expand...
 
Top