Updating Distributed VBA Macro Code

    Assume the follownig:-


    • a centralalised xls file containing VBA code
    • users use this xls by copying it to their own C-drive
    • I find a bug in the VBA code and update the centralised xls file


    I'm wondering if there is a way to update the user's C-drive VBA code with some centralised VBA code when required. I've written code that can check if a file has been changed compared to the last time it was read in, so that shouldn't be a problem, but I'm not sure if updating/replacing VBA code is doable, and if so, what would be the best way to "store" the updated VBA code (in an xls file, in some sort of simple external text file, ....)

    Re: Updating Distributed Vba Code


    Create an Excel Add-in (.xla) rather than .xls


    Or, send out the updated Modules after exporting them to you hard drive.


    The Add-in by far the best way, though.

    Addins


    Wasn't sure how to specify a title for this topic and didn't want to second-guess where the error was - thereof the concise title.


    Am trying to get my head round addins and how to use them. The background is as follows. I have a workbook (call it X) on a network drive. When users run an application, this workbook is copied to a drive the user owns (if not already there) and the next available sheet is populated with data via VBA code. The actual code that populates the sheet is quite complicated, so I wanted to be able to update the user's workbook with updated VBA code as required (without them having to re-copy the workbook from the network dive). I read this site's description on addins and got the impression that that was what I was after. I therefore created the following

    • an xla file with ONLY VBA code (including VBA code for the open workbook event)
    • an xls file with ALL VBA code stripped from it


    I then added the xla addin to the xls file on the network drive.


    My problem is that when I then open Excel (empty) , the open workbook code from the addin is run automatically (ie, the X application code is run). If instead I open Excel with parameters using X, it seems that I first run the xla code, and then, once this has run, I get the standard question "Do you want to enable/disable macros" and the application code is run again.


    Long and the short - is there a better way to ensure distributed VBA code can be updated automatically from a central network drive when that VBA code is updated ?

    Re: Updating Distributed VBA Macro Code


    when running addins it's best to have some form of trigger for the macro other than the events - in the scenario you describe it sounds like the macro is running both from the sheet and from the addin. Have you considered having some form of update hotkey, like Ctrl+Shift+U rather than having it run on open? This would avoid the update running on unrelated workbooks automatically. The alternative is to name your macro sequence as a public Subroutine (put it in a code module to make sure you can call it directly) and call that from the xls workbook you wish to update, i.e.


    put the following code in the xls file:

    Code
    Private Sub Workbook_Open()
   Application.Run "UbdateWBData"
End Sub


    It will run the "UbdateWBData" Sub from your active addins (change this to whatever your Sub name actually is), but that macro will not run automatically from any other workbook.


    Also, are you sure when you opened the xls it was the version that was stripped of code? It should not normally ask you whether to run code from addins, so the prompt sounds like it was launched from your xls file opening.

    "I'm sorry, the number you have reached is an imaginary number. Please rotate your phone 90 degrees and dial again."

    Re: Addins


    I'm unsure what your problem is, do you want the code to run upon opening workbook x?, how are you triggering the code?


    When running a macro in any workbook where the security is set to medium or higher you will always get that question, it's there to give the user a choice.


    Have i missed the point?

    Re: Addins


    The open workbook event will trigger everytime you open Excel, as installed add-ins open with excel regardless of the workbook or link used to open the application.


    Have you considered using the workbook's open event to create a toolbar with a button that will trigger the vba code. This is the method I use for creating add-ins. YOu can also place a new menu item on one of the menus in excel.


    The workbook open event on an add-in should not be used to launch the add-in's code other than to create a button or menu item.


    If this code needs to run unattended, I would suggest a different method.


    If you need some help with the code for the toolbar button, let me know and I will throw some out there.

    Re: Addins


    Thank you both for answering. Let's see if I can answer your suggestions as I understand them. At the moment, imagine my application is started from a DOS window. I pass it the name of the workbook I want to open, as well as a long parameter string. The open event in the workbook takes the long string and runs a function that analyses the string and populates a sheet with data as a result. This is working fine at the moment. My problem is that this workbook is copied to each user's private drive (obviously including any vba code). As a result, if thecentral workbook VBA code is updated, the users never see it (since they're now accessing their own private copy).


    To reply to your suggestions.


    Tony. I don't want the user to have to press any button after the workbook is loaded. Is there a possibility to start excel, pass the name of the workbook as a parm and the name of some function to run ? (This doesn't get me past the updated VBA code though)


    Simon. Yes _ I want the code to run on opening the workbook (which, as mentioned, it's doing).


    Like I say, what happens if the central VBA code is updated though ?

    Re: Updating Distributed VBA Macro Code


    Thank you. Unfortunately, it's difficult sometimes to explain exactly what you're trying to do, without getting bogged down in extraneous explanations.


    I've never used addins before, and reading up on them, I got the impression that they were the way to maintain centralized VBA code (that I can change), rather than each user having their own workbook with (possibly) outdated copies of the "same" (centralized) VBA code. (Bit like the fact that as soon as you start Firefox, if it's been updated at mozilla.org, your local code is updated automatically)


    My users' scenario are 2.

    • User runs a non-excel application. This starts excel, passing the name of the workbook and a long string as a parameter as well. The open event detects this string and as a result, runs a function that imports data and populates a sheet (having changed the workbook address to the user's C-drive)
    • User opens the previous (C-drive) workbook. This time, the open workbook event detects no string passed, and does nothing, and simply allows the user to review their data etc etc


    Note that in item 1, the user doesn't have to press any buttons or do anything to get the data passed as the parm into a sheet.


    I want to try and achieve exactly this, but somehow or other have the workbook run external code that is placed and maintained centrally. Ideally (?), when the (C-drive) workbook is called via item 1, it will recognize that it has been passed a string parm and will run VBA code in a workbook on a network drive. As to whether the network drive code is defined as an addin or not, that's beyond my competence.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login