Automating Repetitive & Complex Tasks with Excel Macros/VBA PDF

Preview

Summary

The Hong Kong Polytechnic University Information Technology Services Office Staff IT Training Workshop Automating Repetitive & Complex Tasks with Excel Macros/VBA This training material is compiled for internal training purpose only. Table of Contents Overview of MS Excel Macros/VBA ...

Description

The Hong Kong Polytechnic University Information Technology Services Office

Staff IT Training Workshop

Automating Repetitive & Complex Tasks with Excel Macros/VBA

This training material is compiled for internal training purpose only.

Table of Contents Overview of MS Excel Macros/VBA _____________________________________________ 2 Quick start: Create a macro ___________________________________________________ 2 Automate tasks with the M Macro acro Recorder ________________________________________ 4 Show the Developer tab ______________________________________________________ 4 Assign a macro to a button ___________________________________________________ 8 A simple macro example – autom automating ating data entries ______________________________ 12 Exercise ______________________________________________________________________ 17

Stepping through the program with the debugger ________________________________ 20 Assigning button to macro ___________________________________________________ 23 Exercise ______________________________________________________________________ 25

Declaration of variables, Input & Output (if time allows) ___________________________ 28 Example: The Age program ______________________________________________________ 28 Example: The Sum program ______________________________________________________ 29

Appendix _________________________________________________________________ 32 Add, remove, or change a trusted location __________________________________________ 32 Data Type Summary ____________________________________________________________ 34 Using the Immediate Window ____________________________________________________ 36

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 1

Overview of MS Excel Macros/VBA Quick start: Create a macro If you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks. A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes. After you create a macro, you can edit it to make minor changes to the way it works. Suppose that every month, you create a report for your accounting manager. You want to format the names of the customers with overdue accounts in red, and also apply bold formatting. You can create and then run a macro that quickly applies these formatting changes to the cells you select.

How? Before you record a macro Macros and VBA tools can be found on the Developer tab, which is hidden by default, so the first step is to enable it. See next section for details.

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 2

Record a mac macro ro 1. In the Code group on the Developer tab, click Record Macro. 2. Optionally, enter a name for the macro in the Macro name box, enter a shortcut key in the Shortcut key box, and a description in the Description box, and then click OK to start recording.

3. Perform the actions you want to automate, such as entering boilerplate text or filling down a column of data. 4. On the Developer tab, click Stop Recording .

Take a closer look at the macro You can learn a little about the Visual Basic programming language by editing a macro. To edit a macro, in the Code group on the Developer tab, click Macros, select the name of the macro, and click Edit Edit. This starts the Visual Basic Editor. See how the actions that you recorded appear as code. Some of the code will probably be clear to you, and some of it may be a little mysterious. Experiment with the code, close the Visual Basic Editor, and run your macro again. This time, see if anything different happens!

Next steps  

To learn more about creating macros, see Create or delete a macro. To learn about how to run a macro, see Run a macro.

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 3

Automate tasks with the Macro Recorder To automate a repetitive task, you can record a macro with the Macro Recorder in Microsoft Excel. Imagine you have dates in random formats and you want to apply a single format to all of them. A macro can do that for you. You can record a macro applying the format you want, and then replay the macro whenever needed.

Macros and VBA tools can be found on the Developer tab, which is hidden by default, so the first step is to enable it.

Show the Developer tab 1. 2. 3. 4.

Click the File tab. Click Options. Click Customize Ribbon. Under Customize the Ribbon and under Main Tabs, select the Developer check box.

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 4

Record a macro Follow these steps to record a macro. 1. On the Developer tab, in the Code group, click Record Macro. -ORPress Alt+T+M+R .

2. In the Macro name box, enter a name for the macro. Make the name as descriptive as possible so you can quickly find it if you create more than one macro. Note: The first character of the macro name must be a letter. Subsequent characters can be letters, numbers, or underscore characters. Spaces cannot be used in a macro name; an underscore character works well as a word separator. If you use a macro name that is also a cell reference, you may get an error message that the macro name is not valid.

3. To assign a keyboard shortcut to run the macro, in the Shortcut key box, type any letter (both uppercase or lowercase will work) that you want to use. It is best to use Ctrl + Shift (uppercase) key combinations, because the macro shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. For instance, if you use Ctrl+Z (Undo), you will lose the ability to Undo in that Excel instance. 4. In the Store macro in list, select where you want to store the macro. In general, you’ll save your macro in the This Workbook location, but if you want a macro to be available whenever you use Excel, select Personal Macro Workbook . When you select Personal Macro Workbook, Excel creates a hidden personal macro

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 5

