How do I package up a VB Excel macro??

londonman81

Hi all,


I've written a series of VB Excel macros in one Excel workbook.

I would like to use the same macros on different workbooks in future.

So I need to know how I can package up the macros and have them sit somewhere independent of the workbook they are in now. I do not known of a way to take the code and put it somewhere outside all workbooks.

I'm new to VB Excel - so apologies for the novice question.

Thanks!

criminal_smile

have a quick look at THIS FORUM , as they seem to have discussed exactly what you are looking for.

londonman81

criminal_smile wrote:

have a quick look at THIS FORUM , as they seem to have discussed exactly what you are looking for.

It looks like the question in that forum is slightly different to mine......

Just to make mine clearer - Im not looking to make a stand alone program out of my macro - I just want it to run on other workbooks within Excel.

As usual - the simplest things seem to be impossible - and I've look in all the supposedly good books/websites .....they dont seem to cover such basic things......


It must surely be possible to package up a macro so it can be used on other workbooks in future?!

DVB_3

After you have created your macro(s), open it (them) in Visual Basic Editor. From there (File menu) you can both export and import them.

feival

You simply need to save the macros into your personal.xls which is the template spreadsheet that opens when you start a blank spreadsheet. This is the same file that stores your custom tool bars as well as personal macros.

Chippy_Minton

londonman81 wrote:

It looks like the question in that forum is slightly different to mine......

Just to make mine clearer - Im not looking to make a stand alone program out of my macro - I just want it to run on other workbooks within Excel.

Nope, the clue is in the 2nd reply in londonman81's link. You'll need the Visual Basic IDE to create and compile a .DLL containing your functions. Then in the Excel VBA editor you reference your .DLL by clicking Tools - References - Browse to your .DLL.

Now I guess your .DLL functions could contain just 'pure' VB code or reference Excel objects like Worksheet, Cell etc.

For the former you might have a function in your DLL called AddTwoCells(cell1 as integer, cell2 as integer). This would be called in your Excel VBA using AddTwoCells("A1".Value, "A2".Value).

For the latter, it might be AddTwoCells(cell1 as Excel.Cell, cell2 as Excel.Cell) and your Excel VBA would pass the cell object as the function arguments: AddTwoCells("A1","A2"). Your VB .DLL can then access the Excel cell properties as required.

I've forgotten the Excel syntax and object names for referencing a cell or its value, but hope the above description of the 2 techniques makes sense.