Excel VBA: No Experience Necessary - Lesson 3

A quick recap…
In Lesson 1, we covered the introductory topics of: recording and running a macro, the Visual Basic Editor, the for loop, and referencing a cell. In Lesson 2, we covered variables and data types, Excel objects, properties, and methods, and object collections. In this, Lesson 3, I will touch on debugging, user-defined functions, and using Excel functions in VBA code.

But first, the solution to the exercise at the end of Lesson 2. The challenge was to write a subroutine called "CreateWorkbook" which would automatically create a new workbook and set the names of each worksheet in the new workbook according to names stored in the first sheet of the initial workbook.

You may download the complete solution here: Excel VBA Article 2 Example Solution.xls (in zipped format).

The first step is easy. A new Excel workbook can be created by simply using the Add method of the Workbooks collection:

Example 1a.

Sub CreateWorkbook()
    Workbooks.Add
End Sub

Next we need to determine how many worksheets should be in the new workbook. We can do this by counting how many names are listed in the original workbook (the workbook with the VBA code). Keep in mind that we now have two workbooks open, the original and the new one created by our subroutine. When we added the new workbook, it became the "active" workbook, but since we want to count the names which are stored in the original workbook, we need to reference it as "ThisWorkbook". This signifies that the code is referencing the workbook in which it (the code) is contained.

We are assuming that the names are contained on the first worksheet, so we will use ThisWorkbook.Worksheets(1). You may remember from Lesson 1 that we can find out how many rows are actually used in a worksheet by looking at the "UsedRange" property of the Worksheet object.

Example 1b.

Sub CreateWorkbook()

    Dim iSheetCount As Integer

    Workbooks.Add
    iSheetCount = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count

End Sub

The next step is to set the correct number of worksheets in the new workbook. To do this we will use two separate "while" loops. One will be used if we need to remove worksheets, the second if we need to add worksheets. As I mentioned above, when a new workbook is added, it becomes the active workbook, so we can reference the new workbook using "ActiveWorkbook".

Example 1c.

Sub CreateWorkbook()

    Dim iSheetCount As Integer

    Workbooks.Add
    iSheetCount = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count

    Application.DisplayAlerts = False
    ' If we have too many worksheets, delete one at a time until
    ' we reach the correct number:
    While ActiveWorkbook.Worksheets.Count > iSheetCount
        ActiveWorkbook.Worksheets(1).Delete
    Wend

    ' If we don’t have enough worksheets, add more one at a time
    ' until we reach the correct number:
    While ActiveWorkbook.Worksheets.Count < iSheetCount
        ActiveWorkbook.Worksheets.Add
    Wend

End Sub

The line "Application.DisplayAlerts = False" is handy when you want your VBA code to do something that Excel normally gives you an "Are you sure?" message about when you try to do it by hand. Without this line of code, Excel will display a warning message each time the code tries to delete a worksheet. Setting the DisplayAlerts property of the Application object to False turns off such messages.

Lastly, we need to set the names of the worksheets in the new workbook according to the contents of the original workbook. For this we will use a simple "for" loop, reading each name from ThisWorkbook, and assigning it to the Name property of each Worksheet object in the ActiveWorkbook.

Example 1d.

Sub CreateWorkbook()

    Dim iSheetCount As Integer
    Dim iSheet As Integer

    Workbooks.Add
    iSheetCount = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count

    Application.DisplayAlerts = False
    ' If we have too many worksheets, delete one at a time until
    ' we reach the correct number:
    While ActiveWorkbook.Worksheets.Count > iSheetCount
        ActiveWorkbook.Worksheets(1).Delete
    Wend

    ' If we don’t have enough worksheets, add more one at a time
    ' until we reach the correct number:
    While ActiveWorkbook.Worksheets.Count < iSheetCount
        ActiveWorkbook.Worksheets.Add
    Wend

    For iSheet = 1 To iSheetCount
        ActiveWorkbook.Worksheets(iSheet).Name = _
            ThisWorkbook.Worksheets(1).Cells(iSheet, 1).Value
    Next iSheet

End Sub

Note that we use the iSheet variable as both a reference into the Worksheets collection of the new workbook, and as the row number when reading the names from the original workbook.

You may also notice that an underscore character can be used to split a long line of code over two lines. The underscore must be preceded by a space.

