Introduction to Office Automation: Tutorial Exercise 1

Tutorial Sheet 1: Introduction to Office 97 Macros in Excel Whereas WORD is basically about organising textual information, EXCEL is especially suitable for numerical information, and it contains many built-in statistical and mathematical functions that you can call upon to assist in your analysis. For the most part, Excel will be of greatest use to you, so in the majority of these exercises we will concentrate on the spreadsheet. Many of the skills you will develop will be equally applicable to Word and Powerpoint, although each of those applications will of course have their own set of specialised functions. We will begin by learning about macros, how to record and use them and about the editing environment provided by Office 97.

Task 1.1 : Creating a Simple Macro Follow the instructions on pages 5-7 of the accompanying reference notes booklet to create the BoldItalic macro. To view what you have recorded, open the Visual Basic editor, by right-clicking a blank part of the toolbar and selecting “Visual Basic”. The following toolbar will appear. You select the fourth from the left to activate the VBA Editor. The Project window gives the workbook together with a list of loaded worksheets. Also included is a list of any code modules which are loaded. The macro you

have just recorded will have been placed in “Module1”. If you locate and highlight that, you should get a listing of your macro as illustrated above. You may find that the macro sheet is not maximised as illustrated in the figure above – if so, use the maximise button to enlarge the window as far as possible – this will give you more room in which to work. It may also help to resize the VBA Editor window so that you can see both it and the Excel window underneath. Take the time to look at the macro code to see what it means. Think about what actions you took to create the macro, and match them up to the lines of code it has produced. Follow the instructions given in the booklet of notes to assign this macro to a key-combination. Here are some simple additional things to try: 1.

Edit the macro to alter the range affected by the macro, and test it out.

2.

Try also making the text underlined

3.

Create a ‘sister’ macro that returns the state of the cells to normal.

1

Introduction to Office Automation: Tutorial Exercise 1

TASK 1.2 In this example, we will continue with the macro recorder and get it to do a little more! First, resize the Excel and the VBA windows so both are visible. Select the range A15 to A20 and click the “Record Macro” button on the VB toolbar, assuming it is still visible. This is the button that looks like a blue dot. 1.

In the Record Macro dialog box, change the name to MergeVertical and set CTRL+SHIFT+M as the shortcut key.

2.

Click OK. In the module window, you can see that the recorder immediately puts the comment lines and the Sub and End Sub lines into the macro code.

3.

On the Format menu, click Cells. In the Format Cells dialog box, on the Alignment tab, select the Merge Cells check box, set the alignment to 90 degrees, and click OK. Note that the recorder puts several lines of code into the macro all at once.

4.

Click the Stop Recording button.

5.

Switch to a view of the macro code, either by selecting the VBA editor directly or, if you did not have it visible, click Run Macro, select MergeVertical and click Edit.

The macro shows 6 different property settings for the cell alignment – each corresponds exactly to the controls you saw in the dialog box. Each of these affects the current selection – note the use of the With..End With construction. You can remove some unnecessary lines in this (and most) recorded macros. When recording the output from dialog boxes, the macro recorder includes all possible properties, whether or not they modify the default values. It makes your macro shorter and easier to read if you edit the macro so that only the properties you wish to change are referred to. Edit the macro so that only the lines relating to Orientation and MergeCells are left within the

With..End With block. Test your macro out!

TASK 1.3 Try recording a macro that inserts a 3x3 table, centres it, and makes its font “Times New Roman”. Edit the code to remove unnecessary lines, and change the number of rows to 6.

TASK 1.4 – Toggle the value of a property with a macro By this we mean taking a property which has two states (e.g. Bold) and letting the macro switch between them. Suppose we wanted to toggle the display of the gridlines on a worksheet. You could create one macro to turn the gridlines off and a second one to turn them back on, but it is more efficient to get one macro to do both! In order to toggle the value of a property you first need to obtain the value of that property from Excel. This can then be stored in a special container called a variable. The current value of the property is assigned to the variable. Try typing this into a new macro:

Sub ToggleGrid() temp = ActiveWindow.DisplayGridlines ActiveWindow.DisplayGridlines = Not temp End Sub NB: If “Option Explicit” appears at the top of the module, delete it before running this macro. See if you can think how you might avoid the use of the variable “temp” altogether.

2

Introduction to Office Automation: Tutorial Exercise 1

TASK 1.5 – Copying Text In this example we will copy some information from one cell to a range of other cells. Type some information into a cell, start the macro recorder, copy the contents of this cell and paste it to a range of other cells. Stop the macro recorder and look at the generated code. It should look something like this:

Sub Macro1() Range("A4").Select Selection.Copy Range("C4:C10").Select ActiveSheet.Paste Range("D4").Select End Sub The basic structure of this macro is much the same as the others, but it is worth highlighting some important features of the VBA syntax. In many cases the code basically sets the various properties of Excel objects, however, in some cases (as shown above) the code does not assign properties (there are no = signs). Here, the code comprises actions that must be carried out – e.g. Selection.Copy is an instruction to copy the current selection to memory. Such actions are referred to as methods. Some methods do not require extra information, as here. In other cases (e.g. PasteSpecial) Excel needs to be told more about what you want to do. When using Excel in the normal way, you would be presented with a dialog box via which you can provide the extra information. In VBA you have to do this by means of arguments supplied with the method. Try recording another macro, similar to the above, but this time, use the PasteSpecial option when you paste into the new cells. You should get some code along these lines:

Sub Macro3() Range("A4").Select Application.CutCopyMode = False Selection.Copy Range("B12:B17").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End Sub Note that the options you selected in the dialog box are represented by the arguments to the PasteSpecial method.

TASK 1.6 - Writing Some Simple Macros To gain expertise at coding in VBA, you must try to develop some solutions to typical problems. This section comprises a number of elementary tasks which test skills that are frequently used in creating applications. In that sense, these are core skills that you will need to develop more complex applications. 1.6.1

Write VBA macros to: • find and report the row and column of a selected cell. (Look for row and column properties in help) • obtain the sum of all numbers in the row containing the selected cell • as above, but for the column • count the number of cells in a selected range, and the number of these containing numerical data

1.6.2 Assuming you had a worksheet column containing a list of names (Forename, Surname) some of which were repeated, write a macro to find all the different names.

3