Chapter 10Using Macros, Controls

and Visual Basic for Applications (VBA) with

Excel

MicrosoftExcel 2013

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 2

• Use passwords to assign protected and unprotected status to a worksheet

• Use the macro recorder to create a macro• Execute a macro and view and print code for a

macro• Customize the Quick Access Toolbar by adding a

button• Use a Data Form to add data to a Worksheet

Objectives

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 3

• Understand Visual Basic for Applications (VBA) code and explain event-driven programs

• Add controls such as command buttons, option buttons, and check boxes to a worksheet

• Assign properties to controls• Review a digital signature on a workbook

Objectives

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 4

Project – Waterfront Studios

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 5

• Before a computer can take an action and produce a desired result, it must have a step-by-step description of the task to be accomplished

• The step-by-step description is a series of precise instructions called a procedure

• Program and code are other names for a procedure

• The process of writing a procedure is called computer programming

Writing a Procedure

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 6

• Because Excel does not have a command or button for every possible worksheet task, Microsoft has included a powerful programming language called Visual Basic for Applications

• Visual Basic for Applications (VBA) is a programming language that allows you to customize and extend the capabilities of Excel

Visual Basic for Applications (VBA)

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 7

• A macro is a procedure composed of VBA code• It is called a macro rather than a procedure

because it is created using the macro recorder• You can create a macro to group together

commonly used combinations of tasks, which then can be reused later

• Use macros to ensure consistency in calculations, formatting, and manipulation of nonnumeric data

• Macros save time and automate repetitive tasks

Macros

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 8

• Excel includes a macro recorder that can record a series of actions and save them as a macro

• The macro recorder can be turned on, during which time it records your activities, and then turned off to stop the recording

• After recording a macro, you can play it back, or execute it, as often as you want to repeat the steps you recorded with the macro recorder

Recording a Macro

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 9

• Three steps must be taken in preparation for working with macros in Excel– The DEVELOPER tab, which be default does not appear

on the ribbon, must be made available by changing an Excel option

– Second, a security setting in Excel must be modified to enable macros whenever you use Excel

– Finally, Excel requires a workbook that includes macros to be saved as an Excel Macro-Enabled Workbook file type

Working with Macros

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 10

Displaying the DEVELOPER Tab

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 11

• A macro is created by performing a set of steps and recording the steps as they are performed

• The steps and their order should be determined and rehearsed before creating the macro

• When you create a macro, you assign a name to it• A macro name can be up to 255 characters long; it can contain

numbers, letters, and underscores, but it cannot contain spaces or other punctuation

• The name is used later to identify the macro when you want to execute it

• Executing a macro causes Excel to step through all of the recorded steps just as they were recorded

Naming and Executing a Macro

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 12

• Once you start recording a macro, any task you perform in Excel will be part of the macro

• If you make a mistake while recording a macro, delete the macro and record it again

Recording a Macro

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 13

• You can set a keyboard shortcut key combination for executing a macro with an option in the Record Macro dialog box

Setting a Keyboard Shortcut in a Macro

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 14

Recording a Macro to Reformat the Artist Data Where First Name Appears First

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 15

• Macros provide you with an opportunity to make certain tasks much more efficient and accurate

• However, macros introduce an element of risk to your work in the form of computer viruses

• Macros are known carriers of viruses because of the ease with which a person can add programming code to macros

Macros and Viruses

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 16

• Excel provides four levels of protection from macro viruses: Disable all macros without notification, Disable all macros with notification, Disable all macros except digitally signed macros, and Enable all macros

• By default, the macro security level is set to Disable all macros with notification, meaning that only macros from trusted sources can be used

Setting the Macro Security Level

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 17

Setting the Macro Security Level

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 18

• Excel remembers your decision about enabling macros• If you have enabled macros in a worksheet, Excel will not

ask you about enabling them the next time you open the worksheet, but will open the worksheet with macros enabled

• If you are uncertain about the reliability of the source of a workbook and its macros, you can work with the macros disabled, which means that the code is not executable

• If you are confident of the source of a workbook and its macros, click the Enable Content button on the SECURITY WARNING bar

Enabling/Disabling Macros

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 19

• When you work with a workbook created by someone else, you should review the content and structure of the workbook before you make changes to it

• You can learn more about a workbook by doing as much of the following as possible to the worksheet: – Display any formulas to gain an understanding of what formulas

and functions are used in the worksheet and which cells are referenced by the formulas and functions

– Use Range Finder or the auditing commands to show which cells are referenced in formulas and functions

