Starting with VBA for MS Excel
How to write macros in Microsoft Excel to perform simple decision based calculations using VBA.
Decision based calculations are often at the core of many programming tasks. Combining Excel and Visual Basic for Applications can help to build efficient solutions.
Introduction
Visual Basic for Applications (or VBA) is a variation on the Visual Basic language which extends the basic syntax to include specific object trees used to describe Microsoft Office application components. These include documents, worksheets, and other containers, as well as some useful built-in functions for manipulating them.
Like VB, VBA is based on the BASIC language, and so should be familiar. Unlike VB, it is interpreted-only, when used in the context of Microsoft Office, and as such might be significantly slower than it's compiled cousin. It is generally only used to enhance an application document, or connect across Microsoft Office applications.
The blog entry (here) gives the basic premise. It is worthwhile to read it and then follow the link back to this page, to give some context to the article. If, however, the reader is familiar with the conditional sum statement, then what follows might prove familiar enough to skip over the blog entry.
The Conditional Sum Plug-in
Let us fist recap what the solution is trying to achieve. We wish to use data in the Active Sheet to provide conditions under which data from the Data sheet should be summed. For a clear explanation of the Conditional Sum function, just search for it in the Excel help files.
However, the Conditional Sum function of Excel does not quite manage to sum data in a specific cell in a row, based on possible values taken from other rows, where the conditions are fed in from cells in another sheet. The blog entry gives a specific example of this.
So, a macro needs to be created to automatically sift the input data and sum those rows which match the conditions from the Active Sheet. A macro, however, is not an efficient way to proceed. We will need a real VBA solution.
The VBA Solution
Implementing the VBA solution requires that we create a function (which is a procedure that returns a value) to perform the actual condition sum. We then need to call that function with the range of cells that contain the conditions under which the total should be returned.
We assume that the extreme left and top cells give the conditions, and that the cell itself should contain the total. It is the user that provides the range, as it could change from sheet to sheet.
If the reader is following with Excel open, then the first thing to do is to open the VBA editor. This can be arrived at in one of two ways:
- Alt+F11 on the keyboard
- 'Tools->Macro->Visual Basic Editor' from the menu
From here, code can be introduced.
The Summing Function
We define our conditional sum function as follows:
End Sub
The above function returns a large number (Double) and takes the row and column as input parameters.
Next, we need to retrieve the condition values from the Active Sheet. We assume that these are the Fruit and Size, from the first cell in nRow and the first cell in nColumn, respectively.
szFruitSize = ActiveSheet.Cells(1, nColumn)
One quirk of Excel is that the Cells collection in a Sheet is referenced by row, column (y, x) which runs contrary to some usual programming practices where arrays tend to be referenced (x, y). Bearing this in mind, we can create the loop which will cycle through the Data sheet:
Rem Do calculation here
Next nCellRow
Finally, we can insert the conditional summing code:
If StrComp(curRow.Cells(1, 1), szFruit) = 0 Then
If StrComp(curRow.Cells(1, 2), szSize) Then
nTotal = nTotal + curRow.Cells(1, 3)
End If
End If
Notice, that in the above,we have returned the current row in curRow as a Range object. As such, there is only one row, so the Cells collection will always be in the Cells(1, nColumn) form. Trying to access other rows will fail, as the curRow range only contains a single row.
In order to fill a range of cells in the Active Sheet, we create a separate function to ask the user for the range, and kick off the SumWithCriteria function:
szStart = InputBox("Please enter starting row, column")
szEnd = InputBox("Please enter ending row, column")
sStartRowCol = Split(szStart, ",")
sEndRowCol = Split(szEnd, ",")
For nRow = sStartRowCol(0) To sEndRowCol(0)
For nColumn = sStartRowCol(1) To sEndRowCol(1)
If Not IsEmpty(ActiveSheet.Cells(nRow, 1)) Then
ActiveSheet.Cells(nRow, nColumn) = SumWithCriteria(nRow, nColumn)
End If
Next nColumn
Next nRow
End Sub
To activate this we can just choose 'Tools->Macros->Macro...' or press Alt+F8 from the keyboard. We then select the StartSumWithCritera macro, and follow the instructions.
Summary
The above is not optimized. It is presented here as a possible solution to the specific problem outlined in the blog entry. If anything is unclear, please feel free to start a discussion below. If there are better ways to write the code (of course there are), then please share them with the group!
We could also change the formatting of the cell, according to the result of the conditional sum. The following article Conditionally Changing Cell Color and Formatting with VBA in MS Excel shows exactly how to do this. Have fun!
| Here's the follow-up discussion on this article: | View all related messages |
For a complete listing of article comments, questions, and other discussions related to Guy Lecky-Thompson's Computer Programming topic, please visit the Discussions page.