In Chapter 9, “Using Built-in Functions,” you learned how to use VBA's built-in functions. In this chapter, you'll learn how to create your own functions. You create a function the same way you create a subprocedure: by typing in the Code window. (You can't record a function in Excel and Word—the applications that provide a Macro Recorder. Instead, you have to write functions yourself because the Recorder creates only subprocedures.)
It's important to recall that, although both are procedures, functions differ from subs. The primary difference is that functions interact more with other procedures: They accept arguments (incoming data) from the procedure that calls them, and they return a value (outgoing data) back to the procedure that calls them. Subs, by contrast, normally don't require arguments and never return any data.
But functions are used in VBA far less often than subs. Most macros are self-contained subs. That's because most macros are small, brief automations: They perform simple, quick jobs like inserting a date into a document or saving a document using a particular filename.
But you aren't limited to brief macros. You are free to create more complex, larger, and more sophisticated programs in VBA. And if you do create a large project, you'll want to use multiple procedures, not just one sub. This allows you to divide your work into multiple logical units that can each be individually tested and more easily modified. When you're using multiple procedures, however, they must work together and need to communicate among themselves. This is why you often use functions in large projects. Remember that the key feature of a function is that it facilitates communication—sending values back and forth—among multiple procedures.
This chapter will cover several ways to employ functions with the various Office 2013 applications. I'll start by explaining the components of a function and showing you how to put them together. You'll then create some functions that work in any VBA host and some functions that are specific to Word, Excel, and PowerPoint.
In this chapter you will learn to do the following:
To create a function, you use a Function statement. This is essentially the same way you create a Sub: just type in the word Function followed by the name you're giving the function.
The syntax for the Function statement is as follows:
[Public | Private] [Static] Function function_name [(argument_list)] [As type] [statements] [function_name = expression] [Exit Function] [statements] [function_name = expression] End Function
This syntax, most of which is optional, breaks down like this:
Function AddStateTax(SubTotal) AddStateTax = SubTotal * 1.07 'do the math and assign the result 'to the function name so it gets passed back End Function
[Optional] [ByRef | ByVal] [ParamArray] variable_name[( )] [As type] [= default_value]
Here's a description of the elements of the argument_list:
YOU CAN PASS TO A FUNCTION EITHER A VALUE'S ADDRESS OR A COPY OF THE ACTUAL VALUE
When a procedure (either a function or a subroutine) passes an argument to a function by reference, the recipient procedure gets access to the actual memory location where the original variable is stored and can thus change the value held in the original variable. By contrast, when an argument is passed by value, the function gets only a copy of the information in the variable and therefore can't change the value held in the original variable (the recipient procedure doesn't even know where the original variable is located). By reference is the default way to pass an argument, and there is rarely any reason to pass by value, so just use the default.
The following sections walk you through the process of creating a function.
The easiest way to start creating a function is to type into the VBA Code window the word Function followed by the name you want to give to the function and any necessary arguments in parentheses, and then press Enter. VBA automatically enters a blank line and an End Function statement for you and places the insertion point on the blank line ready for you to create the programming code inside the new function.
For example, if you type the following line and press Enter, the Visual Basic Editor displays what you see in Figure 10.1:
Function MyFunction(MaxTemp, MinTemp)
FIGURE 10.1 When you type a Function statement and press Enter, the Visual Basic Editor automatically inserts a blank line and an End Function statement for you.
If you like to make the Visual Basic Editor work for you as much as possible (and prefer the slow way of doing things), you can also start creating a new function by using the Add Procedure dialog box:
FIGURE 10.2 You can also use the Add Procedure dialog box to specify elements of a new function.
The arguments that will be passed to a function are listed in parentheses, separated by commas. In the following example code, the function states that it requires an argument named MaxTemp and an argument named MinTemp. These data must be passed to (sent to) this function for it to work:
Function GetTemps(MaxTemp As Double, MinTemp As Double)
If somewhere in your code you attempt to call this function without passing the data it requires, VBA will display the error message “Argument Not Optional.”
You can also specify the data type of the arguments if you want by including an As statement with the data type after the argument's name. For example, you could use the following statement to set the MaxTemp and MinTemp arguments to the Double numeric data type:
Function GetTemps(MaxTemp As Double, MinTemp As Double)
Passing an argument by reference (the default) is useful when you want to manipulate the variable in the recipient procedure and then return the variable to the procedure from which it originated. Alternatively, passing an argument by value is useful when you want to use the information stored in the variable in the recipient procedure and at the same time ensure that the original information in the variable doesn't change (but this isn't typically necessary).
Because by reference is the default way of passing an argument, both of the following statements pass the argument MyArg by reference:
Function PassByReference(MyArg) Function PassByReference(ByRef MyArg)
As you see, you can omit the default ByRef command. However, to pass an argument by value, you must use the ByVal keyword. The following statement passes the ValArg argument by value:
Function PassByValue(ByVal ValArg)
If necessary, you can pass some arguments for a procedure by reference and others by value. The following statement passes the MyArg argument by reference and the ValArg argument by value:
Function PassBoth(ByRef MyArg, ByVal ValArg)
In practice, though, you're likely to simply use the default ByRef approach for most, if not all, of your programming.
You can explicitly declare the data types of arguments. This conserves memory (although this is rarely an issue anymore) and ensures that the outside (calling) procedures are passing the correct type of information to your function. For this second reason, it's always a good idea to specify the data type. You avoid some kinds of errors that way.
When passing an argument, you want to ensure that the data type of the argument you're passing matches the data type expected in the procedure. For example, if you declare a string and try to pass it as an argument when the receiving function specifies that it is expecting a Variant, VBA displays an error message.
To declare the data type of an argument, just include the usual data-type declaration in the argument list. The following statement declares MyStrArg, specifying with As that a string must be passed and specifying a variant with VarArg:
Function PassType(MyStrArg As String, VarArg As Variant)
You can specify an optional argument by using the Optional keyword:
Function PassBoth(ByRef MyArg As String, ByVal ValArg As Variant, _ Optional ByVal strName As String)
When you specify an optional argument, it's a good idea to assign a default value to it. Doing so makes the code less susceptible to errors and gives the programmer a clue as to what kind of information is used here. To assign the default value, type an equal sign after the variable's definition, and then type the default value (use double quotation marks for a String value). For example, the following function statement declares the strName optional argument and assigns the default value if no value is passed:
Function PassBoth(ByRef MyArg As String, ByVal ValArg As Variant, _ Optional ByVal strName As String = "Sacramento")
What happens here is that this macro is being used by a company located in Sacramento, so they most often use that city's name for the literal value in this particular macro. Your default literal will differ, depending on what your macro is supposed to accomplish.
Like a subroutine, a function can have private or public scope. Private scope makes the function available only to procedures in the module that contains it, and public scope makes the function available to all open modules in your project.
If you don't specify whether a function is private or public, VBA makes it public by default, so you don't need to specify the scope of a function unless you want it to have private scope. However, if you do use explicit Public declarations for those functions you want to be public, your code will be somewhat easier to read:
Private Function MyFunction(MaxTemp, MinTemp) Public Function AnotherFunction(Industry, Average)
This part of the chapter contains two examples of functions that will work in any application that hosts VBA. That's because these functions don't access objects particular to any specific Office application.
Later in this chapter, you'll see examples of functions that employ resources or features particular to a specific Office application.
To start, first declare the function and its arguments. The following statement declares a function named NetProfit:
Function NetProfit(Gross As Double, Expenses As Double) As Double
NetProfit uses two arguments, Gross and Expenses, declaring each as the Double data type; it's a floating-point (has a decimal point) number.
At the end of this statement, we have specified that our function returns a Double value type. It's important to explicitly specify the variable types of the arguments and the type of the value that the function returns to the caller. This avoids unpleasant surprises (bugs) in your code because VBA catches and reports any attempt to pass the wrong data type to the function or send the wrong type of data back to whatever code called (executed) your function.
Armed with the arguments (and their type, if you explicitly type them as I'm suggesting you do), you call (execute) your NetProfit function the same way you would execute a prewritten function that's built into VBA (like MsgBox). You simply use the function's name and supply the two arguments it needs, like this:
MyProfit = NetProfit(44000, 34000)
Here, the variable MyProfit is assigned the value of the NetProfit function. In other words, after this function finishes its job and execution resumes in the caller (the procedure that invoked the function), the returned value is assigned to the variable MyProfit.
In this example, the NetProfit function is provided with a Gross argument of 44000 and an Expenses argument of 34000.
Once you've created a function, the Visual Basic Editor displays its argument list when you type the name of the function in a caller procedure, as shown in Figure 10.3.
FIGURE 10.3 The Visual Basic Editor displays a ToolTip of Auto Quick Info for functions you create as well as for its built-in functions.
Listing 10.1 contains an example of calling a function: The ShowProfit procedure calls the NetProfit function and displays the result in a message box.
LISTING 10.1: How to call a function
1. Sub ShowProfit() 2. MsgBox (NetProfit(44000, 34000)),, "Net Profit" 3. End Sub 4. 5. Function NetProfit(Gross As Double, Expenses As Double) As Double 6. NetProfit = (Gross - Expenses) * 0.9 7. End Function
In Listing 10.1, lines 1 through 3 contain the ShowProfit procedure, which simply calls the NetProfit function in line 2, passes it the arguments 44000 for Gross and 34000 for Expenses, and displays the result in a message box titled Net Profit. Notice that in line 2 we have employed a shortcut: using the function call inside an argument list. Line 2 does the same thing as this longer version:
Dim result as double Result = NetProfit(44000, 34000) MsgBox (Result),, "Net Profit"
Lines 5 through 7 contain the NetProfit function. Line 5 declares the function as working with two Double arguments, Gross and Expenses, telling VBA what to do with the two arguments that line 2 has passed to the function.
Line 6 calculates NetProfit to be 90 percent (0.9) of the value of Gross minus Expenses.
It's important to notice what else happens in line 6: the information calculated by the function is being assigned to the name of the function. This is how the information gets passed back to the ShowProfit procedure that called the function.
To make this process a bit clearer, let's write the code in a more verbose way. We'll do this the long way, without using the shortcut of doing both the calculating and assigning all on the same line. Here's how a function goes about its business.
There are three main steps: calculation, assignment, and return. They are labeled as lines 1, 2, and 3 in the following listing.
The function first does some computing—in this case, calculating a net profit. Then, second, it assigns the results of the calculation to its own name (NetProfit in this case). This assignment is how the data gets passed back to the caller. And finally, third, with the End command, it sends the results back to whatever procedure called the function:
Function NetProfit(Gross As Double, Expenses As Double) As Double Dim Result As Double 1. Result = (Gross - Expenses) * 0.9 'do the calculating 2. NetProfit = Result 'store the information to be sent back 3. End Function 'send the information back
Listing 10.2 contains a function that returns a String argument.
LISTING 10.2: A function that returns a string
1. Sub TestForSmog() 2. Dim intCYear As Integer, strThisCar As String 3. BadValueLoop: 4. On Error GoTo Bye 5. intCYear = InputBox("Enter the year of your car.", _ "Do I Need a Smog Check?") 6. strThisCar = NeedsSmog(intCYear) 7. If strThisCar = "Yes" Then 8. MsgBox "Your car needs a smog check.", _ vbOKOnly + vbExclamation, "Smog Check" 9. ElseIf strThisCar = "BadValue" Then 10. MsgBox "The year you entered is in the future.", _ vbOKOnly + vbCritical, "Smog Check" 11. GoTo BadValueLoop 12. Else 13. MsgBox "Your car does not need a smog check.", _ vbOKOnly + vbInformation, "Smog Check" 14. End If 15. Bye:
16. End Sub 17. 18. Function NeedsSmog(CarYear As Integer) As String 19. If CarYear > Year(Now) Then 20. NeedsSmog = "BadValue" 21. ElseIf CarYear <= Year(Now) - 3 Then 22. NeedsSmog = "Yes" 23. Else 24. NeedsSmog = "No" 25. End If 26. End Function
Listing 10.2 contains the procedure TestForSmog (lines 1 through 16) and the NeedsSmog function (lines 18 through 26). The TestForSmog procedure calls the NeedsSmog function, which returns a value indicating whether the user's car needs a smog check. TestForSmog uses this value to display a message box (see Figure 10.4) informing users whether or not their car needs a smog check.
FIGURE 10.4 The TestForSmog procedure prompts for the car's year and then displays a message box stating whether the car needs a smog test.
Here's how the code works:
GoTo BadValueLoop
Functions can be more complex than the simple, stand-alone examples shown here. For instance, you can include a function as part of a larger expression. You could add the results of the functions NetProfit and CurrentBalance (which takes a single argument) by using a statement such as this:
CurrentEstimate = NetProfit(44000, 33000) + CurrentBalance(MainAccount)
Functions such as those shown in the previous section work in any VBA-hosting application because they do not call any application-specific features. This section and the following three sections show you examples of functions that are specific to applications.
The task accomplished by the example program shown in Listing 10.3 is to remove some special types of formatting (hyperlinks, bookmarks, and fields) but retain any text in those special zones.
Real World Scenario
CREATING CUSTOM FUNCTION LIBRARIES
Some programmers like to keep functions they write (that aren't application-specific) in separate modules in the Editor. These little libraries represent your own collections of tested, useful, generic procedures. Need to calculate sales tax? Don't reinvent the wheel. Just import your library of math functions, among which is just this procedure. You can export a module as a file with the .bas filename extension and import it into whichever application needs the functions. Choose File Export File (or press Ctrl+E). For example, you might maintain separate modules that contain your math equations, your string-manipulation functions, and other custom functions that work in any VBA host. A .bas file is merely an ordinary text file containing a module's source code (its subroutines and functions). You can read it in Notepad, but you can also use the File Import feature to add it to a VBA project. When imported, it will appear in the Project Explorer as a new module.
The function shown in Listing 10.3 is for Word and—unusually for a function—returns no information (technically it returns a null value). The function's main purpose is to perform several operations on the specified document. So no data needs to be returned to the caller.
LISTING 10.3: A function that returns a null value
1. Option Explicit 2. 3. Function Strip_Hyperlinks_Bookmarks_Fields() 4. Dim myLink As Hyperlink 5. Dim myBookmark As Bookmark 6. Dim myField As Field 7. With ActiveDocument 8. For Each myLink In .Hyperlinks 9. myLink.Delete 10. Next myLink 11. For Each myBookmark In .Bookmarks 12. myBookmark.Delete 13. Next myBookmark 14. For Each myField In .Fields
15. myField.Unlink 16. Next myField 17. End With 18. End Function 19. 20. Sub Clean_Up_Document_for_Conversion() 21. Call Strip_Hyperlinks_Bookmarks_Fields 22. 'other cleanup functions here 23. End Sub
Here's how the code works:
This section shows you a function for Excel. The function in Listing 10.4 checks whether a workbook contains any unused sheets.
LISTING 10.4: An Excel function
1. Option Explicit 2. 3. Function BlankSheetsInWorkbook(ByRef WorkbookToTest As Workbook) As Boolean 4. Dim objWorksheet As Worksheet 5. BlankSheetsInWorkbook = False 6. For Each objWorksheet In WorkbookToTest.Worksheets 7. If Application.WorksheetFunction.CountBlank _ (objWorksheet.Range("A1:IV65536")) = 16777216 Then 8. BlankSheetsInWorkbook = True 9. Exit Function 10. End If 11. Next objWorksheet 12. End Function 13. 14. Sub Check_Workbook_for_Blank_Worksheets() 15. If BlankSheetsInWorkbook(ActiveWorkbook) = True Then 16. MsgBox "This workbook contains one or more blank worksheets." & _ vbCr & vbCr & "Please remove all blank worksheets before" & _ " submitting the workbook.", vbOKOnly & vbExclamation, _ "Check Workbook for Blank Worksheets" 17. End If 18. End Sub
Here's how the code works:
This section includes an example function for PowerPoint. The function in Listing 10.5 checks that all the text on a slide is at least the minimum font size specified and displays an error-message box if any font is too small. (If, when you press Alt+F11 to open the VBA Editor, you see nothing in the Code window, choose Insert Module so you'll have a container for your code.)
LISTING 10.5: A function in PowerPoint
1. Option Explicit 2. 3. Function CheckMinFontSize(objPresentation As Presentation) As Boolean 4. 5. Dim objSlide As Slide 6. Dim objShape As Shape 7. 8. CheckMinFontSize = True 9. 10. For Each objSlide In objPresentation.Slides 11. objSlide.Select 12. objSlide.Shapes.SelectAll 13. For Each objShape In Windows(1).Selection.ShapeRange 14. If objShape.Type = msoPlaceholder Then 15. If objShape.TextFrame.TextRange.Font.Size < 14 Then 16. CheckMinFontSize = False 17. Exit Function 18. End If 19. End If 20. Next objShape 21. Next objSlide 22. End Function 23.
24. Sub Font_Check() 25. If CheckMinFontSize(ActivePresentation) = False Then 26. MsgBox "Some of the fonts in this presentation are too small." _ & vbCr & vbCr & "Please change all fonts to 14 points or larger.", _ vbCritical + vbOKOnly, "Font Size Check" 27. End If 28. End Sub
Here's how the code works:
You can create functions for Access the same way you do for any other VBA-enabled Office 2013 application—just type in the word Function and give this function a name.
However, Access often has special ways of programming, and it has several unique aspects to its object model. The first thing you'll notice is a general-purpose object named DoCmd. This object has no properties, but it has lots of methods that accomplish such common tasks as launching other applications, locating records, and opening reports and forms.
Before we create a macro to illustrate how to use the DoCmd object, it's necessary to have a little database set up that you can experiment with. Access comes with several templates, so we'll use one of them. Follow these steps:
Now you can use the DoCmd to locate a particular record by its ID number. Press Alt+F11 to open the Visual Basic Editor in Access; then right-click the database name (it's the one in boldface) in the Project Explorer. Choose Insert Module from the context menu. In your new module, type the following code, which will move the insertion pointer to a new record:
1. Function MoveToNew() 2. 3. DoCmd.OpenForm "Contact List" 4. DoCmd.GoToRecord , , acNewRec 5. 6. End Function
To test this macro, click somewhere in one of the existing records so the blinking insertion cursor is located above the New record line. Then switch to the VBA Editor and click inside the MoveToNew function to place the Editor's cursor there. Press F5. Then go back to Access, and you should see that the blinking cursor has moved to the New record.
FIGURE 10.5 Type in some data—any data will do—so you can experiment with Access's DoCmd object.
Here's how the code works:
Understand the components of a function statement. Arguments can be passed from the calling code to a function in one of two ways: by reference or by value.
Master It Describe the difference between passing data by reference and passing data by value.
Create a generic function. You can write, and save (File Export File), sets of generic functions that work in any VBA-enabled application.
Master It Create a function that displays the current year in a message box. This function will require no arguments, nor will it return any value.
Create a function for Word. Word contains a whole set of objects and members unique to word-processing tasks. Functions that are specific to Word employ one or more of these unique features of the Word object model.
Master It Write a function that displays the number of hyperlinks in the currently active document. Use Word's Hyperlinks collection to get this information.
Create a function for Excel. Excel uses an ActiveWorkbook object to represent the currently selected workbook. You can employ a full set of built-in methods to manipulate the features of any workbook.
Master It Using the Sheets collection of Excel's ActiveWorkbook object, write a function that displays the number of sheets in the current workbook.
Create a function for PowerPoint. PowerPoint's object model includes an ActivePresentation object, representing the currently selected presentation. Functions can make good use of this object and its members.
Master It Write a function that returns how many slides are on a presentation. Pass the ActivePresentation object as an argument to this function; then display the number of slides the presentation contains. Call this function from a subroutine.
Create a function for Access. Access often works a little differently from other VBA-enabled Office applications. For example, some common tasks are carried out by using methods of the special DoCmd object rather than methods of a Form or Table object.
Master It Write a function that closes Access by using the DoCmd object's Quit method. Ensure that all data is saved by employing the acQuitSaveAll constant as an argument for the Quit method.
18.116.36.174