User Defined Function

  • Thread starter Thread starter Sandro
  • Start date Start date
S

Sandro

In creating a Macro or User Defined Function is it
possible to make that function generally available to all
worksheets I create without having to copy the same
macro / function to all new files?

Thanks in advance.
 
Hi Sandro!

Use your Personal.xls file for this.

It’s handy to have the Personal.xls in existence ready for use
immediately you want it.

First establish that you don’t already have one (under the current
user log-in if appropriate):

Window > Unhide [check there isn’t a PERSONAL.XLS entry]
Cancel

Open a new workbook [Just so you don’t damage anything you have]
Tools > Macro > Record New Macro [Up pops the Record Macro dialog]
Click dropdown arrow for “Store Macro In:”
Select “Personal Macro Workbook”
OK
Tools > Macro > Stop Recording

If you now open the Visual Basic Editor you will find that you now
have a Personal.xls file:

Alt + F11 [Opens the Visual Basic Editor]

In top left box you’ll see, possibly among other listings, an entry:

Personal (PERSONAL.XLS)

Expand the tree and you’ll find under Modules that you have a Module1
that contains whatever you recorded between pressing OK in the above
procedure and stopping the recording process.

Select the Personal(PERSONAL.XLS) entry
File > Save

You now have a saved Personal.xls file and you can check that it now
exists using the Window > Unhide > Cancel process.
 
Thanks Norman,

I've tried a quick check and it appears to work. I'm going
to experiment further with that.
Also does this allow similar user defined 'Functions' to
be automatically loaded in the 'Fx'(Paste Function) drop
Down list using the same method?

Sandro
Australia
 
Hi Sandro!

Functions in your Personal.xls file will star in the User Defined
category of the Paste Function dialog.

You can insert them into their own category (eg Financial) but that's
probably best left to when you create an Addin. Talking of which, if
you have a lot of user defined functions you might investigate putting
them all in an Addin. This allows you to take them to another
computer.
 
Thanks again Norman.

I will start using the basic feature before I use ADDINs.
However, I have noticed that when using the function I
must have the Personal.XLS file open to enable that
UD.Function - Is that correct? That is, the function does
not operate unless I re-establish the link to the
Personal.XLS file.
Is this where ADDINs become appropriate?

Regards,
Sandro
 
Hi Sandro!

The beauty of the Personal.xls file is that it is opened as a hidden
file every time that you open Excel.

If you have created it, just close Excel and re-open. Then use:

Windows > Unhide
Cancel

You'll see it sitting there.
 
Hello Norman,

Every time I close XL and reopen it, the Personal.XLS file
auto-opens as a 'visible' file, irrespective of the last
file I opened. Then upon closing the Personal.xls
file, 'Unhide' under 'Window' is disabled. Does the above
operate for XL97 or is there a setting I should be
applying?

Regards,
Sandro
 
Hi Sandro!

Use:

Select the Personal.xls
Windows > Hide

Now close Excel. You'll get a message asking if you want to save
changes to the Personal.xls file. Click "Yes".

That should make the Personal.xls open in hidden form.
 
Sandro, just to expand on a point Norman made earlier. If someone else opens
any of these files then those functions will not work for them as they are
stored in YOUR personal.xls on YOUR computer only, so if you are sharing these
files with anyone else, you may actually want to store them in that file, or go
the addin route as suggested.

Think about what happens if you leave that job and someone else takes over -
Your personal.xls is tied to your userid if you are on a network, so even if
someone else logs onto that PC, if they are under a different logon then these
functions will not be available to them. Even on XP, if you have different
accounts then you will have the same issue. The data should stay intact when
they open the file, but if they try and use the same function then they will get
prompted with dialog boxes looking for a file containing that function to link
to. May not be an issue, but just so you are aware.
 
Back
Top