Basic Visual Basic for Excel Part II

In a previous post, I demonstrated how to create and execute a very simple macro in Microsoft Excel. In this post, I will demonstrate a number of fairly simple enhancements to that macro. The code we will start with is as follows:

Sub lastRun()

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

End Sub

Before beginning, we should assign a shortcut to the macro to make it easier to run. This is accomplished by clicking Alt+F8 from within Excel, selecting the lastRun macro, clicking "Options," and assigning a shortcut key such as Ctrl+Shift+A. Now you can run the macro from Excel simply by clicking Ctrl+Shift+A.

The first enhancement to lastRun is to have the macro enter the time it was last run not in the active cell, but in a cell relative to the active cell on the spreadsheet:

    ActiveCell.Offset(2, 4).Value = "Sub last run at " & Time()

The numbers following "Offset" are the number of respective rows and columns from the active cell into which the time will be entered. Positive numbers offset rows down and columns to the right, while negative numbers offset rows up and columns to the left.

We can also refer to cells without reference to whatever cell is currently selected; in the "Cells" reference below, the row number of the spreadsheet appears first and the column number follows. Of course, the cell we are changing can be set equal to any value we describe as well. Type a message into cell A1 on your spreadsheet; then try the following code:

    Cells(2, 1).Value = "Cell A1 said " & Cells(1, 1).Value & " when the sub ran at " & Time()

Another useful function in Visual Basic is an If Then statement, which works similar to the IF function in Excel. Here is an example of how the lastRun function can run an exception if cell A1 is blank by using an If Then statement:

    If Cells(1,1) <> "" Then

        Cells(2, 1).Value = "Cell A1 said " & Cells(1, 1).Value & " when the sub ran at " & Time()

    Else

        MsgBox "Cell A1 is blank!", vbOKOnly, "For your information..."
        Cells(2, 1).Value = "Cell A1 was blank at " & Time()
        'This is a sample comment in VB

    End If

Note that it is good practice to insert comments throughout your code, as above, so it is easy to remember what the section is supposed to accomplish; the first character of a comment line is an apostrophe, followed by your notes. Another tip is to use blank lines and tabs often to separate and group different sections of your code. In my next post, we will examine the Do Loop, a powerful tool in Visual Basic for people who use Excel frequently.

0 comments: