Purpose
It is fitting to start up with Startup. This was not done before the others began, but grew as a collection throughout their development. The Calendar was reapplied in whole or in part in other projects. The Controls and Symbols sheets have been frequently used as a reference. These and other sheets were made to shine a light into places where I could not see well.
A well-designed personal startup file is like a home-made multi-function tool. It has all your favorite tools and stays within reach in your pocket. When you need it, you take it out and open the tool for the job. When you’re done, you fold it up and put it back in your pocket. Even if you seldom use it, you are comforted knowing it is there when you need it.
Any file in your startup folder opens automatically when you start Excel, that much is given. How to take advantage of that is up to you. First, you need to find the startup folder. Search the local drive for XLSTART. This path should also be in your trusted locations, see Trust Manager. There is also an alternate startup folder that you can set in Options: Alt-F,T,A, then keep hitting Alt+L until you find it.
Putting a file in the startup folder is like clipping it to your belt. It’s there for you, but you might not want it to be so visible. To stow the file in your pocket, hide the window, Alt-W,H. It will no longer be visible in thumbnails, Alt+Tab, or anywhere. The only clue to where it’s hiding is the unhide button, Alt-W,U.
To make it open hidden, hide it, then when you shut down Excel, click Save when prompted. Restart Excel, and Alt-W,U to confirm it’s there. You can continue to build it as you learn and develop tools, avoiding multiple files to keep track of. Unhide, maintain, hide, and save on exit. You can also manually save it in the VBA window, it is always visible there, even if it has no VBA code.
Macros in the file remain accessible while it’s hidden. In fact, if you’re starting with macros, Excel provides PERSONAL.xlsb for this purpose. When you record a macro, Alt-L,R, look in the Store macro in: box, you’ll see Personal Macro Workbook. This file is hidden by default, but you will see it in the VBA window (Alt+F11) or in the unhide window (Alt-W,U). When you exit Excel, it will offer to save the file, with a little sales pitch on the benefit.
If you don’t have a startup file yet, you can use this option as a convenience, since it is automatically named, hidden, and saved to your startup folder. Just record a simple macro in it, and save it. The .xlsb extension is a binary file that bypasses macro security warnings, since this particular file is for you only. It has other advantages and disadvantages over .xlsx and .xlsm, but there’s nothing special about it. You can manually name and store your own startup file, or you can use PERSONAL.xlsb as your startup file. Just unhide it to store stuff in sheets.
My own preference and usage of the startup file is more as a reference than as a macro library. Most macros I write are specific to a project, I have just a few in my startup. Many people who write macros are not aware of the Personal Macro Workbook option, nor of how to make code reusable.
I’ve included several sheets that serve as tools or references. The intent is to collect the kind of help that’s hard to find, with examples and reminders. Start your file with whatever you like. Consolidate every such thing into one file, naming sheets sensibly. If it gets large, think about a dashboard, and consider .xlsb format to make it save and open faster.
This is just for you, it doesn’t have to be pretty, but you’ll want to keep it reasonably organized like a proper toolbox. You’ll also find it’s a convenient notepad with temporary sheets for anything from trip itineraries to shopping comparisons. Don’t forget to clean it out once in a while.
If you use your personal file to store and run macros or define functions, you can use those macros and functions in other workbooks you have open at the same time. If you share those other workbooks, other people will not be able to execute the same code. Be stingy with your own tools to prevent breaking shared files, or copy selected code to the shared file as appropriate.
Macros are only as good as their marketing. It’s easy to forget the cool stuff you’ve done when it’s not visible. When you design a workbook with macros you can put a button on the sheet. If you write general macros that you want to be file-independent, you need a way to see and run them.
For your own use, you can add macros to the Ribbon, Alt-F,T,C, or to the Quick Access Toolbar (QAT), Alt-F,T,Q. Select the macro in Choose commands from, then Add > >. Optionally, select Modify... to change the button. If you elect to Customize Ribbon, create a new tab to organize your macros without changing the standard ribbon. The QAT is all custom and you can use <Separator> to create a macro group.
Each of the several sheets I have created is explained separately:
Comments