Embedded Files
Welcome
Welcome to My Excel Toolbox, which describes numerous Visual Basic for Applications (VBA) procedures you might find useful when working with Microsoft Excel. Although many might be compatible with the Mac or online versions, the focus is on Excel for Windows. These procedures are provided in modules with supporting user forms (where applicable). All are available for download from Google Drive.
This is the Home page. Please visit the Caution, Comment, and About pages as well; they are linked in the navigation banner at the top of each page. If you need an introduction to VBA, see What Is VBA at www.TheVBAHelp.com/blog.
Summary
My Excel Toolbox includes over 500 Function and Sub procedures and user forms in over 30 module and form files. An all-inclusive Excel add-in file is also available. These files can be downloaded from Google Drive.
Some of the more significant features of My Excel Toolbox include:
Backup and restore a worksheet's conditional formatting using named ranges, which auto-adjust to worksheet changes
Format, resize, or reposition unthreaded cell Comments (now called Notes)
Attach a hyperlink to a Comment and/or include an image in a Comment
Modify VBA's MsgBox function with:
– Custom position and/or button labels
– A modeless MsgBox with optional followup procedure
– A timed MsgBox (Popup) or Action Center Notification (BalloonTip)Modify VBA.InputBox and Application.InputBox functions with custom position
– Determine screen coordinates of a cell accounting for zoom, split, and freeze panesTwo progress bars, text (for Excel's status bar) and a popup user form, each illustrating action
A lightbox, to obscure Excel's active window under a user form or dialog box
SuperLink and RangeLink, superior substitutes for Excel's HYPERLINK function
Hyperlink functions based on SuperLink to initiate certain actions:
– RunCommandLink, to run a Shell command (without Windows' warning dialog)
– RunMacroLink, to run a macro (a Sub with no parameters)
– RunSubLink, to run a Sub or Function with parameters
– SheetNameLink, to activate any worksheet or chart sheet without disturbing its Selection
– MultiActionLink, to perform multiple actions (ordinary hyperlink actions plus those listed above)
– SheetListUpdateLink, to list a workbook's sheets (including hidden sheets) with hyperlinks to hide or activate (unhide)MAXIF and MINIF functions (not included in Excel)
Functions that support a 3D range reference (common contiguous range on sequential worksheets):
COUNTBLANK3D, COUNTIF3D, SUMIF3D, AVERAGEIF3D, MAXIF3D, MINIF3DDate functions: EasterDate, BankHolidayDate, FederalHolidayDate, FileLastDate, LastWeekdayOfMonth, NthWeekdayOfMonth, ZodiacSign
Functions and macros to add an ordinal suffix to numbers: OrdinalNumberText, OrdinalDateText, OrdinalNumberCF, OrdinalDateCF (CF for Conditional Format)
Functions to spell a numeric value as words: SpellDecimal, SpellDollars, SpellOrdinal
Array functions: ArraySize, ForNext, GetCols, GetRows, JoinCols, JoinRows, ListAppProperties, ListComments, ListHyperlinks, ListExLinks, ListFiles, ListFormatConditions, ListNames, ListShapes, ListTables, List... (about 50 more), SortPlus, SplitText, UniquePlus, SpillArray (simulating a dynamic array in older versions of Excel)
Financial functions (requiring Excel's STOCKHISTORY function): StockAsOfDate and StockEndOfPeriod
Other useful functions: Between, CalcMode, FindRev, IsLike, RangeAddress, SetChars, SetFill, SetFont, SetHeaderFooter, SetNumberFormat, SetStyle, Speak, TimeDif, TimeNow, Title, and more
Other useful macros: CopyValues, DisplayScreenMetrics, DynamicImage (dashboard), FindAndUnhide, HyperlinkProperties, LinksToMe, MonthPlusOne, NamesInFormulas, PasteThenGoBelow, ReplaceShapeText, ReportDents, SetColumnWidthByCell, SetRowHeightByCell, SwapTwoCells, ToggleRowLiner, UnhideNames, WindowDressing, and more
The following section provides a detailed list of the procedures, modules, and forms available in My Excel Toolbox.
NOTE: This site is most useful if you know enough about VBA to assemble the tools of interest into your own toolbox. But even if you don't, you can still take advantage of My Excel Toolbox. Please read the remainder of this Home page to learn how.
Description
Information describing My Excel Toolbox is provided in a Google Sheets workbook named My Toolbox Info. An embedded view of that workbook appears in the following frame. You can select each sheet by clicking its tab at the bottom of the frame, but intra-workbook (internal) hyperlinks in the first sheet (Contents) and the next-to-last sheet (Section 9) do not work properly in this embedded frame. So please open My Toolbox Info in Google Sheets by clicking the Open button in the frame's top-right corner, then return to this Home page for description of its contents.
The first sheet in My Toolbox Info (Contents) includes a date signature at the top. You can check periodically to see if you have the latest version of My Excel Toolbox. The latest update of each procedure and form is identified in subsequent sheets.
The next-to-last sheet in My Toolbox Info (Section 9) lists all of the procedures and forms described in the preceding sheets. The last column of Section 9 provides an internal hyperlink to each item. You can use the Data menu in Google Sheets to Sort by any column or create a Filter for any sheet.
The last sheet in My Toolbox Info (Section 10) provides external hyperlinks for downloading module and form files from Google Drive. Each file's latest date is included. Some modules utilize procedures from other modules or forms; these requirements are indicated in the last column of Section 10. The last rows reference useful PDF documents that can be opened in Google Drive; see Caution for more about Google Drive's PDF viewer. (The MyToolbox add-in file is discussed later in this Home page.)
Although Google Drive has native viewers for text and PDF files, it does not know that module (.bas, .cls) files are simply text. But you can connect an app that will recognize the module file's contents. You might consider the free (ad supported) Text Editor app for that purpose. Then you can review the contents of module files within Google Drive.
After downloading from Google Drive, module files can be viewed in a text editor or imported to your VBAProject in the Visual Basic Editor (VBE). User form (.zip) files include a class (.frm) text file and a graphic (.frx) file; both must be extracted (unzipped) before importing a form's class file, which automatically imports its graphic file. After reviewing a module or class file in Notepad (or similar), you should use VBE's File > Import File... (Ctrl+M) rather than copy/paste, so the text file's Attribute statements are properly incorporated.
If your configuration includes module M_Miscellaneous, you should review the public macros named MyToolboxMacros and MyToolboxShortcuts to modify ArrayList AL if necessary; see the list of macros in Section 3 of My Toolbox Info. If you are working with Personal.xlsb or a custom Excel add-in (see below), remember to save any changes before closing the VBE.
My Excel Toolbox modules reference certain libraries illustrated in this screenshot of VBE's Tools > References dialog. If necessary, use the vertical scroll bar to locate each library with a checked box until your dialog matches. The mscorlib.dll library requires access to both Microsoft .NET Framework 3.5 and 4.8 (or later). See Microsoft .NET Framework for IMPORTANT additional information.
Some of the procedures in My Excel Toolbox might not function correctly when referenced in a new workbook that has not been saved; simply save the workbook before proceeding. And some require VBA7, which was introduced with Excel 2010. All of the procedures have been well tested using 64-bit Excel 365 and Windows 10/11. If you have an older version of Excel, additional testing is recommended. If you use the Mac or online version, then you're on your own.
My Excel Toolbox procedures can be used in multiple workbooks if you import them to your personal macro workbook Personal.xlsb or if you create and install a custom Excel add-in (.xlam) file. Here are two articles about the use of Personal.xlsb:
Here are five articles about creating, installing, and updating a custom Excel add-in:
Notice if an Excel workbook's IsAddin property is True, then that workbook is an Excel add-in. See Microsoft's IsAddin description for more information.
The Personal.xlsb approach is perhaps easier but not as flexible as a custom add-in. Either way, be careful to save any changes you make because the VBE is unlikely to remind you. For example, you can edit an installed unprotected add-in's code, but it must be saved using the VBE while the add-in is selected. Also, consider including a reference as described in these two articles:
You can use File > Options > Customize Ribbon > New Tab... > Choose...: Macros to add a custom ribbon for your toolbox macros. Here is a screenshot of my custom Toolbox ribbon. RibbonX provides another approach as described in Adding a Button (and a ComboBox) to the Ribbon.
Finally, you might wonder if you can simply download My Excel Toolbox as an all-inclusive Excel add-in (.xlam) file. There are two reasons for not doing this:
You should avoid opening a stranger's Excel workbook or add-in for security reasons.
You can easily assemble your own Excel toolbox, which is a good learning experience.
But if these reasons don't apply to you, here is a link to the latest complete version of MyToolbox.xlam. After downloading that file, you will probably need to Unblock it using its Properties dialog. Placing MyToolbox.xlam in a Trusted Location is recommended; use File > Options > Trust Center and open Trust Center Settings... to identify or add a Trusted Location. For installation instructions, see Add or Remove Add-Ins in Excel or Installing and Updating Add-Ins. And see Microsoft .NET Framework for IMPORTANT additional information.
An add-in's macros and functions are available to each workbook you open. With the MyToolbox add-in installed, you should be able to access each of its macros by pressing Ctrl+M to open the macro named MyToolboxMacros. (Do not press Ctrl+Shift+M, the keyboard shortcut for MonthPlusOne.) You should also be able to access each of its functions by pressing Shift+F3 and selecting the MyT ... category in the Insert Function dialog. (Notice MyT sounds like Mighty but stands for MyToolbox.) The MyToolbox add-in does not include a custom ribbon because your choices might differ from mine.
Caution
Click this link regarding certain issues.
Comment
Click this link to add your comment.
About
Click this link for more information.
Page updated
Google Sites
Report abuse