Chapter 10. Creating Your Own Functions

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

Components of a Function

To create a function, you use a Function statement. 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:

  • 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[( )] [As type]

[= 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.

      You Can Pass a Value's Address, or a Copy of the Value, to a Function

      When a procedure (either a function or a subroutine) passes an argument to another procedure by reference, the recipient procedure gets access to the actual memory location where the original variable is stored and can thus change the original variable. By contrast, when an argument is passed by value, the recipient procedure or function gets only a copy of the information in the variable and can't change the information 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, but you can also include the ByRef keyword to state explicitly that you want to pass an argument by reference.

    • 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.

Creating a Function

The following sections walk you through the process of creating a function.

Starting a Function Manually

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)
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.

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.

Starting a Function by Using the Add Procedure Dialog Box

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:

  1. Choose Insert

    Starting a Function by Using the Add Procedure Dialog Box

  2. Type the name for the procedure in the Name text box.

  3. Select the Function option button in the Type group box.

  4. Select the Public option button or the Private option button (as appropriate) in the Scope group box.

  5. 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.

  6. Click OK to enter the stub for the function, and then enter any arguments for the function in the parentheses manually.

You can also use the Add Procedure dialog box to specify elements of a new function.

Figure 10.2. You can also use the Add Procedure dialog box to specify elements of a new function.

Passing Arguments to the Function

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(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.

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.

Declaring the Data Types of Arguments

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 MyArg As String, ByVal ValArg As Variant)

Specifying an Optional Argument

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")

Controlling the Scope of a Function

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)

Examples of Functions for Any VBA-Enabled Application

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.

The Visual Basic Editor displays a ToolTip of Quick Info for functions you create as well as for its built-in functions.

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.

The TestForSmog procedure prompts for the car's year and then displays a message box stating whether the car needs a smog test.

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)

Creating a Function for Word

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.

Creating Custom Function Libraries

Some programmers like to keep functions they write (that aren't application specific) in separate modules. 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

Creating Custom Function Libraries

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.

Creating a Function for Excel

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.

Creating a Function for PowerPoint

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

Creating a Function for PowerPoint

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.

Creating a Function for Access

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:

  1. Run Access.

  2. Click the File tab of the Ribbon.

  3. Click the Sample Templates option.

  4. Double-click Contacts Web Database to open that database template.

  5. If you see a security warning message (a yellow strip below the Ribbon), click the Enable Content button.

  6. Click the Datasheet tab in the database.

  7. 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).

  8. Click the Save And New button in the Contact Details dialog box each time you add a new contact. Add about three contacts.

Type in some data—any data will do—so you can experiment with Access's DoCmd object.

Figure 10.5. Type in some data—any data will do—so you can experiment with Access's DoCmd object.

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

Type in some data—any data will do—so you can experiment with Access's DoCmd object.
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.

The Bottom Line

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

The Bottom Line
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 you 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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.17.166.34