none
Distributed Excel VBA RRS feed

  • Question

  • Hi - here is my problem.

    I have a master  spreadsheet which has 10 sheets with associated VBA code - some related to the sheets and some contained within modules.

    I then create 5 copies from the master , rename each and issue each to a different user for them to populate.

    At some time later I want to make changes to the VBA code in the master file. I do not want to individually change the code  in each of the 5 files that I created from the master. I want to some how 'distribute' these changes.

    I have two ideas but do not know if these are possible.

    1. Have the macros in the Master file and in order to execute them 'call' them from the individual files. So the individual files do not have the actual macros they are held centrally.  This might work - but I would be concerned about the performance of the individual workbooks.

    2.  Have the macros in the individual work books but have some sort of 'utitlity' which could be called to overwrite existing macros with new updated macros.

    I am not sure if either of these is possible - or if there is a slicker solution to this problem.

    Any advice / help would be much appreciated.

    thank you.

    Peter

    Friday, February 26, 2016 1:53 PM

Answers

  • This is a solution using #2 from your post.

    If you have not added any new modules (or renamed them), then you can set a reference to MS VBA Extensibility and use code like this in your master workbook. Have the old workbook closed, and select it when prompted.  The code will replace the code in all modules of the old workbook with the code from the same modules in your updated master workbook.  Note that some anti-virus programs may flag this code as a virus, even though it requires that the old file have the same exact structure (so it is safe to use). Just keep a copy somewhere your anti-virus program does not check - my original utility to do this was summarily deleted with no warning. It does not set a reference to MS VBA Extensibility in the old workbook, so it will not run in the old workbook.

    Sub UpdateCodeInOldFile()
        Dim strNewCode As String
        Dim wbOld As Workbook
        Dim modVBA As VBComponent
        
        Set wbOld = Workbooks.Open(Application.GetOpenFilename(Title:="Select the file to be updated"))
        For Each modVBA In ThisWorkbook.VBProject.VBComponents
            With modVBA.CodeModule
                strNewCode = .Lines(1, .CountOfLines)
            End With
            With wbOld.VBProject.VBComponents(modVBA.Name).CodeModule
                .DeleteLines 1, .CountOfLines
                .InsertLines .CountOfLines + 1, strNewCode
            End With
        Next modVBA
        
        Application.DisplayAlerts = False
        wbOld.Close True
        Application.DisplayAlerts = True
    End Sub



    Friday, February 26, 2016 3:30 PM
  • Thanks Bernie

    I will give this a try.

    many thanks

    • Marked as answer by py1 Friday, February 26, 2016 4:11 PM
    Friday, February 26, 2016 4:11 PM
  • Bernie

    Sorry for my delay in responding- I will try that , thanks!

    Peter

    • Marked as answer by py1 Tuesday, March 1, 2016 1:09 PM
    Tuesday, March 1, 2016 1:09 PM

All replies

  • This is a solution using #2 from your post.

    If you have not added any new modules (or renamed them), then you can set a reference to MS VBA Extensibility and use code like this in your master workbook. Have the old workbook closed, and select it when prompted.  The code will replace the code in all modules of the old workbook with the code from the same modules in your updated master workbook.  Note that some anti-virus programs may flag this code as a virus, even though it requires that the old file have the same exact structure (so it is safe to use). Just keep a copy somewhere your anti-virus program does not check - my original utility to do this was summarily deleted with no warning. It does not set a reference to MS VBA Extensibility in the old workbook, so it will not run in the old workbook.

    Sub UpdateCodeInOldFile()
        Dim strNewCode As String
        Dim wbOld As Workbook
        Dim modVBA As VBComponent
        
        Set wbOld = Workbooks.Open(Application.GetOpenFilename(Title:="Select the file to be updated"))
        For Each modVBA In ThisWorkbook.VBProject.VBComponents
            With modVBA.CodeModule
                strNewCode = .Lines(1, .CountOfLines)
            End With
            With wbOld.VBProject.VBComponents(modVBA.Name).CodeModule
                .DeleteLines 1, .CountOfLines
                .InsertLines .CountOfLines + 1, strNewCode
            End With
        Next modVBA
        
        Application.DisplayAlerts = False
        wbOld.Close True
        Application.DisplayAlerts = True
    End Sub



    Friday, February 26, 2016 3:30 PM
  • Thanks Bernie

    I will give this a try.

    many thanks

    • Marked as answer by py1 Friday, February 26, 2016 4:11 PM
    Friday, February 26, 2016 4:11 PM
  • Hi Bernie

    With reference to the statement -Dim modVBA As VBComponent

    My version of Excel 2013 does not recognize VBComponet?

    Can you advise please?

    thanks

    Peter

    Friday, February 26, 2016 4:22 PM
  • Did you set a reference to MS VBA Extensibility?

    Friday, February 26, 2016 4:38 PM
  • Bernie,

    Sorry no - I didn't read your instructions closely enough!

    I now have done this and I get a run time error on the "For each ModVBA" line.

    "Run time error 1004"

    "Programmatic access to Visual Basic is not trusted"

    Any ideas please?

    many thanks ,  Peter

    Monday, February 29, 2016 10:56 AM
  • You need to change a setting in your Trust Center:

    File  /  Options...  Trust Center  /  Trust Center Settings  /  Macro Settings  -  check "Trust access to the VBA project object model"

    Your users or the admin may need to change that too - it cannot be changed through code.


    Monday, February 29, 2016 2:23 PM
  • Bernie

    Sorry for my delay in responding- I will try that , thanks!

    Peter

    • Marked as answer by py1 Tuesday, March 1, 2016 1:09 PM
    Tuesday, March 1, 2016 1:09 PM