Caution

Array Functions

My Excel Toolbox has over 75 array functions in its M_Array module. These are especially useful in newer versions of Excel that support dynamic arrays. In older versions, the SpillArray function can be used to simulate a dynamic array.  See UseSpillArray.pdf for more information.

Many of My Excel Toolbox's array functions named List... (like ListSheets) are volatile so they will update more readily. But some volatile array functions are potentially unstable when used with SpillArray. SpillArrayNV can be used to alleviate this problem. When SpillArrayNV references a normally volatile List… array function, that function will become non-volatile; otherwise, there is no difference between SpillArray and SpillArrayNV. If problems are observed with the former, then switch to the latter.

During April 2024 three List... functions were renamed to prevent an unusual problem when used in a cell formula saved in a macro-enabled workbook:

1.  ListWBProperties was renamed ListWorkbookProperties

2.  ListWNProperties was renamed ListWindowProperties

3.  ListWSProperties was renamed ListWorksheetProperties

The problem was only observed with the first function; the other two were renamed for consistency. ListWBProperties would cause additional instances of ThisWorkbook and Sheet modules  to be created each time the workbook was opened. This was resolved by changing the function's name.

ListProcs and ListMacroSortcuts

The ListProcs and ListMacroShortcuts functions require that "Trust access to the VBA project object model" is enabled in File > Options > Trust Center > Trust Center Settings > Macro Settings.

Google Drive's PDF Viewer

When you click a link to a PDF file like UseSpillArray.pdf while browsing this web site, the file normally opens in Google Drive's PDF viewer.  Unfortunately (as of September 2022), that viewer does not always support internal hyperlinks such as cross-referenced footnotes or figures. (It does support external hyperlinks, however.) To properly view the file, it should be downloaded; use the Download button near the top-right corner of Google Drive's PDF viewer. When the downloaded file is viewed in Chrome or Edge, for example, both internal and external hyperlinks function as intended.

Hyperlinks

Prior to February 2022, certain VBA procedures in My Excel Toolbox modules M_RunCommand and M_RunMacro did not play well with the expanded clipboard features known as Windows Clipboard History (Settings > System > Clipboard) and Office Clipboard. When these multi-item clipboard extensions are enabled, initiating Copy (Ctrl+C) or Cut (Ctrl+X) runs any procedure referenced in a Hyperlink object's SubAddress if the object's Address is blank (""). For example, the MyRange() procedure in the hyperlink created by the following cell formula will be triggered:
=SuperLink("#MyRange()")
A similar hyperlink can be added to a cell by pressing Ctrl+K and typing #MyRange() in the Edit Hyperlink dialog's "Address:" text box. (The procedure must be a Function returning Range.) All such hyperlinked procedures in all sheets of the active workbook are triggered after starting Copy or Cut. This does not happen when the standard single-item clipboard is in effect. 

Note the following cell formula is not affected because it does not create a Hyperlink object (see UseSuperLink.pdf):
=HYPERLINK("#MyRange()")
Curiously, the following cell formula's hyperlink will not be triggered by Cut or Copy:
=SuperLink("?#MyRange()")
And a Ctrl+K hyperlink with ?#MyRange() in the "Address:" text box avoids the issue, too. Excel apparently interprets ? as the local workbook. The workbook is initially set to ? in Hyperlink.Address; the procedure is referenced in Hyperlink.SubAddress. The problem occurs only if Hyperlink.Address is blank. Unfortunately, when Hyperlink.Address is ? it is reset to blank after the hyperlink is followed (manually triggered). Alternatively, the workbook can be specified by a bracketed filename like these two examples:
=SuperLink("[My Book.xlsm]#MyRange()")
=SuperLink("["&NameOf("workbook")&"]#MyRange()")
The first formula will produce an error if the filename is ever changed. The second formula is more flexible because it uses the NameOf function in module M_Miscellaneous.

