Catégorie : Excel VBA Course

Suppose we need to create a macro that activates a worksheet: for example, if Sheet1 is currently active, we want to record a macro that activates Sheet2.

1. Launch Microsoft Office Excel 2010 and make sure the cell pointer is on Sheet1.
2. To start the macro recorder, go to the Developer tab on the ribbon and, in the Code group, click Record Macro.
3. In the Record Macro dialog box, set the necessary parameters for the procedure being recorded: assign, for example, the macro the name ActivateSheet, enter an appropriate description in the Description field explaining the purpose of this macro, and leave the Store macro in field unchanged. Click OK.

NOTE:
The Macro Name and Description fields are used to set the macro’s name and description. Remember that the macro name should not contain spaces. The description is important for macros used repeatedly because it may be difficult to recall the purpose of a macro after some time. By default, macros are named Macro1, Macro2, etc. To make a macro easier to recognize, it is better to assign it a unique, meaningful name rather than using the default.

The Shortcut key field allows you to assign a key combination to the macro, i.e., specify a character that, in combination with <Ctrl>, will execute the macro. Assigning a shortcut key is optional and is recommended mainly for frequently used macros for quick access. Without a shortcut key, a macro in Excel 2010 can always be run as follows: go to the Developer tab on the ribbon and, in the Code group, click Macros.

The Store macro in dropdown allows you to choose the workbook where the macro will be stored.

• Selecting Personal Macro Workbook saves the macro in a special hidden workbook where macros are stored. This workbook is always open but hidden, and the macros recorded in it are available for other workbooks. To display the personal macro workbook, go to the View tab and, in the Window group, click Unhide.
• Selecting This Workbook (default) saves the macro in a new module sheet of the active workbook.
• Selecting New Workbook saves the macro in a new workbook.

• In macro recording mode , go to Sheet2 (the pointer will be placed in cell A1).

• Click Stop Recording in the Code group on the Developer tab to stop recording the macro.
• Save your workbook in a macro-enabled format:
  • Go to the File tab and choose Save As.
  • In the Save As dialog box, select the location for your workbook at the top of the window, enter a name for the workbook in the File name field, and select Excel Macro-Enabled Workbook (*.xlsm) in the Save as type dropdown. Click Save.

• Go to the Developer tab and, in the Code group, click Macros. In the Macro dialog box, select the newly created macro from the list and click Edit. The VBA editor window will open, showing the activated standard module with the code (Listing 1.5) of the macro you just recorded (see also the file 2-Activate_Sheet.xlsm on the CD).

Sub Activate_Sheet()
'
' Activate_Sheet Macro
' Activates a Microsoft Excel worksheet
'
     Sheets("Sheet2").Select
End Sub

1. Now, without closing this workbook, create another workbook: go to the File tab, select New, and in the Available Templates group, choose Blank Workbook.
2. Go to the Developer tab on the ribbon and, in the Code group, click Macros.
3. In the Macro dialog box , select the macro you created and click Run. Make sure that in your new workbook, this macro successfully activates Sheet2.

NOTE:
On the Developer tab, in the Code group, there is a Macro Security button, which opens the Trust Center window in the Macro Settings category . You can always select the desired setting to prevent the execution of potentially harmful code contained in macros from unknown sources.

By selecting Trusted Locations in the Trust Center window and clicking Add new location, you can specify a folder from which your VBA-enabled files will open without blocking the actions in them.

Now, let’s assign our macro to a button on the Quick Access Toolbar:

• Right-click on the Quick Access Toolbar and select More Commands.
• In the opened Excel Options window, under Quick Access Toolbar, choose Macros in the Choose commands from dropdown list.
• In the left column, select your macro Activate_Sheet  and click Add >> to move it to the right column. At the bottom of the right column, the Modify button becomes active; click it to assign a button to the macro.

• In the Modify Button dialog , select a symbol for the button and, if needed, change the Display name for the macro, which will appear as a tooltip on the Quick Access Toolbar. Click OK.

• In the Excel Options window, under Quick Access Toolbar, the macro button is now displayed in the right column . Click OK.

• Verify that the button for your macro appears on the Quick Access Toolbar, and that clicking it executes the actions you recorded.

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.