Visual Basic for Applications (VBA) is a programming language of Microsoft Excel and other Microsoft Office Programs which is used to write macros to automate various tasks in order to save time and to improve output. In this article, I will try to explain the basics of VBA for Microsoft Excel, so that one can get started with it.
To start with VBA code, you need to have Development tab in your Microsoft excel and this will not be available by default.
So, The first step would be to add Development tab in your Microsoft excel’s ribbon or tab if it is missing (by default it will not be there)
To add Development tab, click on “File -> Options” on Microsoft excel as shown below:
You will get below form after selecting “Option” on excel’s menu bar, now select “Customize Ribbon” and then select “Development” check box to see the development tab in your Microsoft excel workbook.
Great! You have just added Development tab to your excel’s ribbon and your excel’s ribbon should look like below after proper setting.
Now, let’s go through the important components of VBA (Excel).
Insert: By using this option, you can add activeX controls on your excel sheet as per your requirement. Below screenshot shows steps to access these activeX controls.
You can add any control you want by simply dragging onto your excel sheet.
Right, click on added control to see options available to customize the same as per your requirement. e.g. control’s property, formatting options etc.
Design Mode: This changes the Excel Sheet’s mode i.e. development mode & normal mode and this should be clicked in order to add/remove/modify any activeX control on your Microsoft Excel sheet. Click again once you are done with editing activeX controls to check the added/modified functionality to run added Marco
Now, as you have already added the activeX control, it’s time to add some macro which will be executed by this control. Below steps will show the steps to write macros in Excel.
Visual Basic: It opens the development environment to write a macro for any control added on excel sheet. You can also write code for the various workbook and excel sheet events. To add code for the previously added button, either you can click Visual Basic button to go to development environment or simply right-click on the button and select “View Code”.
below screen shows the development environment to write macros. The left box is called Project Explorer and you will find all excel sheets, forms, modules etc. Yes! You heard it right! You can add forms to simplify your work, like data entry. On your right, you can write your macros. I suggest exploring as much as you can to understand more available features.
Note: Remember to save your Microsoft excel file as “Macro-Enabled” for its functionality. Once you saved above excel, just click on the button to execute the added command.
You just executed your first VBA code i.e. macro.This article is just to get you started with VBA and most of you are already aware of this.