Using a module in all Excel worksheets??

[k8277]

I created a function inside a module. Is there a way to make this module available to all worksheets that I might create/modify.

The only way I have found so far is to export the module and then import the module into every worksheet that I want to use the function.

 

[Molby]

Save it into your Personal Workbook. It will be available in every workbook then.

If you haven't got a personal workbook, record a macro, in the options Store macro in: Personal workbook. Then just copy your module into there.

 

[xlbo]

You need to create a PERSONAL.xls

Easiest way to do this is to record a very simple macro but in the options set it to save in Personal.xls rather than Active/This workbook

This will create the Personal.xls file for you and you can then just copy / paste your function into that - it will then be available to ALL workbooks.



Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[k8277]

Ok, I created the personal.xls and I copied my module into it. Is there something I have to do to make it available or do I have to call my function and reference that it is in personal.xls in some fashion.

When I call my function, I just get #NAME.

When I view macros, I now see the personal.xls as an option, along with the dummy macro I created.

 

[mbarron]

You may want to look at this thread. thread68-825508


Mike

 

[k8277]

Did you mean to copy the funtion into the macro or into the personal.xls modules?

 

[k8277]

I ended up using the Add-In, however I was able to reference the module by using =Personal.Xls!FunctionName()

Thanks for the help.

 

[Eugene99]

K8277,

You can put a custom button in your tool bar and then assign a macro to this new button. That way, your macro in your personal.xls will run in any workbook that you have open. I use it all the time.

Right click in the tool bar area at the top of Excel, choose Customize, drag a button from the Customize dialog box, right click on your new button, and scroll down to Assign Macro. Works every time...

Hope this helps...
Eugene99

 

[mbarron]

Eugene99,

K8277 wants to be able to readily access a custom made function, not a marco. You can't assign a function to a macro button.



Mike