I have created an Excel workbook with lots of code behind the scenes. Occasionally I need to update the coding. Typically, I create a new version of the workbook and save it to each individual's personal network drive. In a perfect world, they would follow simple instructions for downloading new and delete the old version. But of course, that doesn't happen. Plus my staff is growing so copying these individual workbooks for each team member is becoming cumbersome.
Is there a way that I could create a program that would update the coding in the workbook? Then they should be able to run this patch program to update the coding within their workbook.
For Excel 2010, in the Trust Center, you can opt to allow programmatic access to the VBA object model by clicking the option "Trust access to the VBA project object model", the only option under Macro Settings>Developer Macro Settings. (A risk that needs to be fully undestood and if you are operating in a controlled IT environment and may run counter to your organization's security policies!)
Having done that, you can create a macro-enabled workbook "patcher" to open the target macro-enabled workbook. Then, for example, if you were to operate at the module level, the "patcher" application might to the following to remove the old code:
yourWorkbook.VBProject.VBComponents.Remove modYourModule
and then update it with new code by importing a replacement module:
yourWorkbook.VBProject.VBComponents.Import "c:\Users\Public\yoursource.bas"
Hope this helps point you in the direction you are looking for!