Recording Macros In Excel VBA
Many of the solutions with the use of macros can be used even without knowing the structure of commands and Visual Basic for Applications.
This option allows you to record macros, which will be discussed on the basis of the examples discussed in this lesson.
In the example below user of Excel very often changes the number format so that they are no decimal places and use the separator (space), every three digits. To do this must always select the area for which it wants to change such a format, click inside it, right-click in the window ‘Format Cells’ select the number format, to reduce the number of digits after the decimal point to zero, and select ‘Use the separator’. This user instead of repeatedly in the same activities prefer to devote their time to more interesting things, decided to automate this work using Excel VBA macros. As an active cell set one of the cells that contain numbers that require formatting. From the card ‘Developer‘ choose ‘Record macro‘.
Dialog box ‘Record macro‘ appears. Type a macro name, eg ‘Formatting’ (the name can not contain spaces or special characters that is ? “/ etc.) Choose a shortcut key such as ‘e’ and choose the option to store the macro in the ‘Personal Macro Workbook‘. Press ‘OK‘
In the lower left corner of the screen icon will appear that allows you record a macro to stop, as long as it is displayed, we know that the macro is recorded.
Format the number in cell C4 – no decimal places, using a separator 1000 (format the cell, exactly as you would if it had not lasted for recording macros).
Do not click on anything else and do not select any cells, do not click on cell C4 and, before or after formatting, because everything you do will be recorded. Immediately after you click ‘OK.’ In the ‘Format Cells‘, stop the recording by clicking the icon in the lower left corner or on the ‘Developer‘.
Check if your first macro runs. Select cell C5 and then press ‘Ctrl + e‘. Format numbers will change immediately on the right. Macro works fine.
Keyboard shortcut ‘Ctrl + e‘ entered in the ‘Record Macro‘. The shortcut key is optional and need not be selected for each macro. Using it for frequently used macros significantly improves and speeds up the work. When it comes to selecting the first letter is just as well could be a different letter, I suggest, however, do not use abbreviations already reserved by Excel or for example, Ctrl + c (copy), Ctrl + v (paste), Ctrl + x (cut), Ctrl + s (save), Ctrl + P (Print). If you used one of these letters, the original function would cease to operate. The macro will work properly, not only for a single cell, but also for any area of cells. Select all the data in both tables sheet and press ‘Ctrl + e‘
If you choose not to use the shortcut key, you can run the macro by clicking the icon to which you assign the macro. It is very convenient especially that you choose for each of the macro icon that you will be associated with this macro. A third way to run a recorded macro is to select the card: ‘Developer‘ command ‘Macros‘.
Window appears ‘Macro‘, where you select the macro you want to run. (The list of available macros will be different for each user.) While recording a macro, you select the Store Macro ‘Macro Workbook personal’, so that this macro will be available whenever you start Excel. If you chose the option ‘This workbook’ macro would be available only when the file in which it is located is open. This is how Marco recorder is working in Excel.
Template
Further reading: Basic concepts Getting started with Excel Cell References
