Welcome to CPA at Law, helping individuals and small businesses plan for the future and keep what they have.

This is the personal blog of Sterling Olander, a Certified Public Accountant and Utah-licensed attorney. For over seven years, I have assisted clients with estate planning and administration, tax mitigation, tax controversies, small business planning, asset protection, and nonprofit law.

I write about any legal, tax, or technological information that I find interesting or useful in serving my clients. All ideas expressed herein are my own and don't constitute legal or tax advice.

Basic Visual Basic for Excel Part III

This is the third post in a series explaining how I use Visual Basic in Microsoft Excel to increase productivity. In my previous post, I discussed the If Then statement as well as different ways to reference cells on a sheet; in this post, I will discuss the Do Loop.

The Do Loop allows you to repeat the same block of code until a condition is satisfied. To demonstrate, I will be using the client entity table from this earlier post; this Do Loop will concatenate a list of all a client's entities into a single field.

After recreating the client entity table in your own spreadsheet, select blank cell G2.  The code below will list all the entities in the active cell, starting with the entity name in the same row as the active cell, and ending with the last entity name before the client's name changes.

Before moving further, however, it will be important to declare a variable that can be used to keep track of which of the client's entities will appear next on the list. A good practice when declaring any variable is to use Option Explicit (for an explanation why, see this article). Declaring a variable is accomplished by typing "Dim" followed by the name for the variable you select, followed by the data type, which can be an "Integer" in this case (other data types are capable of storing different sizes or kinds of data). After declaring the existence of the variable "r," we assign it a value of 0 for reasons described below. After doing all of this, I have the following as the starting point for my code:

Option Explicit
Sub concatenateEntities()

    Dim r As Integer
    r = 0

End Sub

With "r" set to zero, we will follow with our Do Loop. The basic Do Loop to accomplish the procedure described above is as follows:

    Do Until ActiveCell.Offset(0, -6) <> ActiveCell.Offset(r, -6)

        ActiveCell.Value = ActiveCell.Value & "; " & ActiveCell.Offset(r, -5)
        r = r + 1

    Loop

By running the macro with cell G2 selected on a spreadsheet containing my entity table, the active cell should now read as follows: ";  ABC Corporation;  Doe Family Partnership;  Doe Rental Property, LLC;  Doe Equipment, LLC". Here is how this was accomplished:

The "Do Until" line tells the computer to repeat the code that follows until it says "Loop," repeating until the client name changes. It starts by comparing the value in column A in the same row as the active cell with the value in column A in each subsequent row, one at a time. The "r = r + 1" line keeps increasing the count of the row after the prior line sets the active cell equal to whatever it's current value, followed by a semicolon, followed by the value of the cell in column B that contains the next entity name on the list.

Notice that we probably don't want to have the first semicolon that appears in the active cell, which occurs due to the fact that the first time the code sets the active cell equal to itself before adding the semicolon, the active cell has nothing in it. See if you can use an If Then statement to fix that problem; I will post my solution in the comments that follow. For a tutorial on recording a macro, please stay tuned for my next installment of Basic Visual Basic for Excel.

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.

Basic Visual Basic for Excel

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.