Debugging
When something goes horribly awry in your code, or your code doesn’t produce the results you expect, you can use the Debug and Run menus in the Visual Basic Editor to help locate the source of the problems. Below are some of the most useful commands:

Before you run your code, save your workbook. It doesn’t happen often, but you may inadvertently have written code which is truly evil and will cause Excel to crash. You don’t want to lose your code, diabolical though it may be. Also, pay particular attention to the keyboard shortcut Ctrl+Break. If your code has gone into an infinite loop, or something is taking much longer than you expected, Ctrl+Break is the easiest way to stop it. Once execution has been halted, you can either Reset it, or step through from where it was paused, using F8.

Try using the debugging commands to step through the homework example solution. As you step through line by line, you can actually Alt+Tab (or Alt+F11) between the VBA Editor and Excel to see each worksheet added and each worksheet name as it changes.

Functions
All of the examples that we’ve used so far have been in the form of subroutines, aka macros. In Lesson 1, I mentioned that your VBA code would be in the form of subroutines and functions, but I have yet to define what a function is, or what the difference is between a function or a subroutine.

Well, the main difference is that a function returns a value. In other words, you can call a function from a subroutine (or another function), and use the value that the function passes back. This may be more easily understood with a simple example.

Example 2.

Function UpperFileName() As String
    UpperFileName = UCase(ActiveWorkbook.Name)
End Function

Sub ShowFileName()
    MsgBox UpperFileName()
End Sub

Here we’ve defined a simple function called UpperFileName. We’ve declared the function type (string) in a way similar to how we declare a variable, using "As" followed by a data type. This represents which data type will be returned by the function. In this example, the function returns a string, which it creates by getting the file name of the current active workbook, and converting it to upper case (using the VB function UCase). We return this string value by using the function name as if it were a variable, and setting it equal to the string. The subroutine ShowFileName calls the function UpperFileName and displays the returned value in a message box.

(This may be apparent to you if you’ve written code before, but if you’re one of the brave souls who’s never programmed before and made it this far, you should note that you can call one subroutine or function from another subroutine or function.)

Function and Subroutine Parameters
To really make a worthwhile function, you will need the ability to send values into the function. These values are known as either parameters or arguments. For example, suppose you wanted to write a function which converted a temperature from degrees fahrenheit to degrees celsius. This function would need to take as input a temperature in fahrenheit. You can do this by listing the parameter with its type within the parentheses that follow the function name.

Example 3.

Function Celsius(dFahrenheit As Double) As Double
    Celsius = (dFahrenheit - 32) * (5 / 9)
End Function

Sub ConvertTemperature()
    Dim dtemperatureF As Double
    Dim dtemperatureC As Double
    dtemperatureF = Cells(1, 4).Value
    dtemperatureC = Celsius(dtemperatureF)
    Cells(2, 4).Value = dtemperatureC
End Sub

The function, named Celsius, is declared as a double, meaning that it returns a double-precision floating point value. It takes, as a parameter, a double value called dFahrenheit. It convert dFahrenheit to the corresponding celsius temperature, then returns this value.

The subroutine ConvertTemperature reads a fahrenheit value from cell D1, converts it to celsius using our function, and places the result in cell D2.

Functions can take more than one parameter. Each parameter should be separated by a comma. The function below, for example, can convert a number to either celsius or fahrenheit, depending on the value of the second parameter, bConvertToCelsius. This is a Boolean variable, which means that it can have a value or either True or False.

Example 4.

Function CelsiusOrFahrenheit(dTemperature As Double, _
    bConvertToCelsius As Boolean) As Double
  If bConvertToCelsius Then     'dTemperature is in Fahrenheit,
                                'so convert to Celsius
        CelsiusOrFahrenheit = (dTemperature - 32) * (5 / 9)
    Else         'dTemperature is in Celsius, convert to Fahrenheit
        CelsiusOrFahrenheit = dTemperature * (9 / 5) + 32
    End If
End Function

Subroutines can also take parameters. Note, however, that a subroutine which requires parameters cannot be called directly from Excel’s Tools, Macros menu. It must be called from another subroutine (or function).

Using Your Functions in the Spreadsheet
The coolest thing about writing your own functions is that you can actually use them directly in your spreadsheet. All of the examples above are contained in the Excel VBA Article 2 Example Solution.xls (link above). If you haven’t already been adding the examples to your own workbook, download the Article 2 Example Solution workbook.

