Étiquette : first_program

Now we will introduce the basics of automating tasks, which is not possible without the use of macros. A macro is a program consisting of a list of commands that an application must execute. A macro serves to combine several different actions into a single procedure. This list of commands mainly consists of macro statements closely related to application commands in Microsoft Office. Most macro statements correspond to menu commands or options set in dialog boxes.

There are three main types of macros:

• Command macros — the most common type of macros, usually consisting of statements equivalent to specific menu commands or dialog box options. The primary purpose of these macros is to perform actions similar to menu commands, i.e., changing the environment and core objects of the application. For example, modifying a worksheet or workspace in Microsoft Excel, saving, or printing, etc. Thus, executing a macro results in changes either to the document being processed or to the overall application environment.
• User-defined functions — work similarly to built-in Microsoft Excel functions. Unlike command macros, these functions use the values of arguments passed to them, perform calculations, and return a result to the calling point, but do not change the application environment.
• Macro functions — a combination of command macros and user-defined functions. Like user-defined functions, they can use arguments and return results, but, like command macros, they can also modify the application environment. Macro functions are often called from other macros and are widely used in modular programming. If a series of identical actions needs to be performed in various macros, these actions are usually separated into a standalone macro function (subroutine).

Typically, macros are used to quickly generate a draft version of code. Keep in mind the sequence of actions involved in macro development:

1. Logical procedure design. First, you need to clearly define the result that the macro should produce and the logical sequence of actions required to achieve this result.
2. Document preparation. Perform preliminary actions that do not need to be included in the procedure (e.g., creating a new worksheet or moving to a specific part of a worksheet, etc.).
3. Recording the macro using the macro recorder. The macro recorder is a translator that creates a program (macro) in VBA language, translating the user’s actions from the moment the macro recorder starts until the recording ends. To record a macro using the macro recorder:
   • Go to the Developer tab on the ribbon and, in the Code group, click Record Macro.
   • In the Record Macro dialog box, set the parameters of the procedure being recorded (name, description, shortcut key, and which documents the macro will be available for) and enter macro recording mode. The Record Macro button on the Developer tab will change to Stop Recording; the Pause button will also become active (if you want to pause the recording temporarily to perform other actions with the document).
   • Perform all necessary actions with the document and its contents as planned in step one.
   • Stop the recording (Stop Recording button in the Code group on the Developer tab).
4. Viewing and editing the created procedure:
   • Click Macros in the Code group on the Developer tab.
   • In the Macro dialog box, select the macro name and click Edit. The main Microsoft Visual Basic editor window and the Module window containing the macro code will open.
   • Make the necessary edits to the macro code and close the editor window.
5. Running the macro:
   • Click Macros in the Code group on the Developer tab.
   • In the Macro dialog box, select the macro name and click Run.

NOTE:
You can assign a button to a recorded procedure and place it on the Quick Access Toolbar to simplify macro execution.

The previous example of calculating the cost of a batch of books is already quite convincing. VBA can indeed make a user’s life easier.

One question arises from the two examples given. In the user-defined functions created, the parameter values were only cell references. Is it possible to create a user-defined function where the parameter values can be references to a range of cells?

Function MySum(ByVal rng As Range) As Double
    Dim c As Range
    Dim s As Double
    s = 0
    For Each c In rng.Cells
        s = s + c.Value
    Next
    MySum = s
End Function

The code demonstrates the use of range references and solves the task of summing values in a specified range of cells (see also the file 1-UserFunctions.xlsm on the CD).

Let us consider a more complex example of creating a user-defined function. Suppose you are a manager responsible for wholesale book sales in a publishing house. To attract customers, your publishing house has introduced a progressive pricing scale.

• If 100 to 200 copies of a book are sold, the discount from its retail price is 7%.
• If 201 to 300 copies are sold, the discount is 10%.
• If more than 300 copies are sold, the discount is 15%.

In addition, for regular customers, an additional 5% discount is provided.

Let us create a user-defined function named Cost to calculate the cost of a batch of books. The parameters of this function will be called PricePerBook, Quantity, and Discount. For the Discount parameter, only two values are allowed: 1 — for regular customers, and 0 — for all others.

We define the user-defined function Cost with the following code:

