Visual Basic Editor is a separate application that is a part of Excel and opens whenever you open an Excel workbook. This shortcut works as a toggle, so when you use it again, it will take you back to the Excel application without closing the VB Editor. This is useful when you want to write code that works only for a specific worksheet. This is usually the case with worksheet events.
Also, it still has an old Excel 97 days look. While Excel has improved tremendously in design and usability over the years, the VB Editor has not seen any change in the way it looks. In this section, I will take you through the different parts of the Visual Basic Editor application. But as you get used to working with VBA, you would get comfortable with most of these. Below is an image of the different components of the VB Editor. These are then described in detail in the below sections of this tutorial.
This is where you have all the options that you can use in the VB Editor. It is similar to the Excel ribbon where you have tabs and options with each tab. You will notice that most of the options in VB Editor have keyboard shortcuts mentioned next to it. Once you get used to a few keyboard shortcuts, working with the VB Editor becomes really easy. This is just like the Quick Access Toolbar in Excel. It gives you quick access to some of the useful options. You can customize it a little by removing or adding options to it by clicking on the small downward pointing arrow at the end of the toolbar.
You can move the toolbar above the menu bar by clicking on the three gray dots at the beginning of the toolbar and dragging it above the menu bar. What you see in the image above which is also the default is the standard toolbar. You can access other toolbars by going to the View option and hovering the cursor on the Toolbars option.
You can add one or more toolbars to the VB Editor if you want. And each of these projects can have a collection of objects in it. For example, in the below image, the Project Explorer shows the two workbooks that are open Book1 and Book2 and the objects in each workbook worksheets, ThisWorkbook, and Module in Book1. There is a plus icon to the left of objects that you can use to collapse the list of objects or expand and see the complete list of objects.
Consider the Project Explorer as a place that outlines all the objects open in Excel at the given time. To close it, simply click the close icon at the top right of the Project Explorer window. Properties window is where you get to see the properties of the select object.
Properties window is a floating window which you can dock in the VB Editor. In the below example, I have docked it just below the Project Explorer. Properties window allows us to change the properties of a selected object. For example, if I want to make a worksheet hidden or very hidden , I can do that by changing the Visible Property of the selected worksheet object. There is a code window for each object that is listed in the Project Explorer.
You can open the code window for an object by double-clicking on it in the Project Explorer area. When you record a macro, the code for it goes into the code window of a module.
Excel automatically inserts a module to place the code in it when recording a macro. The Immediate window is mostly used when debugging code. One way I use the Immediate window is by using a Print. Debug statement within the code and then run the code. It helps me to debug the code and determine where my code gets stuck.
Print before the code and get its result value in the immediate window. Just like Excel Watch Window , the visual basic editor also has a watch window where you can add expressions to track them. You simply need to select the expression and then right-click and go to add a watch.
VBA has its own object browser which can help you to work with all the objects by finding all the properties and which you can use. Imagine if you want to use the range object, object browser will tell you about all the properties and method that comes with it.
Look at the below example. To open the object browser, use the shortcut key F2 or you can also go to the view tab and click on the object browser to open it. Just like the find and replace in Excel, VBA editor has its own find and replace option that you can use to find and replace values from procedures.
The find and replace option in VBE gives you different search patterns, like, if you want to find and replace something from the current procedure, from the current module, or from the current project. You can use the Locals window in VBE to displays all declared variables in the current procedure and their present values. There are two different types of code windows and both look just the same but there is a difference that you need to know. You will be writing most of the VBA code in a module.
When you record a macro that code goes straight into a module that VBA inserts automatically when you use the macro recorder. When you need to write code, you have to insert a module and for this, you simply need to go to the project window and right-click on the project name, and from that menu go to insert and select module.
And when you click on remove, it asks you if you want to back up that module before removing it. There is also an option where you can export a module to save it as a file in your system.
Open the code window for the object you simply need to double click on it and in its the code window, there is a dropdown from where you can select the event that you want to use to execute the code. You can use the visual basic editor in three different modes depending on the face of programming, writing the code, locating an error, and fixing an error.
Just like any other programming language in VBA you can also use comments to define how that code works. Using comments is a good habit and it can help you in so many ways. Each object in VBA comes with some properties and methods and when you insert an object or a command in the code window and then you enter.
In the above example, when I added a. While defining expressions for a property or a method you can use the list of constants available. In the below example, while using the Border Around method it showed me all the constants which are available for the weight argument. While writing VBA codes you will need to use variables and one of the most important things while using variables is to declare their data type.
But when you use the option explicit statement, then you have to declare the data type for every single variable which you are using, and if you fail to do so VBA will show an error message. For this, you can go to the tools menu and open the options. Now every module will have Option Explicit statement at the beginning, and you have to declare every single variable. When you can write multiple codes in a single code window and VBA separate them with a divider, but if you want to have a more focused view, you can change the view of the code window.
Below I have the codes in a single module. When you write a VBA code there could be a chance that that code has a bug or an error that can come while executing that it. In that case, the best way is to execute that code step by step and validate each line of code. For this, you can use shortcut key F8 to execute a code line by line or use step into option from the debug menu. Indenting is basically structuring the code using tabs, below is an example.
The visual basic editor allows you to import VBA codes from a text file without copy-pasting. Once you insert a module you can go to the insert menu and select the file option from there.
It opens the dialog box where you can locate the text file and import all the codes from it to the current module. Even though you can adjust the width of the code window and make it wide but there could be a situation when you will be dealing with long lines of code that makes it hard to read.
The best way to deal with this problem is to add a line break. Basically, a line break is something when you break a line into two lines using the line break character.
When it comes to user interface visual basic editor is not that good looking. But there are few formatting options that you can use to customize it or change its look the way you want.
The following basics will help you get started. Now we will demonstrate how to program in VBA programming language. Here the name is the name you want to assign to your program. While sub stands for a subroutine which we will learn in the later part of the tutorial. Your complete code window should now look as follows.
In this tutorial exercise, we are going to create a simple program that calculates the EMI. The following image shows the formula for calculating EMI.
The above formula is complex and can be written in excel. The good news is excel already took care of the above problem. You can use the PMT function to compute the above. It will open a VBA editor, from where you can select the Excel sheet where you want to run the code. To open VBA editor double click on the worksheet. It will open a VBA editor on the right-hand side of the folder. It will appear like a white space. In this step we are going to see our first VBA program. To read and display our program we need an object.
In next step you have to run this code by clicking on the green run button on top of the editor menu. When you run the code, another window will pops out.
Download the above Excel Code.
0コメント