Creating Excel Macros

Macros are basically a set of functions and commands that help Excel users in performing the same task again and again easily. Creating Excel macros for the execution of repetitious tasks will help in any business organization. Macros not only save time, but it also improve consistent data maintenance and reduce errors.

Spreadsheets in every organization contain lots of raw data.  This data is analyzed with the use of various logical calculations called formulas. When recording a macro, each formula is then substituted with VBA (Visual Basic for Applications) program code.

Most users can start creating Excel macros simply  by using the Macro Recorder in Excel.  Many simple macros can be created without the intervention of an Excel VBA programmer.

Here are few tips that will help you learn how to record Excel macros.

1. Macros need to be recorded before they are run.

Your Excel options must be changed to grant permission for macros to be executed.  To allow the recording of macros, you need to enable the macro option from the tool menu.

2. Look for the Record New Macro option in the menu.

As soon as you select the option, a dialog box opens, where you need to type a name for the macro you are recording.

3. Just after typing the name click the OK button and the recording will start automatically.

4. The Excel user then needs to execute each of the functions that you need the macros to perform.

The commands and functions that you input while recording are all recorded, even the mistakes. Macros are also capable of performing multiple tasks. \

5. Once the functions are recorded, you need to click the Stop Recording button that is present in the small window opened showing that recording is active.

Once the recording is stopped the macros are saved in the VBA Editor.

6. To run the macros you can run them from the Macros dialog, or you can create a button to be placed on the toolbar.

To create a button, you need to select Customize Button from the tool menu and in the command tab click Macros. Once the dialog box appears, choose the name of your macro and press the OK button. Now the button will remain on the tool bar, just one click away to perform that task.

7. By creating a shortcut on the keyboard, you can run the macro by pressing the required keys on the keyboard.

To create a shortcut you can select Option from the macro menu. Just after selecting, a window will open, where you are prompted to press the desired shortcut key. You can select a key combination that is convenient to you, but remember that some shortcut keys are already assigned to important functions.  For example, you may not want to reassign a shortcut such as Ctrl+P, which inherently brings up the Print menu.  There are also predefined shortcuts for pasting (Ctrl+c) and cutting (Ctrl+x). After selecting the key combination you want, press the OK button.

Macros are easy to use and can be effectively used for formatting cells for text, putting data in certain cells by matching column and rows and for adding formulas while creating spreadsheet.

Once the macros are recorded, Excel VBA programmers can make changes as required, depending on the needs of the organization.

by Schew Maker

in Computers / Databases    (submitted 2011-04-15) Use and distribution of this article is subject to our Publisher Guidelines whereby the original author’s information and copyright must be included.

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.