In order to use your function in the spreadsheet, you only need to make one minor change. In the VBA Editor, add the word "Public" before the word "Function". This simply makes the function accessible from outside the VBA module in which it resides.

Next go to the spreadsheet, enter a number (temperature) in one cell, then click in an empty cell. Select Insert, Function from the main menu. The last item in the Function Category list on the left is called User Defined. Click on this and you will see your user-defined functions listed magically on the right. Double-click on Celsius. You will then be presented with a box in which you are prompted for the parameter which the function requires, dFahrenheit. Click on the cell in which you typed a temperature, then click OK. Your temperature has been converted from Fahrenheit to Celsius!

I don’t know about you, but I was pretty excited when I learned I could write my own functions, then use them in my spreadsheet. Then again, maybe you’re not as big a geek as I.

Using Excel Functions in Your Code
Just as you can use your own functions in a spreadsheet, you can also use Excel functions in your code. This is done using the WorksheetFunction object (which is actually a property of the Application object). The WorksheetFunction object provides your VBA code with access to Excel worksheet functions. The example below (also in the Example Solution spreadsheet) calculates the average and standard deviation for each column of numbers on the second worksheet, and places the results below the numbers.

Example 5.

Sub UseExcelFunctions()

    Dim iColumnCount As Integer
    Dim lRowCount As Long
    Dim iCol As Integer
    Dim dAverage As Double
    Dim dStdDev As Double

    Worksheets(2).Activate
    ' Count number of columns and rows in use...
    iColumnCount = ActiveSheet.UsedRange.Columns.Count
    lRowCount = ActiveSheet.UsedRange.Rows.Count

    ' Cycle through each non-empty column...
    For iCol = 1 To iColumnCount
        ' Use the Average and StDev Excel functions to
        ' calculate values for each column...
        dAverage = WorksheetFunction.Average(Columns(iCol))
        dStdDev = WorksheetFunction.StDev(Columns(iCol))
        ' Place results below existing data...
        Cells(lRowCount + 2, iCol).Value = dAverage
        Cells(lRowCount + 3, iCol).Value = dStdDev
    Next iCol

End Sub

"Why Mark?" you ask. "Why write code to do this when I can simply put the actual formulas right in the spreadsheet?" While I admit I do not often use Excel functions in my VBA code, I can think of a couple of instances in which this might come in handy. One example might be if you had to calculate a number of statistics for multiple sets of data, each on a separate worksheet within your workbook, and list all of these statistics on a summary worksheet. This might be easier to do with code. Also, there may be times when you need to send a spreadsheet of calculated data to a client, and don’t want to have any formulas in the spreadsheet, only data. This is a circumstance I often encounter in my work.

An Exercise for You
If you’re game, here’s an exercise for you to try. I will present the solution in my next article. Your assignment, should you choose to accept it, is to write a function called "DayOfYear" which takes one parameter, a Date, and returns an integer which represents which day of the year that date represents. For example, if the date is Feb 1, 2004, the function should return 32. If Dec 31, 2004, it should return 366. Date is another VBA data type and represents both date and time. You will use various VBA date-related functions, such as Month, Day, and Year to write your function. Take a look at the VBA Excel help file for more information on the Date data type and these function.

The second part of the exercise is to write a second function called "ZodiacSign", which also takes a Date parameter, and returns a string representing the sign of the zodiac which corresponds to that date (Capricorn, Aquarius, etc). You will call your DayOfYear function from the ZodiacSign function. Add some dates in your spreadsheet and apply each of your functions to each date.

Here is some sample data you should try to match:

One last hint… I’ve covered the "if" statement briefly, and the "else" clause that can go with it. For your functions, you will set up a series of conditions using "elseif", which allows your code to check if a second (or third, fourth…) condition is true when the previous condition is not. See example 6 below.

Example 6.

If iScore >= 90 Then
    sGrade = "A"
ElseIf iScore >= 80 Then
    sGrade = "B"
ElseIf iScore >= 70 Then
    sGrade = "C"
ElseIf iScore >= 60 Then
    sGrade = "D"
Else
    sGrade = "F"
End If

Good luck.

Need further help getting your Excel VBA code right? Join our free Excel VBA support group! See this link for details: http://groups.yahoo.com/group/ExcelVBA/ .