VBA stands for visual basic for application. It is the default programming language for MS Office. If you want to program MS Excel you should know how to work with VBA Macros. VBA is at the same time is fun and a source of improving productivity. Actually it provides an opportunity to do things what canot be done in usual way or to have features that are not available by default in MS Excel.
Unlike usual programming languages that take only code to create a program, we can also record the actions through a feature in MS Office that is called MACRO Recorder. The Macro Recorder simply records the sequence of actions taken by us. Since it is object oriented language, the actions are recorded as actions taken on a set of objects. And then it could be re-run.
VBA, just like other programming language has a specific syntax and style of coding. It is an object oriented language (a programming methodology that is related to objects).
In this post we will discuss the 7 important facts about VBA and Macros.
Making macros available on all MS Excel worksheet:
When you are recording VBA macros, the menu prompts you to save the macro at certain location. You can choose between these two locations:
- This workbook or the workbook you are currently working on
- Personal.xlsb
If you choose to save the macro in the current workbook, it will not be available in other opened workbooks. In order to use a single macro in several other workbooks, you need to save it in personal.xlsb.
The personal.xlsb is by default a hidden file. It can not be seen until we unhide it. When you will open the MS Excel (and does not open any file) you can see it by pressing “unhide” from View / Unhide.
Assign Shortcut Key for repeatedly running VBA macros:
You assign a short cut key either through VBA Macro menu or by using code with in the macro. If you are looking for assigning it through menu, you can go to Developer’s Tab > Macros and select the desired Macro and press Options. Here you can add the Shortcut key (and the description of the macro as well).
The second approach is to add few lines of the code in the macro body:
Application.MacroOptions Macro:="PERSONAL.XLSB!Macro2", Description:="", _
ShortcutKey:="k"
Type of codes you can find across internet:
There are three types of VBA macros you can come across from Internet. The first one is Sub() that is a macro that runs and when you execute the macro to do certain task. This is the most common type of macro you will come across. The second type is not sub but a function – just like functions SUM() in the excel but this is user defined function or UDF and also uses VBA code. The third one is event procedures that work when certain event is done for example a macro that runs on enter certain value or macro that runs when you open your worksheet or press “Enter” key – all such are examples of event procedures.
It depends on your need what you want to achieve. For example if you have want to format cells to be yellow colored, bold and italic you can record a macro and run it through shortcut key any number of time.
If you have a very complex formula that you don’t want to type again and again, you can define a UDF for that and store in your file for future use.
If you want to display the message “welcome user” when file opens, you can add an event called “Workbook_Open()” to your code.
Where to put the set of code you found from internet:
it is equally important you place your code in the right place. Otherwise it would not work. Note that you need to:
Put Subs and function in Module in the workbook. To insert the module you need to go to Insert>Module select the module and double click to open it and then paste the code. You can put multiple codes in the same module!
For events you need to place in the specific sheet. For example if you want to change the text to bold and italic you need to place it in the specific sheet.
You need to save your workbook as macro enabled worksheet!
A sheet containing macro is different from a normal worksheet and you need to save it as macro enabled worksheet. To do so, when pressing save button, you need to select the second option from the file format menu – i.e. Excel Macro-enabled workbook (*.xlsm)
Learn how to use the immediate window in VBA Editor:
The immediate window in the one that is just below the code editor widows. If it is not visible you can enable it by pressing Ctrl+G. The immediate window is helpful in checking the small pieces of code. For example if you want to check the address of current or active cell you can use it like ?activecell.address. pressing enter will give you the address of the cell and for value you can use ?activecell.value will give you the value of the active cell.
The VBA Macros uses methods to accomplish various tasks:
VBA works on various objects using methods. Method is just like a special set of instructions that works with an object. And not all methods work with all objects. If you are planning to write a macro, make sure to do a research on methods available for that particular object. This will ease up your task by using code that is already available for you and you will not need to reinvent the wheel.
The best place to learn about methods is to the VBA Help. While VBA editor window is open, press F1 or if you are interested exploring the object-method relationship use press F2 (while VBA editor is active). You can see each object followed by the method available for it. Besides the help page MS Office development center has lot of information that can help.