How Excel Macros (VBAs) Can Help You Save Time
If data entry, calculations and creating charts are a regular part of your work, then you probably need no introduction to Microsoft Excel. One of the most powerful and popular spreadsheet applications, Excel allows you to store, organize and manipulate data quickly and easily in many different ways. But few Excel users dare to venture into Excel Macros because most people are intimidated by the thought of ‘programming.’
This need not be the case because anyone can create macros using Visual Basic for Applications (VBAs). VBA is a very simple programming language built into Excel that you can learn to use within a few hours and master in a few days. A macro is an instruction that automatically opens a set of commands to perform a particular task. It can be used to automate repetitive tasks, such as separating the first and last names from a list of a thousand rows and putting them in two different columns.
You will be amazed at how fast you can do things with macros. Imagine the time it takes to change all the labels in a column to lowercase, copy a formula to a hundred cells in a different column, calculate totals and averages, format the cells to two decimal places and copy only the values to another column, and then repeat the entire operation in ten different worksheets. With a macro, you can do that with the click of a button.
To unlock the power of these automations, you will need to access the Developer Tab (which is unselected by default). Here is how you can do it.
File -> Option (or Setting in older versions) -> Customize Ribbon -> Check “Developer” -> Ok
Once you have done that, you will see the ‘Developer’ tab on the menu bar. Click the tab, and you will see some options, including ‘Visual Basic’ and ‘Record Macros.’ Now you can create a macro in two ways: (1) using the ‘Record Macro’ command, and (2) by writing Visual Basic codes.
The former is the easier method because you won’t have to do any coding. But it has limited functionality and is only good for automating simple tasks. Once you click the ‘Record Macro’ button, you will be asked to enter a name for the macro. After that, whatever you do in the worksheet will be recorded until you click the ‘Stop Recording’ button. You can run this macro by clicking the ‘Macro’ button, selecting the macro name and then clicking the ‘Run’ button.
A much better way to create macros is to write your own codes with VBA. Click the ‘Visual Basic’ button, and you will see the Excel VBA Editor, which is a big white screen (with its own menu bar and command buttons) in which you write your code. The beauty of VBA is that the instructions are written using standard English words, which makes it easy to write even the most complex macro once you get the hang of it.
Below is a small VBA program (a macro) that I have written. As you can guess, it changes the contents of the cell range A1 to C4 to bold, underline and italic.
Just in case you’re thinking to yourself that you don’t know how to write code, just remember that VBA is not that difficult once you use it a few times.
You will be amazed at how many hours you can save by making macros an integral part of your Microsoft Excel experience.
You Might Be Interested In
