Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Record Macros

| | Information Helpful? Why Not Donate.

Macro Recording in Excel. Absolute and Relative Reference

RECORDING MACROS IN EXCEL. See Also Assign Macros in Excel & Using The Macro Recorder In Excel-Video

A macro is a series of key strokes or mouse actions that are recorded and saved as a program. You can activate your macro at any time you like to repeat your recorded steps. Macros can be assigned to a shortcut key, an object or even to your toolbar to make them easier to access.

Because of computer viruses, the security levels set for macros in Excel are pretty strict. As a default they are set to High. When set to High, only digitally signed macros that you have received from trusted sources will run. Other macros are automatically disabled. If you wish to enable a macro that has been disabled you will need to change your security setting to Medium. Tools>Macro>Security

There are two types of macros that you can record. A relative recorded macro and an absolute recorded macro.

ABSOLUTE MACROS

The default for recording a macro is Absolute. This means that when you record your macro, it will run in/on exactly the same sheets/cells/columns/rows that you specify.

We will record a macro to bold column A. The first thing we need to do is go to Tools>Macro>Record New Macro. When you do this the Record Macro dialog box will pop up. The first thing we are asked for is to give our macro a name. We will call it BOLD_A. (Note that when naming a macro, you cannot use spaces, but you can use an underscore to separate words). Hit the tab key and you will jump to the Shortcut Key box, this is where you can assign your macro to a shortcut key if you wish. Tab again and you will be taken to the Store Macro In: section of your dialog box. It is here that you nominate where you wish your macro to be stored. The options are:

Personal Macro Workbook - this means your macro will be stored in the Personal.xls workbook and will be available to you every time you Start Excel in any workbook.

New Workbook - Your macro will be created only in a new workbook

This Workbook - Your macro will be created only in the workbook you are
currently in.

We will use the Personal Macro Workbook.

Hit the tab key again and you will jump to the Description area of your dialog box, where you can give our macro a description. Click OK and your box will disappear and the Stop Recording toolbar will pop up on your screen. The icon on the right of this toolbar is used to toggle between Absolute (icon not depressed) & Relative (icon depressed) recording.

(Note that if your Stop Recording toolbar (it has been closed via the X) does not appear, go to View>Toolbars>Customize and under the Toolbars tab, check the Stop Recording toolbar from the list and hit Close).

Also notice the word Recording is displayed in your Excel Status Bar. Everything you do from now on is being recorded. (This includes any errors you make!!!). For this reason, a few dry runs are always worth doing.

Click on the column header for column A (grey background) to highlight the entire column. Click the bold key on your formatting toolbar. Now click the Stop Recording tool on your toolbar. Now, when you exit Excel, you will be prompted to save the changes you have made to your Personal Macro Workbook.

If you are not aware, the Personal Macro Workbook is ONLY created once you have recorded a macro to it. Once created, it will open (as a hidden Workbook) anytime you open Excel on the same PC. This means any macros stored within it are available anytime you are in any Workbook in Excel.

To see your Personal Macro Workbook (which is a normal Excel Workbook), go to Window>Unhide.

RELATIVE MACROS

Relative macros can appear tricky. However, in reality they are not. All you need to remember, when recoding a Relative macro, is that all your movements are *relative* to the active cell at the time your Start recording. For example, if you were in cell B10, Start ed recoding a relative macro, and selected cell A9 and bolded it, your movements would be relative to B10. If we then played back the macro, with C10 active, cell B9 would be selected and bolded. If were in cell A1 and played the macro, it would bug out! This is because it cannot select a cell 1 column to the left and 1 row up.

RUNNING YOUR MACRO

Other then running our macro via a shortcut key, we can also run them via the Macro dialog box. Tools>Macro>Macros (Alt+F8). Note also we can assign a shortcut key to any existing macros.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft