Michael Alexander - Excel 2019 Power Programming with VBA

Each of the 100 elements in this array consists of four components (as specified by the user-defined data type, CustomerInfo). You can refer to a particular component of the record as follows:

Customers(1).Company = "Acme Tools" Customers(1).Contact = "Tim Robertson" Customers(1).RegionCode = 3 Customers(1).Sales = 150674.98

You can also work with an element in the array as a whole. For example, to copy the information from Customers(1)to Customers(2), use this instruction:

Customers(2) = Customers(1)

The preceding example is equivalent to the following instruction block:

Customers(2).Company = Customers(1).Company Customers(2).Contact = Customers(1).Contact Customers(2).RegionCode = Customers(1).RegionCode Customers(2).Sales = Customers(1).Sales

Like most programming languages, VBA has a variety of built-in functions that simplify calculations and operations. Many VBA functions are similar (or identical) to Excel worksheet functions. For example, the VBA function UCase, which converts a string argument to uppercase, is equivalent to the Excel worksheet function UPPER.

Appendix A contains a complete list of VBA functions, with a brief description of each. All are thoroughly described in the VBA Help system.

To get a list of VBA functions while you're writing your code, type VBAfollowed by a period ( .).V BE displays a list of all its members, including functions (see Figure 3.3). The functions are preceded by a green icon. If this technique doesn't work for you, make sure that the Auto List Members option is selected. Choose Tools ➪ Options and then click the Editor tab.

FIGURE 3.3 Displaying a list of VBA functions in VBE

You use functions in VBA expressions in much the same way that you use functions in worksheet formulas. Here's a simple procedure that calculates the square root of a variable (using the VBA Sqrfunction), stores the result in another variable, and then displays the result:

Sub ShowRoot() Dim MyValue As Double Dim SquareRoot As Double MyValue = 25 SquareRoot = Sqr(MyValue) MsgBox SquareRoot End Sub

The VBA Sqrfunction is equivalent to the Excel SQRTworksheet function.

You can use many (but not all) of Excel's worksheet functions in your VBA code. The WorksheetFunctionobject, which is contained in the Applicationobject, holds all the worksheet functions that you can call from your VBA procedures.

To use a worksheet function in a VBA statement, just precede the function name with this:

Application.WorksheetFunction

The following example demonstrates how to use an Excel worksheet function in a VBA procedure. Excel's infrequently used ROMANfunction converts a decimal number into a Roman numeral.

Sub ShowRoman() Dim DecValue As Long Dim RomanValue As String DecValue = 1939 RomanValue = Application.WorksheetFunction.Roman(DecValue) MsgBox RomanValue End Sub

When you execute this procedure, the MsgBoxfunction displays the string MCMXXXIX.

Keep in mind that you can't use worksheet functions that have an equivalent VBA function. For example, VBA can't access the Excel SQRTworksheet function because VBA has its own version of that function, Sqr. Therefore, the following statement generates an error:

MsgBox Application.WorksheetFunction.Sqrt(123) 'error

In Chapter 5, you will discover that you can use VBA to create custom worksheet functions that work just like Excel's built-in worksheet functions.

The MsgBoxfunction is one of the most useful VBA functions. Many of the examples in this chapter use this function to display the value of a variable.

This function often is a good substitute for a simple custom dialog box. It's also a useful debugging tool because you can insert MsgBoxfunctions at any time to pause your code and display the result of a calculation or an assignment.

Most functions return a single value, which you assign to a variable. The MsgBoxfunction not only returns a value but also displays a dialog box to which the user can respond. The value returned by the MsgBoxfunction represents the user's response to the dialog box. You can use the MsgBoxfunction even when you have no interest in the user's response but want to take advantage of the message display.

The official syntax of the MsgBoxfunction has five arguments (those in square brackets are optional).

MsgBox(prompt[, buttons][, title][, helpfile, context])

prompt: Required. The message displayed in the pop-up display.

buttons: Optional. A value that specifies which buttons and which icons, if any, appear in the message box. Use built-in constants—for example, vbYesNo.

title: Optional. The text that appears in the message box's title bar. The default is Microsoft Excel.

helpfile: Optional. The name of the Help file associated with the message box.

context: Optional. The context ID of the Help topic, which represents a specific Help topic to display. If you use the context argument, you must also use the helpfile argument.

You can assign the value returned to a variable, or you can use the function by itself without an assignment statement. This example assigns the result to the variable Ans:

Dim Ans As Long Ans = MsgBox("Continue?", vbYesNo + vbQuestion, "Tell me") If Ans = vbNo Then Exit Sub

Note that we used the sum of two built-in constants ( vbYesNo + vbQuestion) for the buttonsargument. Using vbYesNodisplays two buttons in the message box: one labeled Yes and one labeled No. Adding vbQuestionto the argument also displays a question mark icon. When the first statement is executed, Anscontains one of two values, represented by the constant vbYesor vbNo. In this example, if the user clicks the No button, the procedure ends.

See Chapter 12, “Leveraging Custom Dialog Boxes,” for more information about the MsgBoxfunction.

As an Excel programmer, you'll spend a lot of time working with objects and collections. Therefore, you want to know the most efficient ways to write your code to manipulate these objects and collections. VBA offers two important constructs that can simplify working with objects and collections.

With-End With constructs

For Each-Next constructs

The With- End Withconstruct enables you to perform multiple operations on a single object. To start understanding how the With- End Withconstruct works, examine the following procedure, which modifies six properties of a selection's formatting. (The selection is assumed to be a Rangeobject.)