http://pubs.logicalexpressions.com/pub0009/lpmarticle.asp?id=349

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:

Command


Shortcut Key


Toolbar Button


Description

Run


F5




Executes the current subroutine or function

Toggle Breakpoint


F9





Sets or Clears a breakpoint, which pauses execution of the code

Step Into


F8





Steps through each line of code (when one subroutine is called from another this will "step into" the called subroutine)

Step Over


Shift + F8





Steps through each line of code (when one subroutine is called from another this will "step over" the called subroutine)

Reset







Stops execution of your code. You can restart execution only from the beginning.

Break


Ctrl+Break




Temporarily halts execution of your code. You can continue execution from the where it left off.

Quick Watch


Shift + F9





When execution is paused, displays the value of the highlighted variable. (You may also view the value of a variable by pausing the cursor over it)

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.