99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Break The Mystery Of Code With Excel Macros
Mon 25th October 2010
If you frequently need to format individual cells in a particular way in Excel 2010, then a simple recorded macro is going to make things much easier and cut out any errors.
Macros allow the automation of formatting, charting and other often-used spreadsheet tasks. A recording of the keystrokes and mouse actions involved in performing a particular task, a macro is easy to create, save and reuse. At any time after the macro is created, the task can be automatically performed by accessing the macro, which then plays back the recording. More advanced macros can display custom forms (with command buttons, text boxes, drop-down lists, etc) and interact with other applications; these macros typically involve the writing and editing of Visual Basic for Applications (VBA) program code.
VBA can be used to write your own macro script, or to copy all or part of a macro to a new macro. After you create a macro, you can assign it to an object (such as a toolbar button, graphic, or control) so that you can run it by clicking the object. If you no longer use a macro, you can delete it.
Although VBA (Visual Basic for Applications) is the programming language that macros use to create custom procedures or automate tasks in all of the Office applications, you can create macros without learning VBA (regardless of whether a macro was created by recording or through the Visual Basic Editor, it is stored as VBA code). Although if you create a macro and can't quite get it to do everything you want it to do, it would be wise to edit the macro using VBA to create additional commands. Some macros, particularly those designed to interact with another application, must be created using the Visual Basic Editor.
In Microsoft Office Excel 2007, recording a macro while formatting a chart, or other object, did not produce any macro code. However, in Excel 2010, you can use the macro recorder to record formatting changes to charts and other objects and then reuse those changes repeatedly.
Excel has a macro facility, known as Excel 4 macros (XLM for short), that was the primary macro language prior to the introduction of VBA in Excel 5.0. Most people have long since migrated their Excel 4 macros to VBA. However, some Excel 4 macro capabilities were missing from VBA, which made this migration difficult. In Excel 2010, one of the goals was to remove any remaining barriers that people had to complete the migration of Excel 4 macros to VBA. Excel 2010 still enables the creation, editing and execution of Excel 4 macros. You can use Excel 2010 to migrate your macros.
When you record a macro, the macro recorder records all the steps required to complete the actions that you want your macro to perform. Navigation on the Ribbon is not included in the recorded steps. If the Developer tab is not available, do the following to display it: click the File tab; then click Options, and select Customize Ribbon. In the Customize Ribbon category, in the Main Tabs list, select the Developer check box, and then click OK.
You can change the security level in your Excel document to temporarily enable all macros. However, to help prevent potentially dangerous code from running, it is recommended that you return to any one of the settings that disable all macros after you finish working with macros.
It's even easy to assign a CTRL combination shortcut key to run the macro, by using the in the Shortcut key command The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open.
To assign a macro to an object, graphic, or control, right-click the object, graphic, or control to which you want to assign an existing macro, and then click Assign Macro. In the Macro name box, click the macro that you want to assign. It's as simple as that.
Macros are saved as part of the workbook in which they were created. If you attempt to exit from Excel without saving any macros you created or modified, you will see a warning dialog box giving you the option to save the affected workbook. You can avoid seeing this warning by saving your work in either Excel or the Visual Basic Editor.
If you want to add the macro to a toolbar, then you must perform this procedure after the macro has been created. Also, there is a Relative Reference button on the Macro Recorder toolbar. By default, Relative References is turned OFF, so Absolute References are recorded. In most instances, you will want to record Relative References (perform the procedure relative to the active cell) so make sure to click the Relative References button prior to clicking a command. If you want the macro available to all Excel workbooks, then save it to the Personal Macro workbook. This file, also called personal.xls, is what all new Excel workbooks are based on.
Now you can create a macro with confidence to automate anything from counting rows, columns and sheets, to copying data from a specific range, and deleting empty rows. Even emailing your workbook, inserting the command to go to a sheet, to selecting a date range can all be automated with a macro to suit your needs - all without any complicated code breaking being involved.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Chief Engineer - Race
Happy with service, from my initial contact with Owen, STL accommodated our specific requirements for tailored training, and at relatively short notice.
Red Bee Media
Course content is good.
Manoj Kumar Pandya
A very well organised course with an excellent trainer. The course matter was very good and will help in me reviewing and building on my training today. I cannot see any reason to change.