My Excel Toolbox

Visual Basic for Applications (VBA)

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 400 Function and Sub procedures and user forms in about 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:

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.

My Toolbox Info

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 further 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 Office 2010. All of the procedures have been well tested using 64-bit Excel 365 and Windows 10. 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:

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. For installation instructions, see Add or Remove Add-Ins in Excel or Installing and Updating Add-Ins. See Microsoft .NET Framework for additional information. (The MyToolbox add-in does not include a custom ribbon because your choices might differ from mine.)

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.)

Caution

Click this link regarding certain issues.

Comment

Click this link to add your comment.

About

Click this link for more information.