Results 1 to 7 of 7

Thread: Storing macros

  1. #1
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts

    Storing macros

    Using Excel 2007

    I always have problems with Excel macros, particularly storing them.

    Right now, I have two macros stored in my "Personal.XLSB".

    Now, when I open a spreadsheet AND Excel is not open, then the Personal.XLSB sheet will also open. Which allows me to access my macros.

    But if I close the Personal.XLSB sheet, then I cannot access my macros.

    Is there a better way to store macros so that they are always available in the spreadsheet I open w/o having to have the secondary Personal.XLSB sheet also open? If so, could you please describe how I could do this?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    To the best of my knowledge you can only access VBA {macro code} in an open workbook. This workbook can be either your Personal.xls?, T
    the workbook you opened plus any workbook it opens, and/or any Addin that has been installed.

    If I may ask why don't you want the Personal.xls? file open?

    Note: I used .xls? so it will fit all versions of Excel it could be .xls .xlsm .xlsb.
    Last edited by RetiredGeek; 2011-01-28 at 21:58.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by RetiredGeek View Post
    To the best of my knowledge you can only access VBA {macro code} in an open workbook. This workbook can be either your Personal.xls?, the workbook you opened plus any workbook it opens, and/or any Addin that has been installed.

    If I may ask why don't you want the Personal.xls? file open?
    I don't want to use the Personal.XSLB because then I see TWO entries for Excel in the taskbar. And it doesn't seem right that I should have to open a separate carrier sheet just for macro access. But then again, this is a Microsoft product, sigh.

    What I want to see is when I open any workbook, the macros are automatically made available w/o the 2nd Personal.XSLB sheet also having to open. This is what I would like to know if it is possible to do.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Interesting that it shows on the task bar. I'm using XL2007 on my laptop and when I open Excel it only shows the open workbook unless I do an Alt+F11 to open the VBE. However, my personal macros file is of filetype .xls because I move it back and forth with my desktop which has XL2003 installed. I wonder if changing it to an .xlsm file would make a difference for your task bar display? I don't know if you could change it to an .xls filetype if you are using any 2007 or 2010 only object model items in your macros. Just some things to consider I'm still sure it has to be open to use the code.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts
    OK, I did some Googling. If you HIDE the Personal.XLSB, then it doesn't show up on the tab bar. The downside of this is that when I close an Excel window, the viasible open sheet will close but the main Excel window stays open and has to be closed separately by clicking the "X" again.

    Here is some worthwhile info on XLSB & XLSM. files:

    ============
    Personal.xlsm
    http://us.generation-nt.com/answer/p...145932111.html

    ============
    How do I create a PERSONAL.XLS(B) or Add-in
    Ron de Bruin (last update 25-Oct-2010)
    Go back to the Excel tips page

    PERSONAL.XLS(B)

    If you want that certain code is available in all your workbooks, then use your
    PERSONAL.XLS or in Excel 2007-2010 your PERSONAL.XLSB file.

    What is it:
    This is a hidden workbook that opens when you start Excel.
    The code you copy in this workbook is available in all workbooks you have opened in Excel.

    http://www.rondebruin.nl/personal.htm
    ============

    ============

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    It's true you have to hit the big red X twice to exit in 2007+.
    I toyed around with trying to make Personal.xls? auto exit if it was the only workbook open as follows:
    Code:
    Option Explicit
    Private Sub Workbook_Activate()
       If Workbooks.Count = 1 Then
         Application.DisplayAlerts = False
         Application.Quit
       End If
    End Sub
    Note: above code placed in Personal.xls? ThisWorkbook module.

    If Personal.xls? is hidden the macro will not execute!
    Worse if it isn't hidden you can't start Excel w/o it immediately exiting.

    So it looks like we've been upgraded again. 2003 would automatically close Personal.xls.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Hold Shift and click the application X and it will close both. Another lovely new feature introduced for 2007 onwards.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •