User Defined Function Not Available In Other Worksheets

  • Thread starter Thread starter Joshua
  • Start date Start date
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!
 
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.
 
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.
 
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
 
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.
 
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!!!)
 
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.
 
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'...
 
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...
 
Back
Top