Personal Subs Toolbar


Do you like to write macros in Excel? If yes, then probably you have already met the moment when you were annoyed to remember all keyboard shortcuts of your macros or even worse, to find a new one and not already used. In addition, it's quite comfortable to have at disposal help for macros, some "how it works" - especially for that ones, which you don't use each day. If you agree, this small add-in could help you.

Short description

Personal Subs is a MS Excel add-in. It simplifies call of your personal macros, allows you to call them directly from application toolbar and it is capable to provide help for included macros. It's great tool for studing, testing and using Excel macros.

Important features

  • many macros are accessible by one keyboard shortcut (ctrl+shift+space by default)
  • macros are accessible directly form Excel toolbar
  • easily accessible help can be written for each macro
  • macros can be arranged into meaningful groups
  • macros can be used in any open workbook

Excel versions

Testified in MS Excel 2002/2003/2007. It should work also in Excel 97/2000.

Install/Uninstall

Install:

  • open MS Excel
  • trust VBA code access :
    • MS Excel 97/2002/2003: Tools >> Macro >> Security >> Trusted Publishers >> check Trust access to project in Visual Basic
    • MS Excel 2007: Office Button >> Excel Options >> Trust Center >> Trust Center Settings... >> Macro Settings >> check Trust access to the VBA project object model
  • install add-in:
    • MS Excel 97/2002/2003: Tools >> Add-Ins >> browse for PersonalSubs.xla >> OK
    • MS Excel 2007: Office Button >> Excel Options >> Add-Ins >> choose Excel Add-Ins in Manage dropdown list >> Go... >> browse for PersonalSubs.xla >> OK
  • after install the toolbar will appear:
    • MS Excel 97/2002/2003: as a floating toolbar somewhere over sheet. You can catch it and put it anythere on Excel command bar.
    • MS Excel 2007: on the Add-Ins ribbon. You can put it on Quick Access Toolbar: Office Button >> Excel Options >> Customize >> choose Add-Ins Tab in left dropdown list >> add Custom Toolbar >> OK

Uninstall:

  • open MS Excel
  • uninstall add-in:
    • MS Excel 97/2002/2003: Tools >> Add-Ins >> check off PersonalSubs item >> OK
    • MS Excel 2007: Office Button >> Excel Options >> Add-Ins >> choose Excel Add-Ins in Manage dropdown list >> Go... >> check off PersonalSubs item >> OK

Toolbar Controls

  • list of subs (macros) - to choose a macro to be launched
  • button Run - to launch the selected macro
  • button Help - to show a help for selected macro
  • button to toggle order of macros in toolbar list:
    • caption 123 - macros are ordered according their order in source module including dividers.
    • caption ABC - macros are ordered alphabetically, dividers are omitted.
  • additional controls at the end of list:
    • _ Preferences - to edit settings of toolbar
    • _ Edit Source Module - to open VBA IDE and activate source module code
    • _ Refresh List - to refresh the list of subs from the code in source module
    • _ About - to show information about add-in
  • keyboard shortcut ctrl+shift+space - to launch the selected macro or additional control. It's a substitue for Run button

PersonalSubs

Note: Additional controls are launched directly after their selection from the list. Repetition of the same control can be launched by Run button.

Stability

PS Toolbar should not raise any untreated errors. Errors raised during execution of your macros should not influence functionality of Personals Subs Toolbar. If yes, please let me know.

Note: If after a tough debugging you get into situation, that whatever you do to eliminate bugs still you get problems to run code correctly, then try to save all your project and restart Excel. Sometimes it helps - mostly in the case when there are global variables and after all the error breaks, Excel doesn't arrive to treat them correctly. Of course, restarting Excel you will not eliminate real bugs hidden in the code.

Note: If macro touch the content of sheet and the sheet is protected you will get warning that you can unwillingly change sheet content and formats.

VBA Project

ThisWorkbook module - contains all the code of Personal Subs Toolbar add-in. If you don't want to change the functionality of Toolbar then do not touch this code. All the other modules can be freely changed/renamed/deleted without any influence on functionality of Toolbar (of course it can influence functionality and accessibility of your macros).

