Macros in Excel (version 2002 if I read right "About.." in the Help menu).

Just completed an application using VBA in Excel with an user form and a custom toolbar involved. The application, "Frame.xls" was saved with two other names. Those copies were erased thus not available.

Trying to activate any macro raises a warning that the copy with the macros is not found. (One of the erased ones is given!!)

I learnt this morning of .xla but I found no way to assign, once for all the macros in "Frame.xla" to my buttons and control. The 3 options given in the menu they do not allow to even search, much less write, "Frame.xla".

I want this application to be run in two or three more computers with no furthe hassle.

Besides the problem above and thinking ahead, even if a overcome all this: what is the right way to "distribute" my application to those PCs with no further hassle other than enabling macros every time I start Excel?

 

I handle financial systems for my company so I create a lot of macros for people. In most cases, I just distribute the macro as part of the workbook, meaning everyone has their own local copy of the code. I recommend kicking off your macros using ActiveX controls instead of form controls, as form controls will keep their current reference even if their container is moved (i.e., if you have a form button that activates a macro in the current workbook, and you copy the sheet to another book, the button will still refer to the original workbook. ActiveX controls don't have this problem). The disadvantage of distributing macros this way, of course, is that code changes require new workbooks to be redistributed to everyone, and that may annoy users if they've customized their copy of the workbook.

And xla (Excel add-in) file can be created. Once the add-in is added by the user, you can programatically refer to the objects in the xla file (just like adding a VBA reference to a COM+ DLL gives you access to that DLL's object model). If you use ActiveX controls, you don't have to worry about "assigning" the macro to the add-in code, you just call the add-in's code using VBA (your local workbook just contains a wrapper sub that does nothing except call the correct sub in the add-in).

 

I recommend kicking off your macros using ActiveX controls instead of form controls, as form controls will keep their current reference even if their container is moved (i.e., if you have a form button that activates a macro in the current workbook, and you copy the sheet to another book, the button will still refer to the original workbook. ActiveX controls don't have this problem). The disadvantage of distributing macros this way, of course, is that code changes require new workbooks to be redistributed to everyone, and that may annoy users if they've customized their copy of the workbook.

Yes, my current version of the application, go figure, is asking for a file that is not available anymore. Horrible.

And xla (Excel add-in) file can be created. Once the add-in is added by the user, you can programatically refer to the objects in the xla file (just like adding a VBA reference to a COM+ DLL gives you access to that DLL's object model). If you use ActiveX controls, you don't have to worry about "assigning" the macro to the add-in code, you just call the add-in's code using VBA (your local workbook just contains a wrapper sub that does nothing except call the correct sub in the add-in).

Just learnt about .xla and saved "Frames.xls" as "Frame.xla". I hope it is the right way. Is it?

If so, so far so good. How to tell Excel that all the macros inside "Frames.xls" should refer to "Frames.xla"?

What if in the future I create "frames and shapes. xls" and want to make reference to any other "XXX.xla"?

Could you give me the details because it seems that completing this will take me another three months from the little time I have after my demanding job.

Just in case, please note that the sole solution I see now is to reassign those macros (more than 80). Yes, I made intensive use of controls and buttons in a custom toolbar.

Thanks for replying.

 

To add macros from your add-in, you have to add a VBA reference to your add-in from your xls workbook. Open the xls file, go to the VBA editor, Tools->References, Browse, and under the "Files of type" dropdown select "Microsoft Excel Files", and add your xla file. Once that's done, you'll be able to see your add-ins public members in the object browser and refer them in your code. Remember that only members declared as public will show up.

 

I did try getting the reference but Excel refused to take it allegging similarity in names already in my current .xls.

Public Subs in the .xla cannot have same names as Public Subs in the current .xls?

Pity is that I cannot test this extensively so I need kind of a "recipe" to go away with it.

Gracias for your time.

 

I did try getting the reference but Excel refused to take it allegging similarity in names already in my current .xls.

Public Subs in the .xla cannot have same names as Public Subs in the current .xls?

Pity is that I cannot test this extensively so I need kind of a "recipe" to go away with it.

Gracias for your time.

Just give the subs/functions in your xla file different names from any subs/functions in your workbook.

 

Hola vpoko

Problem solved. Finally learnt what is needed.

I started afresh and reasigned each macro to its button in the toolbar and they work OK.

Now I know that I do not need any Addin but it is good that I learnt how to create, refrence and use them.

Gracias for your help.