Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

make module available everyone

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Make a module available to everyone

Make a module available to everyone

ResolvedVersion 2003

Sarah has attended:
Excel VBA Intro Intermediate course
Access Advanced course
Access Intermediate course

Make a module available to everyone

A simple question which I've forgotten the answer to - what do I type to make a module available to all, not just that worksheet/project?

RE: Make a module available to everyone

Hi Sarah,

Thank you for your question.

You need to save the workbook as an Add-In via the File Save As command and save it to a network drive where everyone has access to.

Your colleagues then need to open Excel and go to Tools, Add-In's and then Browse for the add-in file and select it.

I hope this helps.

Regards

Simon

RE: Make a module available to everyone

Isn't there some coding to do with 'public' or 'explicit' or something like that, that goes at the top of the page?

RE: Make a module available to everyone

Hi Sarah,

Thank you for your response.

Do you mean you want the contents of the module available to other modules you have written?

If so you need to have option explicit at the top of the code which forces you to declare your variables as either private or public and declare what data they will hold such as string or integer.

Declaring variables or procedures as public means you can call and use them in all the other modules in your Project Explorer window. If you declare your variables as private(DIM) then those variables can only be called from the current module.

The answer I gave you relates to sharing the code you created with other people in your organisation. As long as you have declared your code correctly you can use this method to then share the module of code with colleagues:

1. Right click on the Module name in the Project Explorer window and choose Export File.

2. Give the file a name and the file extension will be .bas

3. Save the file on a network resource where everyone has access to it.

4. Each of your colleagues then need to open the Visual Basic Editor window(ALT+F8) and choose File and Import File.

5. Navigate to the location of the .bas file, select it and click OK.

The module of code is then copied into the Modules folder of your current workbook. This code can then be copied into the Personal macro Workbook meaning it is available to run on any file you create or open.

I hope this answers your question.

Regards

Simon

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Viewing two Excel 2010 Workbooks at the same time

Did you know you can view two Excel 2010 workbooks side by side? Very useful when comparing data without constantly having to go back and forth!

1) Open both Excel workbooks
2) Select Window then select Compare Side by Side with (Spreadsheet 2)
3) When you have finished, select Window again and click Close Side by Side

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.