Function Cost(PricePerBook, Quantity, Discount)
    If Quantity < 100 Then
        CostWithoutDiscount = PricePerBook * Quantity
    ElseIf Quantity <= 200 Then
        CostWithoutDiscount = PricePerBook * Quantity * 0.93
    ElseIf Quantity <= 300 Then
        CostWithoutDiscount = PricePerBook * Quantity * 0.9
    Else
        CostWithoutDiscount = PricePerBook * Quantity * 0.85
    End If
    If Discount = 0 Then
        Cost = CostWithoutDiscount
    Else
        Cost = CostWithoutDiscount * 0.95
    End If
End Function

So, the user-defined function Cost is created. Since VBA allows English-language names, the program text is clear and easy to understand. This also makes it simple to use the Function Wizard dialog box for this function.

The names of all parameters of the Cost function are displayed in the Function Wizard window, allowing any user to use it, even without knowledge of VBA.

For convenience, it is recommended to predefine the input values for the function parameters (PricePerBook, Quantity, and Discount) on the worksheet. However, this is not mandatory: the required values can also be entered directly in the Function Wizard window.

Now you can proceed directly to writing a user-defined function.
Let’s start by writing code to calculate a simple function, for example:

F(x)=x3+x2F(x) = x^3 + x^2F(x)=x3+x2

To implement this task, you need to perform the following steps:

1. In the VBA editor window, add a standard module (if you haven’t created one yet) by executing the command Insert | Module.
2. In the window of the created module , type the code from:

Function F(x As Double) As Double
    F = x ^ 3 + x ^ 2
End Function

It should be noted that in VBA there is a universal data type Variant, which is assumed by default if the type of a variable or function has not been explicitly declared. Therefore, the same function could also be coded as follows.

Listing 1.2. User-defined function using the Variant type

Function F(x)
    F = x ^ 3 + x ^ 2
End Function

NOTE:
Once again, note that the user-defined function code is entered in a standard module, which is added to the project using Insert | Module. If there are many modules in the project, do not confuse them. The active module is highlighted in gray in the Project – VBAProject window.

So, the user-defined function has been created. By default, it appears in the User Defined category in the Function Wizard list. Let’s find, for example, the value of this function when x = 4.7. To do this:

1. Go to the Microsoft Office Excel 2010 workbook window.
2. Enter the number 4.7 in cell A1 of the worksheet (for example, Sheet1).
3. Go to cell B1, where we will find the function value.
4. Go to the Formulas tab on the ribbon and in the Function Library group, click Insert Function.
5. In the first Function Wizard window, select the category User Defined from the list and choose the function F. Click OK.
6. In the second Function Wizard window, enter the reference to cell A1 in the X field (or click the corresponding worksheet cell with the mouse) and click OK (Fig. 1.5). The function value is calculated.

To write a user-defined function, you need to go into the VBA editor. First, make sure that the Developer tab is displayed on the ribbon in Microsoft Office Excel 2010.

If it is not displayed, follow these steps:

1. Go to the File tab on the ribbon and click Options.
2. In the Excel Options window that opens, select Customize Ribbon from the list on the left, and on the right, in the Customize the Ribbon group, choose Main Tabs from the dropdown list.
3. Check the box for Developer  and click OK.

Now, on the Developer tab, go to the Code group and click Visual Basic: the Integrated Development Environment (IDE) of the Visual Basic editor will open .

NOTE
To quickly launch the VBA editor, simply press the keyboard shortcut +.

The development environment has a standard interface typical of Windows applications: a title bar, a menu bar, a toolbar (in this case Standard), and two windows: Project – VBAProject and Properties.

In the Project – VBAProject window, all the modules and forms that are part of the project are listed. A module is displayed as a Module window, in which the main part is the working area — a sheet (not to be confused with an Excel worksheet), where the code is written. To open a module in the Project – VBAProject window, simply double-click the corresponding icon. The icon for the active module is highlighted in gray.

In VBA, each worksheet has its own module, and the workbook also has its own. Moreover, if user forms are created in the project, each of them also has its own module. You can add class modules to the project to describe custom classes. However, to create a user-defined function, you will need a standard module, which can be added to the project with the command:
Insert | Module.