The menu in VBA consists of different menu options. These options are mostly available in many and all the menu bars. All the menu options in the Menu bar have different sub-options and functions which are used to process the subsequent work. Just below the Menu bar, the Toolbar is available. The Toolbar is that section in VBA, where we can find some shortcut buttons which are there in some of the menu bars. Project Window is the place in VBA where we can find all the sheets, module and project names.
This window is most of the time located on the left side of the VBA window. The properties window is mostly located below the Project window.
We can move this window as per our needs and requirement. This window shows all the properties of the selected Workbook, Worksheet or Module. As here, we have selected Workbook so that properties of that are getting reflected. But sometimes we will not be able to find it. The module is the place where we mostly write the code. Although we can write the code in Sheet, UserForm as well.
The benefit of writing the code in Module is that it is easy to execute, and we can use it anywhere we want to. Its beyond the scope of this article to go into how to split out your code into objects but modules gives the user a powerful tool to create efficient and well organized code.
To get started writing code you will need to create a new module. Delete a module by simply right clicking, Remove Module. Excel will prompt you to confirm before deleting. Recording a macro in Excel is a great way to automate tasks as well as a great tool to help learn more VBA code. A common trick is to record a macro and then review the VBA code to see how it works. Now you know how to open the VBA editor in Excel you should be able to record a macro, open the editor and view the code.
These can be things as simple as formatting, copying, pasting, deleting or selecting a sell. Alternatively, you could get quite advanced and carry out entire tasks before going into the Excel visual basic editor to review the code.
Let me first quickly clear the difference between adding a code in a module vs adding a code in an object code window.
For example, if you want to unhide all the worksheets in a workbook as soon as you open that workbook, then the code would go in the ThisWorkbook object which represents the workbook. Similarly, if you want to protect a worksheet as soon as some other worksheet is activated, the code for that would go in the worksheet code window. These triggers are called events and you can associate a code to be executed when an event occurs.
On the contrary, the code in the module needs to be executed either manually or it can be called from other subroutines as well. When you record a macro, Excel automatically creates a module and inserts the recorded macro code in it. Now if you have to run this code, you need to manually execute the macro. While recording a macro automatically creates a module and inserts the code in it, there are some limitations when using a macro recorder.
For example, it can not use loops or If Then Else conditions. This would instantly create a folder called Module and insert an object called Module 1. If you already have a module inserted, the above steps would insert another module. Once the module is inserted, you can double click on the module object in the Project Explorer and it will open the code window for it.
Note: You can export a module before removing it. It gets saved as a. When it opens, you can enter the code manually or copy-paste the code from other modules or from the internet. Note that some of the objects allow you to choose the event for which you want to write the code. For example, if you want to write a code for something to happen when selection is changed in the worksheet, you need to first select worksheets from the drop-down at the top left of the code window and then select the change event from the drop-down on the right.
Note: These events are specific to the object. When you open the code window for a workbook, you will see the events related to the workbook object. When you open the code window for a worksheet, you will see the events related to the worksheet object.
While the default settings of the Visual Basic Editor are good enough for most users, it does allow you to further customize the interface and a few functionalities. In this section of the tutorial, I will show you all the options you have when customizing the VB Editor. This would open the Options dialog box which will give you all the customization options in the VB Editor.
While the inbuilt settings work fine in most cases, let me still go through the options in this tab. When working with VBA in Excel, as soon as you make a syntax error, you will be greeted by a pop-up dialog box with some description about the error.
Something as shown below:. If you disable this option, this pop-up box will not appear even when you make a syntax error. However, there would be a change in color in the code text to indicate that there is an error. As you get more experienced with coding, you may start finding these pop-up boxes irritating, and then you can disable this option. So when I try to run the code, it shows an error. This option is quite useful when you have a lot of variables.
It often helps me find misspelled variables names as they are considered as undeclared and an error is shown. For example, if I want to delete a worksheet Sheet1 , I need to use the line Sheet1. While I am typing the code, as soon as I type the dot, it will show me all the methods and properties associated with the Worksheet object as shown below. When you type a function in Excel worksheet, it shows you some information about the function — such as the arguments it takes.
Similarly, when you type a function in VBA, it shows you some information as shown below. But for that to happen, you need to make sure the Auto Quick Info option is enabled which it is by default. I find it quite useful when debugging the code or going through the code line by line which has loops in it.
In the above example, as soon as I put the cursor over the variable var , it shows the value it holds.
0コメント