The basic use of Excel is required for the VBA. As long as you are reasonably familiar with Excel then it should be easy to understand the tutorial. In case you do not know Excel at all, it is probably better to first explore the world of Excel at your end. This tutorial is especially only for those at those who have good knowledge of Excel and want to upgrade their work with only a bit of VBA knowledge.
Firstly you need to have Visual Basic for Excel installed on your computer. A quick way to test on either operating system if you have Visual Basic installed is to press ALT+F11. If the VBA editing window opens you are ok, otherwise go you will have to install VBA.
Macro Security Level
“Tools -> Options -> Security -> Macro Security” and set the security level to medium, or high so you can indicate whether or not to permit the execution of macros depending on the workbook you are opening
Excel for Mac 2011, go to “Excel -> Preferences… -> Security -> Macro Security” and tick the box “Warn before opening a file that contains macros
VBA stands for Visual Basic for Applications.VBA enables you to automate various activities in Excel like generating reports, preparing charts & graphs, doing calculations, etc. This automation activity is also often referred as Macro. This way it helps users to save their time spent behind running the repetitive steps.
A macro is a sequence of instructions. This sequence of instructions automates some aspect of Excel you do regularly. When you’re automating some of your works in Excel using Macro, you can perform more efficiently and with fewer errors. For example, you may create a macro to format and print your month-end sales report. After the macro is developed, you can then execute the macro to perform many time-consuming procedures automatically.
Language Scope (Examples)
VBA help in Automate a procedure that you do frequently in Excel. For example, you may need to prepare a month-end sales report. If the task is straightforward, you can develop a macro to do it for you.
Automate repetitive operations. If you need to perform the same action in different workbooks, you can record a macro when you are performing the task in your first workbook. Then you can let the macro repeat your action in the other workbooks.
Create a custom command. For example, you can combine several Excel commands so that they’re executed from a single key or from a single mouse click.
You can create a simplified front end or User Interface for users who don’t know much how to use Excel. For example, you can set up a new visitor or employee information template.
You can develop a new worksheet function. Although Excel has a wide range of built-in functions, you can also create custom functions that greatly simplify your formulas and could be use anywhere in workbook denoted by = sign.
Create complete macro-driven applications. You can create custom dialog boxes and add new commands to the Ribbon using Excel Macro.
To start working on a new macro we first need to create a module. A module is a sheet in which you can type the programming code
“Option Explicit” is a module-level statement, which means it holds for all the programming you do in this module. Writing Option Explicit at the top of a module means you have to define (“declare”) all variables used in the module. This is good programming practice as it prevents possible mistakes in your macros.