The VBA procedures in modules M_RunCommand and M_RunMacro that were compromised by expanded clipboards have been updated to avoid the problem described above. But if you observe strange behavior related to My Excel Toolbox when using the clipboard, consider disabling any multi-item clipboard extensions that might be active. For example, see the following articles:

• Clipboard in Windows

• Copy and Paste Using the Office Clipboard

Such problems have not been observed with the standard single-item clipboard.

The necessary update to modules M_RunCommand and M_RunMacro requires that procedures possibly triggered by a hyperlink must skip their usual activity when a Cut or Copy operation is pending (see CutCopyMode). Note that other My Excel Toolbox modules might reference such procedures in M_RunCommand and M_RunMacro (see Section 10 of My Toolbox Info). Finally, user defined functions such as MyRange() in the SuperLink cell formula examples above should include the following statement:
If Application.CutCopyMode Then Exit Function
These changes apply whatever clipboard feature (standard or expanded) is in effect.

For more information, review the following PDF files that discuss My Excel Toolbox procedures related to hyperlinks: UseSuperLink, UseRunCommand, UseSheetName, UseMultiAction, UseRunMacro, UseSheetList. Also, consider the following PDF file about an Excel bug that is apparent when copying hyperlink cells: ExcelBugReport.

Conditional Formatting

It is convenient to use My Excel Toolbox as an add-in; however, an add-in's functions might be rejected in a conditional formatting formula rule. For example, you can add a fill color to all cells that include a comment by applying the following conditional formatting formula rule:
=NOT(ISERROR(CommentText(A1)))
Your version of Excel might reject that formula if the CommentText function is part of another workbook or an add-in (see error message). But there is a work-around. First click Formulas > Define Name... and specify
Name: CellThis
Scope: Workbook
Comment: Relative reference to this cell in this workbook
Refers to: =INDIRECT("R[0]C[0]",FALSE)
Then define another name with
Name: CommentCell
Scope: Workbook
Comment: TRUE if this cell has a comment
Refers to: =NOT(ISERROR(CommentText(CellThis)))
With these defined names it should be possible to specify the following conditional formatting formula rule:
=CommentCell
That rule can be used over any range on any worksheet of the workbook to apply a fill color to cells that include a comment (cells where CommentText does not return an error). Each cell's fill color will update whenever a comment is added or deleted (press Shift+F9 to recalculate).

Using function CommentText is valid to illustrate a point in the example above; however, it would be more efficient to redefine the name CommentCell as follows:
Name: CommentCell
Scope: Workbook
Comment: TRUE if this cell has a comment
Refers to: =HasComment(CellThis)
Both functions CommentText and HasComment are included in My Excel Toolbox module M_Comments.

Notice the choice of CellThis instead of ThisCell for the name defined above; this prevents possible (but unlikely) conflict with Excel's Application.ThisCell property. Also, when a defined name refers to a formula using A1 style cell references it automatically becomes worksheet specific; relative R1C1 style cell references avoid this problem and permit Workbook scope. Here is a similar defined name you might consider:
Name: CellAbove
Scope: Workbook
Comment: Relative reference to the cell above this cell in this workbook
Refers to: =INDIRECT("R[-1]C[0]",FALSE)
This is especially useful in formulas if you frequently insert new rows. You could also define names like CellLeft, CellRight, and CellBelow.

Under certain unusual circumstances, using a defined name in a conditional formatting formula rule as described above might cause Excel to become unstable the next time your workbook is opened. If that happens, open a new blank workbook and click Developer > Excel Add-ins to temporarily disable the MyToolbox add-in, then open your workbook and press Alt+F11 for the Visual Basic Editor, then press Alt+I+M to insert a standard code module and add a local VBA function like this:

Function CommentCell(Target As Range) As Boolean
CommentCell = HasComment(Target)
End Function

After enabling the MyToolbox add-in, change the example's conditional formatting formula rule as follows:
=CommentCell(CellThis)
In this case the workbook must be saved as macro-enabled (.xlsm) or binary (.xlsb).

Capitalizing a Function Name

