Encapsulating common vba code in a separated workbook
I have many Excel workbooks in the same shared folder on a file server. In each workbook there's some data and an "Update charts" button that generates / updates charts inside the workbook based on the data.
This is all implemented and works, but I have a maintenance problem... If I have to change or fix something in my macros, I have to update the macros in all workbooks.
I would prefer to have one additional workbook that contains nothing else, but my macros to generate / update the charts. All the other workbooks should call these macros when the button is pressed. That way, when I have to change the chart generation, I could do it in one place. The best would be if the users don't even have to open the macro workbook.
Is there a best practice / advice for that? Can this be safely implemented at all provided that multiple users are simultaneously updating the workbooks and generating the charts using the "Update charts" button?
You need to create an add-in with the code. The user loads the add-in once and it stays loaded in Excel until it is removed. The code is always there ready to be executed.
The add-in needs to be able to recognize when a workbook is the right workbook for the code. I use a defined name in all the workbooks that are "appicable" to the add-in. If the activeworkbook has that name, then the code will run. Otherwise it won't.
In the add-in, you need a custom class module that dimensions an Application variable WithEvents. Then you will use the Workbook_Open or Workbook_Activate event to determine if a particular workbook has the defined names.
I don't like to use on-worksheet buttons to do this kind of thing. ActiveX controls are flaky when the control is on a different sheet than the code (you get a lot of Automation errors). And Forms buttons don't have events so they're harder to hook up.
I generally use a Ribbon button with a callback. The callback enables/disables the ribbon based on whether the activeworkbook is applicable.
You don't give a lot of specifics, so neither can I. But hopefully this will start you down the right path.
One of the ways that you can make sure your code only runs under the right conditions is to check those conditions at the top of your procedure. The way I prefer to do it, however, is to make the user interface elements unavailable except when the conditions are appropriate.
In the old days, that meant setting the Enabled property of a CommandBarButton. I would use application level events so that every time a workbook window was activated, code like this would run
Application.Commandbars("mybar").Controls("PrintInvoice").Enabled = IsInvoice(ActiveWorkbook)
With the Ribbon, it's different. Ribbon controls (which are defined in XML) have an enabled attribute. You can set that to True or False. But you can also choose not use the enabled attribute and instead use the getEnabled attribute.
Then in your VBA, you can still use the application level events. But instead of manipulating a CommandBarButton, you simply invalidate the Ribbon. When you invalidate the Ribbon, it forces it to run your InvoiceAppBtnPrintGetEnabled procedure which then enables or disables the ribbon button.
See Ron de Bruin's site for more info: http://www.rondebruin.nl/win/s2/win013.htm