Basic Visual Basic for Excel Part IV

This is the fourth and last post in my introduction to learning and utilizing Visual Basic in Excel. As you may have guessed or experienced, writing Visual Basic code from scratch is extremely difficult. Realistically, there are simply too may classes, properties, commands, and syntax rules to learn before a beginner can automate a task by coding from a blank slate. Fortunately, there is an easier way.

If you need to automate a repetitive task in Excel, consider recording a macro, whereby the code for the actions you take is written automatically. The Record Macro button is located in the Macros menu, which is within Excel's View menu. The "Use Relative References" button at the bottom toggles between relative and absolute references on your spreadsheet. If the task you are trying to automate will always occur at the same location on the spreadsheet, make sure Use Relative References is off. However, if the task you are trying to automate will happen at different locations on the spreadsheet, highlight Use Relative References.

One task I find myself repeating a lot, when working with a filtered table in Excel and after having implemented multiple filters, is needing to unfilter the whole table. To do this, I usually just click the Filter button from the Data menu to turn off filtering, select the whole sheet, and click the Filter button again. To demonstrate how to automate this repetitive task, I will record a macro. In order to follow along, you will need a filtered table in Excel (if you need a quick data set, feel free to copy the entity table I used in this previous post).

After filtering one of the columns in your table, click View, from the Macros menu click Record Macro, select a shortcut key (such as Ctrl+Shift+A), and press OK. Everything you do until you click Stop Recording from the same Macros menu will be automatically coded into a new module. While recording, first unfilter the entire sheet by clicking View, Filter; second, select the entire sheet by clicking in the blue box to the top left of A1; and third, click Filter again. Press the Stop Recording button, and within your Visual Basic editor (click Alt+F11 to get there) you should have the following code in Module1:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Selection.AutoFilter
    Cells.Select
    Selection.AutoFilter

End Sub

From the spreadsheet, you are now able to unfilter the entire table easily by clicking Ctrl+Shift+A each time. This has been an example of recording a macro, and this concludes my four-part primer on how to automate tasks in Excel with Visual Basic. I anticipate posting subroutines that I use from time to time, but feel free to comment or contact me with your coding challenges or successes; I may address them in a future post as well.

Posted in: Spreadsheet,Visual Basic