If you create a user defined function (UDF) named MyFunc() but mistakenly type myfunc() the first time you use it in a cell formula, Excel will always change it to myfunc() no matter how you capitalize it later. This can be very frustrating, but the solution is quite simple:

1. Select an empty cell and use Formulas > Define Name… to name it MyFunc with the preferred capitalization (but no parentheses). The formulas you entered earlier will now reference MyFunc() as intended but return a #REF! error.

2. Use Formulas > Name Manager to delete the empty cell's name defined in step 1. The formulas you entered earlier will retain the preferred capitalization and recalculate normally.

Now Excel will remember the capitalization you intended whenever you use that function in a formula.

Ctrl+T and Ctrl+L

If you install the MyToolbox add-in as described near the bottom of the Home page, you should be able to access each of its macros by pressing Ctrl+M (not Ctrl+Shift+M) to run MyToolboxMacros. Some of those macros can also be run by pressing a Ctrl+Shift+... shortcut like Ctrl+Shift+M for MonthPlusOne. 

There is also a macro named MyToolboxShortcuts that will start when you press Ctrl+T (not Ctrl+Shift+T). MyToolboxShortcuts provides access to a subset of MyToolboxMacros that can be run by pressing two additional shortcut keys. For example, Ctrl+T C A will start the CenterAcrossSelection macro. Ctrl and Shift are ignored for the two additional shortcut keys.

By default, Excel allocates Ctrl+T as a shortcut for its Create Table dialog; an alternate is Alt+N+T for Insert > Table. But early versions of Tables were called Lists, and Ctrl+L was used as a shortcut for the equivalent dialog. Since either Ctrl+L or Alt+N+T will open the Create Table dialog, Ctrl+T was appropriated by the MyToolbox add-in as a shortcut for its MyToolboxShortcuts macro. This applies only if you install the MyToolbox add-in (MyToolbox.xlam).

The latest version of MyToolbox.xlam includes the CreateTable macro, which simply opens Excel's Create Table dialog. That macro will run when you press either Ctrl+Shift+T or Ctrl+T C T; the result is the same as Ctrl+L or Alt+N+T.

Ctrl+Shift+F, Ctrl+Shift+P, and Ctrl+1

If you install the MyToolbox add-in as discussed in the previous section above, you might discover that Ctrl+Shift+F starts its FindAndUnhide macro instead of Excel's expected Format Cells Font dialog. By default, Excel allocates both Ctrl+Shift+F and Ctrl+Shift+P as keyboard shortcuts for Format Cells Font; an alternate is Ctrl+1 for the general (not Font specific) Format Cells dialog.  Since either Ctrl+Shift+P or Ctrl+1 will open the Format Cells dialog, Ctrl+Shift+F was appropriated by the MyToolbox add-in as a shortcut for its FindAndUnhide macro. This applies only if you install the MyToolbox add-in (MyToolbox.xlam).

Registering Functions

Before April 2023, each Public Function Name in My Excel Toolbox had a matching Private Sub Name_Register procedure that provided information for the Insert Function dialog (Shift+F3). The results were not very reliable. Now all of those Private Sub Name_Register procedures in a given module are combined into a single Private Sub Register_Functions procedure within that module. And the MyToolbox add-in includes procedures in its ThisWorkbook.cls module to automatically re-register all of its functions, which reliably updates the Insert Function dialog.

Error 35010

A VBA dialog box with “Unexpected error (35010)” might (or might not) be caused by issues related to compiling a large VBA project. To fix the problem, try this: In the VB Editor (VBE), click Tools > Options, then pick the General tab of the Options dialog and disable Compile On Demand.

Experience

Experience is the best teacher, and this applies to VBA and the Excel object model (especially the latter). The earliest procedures in My Excel Toolbox date back to 2017. Much has been learned since then, and Microsoft's online documents are better now. So with the benefit of experience, some of these procedures might be improved if overhauled today. But the often quoted principle still pertains: "If it ain't broke, don't fix it."