In Chapter 9, you learned how to use VBA's built-in functions. In this chapter, you'll learn how to create your own functions. As you'll see, you create a function just like you create a subprocedure: by working in the Code window in the module in which you want to store the function. (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 two recorders create only subprocedures.)
It's important to note 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) to the procedure that calls them. Subs, by contrast, normally don't require arguments and never return any data. 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 among procedures.
This chapter will cover a variety of ways to employ functions with the various Office 2010 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:
Understand the components of a function statement
Create a generic function
Create a function for Word
Create a function for Excel
Create a function for PowerPoint
Create a function for Access
To create a function, you use a Function
statement. The syntax for the Function
statement is as follows:
[Public | Private] [Static] Functionfunction_name
[(argument_list
)] [Astype
] [statements
] [function_name
=expression
] [Exit Function] [statements
] [function_name
=expression
] End Function
This syntax, most of which is optional, breaks down like this:
Public
is an optional keyword that you can use to make the function publicly accessible—accessible to all other procedures in all loaded modules. (If you need to limit the function's scope to the project that contains it, you can override this public availability by putting an Option Private Module
statement in the module that contains the function.)
Private
is an optional keyword that you can use to make the function accessible to the other procedures in the module that contains it. The function is hidden from procedures in any other module.
Static
is an optional keyword that you can use to make local variables in the function retain their value between calls to the function.
function_name
is a required argument that specifies a name for the function. Functions follow the same naming rules as other VBA items, such as the rules for variable names: Alphanumerics and underscores are fine, but no spaces, symbols, or punctuation.
argument_list
is an optional argument supplying the list of variables that represent arguments passed to the function when it is invoked. argument_list
takes the syntax shown here:
[Optional] [ByRef | ByVal] [ParamArray]variable_name
[( )] [Astype
] [=default_value
]
Here's a description of the elements of the argument_list
:
Optional
is an optional keyword that you can use to denote that an argument is optional—in other words, that it is not required. Once you've used Optional
to declare an optional argument, any subsequent arguments in the argument_list
also have to be optional. That means you have to put any required arguments before the optional arguments, the same way VBA does with its built-in functions' argument lists. It's a good idea to give optional arguments a default value.
ByRef
is an optional keyword that you can use to specify that an argument be passed by reference; ByVal
is an optional keyword that you can use to specify that an argument be passed by value. You can pass an argument either by reference or by value.
ParamArray
is an optional keyword you can use as the last argument in argument_list
to denote an optional array of Variants. You can't use ParamArray
with ByVal, ByRef
, or Optional
.
variable_name
is the name of the variable that you want to use for this argument. When the function is called and a value is supplied for this argument, this variable can be used in your code.
type
is an optional keyword giving the data type of the argument (Byte, Boolean, Currency, Date, Decimal, Double, Integer, Long, Object, Single, variable-length String, or Variant). For nonoptional arguments, you can also specify an object type (forexample, a Worksheet
object) or a custom object (one you've created).
default_value
is an optional constant or constant expression that you use to specify a default value for optional parameters.
type
is an optional argument specifying the data type of value that the function returns: Byte, Boolean, Currency, Date, Decimal, Double, Integer, Long, Object, Single, variable-length String, Variant, or a custom type.
statements
represents the statement or statements in the function (the code that does the job the function is supposed to accomplish). In theory, statements
is optional, but in practice, most functions will need one or more statements.
expression
represents the value the function returns. expression
is also optional.
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)
If you like to make the Visual Basic Editor work for you as much as possible, you can also start creating a new function by using the Add Procedure dialog box:
Choose Insert
Type the name for the procedure in the Name text box.
Select the Function option button in the Type group box.
Select the Public option button or the Private option button (as appropriate) in the Scope group box.
If you want all local variables in the function to be static (which you usually won't), select the All Local Variables As Statics check box.
Click OK to enter the stub for the function, and then enter any arguments for the function in the parentheses manually.
The arguments that will be passed to a function are listed in parentheses, separated by a comma. 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 (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 define 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 data type:
Function GetTemps(MaxTempAs Double
, MinTempAs 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.
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 by reference (which is almost always the case), you do need 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 MyArg
as a string to be passed by reference and ValArg
as a Variant to be passed by value:
Function PassBoth(ByRef MyArgAs String
, ByVal ValArgAs Variant
)
You can specify an optional argument by using the Optional
keyword. Place the Optional
keyword before the ByRef
or ByVal
keyword if you need to use ByRef
or ByVal
:
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. To assign the default value, type an equal sign after the variable's definition and then 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"
)
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 on those functions you intend to be public, your code will be somewhat easier to read:
Private
Function MyFunction(MaxTemp, MinTemp)Public
Function AnotherFunction(Industry, Average)
This section 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 are specific to certain applications.
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. Likewise, at the end of this statement, the function explicitly types its return value as a Double. Explicitly typing the arguments and the return value can help you avoid unpleasant surprises in your code because VBA catches and reports any attempt to pass the wrong data type to the function and alerts you if the function is asked to return a data type other than its declared type.
Armed with the arguments (and their type, if they're explicitly typed), you call (execute) NetProfit
just as 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 code 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 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.
Example 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 to 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
.
Lines 5 to 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 sets NetProfit
to be 90 percent (0.9
) of the value of Gross
minus Expenses
. Line 7 ends the function, at which point the value of NetProfit
is passed back to line 2, which displays the message box containing the result.
Listing 10.2 contains a function that returns a String argument.
Example 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 to 16) and the NeedsSmog
function (lines 18 to 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:
TestForSmog
starts by declaring the Integer variable intCYear
and the String variable strThisCar
in line 2.
Line 3 contains the BadValueLoop
label, to which execution returns from line 11 if the user has entered an unsuitable value for the year of the car. We'll want to display the input box again, to see if they can get it right this time. Note that if you want execution to jump to a particular zone in your code, you just type in a name for the location, such as BadValueLoop
here, and end with a colon. Then elsewhere in your code you can transfer execution to this line by using the GoTo
command like this:
GoTo BadValueLoop
Line 4 contains an On Error
statement to transfer execution to the Bye
label in line 15 if an error occurs. An error occurs if the user cancels the upcoming input box or clicks its OK button with no value entered in its text box.
Line 5 displays an input box prompting the user to enter the year of the car. This line assigns to the intCYear
variable the value the user enters in the input box.
Line 6 then sets the value of the String variable strThisCar
to the result of the NeedsSmog
function running on the intCYear
integer variable.
Execution now shifts to the NeedsSmog
function (line 18), which evaluates intCYear
and returns the value for strThisCar
. Line 18 declares the function, assigning its value to NeedsSmog
. The function takes one argument, CarYear
, which is declared as the Integer data type.
Line 19 checks to see whether CarYear
is greater than the value of the current year (Year(Now)
). If so, line 20 sets the value of NeedsSmog
to BadValue
, which is used to indicate that the user has entered a date in the future. If not, the ElseIf
statement in line 21 runs, checking if the value of CarYear
is less than or equal to Year(Now) - 3
, the current year minus three. If so, line 22 sets the value of NeedsSmog
to Yes
; if not, the Else
statement in line 23 runs, and line 24 sets the value of NeedsSmog
to No
. Line 25 ends the If
statement, and line 26 ends the function.
Execution then returns to the calling line (line 6) in the TestForSmog
procedure, to which the NeedsSmog
function returns the value it has assigned to the strThisCar
variable.
The rest of the TestForSmog
procedure then works with the strThisCar
variable. Line 7 compares strThisCar
to Yes
. If it matches, line 8 displays a message box stating that the car needs a smog check. If strThisCar
doesn't match Yes
, line 9 compares ThisCar
to BadValue
. If it matches, line 10 displays an alert message box, and line 11 returns execution to the BadValueLoop
label in line 3. If strThisCar
doesn't match BadValue
, the Else
statement in line 12 runs, and line 13 displays a message box stating that the car doesn't need a smog check.
Line 14 ends the If
statement, line 15 contains the Bye
label, and line 16 ends the procedure.
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 example program in Listing 10.3 removes some special types of formatting (hyperlinks, bookmarks, and fields) but retains any text used in those special zones.
The function shown in Listing 10.3 is for Word and—unusually for a function—returns a null value. The function's main purpose is to perform several operations on the specified document. No data needs to be returned to the caller.
Example 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:
Line 1 contains the Option Explicit
statement for the module to force explicit declarations of all variables. Line 2 is a spacer.
Line 3 starts the function named Strip_Hyperlinks_Bookmarks_Fields
, which removes all hyperlinks, bookmarks, and fields from the active document. The function continues until the End Function
statement in line 18.
Line 4 declares a variable named myLink
as being of the Hyperlink
type. Line 5 declares a variable named myBookmark
as being of the Bookmark
type. Line 6 declares a variable named myField
as being of the Field
type.
Line 7 begins a With
statement that works with the ActiveDocument
object and continues until the End With
statement in line 17. This With
statement contains three For Each...Next
loops.
The first For Each...Next
loop, in lines 8 through 10, goes through each myLink
object in the current document's Hyperlinks
collection. Line 9 uses the Delete
method to delete each of the links in turn. Deleting a hyperlink removes the link from the document but leaves the text that was displayed for the hyperlink.
The second For Each...Next
loop, in lines 11 through 13, works with each myBookmark
object in the Bookmarks
collection. Line 12 uses the Delete
method to delete each of the bookmarks in turn. Deleting a bookmark removes the marker from the document but leaves any text or other object that the bookmark contained.
The third For Each...Next
loop, in lines 14 through 16, works with each myField
object in the Fields
collection. Line 15 uses the Unlink
method to unlink each of the fields in turn. Unlinking a field leaves the field's contents in the document as text or as an object but removes the field link.
Line 17 contains the End With
statement that ends the With
statement, and line 18 contains the End Function
statement that ends the function. Line 19 is a spacer.
Lines 20 through 23 contain a short subprocedure that simply calls the Strip_Hyperlinks_Bookmarks_Fields
function. Line 22 contains a comment stating that the subprocedure would call other cleanup functions. But the code to call other functions hasn't yet been written. It's a reminder.
This section shows you a function for Excel. The function in Listing 10.4 checks whether a workbook contains any unused sheets.
Example 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:
Line 1 contains the Option Explicit
statement for the module to force explicit declarations of all variables. Line 2 is a spacer.
Line 3 starts the function named BlankSheetsInWorkbook
, which it declares as a Boolean function. The function works on an object named WorkbookToTest
, which has the type Workbook
—in other words, it's a workbook.
Line 4 declares a variable named objWorksheet
that is of the Worksheet
type.
Line 5 sets the value of the BlankSheetsInWorkbook
function to False
.
Line 6 starts a For Each...Next
loop that runs for each objWorksheet
object (each worksheet) in the Worksheets
collection in the WorkbookToTest
object—that is, with each worksheet in the workbook that is passed to the function.
Line 7 uses the COUNTBLANK
worksheet function to count the number of blank cells in the range A1:IV65536 in the worksheet being tested by the loop. If the number of blank cells is 16777216, the worksheet is blank because this is the number of cells in a worksheet. Line 8 then sets the value of the BlankSheetsInWorkbook
function to True
, and line 9 uses an Exit Function
statement to exit the function. This is because there is no need to test any more worksheets once the function has found that one worksheet is blank.
Line 10 contains the End If
statement that ends the If
statement. Line 11 contains the Next objWorksheet
statement that ends the For Each...Next
loop. And line 12 contains the End Function
statement that ends the function. Line 13 is a spacer.
Line 14 begins a short subprocedure named Check_Workbook_for_Blank_Worksheets
. Line 15 runs the BlankSheetsInWorkbook
function on the ActiveWorkbook
object, which represents the active workbook in the Excel session. If the BlankSheetsInWorkbook
function returns True
, line 16 displays a message box that points out to the user that the workbook contains one or more blank worksheets and tells the user to remove them.
This section shows 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
Example 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:
Line 1 contains the Option Explicit
statement for the module to force explicit declarations of all variables. Line 2 is a spacer.
Line 3 declares the function named CheckMinFontSize
as Boolean and specifies that it works on a variable named objPresentation
, which is of the Presentation
type. Line 4 is a spacer.
Line 5 declares a variable named objSlide
that is of the Slide
type. Line 6 declares a variable named objShape
that is of the Shape
type. Line 7 is a spacer.
Line 8 sets the value of the CheckMinFontSize
function to True
. This indicates that the font sizes are the minimum size or larger. Line 9 is a spacer.
Line 10 starts a For Each...Next
loop that continues until line 21 and works with each objSlide
object in the Slides
collection in the objPresentation
object. This loop makes the function examine each of the Slide
objects in the presentation that is passed to the function.
Line 11 selects the current objSlide
object, and line 12 uses the SelectAll
method of the Slides
collection.
Line 13 starts a nested For Each...Next
loop that runs once for each of the objShape
objects in the ShapeRange
object in the Selection
object in the first window (Windows(1)
). The ShapeRange
object contains all of the Shape
objects within the selection. Here, the Shape
objects are represented by the objShape
variable.
Line 14 uses an If
statement to see if the Type
property of the current Shape
object is msoPlaceholder
, the type that indicates a placeholder used for text. If the shape is a placeholder, line 15 checks if the font size used in the TextRange
object within the TextFrame
object within the Shape
object is smaller than 14 points. If so, line 16 assigns the value False
to the CheckMinFontSize
function, and line 17 uses an Exit Function
statement to stop execution of the function. This is because, once a font smaller than the minimum permitted size has been found, there is no need to check further.
Line 18 contains the End If
statement that ends the nested If
structure, and line 19 contains the End If
statement that ends the outer If
structure.
Line 20 contains the Next objShape
statement that ends the nested For Each...Next
loop, and line 21 contains the Next objSlide
statement that ends the outer For Each...Next
loop.
Line 22 contains the End Function
statement that ends the function. Line 23 is a spacer.
Lines 24 through 28 contain a subroutine named Font_Check
that runs the CheckMinFontSize
function on the ActivePresentation
object. If the function returns False
, the subprocedure displays a message box alerting the user to the problem.
You can create functions for Access the same way you do for any other VBA-enabled Office 2010 application—just type in the word Function
and give it a name.
However, Access often has special ways of programming, and it has several unique qualities in its object model. The first thing you'll notice is a general-purpose object named DoCmd
. It 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:
Run Access.
Click the File tab of the Ribbon.
Click the Sample Templates option.
Double-click Contacts Web Database to open that database template.
If you see a security warning message (a yellow strip below the Ribbon), click the Enable Content button.
Click the Datasheet tab in the database.
Type in some random data by clicking the (New) link in the ID column on the left side, as shown in Figure 10.5. A Contacts Details dialog box opens (not shown in the Figure).
Click the Save And New button in the Contact Details dialog box each time you add a new contact. Add about three contacts.
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 Contacts in the Project Explorer. Choose Insert
1. Function MoveToNew() 2. 3. DoCmd.OpenForm "Contacts" 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.
Here's how the code works:
Line 3 ensures that the correct form is open. Because you've just started working with this Contacts database and filled in some information in the Contacts form, the correct form is open and has the focus. However, it's possible that later additional forms will be added. It's always a good idea to specify which form, table, or other object you want to work with. You can't assume that a macro will always be executed in a specific context (such as with the correct form having the focus).
Line 4 employs the GoToRecord
method of the DoCmd
object. The acNewRec
constant specifies a new, rather than an existing, record.
Arguments can be passed from the calling code to a function in one of two ways: by reference or by value.
Describe the difference between passing data by reference and passing data by value?
You can write, and save (File
Create a function that displays the current year in a message box. This function will require no arguments, nor will it return any value.
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.
Write a function that displays the number of hyperlinks in the currently active document. Use Word's Hyperlinks
collection to get this information.
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.
Using the Sheets
collection of Excel's ActiveWorkbook
object, write a function that displays the number of sheets in the current workbook.
PowerPoint's object model includes an ActivePresentation
object, representing the currently selected presentation. Functions can make good use of this object and its members.
Write a function that returns how many slides are on a presentation. Pass the ActivePresentation
object as an argument to this function; then you display the number of slides the presentation contains. Call this function from a subroutine.
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.
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.
3.145.12.3