PersonalSubs module - this is default source module for personal subs. Here you should put your macros which you would like to access form PS Toolbar. There are already some macros - all of them I have written to solve/simplify my excel tasks. Maybe you will find something useful for you. If not, just rewrite it.

GeneralFunctions module - it contains general functions to build the code of macros in PersonalSubs module. All this functions could be placed also directly in PersonalSubs module, but it would not be so transparent.

UserGuide module - contains this page in plain text version.

You can add your own modules and forms. To launch a form use a macro in source module.

Source Module

Module PersonalSubs is default source module. It contains macros which will appear in the dropdown list of the Personal Subs Toolbar. Use _ Preferences >> Set Source Module to change the source module.

There are 4 rules to enter a new macro to any source module and make it visible in toolbar list:

  1. Subs (macros)
    Any macro written in source module is visible in Personal Subs toolbar dropdown list provided that its declaration starts with Sub or Public Sub statements, there is no input argument and the declaration starts from the beginning of line without any preceding space:

    'let's suppose that this comment is written from beginning of line

    Sub MySub1()
     ...
    End Sub

    Item MySub1 will appear in dropdown list

    Note: If there is a space in front of declaration then the macro is not visible in dropdown list:

     Sub MySub2()
     ...
    End Sub

    Item MySub2 will not appear in dropdown list

  2. Nickname
    To use a nickname for some macro put this nickname into a comment directly behind declaration of the macro:

    Sub MySub() 'My Sub Nickname
     ...
    End Sub

    Item My Sub Nickname will appear in dropdown list

    Note: As a nickname is considered only commented text until the next apostrophe:

    Sub MySub() 'My Sub Nickname 'just a comment
     ...
    End Sub

    Item My Sub Nickname will appear in dropdown list

  3. Help
    To write a help for the macro put help text into a commented lines directly under declaration of the macro. All lines till the nearest uncommented line are considered as help lines:

    Sub MySub() 'MySubNickname
     'help text line 1
     'help text line 2
     'help text line 3
     ...(any uncommented code)
     'just a comment
     ...
    End Sub

    Text help text line 1,2,3 will appear after click on Help button or on drag the cursor over list control, when MySubNickname selected

  4. Divider
    The default divider prefix is " ---". To place a divider between different groups of macros put a divider prefix at the beginning of commented line. All text introduced behind the divider prefix until the end of line or next apostrophe is included in to divider:

    '*** SOURCE MODULE CODE ****** DROPDOWN LIST ITEMS ***
     
    ' --- Group 1 ---' just a comment --- Group 1 ---
     MySub1Nickname
    Sub MySub1() 'MySub1NicknameMySub2Nickname
     ... --- Group 2 -:-
    End SubMySub3Nickname
     
    Sub MySub2() 'MySub2Nickname
     ...
    End Sub
     
    ' --- Group 2 -:-
     
    Sub MySub3() 'MySub3Nickname
     ...
    End Sub

    Note: to define you own divider prefix put at the beginning of the source module commented statement '#DividerPrefix and directly behind it put your divider prefix enclosed in quotation marks:

    '*** SOURCE MODULE CODE ****** DROPDOWN LIST ITEMS ***
     
    '#DividerPrefix " ---<"
    ' ---< Group 1 >---' just a comment ---< Group 1 >---
     MySub1Nickname
    Sub MySub1() 'MySub1NicknameMySub2Nickname
     ...  ---< Group 3 >---
    End SubMySub3Nickname
    ' --- Group 2 ---
    Sub MySub2() 'MySub2Nickname
     ...
    End Sub
     
    ' ---< Group 3 >---
     
    Sub MySub3() 'MySub3Nickname
     ...
    End Sub

    The --- Group 2 --- is not recognized as divider.

    Divider prefix can be composed from any writable ASCII chars except apostrophe. If the definition of divider prefix is not at the beginning of module or it contains apostrophe, then it is ignored and the default divider prefix is used.

Download

Download the PS Toolbar here.