Lesson 19: Recording and using macros

Recording and using macros

You can create a macro by recording successive commands:

Select a blank cell in the worksheet
Select ‘Macro’, ‘Record New Macro’ (Record new macro) from the Tools menu (Tools)
Choose the “Record New Macro” option
Type the desired name in the “Macro name”
Choose “Personal Macro Workbook” if the macro should be available in all workbooks

Image1les19_en

Click Ok
A menu bar appears in the worksheet with ‘Stop Recording’
and “Relative References” buttons
If you want to use the macro later at any place in the worksheet, click the “Relative References” button
Return to the worksheet and make the necessary actions.
Once this is done click on the ‘Stop Recording’ button
To start a recorded macro, select the cell where you want to place the macro
Choose the “Macro” option from the Tools menu.
Click on the desired macro name
Click the command ‘Start’ button
and the macro performs all operations

Create Macro buttons

If you have a macro that you need to use repeatedly in the same worksheet , it is useful to make this a button on your worksheet:

First you need to draw the macro button
Right click in the toolbar and select the ‘Forms’ toolbar
Click on the “New”button This button can be found in the forms menu bar.
Click and drag the mouse button to draw on the worksheet .
Once you release the mouse button, a dialog box opens

Image4les19_en

Select the macro name
Click OK
The dialog box is closed
Select the text in the button and give a name to the button
Place the cursor anywhere in your worksheet
Click the macro button
The macro is run

You can also to cut, copy and paste this button to another location or even to another worksheet or workbook:

Click with the RIGHT mouse button and choose. eg: copy
Open another worksheet or workbook
Click with the RIGHT mouse button on any cell
Select ‘Paste’

Assigning a shortcut key for a macro

You can assign a shortcut key to a macro:

Choose Macro from the Tools menu
Choose the “Record New Macro” option
Type the desired macro name
Click on the Tab key
Choose a letter for the ‘Shortcut key’ (Shortcut key)

Image5les19_en

Click OK
Perform the operations of the macro
Press the “Stop” button after recording the macro

Note for the use of keyboard shortcuts: if this button is already used by Excel
(Eg Ctrl. G = ‘Go To’) , you cannot use this shortcut.

Assigning macros to a button in the toolbar

You can assign a macro to a button on the toolbar or you can create a toolbar with different macros:

In the example below we create a macro to filter lists:

Choose the Tools menu, select ‘Macro’, ‘Record Macro’ (Record Macro)
Give the macro a name eg: ‘Filter’
Check the “Personal Macro Workbook” (Personel Macro Workbook) box if you want to use macros in all workbooks
Click Ok

Create the macro:

Select any cell
Choose “Filter” from the menu ‘Data’
Select ‘Auto Filter’ (AutoFilter) option
Click on the ‘stop macro’

Now we make a button for this macro and place it on the toolbar:

Click with the RIGHT mouse button on an empty space in the toolbar
Select ‘Toolbars’ (Customize)
Click on the (Commands) tab to add a button to the existing toolbar
In the “Commands” window choose the ‘Macro’ category
Drag the custom button to the toolbar
Click on the ‘Modify Selection‘ button and choose ‘Change button image’
You get a few buttons, choose the one you want

Image7les19_en

Click Close (close)
Click on the button to assign the macro (Assign Macro)

Image8les19_en

Select the macro
Click Ok
Every time you click this button the macro will run

You can create macros for all operations

Delete Macro

Click Tools (Tools)
Select macro
Click the macro name
and click Delete

Delete Macro

Click with the RIGHT mouse button on an empty space in the toolbar
Select ‘Toolbars’ (Customize) in the menu
Drag the button to be deleted from the dialog

Hyperlinks

Type some text in a cell of the worksheet
Click Enter
Select the text
Select ‘Hyperlink’ from the ‘Insert’ (Insert) menu

Image9les19_en

Enter the hyperlink or click “Browse” to insert a hyperlink or a link to a file
Click OK
Clicking on the hyperlink takes you immediately to the Web
To return to the Excel worksheet, click on ‘Previous’

Awesome!
You've completed this course START NEXT COURSE

Lesson 19

Office 97 - Excel