workbook (Personal.xlsb) if it does not already exist, and saves the macro in this workbook. 5. In the Description box, optionally type a brief description of what the macro does. Although the description field is optional, it is recommended you enter one. Also, try to enter a meaningful description with any information that may be useful to you or other users who will be running the macro. If you create a lot of macros, the description can help you quickly identify which macro does what, otherwise you might have to guess. 6. Click OK to start recording. 7. Perform the actions that you want to record. 8. On the Developer tab, in the Code group, click Stop Recording

.

-ORPress Alt+T+M+R .

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 6

Working with recorded macros in Excel In the Developer tab, click Macros to view macros associated to a workbook. Or press Alt+ F8. This opens the Macro dialog box.

Caution: Macros cannot be undone. Before you run a recorded macro for the first time, make sure that you’ve either saved the workbook where you want to run the macro, or better yet work on a copy of the workbook to prevent unwanted changes. If you run a macro and it doesn’t do what you want, you can close the workbook without saving it.

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 7

Assign a macro to a button You can use a button (a form control) to run a macro that performs an action when a user clicks it. For example, you might use a button to automate the printing of a worksheet, the filtering of data, or the calculation of numbers. After you create a macro, you can assign it to a button you click to run the macro. You can assign a macro to a button on the Quick Access Toolbar or to a button in your own personal group on the ribbon. If you want a macro button to be available in other workbooks, assign it to a macro that was created in a personal workbook.

Add a macro button to the Quick Access Toolbar 1. Click File > Options > Quick Access Toolbar. 2. In the Choose commands from list, click Macros.

3. Select the macro you want to assign a button to. 4. Click Add to move the macro to the list of buttons on the Quick Access Toolbar. 5. To replace the default macro icon with a different button for your macro, click Modify. 6. Under Symbol, select a button icon for your macro.

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 8

7. To use a friendlier name for the button, in the Display name box, enter the name you want. You can enter a space in the button name. 8. Click OK twice. The new button appears on the Quick Access Toolbar, where you can click it to run the macro. Tip: When you save the workbook, buttons you assign to macros in the personal workbook will be available in every workbook you open.

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 9

Add a macro button to your own group on the ribbon 1. Click File > Options > Customize Ribbon. 2. Under Customize the Ribbon, in the Main Tabs list, check the Developer box if it is not already checked.

3. Pick the tab where you want to add your own group. For example, pick Home, to add your group to the Home tab. 4. Select New Group. That adds New Group (Custom) to the tab you picked. 5. To use a better name for your new group, click Rename, type the name you want in the Display name box, and then click OK. You can enter a space in the name. For example, type My Macros. 6. To add a macro to the group, in the Choose commands from list, click Macros.

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 10

7. Select the macro you want to add to your new group, and then click Add. The macro is added to the My Macros group. 8. To use a friendlier name, click Rename, and then type the name you want in the Display name box. You can enter a space in the name. 9. Under Symbol, select a button icon for your macro. 10. Click OK twice. Your new group appears on the tab you picked, where you can click the button to run the macro.

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 11

A simple macro example – automatin automating g data entries

Starting the macro recorder

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 12

Make sure “Use Relative References” is selected

Give an appropriate macro name & shortcut key.

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 13

Type in season names for the following cell entries:

Stop the macro recorder

Now try out the macro … move to any empty cell

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 14

Click on the “Macros” button to see a list of available macros

Make sure the macro “seasons” is selected, and click “Run”

Now verify the macro is executed as expected

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 15

Now click “Macros”, and look at the seasons macro by selecting the macro and clicking on “edit”

Click on the “Excel” icon to go back to spreadsheet view

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 16

Exercise Record another macro for “moving to next column” (making sure “relative reference” is selected), then stop the recording

Compare the 2 macros “seasons” and “nextCol”.

What can you tell about the structure of a macro program? Now see if you can combine the nextCol macro into the seasons macro (i.e. enhancing the “seasons” macro with automatic “next-column” moving capability)

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 17

Example: Highlighting alternate rows in a table

This time, let’s see what code is being generated as we are doing the macro recording. Click on the “Visual Basic Editor” icon to activate the VBE window

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 18

Arrange the Excel window and the VBE window to a comfortable view.

