Post a Comment On: CPA at Law

This will be the first post in a series of posts explaining how to use Visual Basic with Microsoft Excel to accomplish a number of useful tasks. While I am by no means a programmer, I hope I can demonstrate some practical ways to use Visual Basic to help you increase your productivity on a PC.

The first step after opening Excel is to open the Visual Basic Editor. This is accomplished by clicking on the Developer tab within Excel and clicking the Visual Basic button. Alternatively, you can press Alt+F11 to open the editor.

The next step is to insert a new module. In the column on the left, the Visual Basic Editor displays the various Excel spreadsheets that are open. Make sure that the spreadsheet you want the code associated with is selected, and click the Insert menu and select Module. This will bring up a blank screen into which you will type your code.

The next step is to create a subroutine within the module window. This is accomplished by typing "Sub" followed by the name of subroutine followed by "()". The name can be almost anything you want, but it can't have spaces and should describe what the code does so that you can select the subroutine you want from a potentially long list. For a very simple example, I will create a subroutine named lastRun; it will display the time that the subroutine was last executed. Type the following to set up the lastRun macro (the "End Sub" usually appears automatically):

    Sub lastRun()

    End Sub

Between these two lines will be the rest of the code, which in the lastRun example, will be the following:

    ActiveCell.Value = "Subroutine last run at " & Time()

The lastRun subroutine will change the value of the active cell, or the cell that is highlighted on the spreadsheet, to the text in parentheses followed by the time that the subroutine was run. In order to run the subroutine, save your spreadsheet as an .xlsm file (always, always save before testing new code!) and then click the green triangle "Play" button at the top of the Visual Basic Editor. Alternatively, from within the spreadsheet, click View, Macros, make sure lastRun is highlighted, and click the Run button. Note that the text and time appear in whatever cell on the spreadsheet is highlighted when the macro is run.

This has been an introduction to utilizing Visual Basic with a very simple subroutine; in my next post, I will show how to increase the functionality of this simple bit of code.