#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 252
Default Custom Functions

I have several functions in my personal workbook. Is there a way to call
these functions without using the "PERSONAL.XLS!" tag in front of it?

so instead of using
=PERSONAL.XLS!newfunction(A1)
I would like to be able to use
=newfunction(A1)
without copying the VBA function into the current workbook.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default Custom Functions

Not really.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sloth" wrote in message
...
I have several functions in my personal workbook. Is there a way to call
these functions without using the "PERSONAL.XLS!" tag in front of it?

so instead of using
=PERSONAL.XLS!newfunction(A1)
I would like to be able to use
=newfunction(A1)
without copying the VBA function into the current workbook.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Custom Functions

If you save your personal.xls workbook as an addin (also in XLStart) and move
personal.xls out of XLStart, you can use formulas like this.

But if you have macros (subroutines) in personal.xls that you run via
Tools|macro|macros, you'll have to do more work.

Either give the user a nice interface to get to the macros--on the menubar or a
toolbar????

Or break apart your personal.xls into (at least) two files.

One with the UDF's and one with the Subs.

You can keep them both in your XLStart folder.

(I put all my stuff into personal.xla and add to the worksheet menubar.)

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

Sloth wrote:

I have several functions in my personal workbook. Is there a way to call
these functions without using the "PERSONAL.XLS!" tag in front of it?

so instead of using
=PERSONAL.XLS!newfunction(A1)
I would like to be able to use
=newfunction(A1)
without copying the VBA function into the current workbook.


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Custom Functions

Sloth

Not possible.

What works best is to save your Personal.xls as an Add-in and load it through
ToolsAdd-ins.

Then you won't have to precede the UDF with a workbook name.

Just enter as =newfunction(A1)


Gord Dibben MS Excel MVP

On Tue, 25 Jul 2006 07:17:02 -0700, Sloth
wrote:

I have several functions in my personal workbook. Is there a way to call
these functions without using the "PERSONAL.XLS!" tag in front of it?

so instead of using
=PERSONAL.XLS!newfunction(A1)
I would like to be able to use
=newfunction(A1)
without copying the VBA function into the current workbook.


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Custom Functions

Another way (I wouldn't use it as a general solution, though) is to go into the
VBE and use Tools|references to that personal.xls workbook.

But this would require that every workbook that you want to use these functions
(without the workbook name) would have to have a reference to this
personal.xls's project.



Sloth wrote:

I have several functions in my personal workbook. Is there a way to call
these functions without using the "PERSONAL.XLS!" tag in front of it?

so instead of using
=PERSONAL.XLS!newfunction(A1)
I would like to be able to use
=newfunction(A1)
without copying the VBA function into the current workbook.


--

Dave Peterson


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 252
Default Custom Functions

Thanks. I geuss you knew what I wanted even though I didn't know how to ask
for it.

I ended up copying the module that contained the functions to a new file and
saved that file as an addin. I then removed the module from the personal
workbook. That way I can still use the macros in personal (I have already
setup a toolbar for them). The end result is what I wanted. Are there any
problems I might run into doing it this way?

"Dave Peterson" wrote:

If you save your personal.xls workbook as an addin (also in XLStart) and move
personal.xls out of XLStart, you can use formulas like this.

But if you have macros (subroutines) in personal.xls that you run via
Tools|macro|macros, you'll have to do more work.

Either give the user a nice interface to get to the macros--on the menubar or a
toolbar????

Or break apart your personal.xls into (at least) two files.

One with the UDF's and one with the Subs.

You can keep them both in your XLStart folder.

(I put all my stuff into personal.xla and add to the worksheet menubar.)

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

Sloth wrote:

I have several functions in my personal workbook. Is there a way to call
these functions without using the "PERSONAL.XLS!" tag in front of it?

so instead of using
=PERSONAL.XLS!newfunction(A1)
I would like to be able to use
=newfunction(A1)
without copying the VBA function into the current workbook.


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom Format Cell nastech Excel Discussion (Misc queries) 13 April 28th 06 08:31 PM
Custom Functions saved as addin dbutcher Excel Worksheet Functions 2 April 7th 06 05:24 PM
How do I add custom colors to pallet Scott Setting up and Configuration of Excel 1 January 19th 06 02:09 AM
Custom Views in Shared workbook Jo Winchester Excel Discussion (Misc queries) 1 May 25th 05 05:58 PM
Force refresh of custom functions donesquire Excel Worksheet Functions 5 May 11th 05 07:36 PM


All times are GMT +1. The time now is 04:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"