– Check which cells are locked and which cells are unlocked– Enter sample data and verify the results

Working with a Workbook Created by Someone Else

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 20

• A password ensures that users cannot unprotect a worksheet simply by clicking the Unprotect button

• Passwords in Excel can contain, in any combination, letters, numbers, spaces, and symbols and can be up to 15 characters long

• Passwords are case sensitive• If you decide to password-protect a worksheet, make sure

you write down the password and keep it in a secure place

• If you lose the password, you cannot open or gain access to the password-protected worksheet

Creating a Password

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 21

Unprotecting a Password-Protected Worksheet

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 22

• With the worksheet unprotected, you can modify the contents of the cells

• Cells must both be locked and the worksheet protected to restrict what users can do to cell contents

Restricting Users

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 23

• Use the PROPER function to convert names entered in all uppercase to mixed case

Converting Names to Proper Case

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 24

• Use the Paste Values command when you want to replace a formula with a text value

Pasting Values

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 25

• Use the LEN function to determine the total number of characters in a name

Using the LEN Function

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 26

• A macro is composed of VBA code, which is created automatically by the macro recorder

• The Visual Basic Editor is used by all Office applications to enter, modify, and view Visual Basic for Application code

Viewing a Macro’s VBA Code

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 27

• Excel provides a feature for entering data when the content is straightforward text or numbers

• A data form is created from a range of cells, and uses the column headings from that range to create the fields on the form

• Data forms are particularly helpful when working with a series of columns that would otherwise require you to scroll horizontally, or when entering a large number of entries into a worksheet

Using a Data Form to Enter Additional Records

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 28

Using a Data Form to Enter Additional Records

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 29

• Two types of controls are used to create the user interface: form controls and ActiveX controls

• Form controls and ActiveX controls look identical in the gallery

• They do have function differences, however, that can help determine which one is the best choice for an object

• Form controls require no knowledge of VBA to use

Form Controls and ActiveX Controls

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 30

• You can assign an Excel macro directly to a form control, allowing the macro to be run with a simple click

• Form controls also allow you to reference cells easily and use Excel functions and expressions to manipulate data

Adding Form Controls to a Worksheet

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 31

Adding Form Controls to a Worksheet

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 32

• ActiveX controls provide great flexibility in terms of their design

• They have extensive properties that can be used to customize their appearance

ActiveX Controls

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 33

Grouping Option Buttons in a User Interface• With form controls, only one of the option

buttons on the form can be selected unless the option buttons are grouped

• Use the group box form control to group one set of the option buttons together

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 34

Grouping Option Buttons in a User Interface

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 35

Adding a Command Button Control to the Worksheet• A command button control can have Visual Basic code

associated with it that accomplishes more complex actions than a macro or a form button can accommodate

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 36

• Each form control and ActiveX control available in the Controls gallery has many properties, or characteristics, that can be set to determine the control’s appearance and behavior

Formatting the Controls

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 37

Formatting the Controls

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 38

• Using the Controls gallery to insert a command button control into a worksheet inserts an object only

• To have the button take action when a user clicks it, you must write VBA code that directs the events in the worksheet after the command button is clicked

• The next step is to write and enter the procedure that will execute when the user clicks the button

Writing VBA Code

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 39

Using the Visual Basic Editor

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 40

• After you determine what you want the procedure to do, write the VBA code on paper

• Test the code before you enter it in the Visual Basic Editor, by stepping through the instructions one at a time yourself

• As you do so, think about how the procedure affects the worksheet

• This process is called desk checking, and it is an important part of the development process

Desk Checking

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 41

• Comments are used to document each procedure• This will help you remember its purpose at a later

date or help somebody else understand its purpose• Comments begin with the word Rem (short for

Remark) or an apostrophe• Comments have no effect on the execution of a

procedure; they simply provide information about the procedure, such as name, creation date, and function

Using Comments to Document a Procedure

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 42

Using Comments to Document a Procedure

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 43

• Some users prefer to attach a digital signature to verify the authenticity of a document

• A digital signature is an electronic, encrypted, and secure stamp of authentication on a document

• This signature confirms that the file originated from the signer and that it has not been altered

Attaching a Digital Signature

Using Macros, Controls and Visual Basic for Applications (VBA) with Excel 44

• The digital signature references a digital certificate• A digital certificate is an attachment to a file or

email message that vouches for its authenticity, provides secure encryption, or supplies a verifiable signature

• Many users who receive files enable the macros based on whether they are digitally signed by a developer on the user’s list of trusted sources

Digital Certificates