Excel 2020: Create Your Own QAT Routines Using VBA Macros

December 23, 2020 - by Bill Jelen

Create Your Own QAT Routines Using VBA Macros. Photo Credit: Markus Spiske at Unsplash.com

There are several short macros you can add to your Personal Macro Workbook and then add to the QAT. In this tip, you will see how to create the Personal Macro Workbook, type some macros, and then assign them to icons on the QAT.

Create a Personal Macro Workbook

Start from any workbook. Go to View, Macros, Record Macro. In the Record Macro dialog, type a one-word name such as HelloWorld. Choose Personal Macro Workbook. Click OK.

In the Record Macro dialog, type a Macro Name of HelloWorld. Store Macro In: Personal Macro Workbook. Recording this first macro ensures that Excel creates a Personal Macro Workbook for you.

Type Hello in the active cell and press Enter. Then, select View, Macros, Stop Recording. These steps will create a Personal.xlsb on your computer where you can store new macros.

Open the VBA Editor and Find Module1 in Personal.xlsb

From Excel, press Alt+F11. (If you don't have Alt+F11, you can add the Visual Basic command to the QAT and click that.

Your first time in the VBA editor, it might be a vast expanse of grey. From the VBA menu, select View, Project Explorer.

Look for an entry called VBAProject (PERSONAL.XLSB). Click the + icon to expand it.

In the Project Explorer, click the Plus icon to the left of VBAProject (PERSONAL.XLSB)

Look for and expand Modules. Right-click Module1 and choose View Code.

Inside of Personal.xlsb, expand the Modules folder. Right-click on Module1 and choose View Code.

You will see your HelloWorld code. After the End Sub, type any of these procedures.

Sub VeryHideActiveSheet()
    ActiveSheet.Visible = xlVeryHidden
End Sub
Sub ShowAllSheets()
    For Each sh In ActiveWorkbook.Worksheets
        sh.Visible = True
    Next sh
End Sub
Sub UpperSelection()
    For Each cell In Selection.SpecialCells(2, 2)
        ' 2, 2 means xlCellTypeConstants, Text
        cell.Value = UCase(cell.Value)
    Next
End Sub
Sub LowerSelection()
    For Each cell In Selection.SpecialCells(2, 2)
        cell.Value = LCase(cell.Value)
    Next
End Sub
Sub ProperSelection()
    For Each cell In Selection.SpecialCells(2, 2)
        cell.Value = Application.WorksheetFunction.Proper(cell.Value)
    Next
End Sub

When you customize the QAT, choose Macros from the top left drop-down menu.

In the Customize the Quick Access Toolbar, open the Choose Commands From menu and choose Macros.

When you choose a macro and click Add>> the icon will default to a flow chart. Click Modify at the bottom right. Choose a new icon. Type a good tooltip.

This shows the right side of the Customize QAT dialog. Five macros have been added, each with a custom icon: Very Hide, Show All Sheets, UPPER Select, Lower Selection, Proper Selection. To choose the icon and type a friendly name, select an entry and use the Modify... button at the bottom of the right list box.

Here is an explanation of how to use these five macros:

Very Hide: Worksheets can be visible, hidden or very hidden. Few people know about Very Hidden sheets because you have to use VBA to create them. A worksheet that is Very Hidden will not show up in Home, Format, Hide and Unhide, Worksheets. (Thanks to -Sagar Malik)
Show All Sheets: It is easy to select 12 sheets and hide them in a single command. But then unhiding the sheets is a one-at-a-time proposition. This macro will unhide all sheets (including very hidden sheets).
Upper Selection: Converts all text in the selection to upper case.
Lower Selection: Converts all text in the selection to lower case.
Proper Selection: Converts all text in the selection to proper case.

Thanks to MF Wong for suggesting some of these macros.

Title Photo: Markus Spiske at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.