none
Macros separate from Workbook

    Question

  • This is a fundamental question, I know (and I'm sorry).

    I have a workbook with macros in it. I want to be able to update the macros separately from the contents of the workbook (ie I don't want the macros in the workbook).

    How is this normally done in Excel (2007).

    I'm an experienced VBA deleveper (particularly for Access), so hopefully all I need is few clues.

    Andrew


    Andrew Gabb, Adelaide, South Australia
    Saturday, July 23, 2011 3:05 AM

Answers

  • HI Gibb,

               I think what you are looking for is a two workbook system as referred to by MrExcel. That is separating the code and data using two different work books.

     

    Here is how the data workbook code should look like:

     

    Private Sub Workbook_Open()

    'If the code file has been opened?

    On Error Resume Next

    X = Workbooks("Code.xlsm").Name

    ErrHolder = Err.Number

    On Error GoTo 0

    If ErrHolder <> 0 Then

           'Try to open Code file

           CodeFile = ThisWorkbook.Path & "Code.xlsm"

           On Error Resume Next

           Workbooks.Open Filename:=CodeFile

           ErrHolder = Err.Number

           On Error GoTo 0

    End If

    ' Run Macro in code file

        If ErrHolder = 0 Then

              Application.Run "'Code.xlsm'!CustFileOpen"

        End If

    End Sub

     

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

         'Note, here, We will ask User to decide if he really wants tio save/unsave/cancel the changes

         ' If w closed the code file, and then the person cancelled to close the workbook, the program will have to run without macro.

        If ThisWorkbook.Saved = False Then

            Msg = "Wookbook has been changed changed, " & "would you like to save the changes?"

            sTitle = "Close Workbook"

            Ans = MsgBox(Prompt:=Msg, Guttons:=vbYesNoCancel + vbExclamation, Title:=sTitle)

           Select Case Ans

           Case vbYes

                  ThisWorkbook.Save

           Case vbNo

                  ThisWorkbook.Saved = True

            Case vbCancel

                  Cancel = True

                  Exit Sub

           End Select

    End If

     ' If the code file has been opened?

    On Error Resume Next

    X = Workbooks("Code.xlsm").Name

    ErrHolder = Err.Number

    On Error GoTo 0

    'Run Macro in code file

    If Errholder = 0 Then

      Application.Run "'Code.xlsm'!custFileClose"

     Workbooks(x).Close SaveChanges:=False

    End If

    End Sub

     

    The last code should cantained all your progams procedures.


    Thanks to all that continue to serve as help HashMan
    • Marked as answer by Gabby22 Sunday, July 24, 2011 11:54 PM
    Saturday, July 23, 2011 11:42 AM
  • Gabby:

    My bad... it actually hid the entire wookbook.  Try it.

    Regards,


    Rich Locus Logicwurks, LLC www.logicwurks.com
    • Marked as answer by Gabby22 Sunday, July 24, 2011 11:54 PM
    Sunday, July 24, 2011 3:44 PM

All replies

  • Hello Andrew:

    The question you asked has a simple answer.  To show you how to get to the "Personal Macro Workbook", in Excel 2007:

    (1) From the Developer tab, click "Record Macro"
    (2) When you receive the dialog box where you would enter the Macro name, click the "Store Macro In" down arrow.
    (3) Select Personal Macro Workbook

    The Personal Macro Workbook is separate from your current workbooks, and is available to all your Excel workbooks.  Just one caveat:  Don't use the "ThisWorkbook" notation inside the macros stored in the Personal Macro Workbook area, because it won't be referring to the ActiveWorkbook, the one that you want to manipulate.

    If you don't currently have a personal macro workbook created, then create a simple macro and store it in your Personal Macro Workbook area.  Then open the workbooks that have the code you want to move to that Personal Workbook.  With both of them open, and in the VBA interface, copy the modules from your regular Excel workbook to the Personal Workbook, recompile and save.

    Regards, 


    Rich Locus Logicwurks, LLC www.logicwurks.com
    Saturday, July 23, 2011 5:02 AM
  • Thanks for the detailed answer, Rich. I didn't know about this.

    Although this would work in my case, I'm a little concerned about the overheads of installing and updating the PMW for my users. Although presumably it's not much more than copying the updated PMW to a folder in  the user Settings area, this could be a bit technical for some of my users.

    I was hoping there was some way you could link two workbooks in the same folder, so the whole thing could be 'stand alone'. (Similar to Access front and back end, I guess.)

    Do you know any way of doing this?

    Andrew


    Andrew Gabb, Adelaide, South Australia
    Saturday, July 23, 2011 7:04 AM
  • HI Gibb,

               I think what you are looking for is a two workbook system as referred to by MrExcel. That is separating the code and data using two different work books.

     

    Here is how the data workbook code should look like:

     

    Private Sub Workbook_Open()

    'If the code file has been opened?

    On Error Resume Next

    X = Workbooks("Code.xlsm").Name

    ErrHolder = Err.Number

    On Error GoTo 0

    If ErrHolder <> 0 Then

           'Try to open Code file

           CodeFile = ThisWorkbook.Path & "Code.xlsm"

           On Error Resume Next

           Workbooks.Open Filename:=CodeFile

           ErrHolder = Err.Number

           On Error GoTo 0

    End If

    ' Run Macro in code file

        If ErrHolder = 0 Then

              Application.Run "'Code.xlsm'!CustFileOpen"

        End If

    End Sub

     

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

         'Note, here, We will ask User to decide if he really wants tio save/unsave/cancel the changes

         ' If w closed the code file, and then the person cancelled to close the workbook, the program will have to run without macro.

        If ThisWorkbook.Saved = False Then

            Msg = "Wookbook has been changed changed, " & "would you like to save the changes?"

            sTitle = "Close Workbook"

            Ans = MsgBox(Prompt:=Msg, Guttons:=vbYesNoCancel + vbExclamation, Title:=sTitle)

           Select Case Ans

           Case vbYes

                  ThisWorkbook.Save

           Case vbNo

                  ThisWorkbook.Saved = True

            Case vbCancel

                  Cancel = True

                  Exit Sub

           End Select

    End If

     ' If the code file has been opened?

    On Error Resume Next

    X = Workbooks("Code.xlsm").Name

    ErrHolder = Err.Number

    On Error GoTo 0

    'Run Macro in code file

    If Errholder = 0 Then

      Application.Run "'Code.xlsm'!custFileClose"

     Workbooks(x).Close SaveChanges:=False

    End If

    End Sub

     

    The last code should cantained all your progams procedures.


    Thanks to all that continue to serve as help HashMan
    • Marked as answer by Gabby22 Sunday, July 24, 2011 11:54 PM
    Saturday, July 23, 2011 11:42 AM
  • Hello HashMan:

    That's a cool tip!!  Just so you know, there is a trick to eliminating the double space in your code.  At the top of the box where you enter your post, there is an "HTML" button.  Click it, then enter your code as follows:

    <pre>
    Paste Your Code Here
    </pre>

    Then SUBMIT.  The <pre> tag says to keep the preformatted state:

    <pre>
    Text in a pre element
    is displayed in a fixed-width
    font, and it preserves
    both      spaces and
    line breaks

    </pre>

    Regards, 


    Rich Locus Logicwurks, LLC www.logicwurks.com
    Saturday, July 23, 2011 3:02 PM
  • Hashman:

    It seems that if you keep the code in an Excel workbook on a network drive, it would be possible to have one location only that would require code changes, and multiple users could use the same single source of code.  It might be worth testing to see if there would be conflicts when multiple users use the same code at the same time.  Unless the code is "copied at run time" to the individual user's instance, global and other VBA variables could be corrupted when more than one user executed the same code at the same time.

    Do you have any experience with network-drive based code?

    Regards,


    Rich Locus Logicwurks, LLC www.logicwurks.com
    Saturday, July 23, 2011 3:32 PM
  •            I think what you are looking for is a two workbook system as referred to by MrExcel. That is separating the code and data using two different work books.Thanks to all that continue to serve as help HashMan

    Thanks a mint, Hashman. This is looking really good.

    What I found was the following: I load both Main.xlsm and MainMac.xlsm, and assign a button in Main to a macro in MainMac. I close both, open Main and click the button. This automatically loads MainMac if it's in the same dir (note that I don't have any event handlers at this stage).

    But I still need to close MainMac when Main closes (using the close event as suggested).

    The only downside of this approach (I'm never satisfied), is that I'd rather that the MainMac workbook wasn't visible. If the user is flicking between different workbooks, it does rather get in the way. Is there any way to hide it? After all, all I need are the macros, not the worksheet.

    What I'd like is the effect you get with templates, PMW or add-ins, where the macros are there, but not the worksheets. Anyone know how to do this.


    Andrew Gabb, Adelaide, South Australia
    Sunday, July 24, 2011 1:44 AM
  • Gabby:

    I was able to make a worksheet appear and disappear using the following code:

        Windows("Code.xlsx").Visible = True   'Make Worksheet Appear

        Windows("Code.xlsx").Visible = False  'Make Worksheet Disappear

    Regards,


    Rich Locus Logicwurks, LLC www.logicwurks.com
    Sunday, July 24, 2011 3:23 AM
  • I was able to make a worksheet appear and disappear using the following code: Rich Locus Logicwurks, LLC www.logicwurks.com

    Yes, but is it the only worksheet in the workbook? When I tried to hide the (only) sheet using the GUI, I got an error. I also saw something in the Help saying you had to have one sheet visible.

    Andrew


    Andrew Gabb, Adelaide, South Australia
    Sunday, July 24, 2011 3:26 AM
  • One way round this is to always close the macro workbook after the macro runs, and I'll probably do that in the case I'm working on at the moment.

    This is OK for this one, because the macros take some time anyway and are run infrequently. For a 'check and refresh' function it might be a bit slow. Time will tell.

    Andrew


    Andrew Gabb, Adelaide, South Australia
    Sunday, July 24, 2011 3:50 AM
  • Another choice would be to write you application in Visual Studio using VBnet.  You can down load a free copy of Visual Studio from MSDN.COM.  Here is a comparison of the diferences between VBA and VBNet (you need to use the PIA which contains the Microsoft Office Interface and has all the Excel Libraries).

     

    VBA advantages/disadvantages

    1) Advantage : Run faster

    2) Disadvantages : Users Can modify the code.

    3) Advantages : Users Can modify the code.

    4) Need office Installed to run the application.

    Visual Studio advantages/disadvantages

    1) Disadvantage : Run slower

    2) Advantages : Users Can modify the code.

    3) Runs without office Installed on every PC

    4) Can use the complete Net Library with the program

    5) Can write the code in C#, and C++ as well as Visual Basic.

    6) Run as a Windows Application.  Don't have to open an Excel file to start the application.

     


    jdweng
    Sunday, July 24, 2011 10:34 AM
  • Gabby:

    My bad... it actually hid the entire wookbook.  Try it.

    Regards,


    Rich Locus Logicwurks, LLC www.logicwurks.com
    • Marked as answer by Gabby22 Sunday, July 24, 2011 11:54 PM
    Sunday, July 24, 2011 3:44 PM
  • Okay! Also my bad - I saw 'worksheet' and looked no further. Thanks, Rich.

    So, to summarise:

    • I have 2 workbooks in the same folder: WB is the 'work' workbook with the data, WBmac is the companion macro workbook - all it has is a dummy sheet and the macros.
    • In WB I have buttons which I assign to public subs in WBmac.
    • If I open WB and click a button, WBmac loads automatically.
    • In WB's Open event handler I have "Windows(ThisWorkbook.Name).Visible = False". This hides the WBmac workbook from the user - but it's still shown in the VBA form so I can work on it if necessary.
    • I could close WBmac when I close WB, but I probably won't bother. Since the user can't see it and change it, and because the macros don't change WBmac, it will sit there until Excel closes.
    Excellent! Thanks Rich, Hashman. Back to work ...

    Andrew


    Andrew Gabb, Adelaide, South Australia
    Sunday, July 24, 2011 11:53 PM
  • Gabby:

    Awesome!! You are obviously an accomplished developer to take our snippets and put together your entire package.

    Regards,


    Rich Locus Logicwurks, LLC www.logicwurks.com
    Monday, July 25, 2011 12:02 AM