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.

Read More

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.

Read More

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.

Read More

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.

Read More

Excel's Sumproduct Function

Excel's SUMPRODUCT function is more useful than it appears.  On its surface, SUMPRODUCT simply "returns the sum of the products of corresponding ranges or arrays." The easiest way to demonstrate this is to use the following table:

	A	B
1	5	3
2	2	6
3	0	10
4	1	8

The formula =SUMPRODUCT(A1:A4,B1:B4) yields 35, which is the sum of the products of columns A and B, calculated as follows: (5*3)+(2*6)+(0*10)+(1*8).

While this functionality alone can be useful in some contexts, the real power of SUMPRODUCT comes from utilizing criteria. The values in any column can be logically tested as TRUE or FALSE in Excel. The logical value TRUE is represented by the number one and the logical value FALSE is represented by zero.

With that in mind, we can analyze a larger table that lists a client's name, the business entities associated with that client, and the due date and fee amount of the annual renewal filing required by the state the entity is formed in:

	A	B	C	D	E	F
1	Client	Entity Name	Entity Type	State	Month Due	Amount
2	John Doe	ABC Corporation	Corp	UT	Apr	15
3	John Doe	Doe Family Partnership	LP	UT	Dec	15
4	John Doe	Doe Rental Property, LLC	LLC	DE	Jun	250
5	John Doe	Doe Equipment, LLC	LLC	DE	Jun	250
6	Bill Smith	Bill Smith, DDS, Inc.	Corp	NV	Jan	325
7	Bill Smith	Smith Family Partnership	LP	WY	Dec	50
8	Bill Smith	Smith Equipment, LLC	LLC	DE	Jun	250
9	Bill Smith	Smith Business Property, LLC	LLC	NV	Oct	325
10	Bill Smith	Smith Rental Property, LLC	LLC	NV	Oct	325
11	Bill Smith	Smith Vacation Home, LLC	LLC	WY	Feb	50

Suppose that Bill Smith is wondering what he will be paying each year for all his annual state renewal filings. This can be easily calculated with the following formula: =SUMPRODUCT(--(A2:A11="Bill Smith"),F2:F11). The result is 1,325.

The array in column A must equal "Bill Smith" in order for the figure in column F to be included in the total. The first four rows do not equal Bill Smith so they return FALSE, and the remaining rows do equal Bill Smith so they return TRUE. The "--" turns the Boolean values TRUE and FALSE into the integer value 1 and 0 respectively. The products of the values from column A and the values in column F added together equal 1,325.

Of course, the above result is also possible with the following SUMIF formula: =SUMIF(A2:A11,"Bill Smith",F2:F11). However, SUMIF does a poor job of handling multiple criteria, while SUMPRODUCT can. Following are some additional examples of SUMPRODUCT formulas and the information they return.

To find the total fees due for Bill Smith's LLCs, use this formula: =SUMPRODUCT(--(A2:A11="Bill Smith"),--(C2:C11="LLC"),F2:F11), which results in 950.

To find the total fees due for Bill Smith's Nevada LLCs, use this formula: =SUMPRODUCT(--(A2:A11="Bill Smith"),--(C2:C11="LLC"),--(D2:D11="NV"),F2:F11), which results in 650.

To find what all clients will be paying for LLC fees in the month of June, use this formula: =SUMPRODUCT(--(C2:C11="LLC"),--(E2:E11="Jun"),F2:F11), which results in 750.

In summary, SUMPRODUCT simply returns the sum of the products of corresponding ranges or arrays. However, if multiple arrays are used as filters, its usefulness increases dramatically.

Read More

In Excel, Use Index-Match Instead of Vlookup

While searching the web for help with a nested VLOOKUP formula I was attempting, I happened upon a post by Charley at ExcelUser Blog about the INDEX-MATCH function combination that has made working with spreadsheet data a little easier and faster for me. VLOOKUP searches a table for a value in the left-most column and can return a value in the same record from different column to the right. A number of times when using VLOOKUP, I've needed to search for a value in a column other than the left-most column and return a value from a record in a column to the right or left.

For example, the following state abbreviation table can be used with VLOOKUP to return the full state name by looking up the abbreviation, but not vice versa.

	A	B	C	D
1	AL	Alabama		1
2	AK	Alaska		Alabama
3	AZ	Arizona		AL

Of course, I could copy column A and paste into column C and use the table B1:C3 to return the abbreviation by looking up the full state name, but that isn't very clean and becomes impractical with larger tables and more complicated data needs.

INDEX-MATCH uses two functions. The MATCH function returns the record number to the INDEX function, which returns the data actually being sought. The formula in D1 is =MATCH(A1,A1:A3,0). The first argument is the search term, "AL", the next argument specifies the table being searched, and third argument specifies that we want an exact match. The result of the formula is the row number on which the lookup value, "AL", appears.

The formula in D2 utilizes the INDEX function to return the actual value we want, the full state name. The first argument is the table or array, and the second argument is the record number, calculated by the same MATCH function from D1. The formula is =INDEX(B1:B3,MATCH(A1,A1:A3,0)). Note that the two table references need to start and stop on the same cells or else incorrect data could result.

The formula in D2 could just as easily be =VLOOKUP(A1,A1:B3,2,FALSE). However, INDEX-MATCH does not require the lookup value to be in the left most column. The formula in D3 looks up the value in B1 and returns the corresponding abbreviation: =INDEX(A1:A3,MATCH(B1,B1:B3,0)). VLOOKUP is unable to accomplish this with the current table.

These same principles apply with HLOOKUP, which is is the same as VLOOKUP except it utilizes a table turned on its side. INDEX-MATCH is more flexible and it apparently faster as well.

Read More