First, let’s create a macro to highlight only the first row of the data Click anywhere on your data within the table Start recording by clicking the record macro button Accept the default name (we’ll try changing the macro name to a more meaningful one with VB Editor soon) Look at what happens at the VB Editor as you walk through the macro recording process Stop the recoding after the process is done Clear the formatting on the data, and playback the macro to see if it is working properly

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 19

Rename the macro to a more meaningful name at the VB editor:

Stepping through the program with the debugger

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 20

Watch carefully as you step through the “highlight_first_row” macro

You will find that not all the lines generated by the macro recorder are useful Try and see if you can remove any redundant lines (“optimize” the macro), and verify the macro to make sure it still works after the “optimization” Assume you are sitting at the cell where the “highlight_first_row” macro has just been run, now create another macro “highlight_aRow” which highlights alternate row Repeat the “optimization” for the “highlight_aRow” macro

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 21

Automating the process - Repetition with Do..Loop

We can use a Do..Loop structure to automate the highlight process However, you may find a problem – for some reason, the process “overshoots”:

Try and see if you can solve the problem (i.e. enhance the program), with the aid of the debugger (hint: try and step through the program, start near the bottom lines of the data)

And here is an updated version of “highlight_aRow”

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 22

Assigning button to macro Start with selecting command button in the control toolbox

Drag and draw a command button at any convenient location

Right-click on the command button and change its properties

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 23

Change its caption and font appropriately

Double-click on the button to assign its definition

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 24

Go back to spreadsheet view, and exit design mode

Now try out the new button

Exercise 1. Make up another macro for CLEARING the “highlight alternate rows” format. 2. Make up a button for each of the 2 macros (“highlight alternate rows” & “clear format”) you just made.

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 25

Exercise: Move and arrange data in a table

The task is to arrange data with 10 entries per column..

until you get all data moved..

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 26

Exercise: Update a students’ report

The task is to replace all marks < 40 (failed) with the “Failed” grade. You may arrive at the following code..

Try to enhance the code for an automatic run on the whole result table.

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 27

Declaration of variables, Input & Output (if time all allows) ows) Example: The Age program The program will first read in the user’s name:

Then, the user is asked to input his/her age:

Finally, the next year’s age of the user is displayed:

Also, the data are recorded in the spreadsheet table:

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 28

Example: The Sum program The program calculates the sum: 1+2+…+n.

First, the user can enter the value of n into a specific cell:

Then, after running the “sum” program:

The result will be displayed in another cell:

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 29

Here is the flow chart for the above logic:

Begin

Sum = 0 Count = 1

Count Options. Click Trust Center > Trust Center Settings > Trust Trusted ed Locations. Click Add new location location. Click Browse to find the folder, select a folder, and then click OK OK.

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 32

Remove a trusted location 1. Click File > Options. 2. Click Trust Center > Trust Center Settings > Trust Trusted ed Locations. 3. Select the location to be removed, and then click R emove emove, and then click OK.

Change a trusted locati location on 1. 2. 3. 4.

Click File > Options. Click Trust Center > Trust Center Settings > Trust Trusted ed Locations. In the Trusted Locations list, select a location, and then click Modify Modify. Make the modifications you want, and then click OK OK.

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 33

Data Type Summary The following table shows the supported data types, including storage sizes and ranges. Data type

Storage size

Range

Byte

1 byte

0 to 255

Boolean

2 bytes

True or False

Integer

2 bytes

-32,768 to 32,767

Long (long integer)

4 bytes

-2,147,483,648 to 2,147,483,647

Single (singleprecision floating-point)

4 bytes

-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values

Double (doubleprecision floating-point)

8 bytes

-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values

Currency (scaled integer)

8 bytes

-922,337,203,685,477.5808 to 922,337,203,685,477.5807

Decimal

14 bytes

+/79,228,162,514,264,337,593,543,950,335 with no decimal point; +/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest non-zero number is +/-0.0000000000000000000000000001

Automating Repetitive & Complex Tasks with Excel Macros/VBA

Page 34

(Cont’d)

Data type

Storage size

Range

Date

8 bytes

January 1, 100 to December 31, 9999

Object

4 bytes

Any Object reference

String (variablelength)

10 bytes + string length

0 to approximately 2 billion

String (fixed-length)

Length of string

1 to approximately 65,400

Variant (with numbers)

16 bytes

Any numeric value up to the range of a Double
...