How to Open VBA in Excel and Use It - The very first thing you need to do in order to begin working with VBA in Excel is to become familiar with the Visual Basic Editor (also called the VBA Editor or VB Editor).
In this guide, I will teach you all there is to know about the VBA Editor, as well as several helpful options that you should be familiar with if you want to do any coding in Excel VBA.
What exactly is meant by the term "Visual Basic Editor" in Excel?
When you open a workbook in Excel, a separate application known as the Visual Basic Editor launches automatically. This editor is a component of Excel but operates independently. It is hidden by default, and in order to access it, you will need to enable it first.
The Visual Basic Editor is the location where the VB code is stored.
In the VB Editor, you can obtain the code in a number of different ways, including the following:
- When you record a macro in the Visual Basic Editor, it will immediately build a new module there and enter the code that you need into that module.
- In the VB editor, you have the option to manually input VB code.
- It is possible to copy code from one workbook to another, as well as from the internet, and then paste it into the VB Editor.
Launching the Visual Basic Editor
Excel provides a number of different entry points for the Visual Basic Editor, including the following:
- Utilizing a Shortcut on the Keyboard (easiest and fastest)
- Utilizing the Developer Tab in the browser.
- Utilizing the Tabs on the Worksheet
Let's make a speedy go of it and go through each of these.
Open the Visual Basic Editor using the Shortcut on the Keyboard
Utilizing the keyboard shortcut ALT + F11 is the quickest and most convenient way to launch the Visual Basic editor (hold the ALT key and press the F11 key).
As soon as you perform this action, a new window dedicated to the Visual Basic editor will immediately pop up.
This keyboard shortcut functions as a toggle, so each time you use it, it will bring you back to the Excel application (without closing the VB Editor).
For the Mac version, the keyboard shortcut is Opt + F11 or Fn + Opt + F11.
Using the Developer Tab
Utilizing the Developer Tab In order to use the Visual Basic Editor from the ribbon, you will need to:
- Click on the tab labeled Developer.
- In the Code group, click on Visual Basic.
Utilizing the Tab Labeled "Worksheet"
This is a method for opening the Vb Editor that is used much less frequently.
Right-click on any of the worksheet tabs, and then select the 'View Code' option from the context menu.
This technique will not only open the Visual Basic Editor (VB Editor), but it will also transport you to the code window for the worksheet object that you specified.
When you wish to develop code that is customized to a certain worksheet, you can leverage this to your advantage. When it comes to worksheet events, this is almost always the case.
Anatomi Editor Visual Basic di Excel
It's possible that the very first time you open the VB Editor it will appear to be quite complicated.
There are a variety of options and divisions that, at first glance, may appear to be totally foreign.
Additionally, it retains the appearance of Excel 97 from years ago. The VB Editor's user interface has not seen any changes over the years, despite the fact that Excel's design and usability have vastly improved over that time period.
In this portion of the tutorial, I will walk you through the many components that make up the Visual Basic Editor application.
Note: When I first started using VBA many years ago, I was very confused by all of the new windows and options that were available to me. However, as your familiarity with dealing with VBA increases, you will become more at ease with most of these. In addition, the vast majority of the time, you won't be necessary to make use of all of the available choices; rather, just a few of them will do.
The many parts of the VB Editor are depicted in the graphic that can be found below. The sections that follow in this tutorial will go on to provide a more in-depth explanation of these topics.
Now, before we go too far ahead of ourselves, let's quickly go through each of these components and understand what each one does:
Menu Bar
In this section of the VB Editor, you will find all of the choices that are available to you for use. It functions in a manner that is analogous to the Excel ribbon, in which you have tabs and options associated with each tab.
Simply clicking on the various components of the menu will allow you to investigate the various possibilities.
You will discover that the majority of the VB Editor's options have a list of keyboard shortcuts right next to the option. Working with the VB Editor will become much simpler as you become accustomed to using a select few keyboard shortcuts.
Bilah Alat
The VB Editor comes equipped with a toolbar that, by default, has a number of helpful features that you are most likely to require on a regular basis. This operates in exactly the same manner as the Quick Access Toolbar in Excel. It grants you immediate access to a number of the helpful settings and features.
You have the ability to make some adjustments to its appearance by removing or adding certain options (by clicking on the small downward pointing arrow at the end of the toolbar).
When working with the VB Editor, the majority of the time, all you will require is the standard toolbar.
By clicking on the three gray dots that are located at the beginning of the toolbar and dragging it over the menu bar, you will be able to relocate the toolbar so that it is located above the menu bar.
Note: The VB Editor has four different toolbars: the Standard toolbar, the Debug toolbar, the Edit toolbar, and the User form toolbar. The conventional toolbar is depicted in the image that can be found above, and it is also the one that is used by default. You can access other toolbars by heading to the View option and hovering the cursor over the Toolbars option. This will give you access to those toolbars. If you so choose, you are able to incorporate any number of toolbars into the VB Editor.
Project Explorer
The Project Explorer is a window that is located on the left side of Excel and displays all of the items that are currently open in the program.
When you're using Excel, any workbook or add-in that's open counts as a project that you're working on. In addition, each of these initiatives may include a curated selection of physical artifacts.
For illustration purposes, the figure below demonstrates how the Project Explorer displays the two open workbooks (Book1 and Book2) together with the items contained in each of those workbooks (worksheets, ThisWorkbook, and Module in Book1).
You can collapse the list of things by clicking the + sign to the left of the objects, or you can expand it to show the full list of objects by clicking the plus sign again.
The Project Explorer may contain any of the following objects at your discretion:
1. All of the open Workbooks - you are able to have the following objects within each workbook, which is also referred to as a project:
- Object of type worksheet for each and every worksheet contained in the workbook
- The object known as ThisWorkbook, which represents the workbook by itself
- object of type chartsheet for each each chart sheet (these are not as common as worksheets)
- Modules are the storage location for the code that was generated by a macro recorder. You are also able to write VBA code here, or copy and paste it.
2. All open Add-ins
Think of the Project Explorer as a location that lists all the objects that are currently active in Excel at the time in question.
Control and R are the shortcut keys on the keyboard that open the Project Explorer (hold the control key and then press R). To close the Project Explorer window, simply click the close symbol located in the top right corner of the window.
Take note that the Project Explorer contains, for each individual object, a code window in which you can type the object's code (or copy and paste it from somewhere). When you do a double click on the object, the code window will open up for you.
Read Also: 250+ Motivational Quotes to Help Reach Your Full Potential Every Day
Properties Window
The Properties window is where you will be able to view the various attributes of the object that is now selected. You can open the Properties window by pressing the F4 shortcut on your keyboard if you do not already have it open (or go to the View tab and click Properties window).
Within the VB Editor, the Properties window appears as a floating window that you have the option to dock. In the following illustration, I have docked it directly underneath the Project Explorer.
The Properties window gives us the ability to change the properties of an item that has been selected. By way of illustration, if I wish to hide a worksheet (or make it very hidden), I can accomplish this by modifying the Visible Property of the worksheet object that is now chosen.
Code Window
Each object that is listed in the Project Explorer has a corresponding code window that may be opened. Simply double-clicking on an object in the Project Explorer section will bring up its associated code window for you to edit.
The code window is where you will either type in your own code or copy and paste code from another location.
The code for a recorded macro is placed in the code window of a module whenever one records a macro. When you record a macro in Excel, the program will automatically insert a module and place the code in that module.
Immediate Window
When debugging code, the Immediate window is typically where one will focus their attention. One of the ways I make advantage of the Immediate window is by including a statement that uses Print.Debug within the code and then running that code.
It allows me to debug the code and figure out where the problem is in my programming. If the output of Print.Debug appears in the immediate window, I am certain that the code was executed correctly up until that point.
If you are new to coding in VBA, it is possible that it will take some time for you to become proficient enough to use the immediate window for debugging.
Within the VB Editor, the immediate window does not appear to be visible by default. You can obtain it by using the keyboard shortcut Control + G (alternatively, you can go to the View tab and click on the "Immediate Window" button).
Where in the VB Editor to Insert New Lines of Code
I really hope that you now have a fundamental comprehension of what VB Editor is and what all of its components are.
I will demonstrate how to insert a VBA code into the Visual Basic Editor in the following section of this tutorial.
Excel provides you with two different locations in which you can insert the VBA code:
- The space in which the object's code is shown. These items could be anything from a workbook to a worksheet to a User Form and beyond.
- The window that displays the module's source code.
Comparing the Object Code Window and the Module Code Window
It is important to note that adding code to a module is not the same as adding code to the object code window. Allow me to explain the distinction between the two.
When you add a piece of code to any of the objects, that code will not be triggered unless that particular object first completes a certain action. If you want to unhide every worksheet in a workbook as soon as you open that workbook, for instance, the necessary code should be placed in the ThisWorkbook object (which represents the workbook).
In this scenario, opening the workbook is the event that sets off the chain of events.
In a same fashion, if you want to secure a worksheet as soon as another worksheet is enabled, the code for that would go into the worksheet code window.
These triggers are referred to as events, and you can associate a piece of code to be carried out whenever an event takes place.
On the other hand, the code contained in the module has to be run either manually or automatically (or it can be called from other subroutines as well).
Excel will construct a module for you automatically and place the recorded macro code within it whenever you record a macro in the program. If you need to run this code now, you will need to manually run the macro.
Including Code Created in VBA in Module
While the act of recording a macro does in fact result in the creation of a module and the incorporation of the code into it, using a macro recorder does come with a few restrictions. For instance, it is unable to make use of loops or conditions based on If Then Else.
In situations like these, you should manually copy and paste the code or write the code yourself, whichever option is more convenient for you.
A module can be used to hold the following types of VBA codes:
- Declarations: You can declare variables in a module. When you declare variables, you get the ability to describe what kinds of data each variable can store. You have the option of declaring a variable only for a specific subroutine or for all of the subroutines in the module (or all modules)
- Procedures, also known as Subroutines: This is the code that contains the steps that you want to have VBA carry out.
- Function Procedures are a type of code that returns a single value and can be used to develop functions that are unique to your application (also called User Defined Functions or UDFs in VBA)
A module is not included in the workbook by its default settings. Before you can use it, you will first need to insert it.
Incorporating a Module into the Visual Basic Editor
The following is a list of the steps required to add a module:
1. Use the right mouse button to select any part of the workbook (in which you want the module).
2. Move the cursor to the Insert option and hover it there.
3. To access the Module menu, click here.
This would result in the immediate creation of a folder titled Module as well as the insertion of an object titled Module 1. Following the instructions above will cause another module to be inserted even if you already have one inserted.
After the module has been placed, you can open its code window by doing a double click on the module object in the Project Explorer. This will bring up the window where the module's code is shown.
At this point, you have the option of manually writing the code or copying and pasting it.
Removing the Module
The following steps must be taken in order to delete a module in Excel VBA:
1. Use the right mouse button to select the module that you want to delete.
2. Select the Remove Module option from the menu.
3. When the dialogue box appears, select "No" from the menu.
Take note that you have the option to export a module before actually uninstalling it. It will be stored as a.bas file, and you will be able to use it in future projects if you so choose. To export a module, right-click on the module, and then click on the "Export file" option that appears in the context menu.
Incorporating New Code into the Object Code Window
Simply double-clicking on an object will bring up the code window for that object.
After it has opened, you will have the option to manually enter the code or to copy and paste it from one of the other modules or from the internet.
You should be aware that some of the objects give you the option of selecting the event for which you want to write the code.
For instance, if you want to write code that causes something to occur whenever the selection in a worksheet is changed, you must first select worksheets from the drop-down menu located at the top left of the code window, and then select the change event from the drop-down menu located on the right. Only after doing so will the code window allow you to continue.
It should be noted that these occurrences are unique to the object. You will be able to view the events that are associated with the workbook object whenever you open the code window for a workbook. When the code window for a worksheet is opened, you will be able to view the events that are associated with the worksheet object.
Modifying Settings in the VB Editor
You are able to further adjust the interface as well as some of the features of the Visual Basic Editor, despite the fact that the default settings of the Visual Basic Editor are sufficient for the majority of users.
When it comes to personalizing the VB Editor, you have a lot of options, which I will walk you through in this part of the course.
You can personalize the VB Editor environment by selecting Tools from the menu bar, and then selecting Options from the submenu that appears.
This will cause the Choices dialog box to popup, which will provide you with all of the available customization options for the VB Editor. The 'Settings' dialog box features four tabs, each of which has a different set of customization options for the Visual Basic Editor (as illustrated in the following image).
Let's do a speedy tour of all of these tabs and the critical settings contained within each one.
Editor Tab
Even if the default settings perform adequately in the vast majority of circumstances, allow me to walk you through the available customizations on this page.
As your expertise with working with VBA in Excel grows, you might find that you wish to personalize the VB Editor by making use of some of these settings.
Auto Syntax Check
A pop-up dialog box will appear if you create a syntax error while working with VBA in Excel. This will happen as soon as the error is made (with some description about the error). Something along these lines, as depicted below:
If you choose to disable this option, the pop-up box that displays when you commit a syntax error will not appear at all. On the other hand, if there was a mistake, the color of the code text would shift to show this fact.
It is highly recommended that you keep this option enabled if you are just starting off. You might start to find these pop-up boxes annoying as you get more skill with coding; if so, you can turn off this option after you've reached that point.
Require Variable Declaration
Enabling this option is one that comes highly recommended by me.
Working with Visual Basic for Applications (VBA) requires the use of variables, which may store a variety of data types and objects.
When you check this box, a statement labeled "Option Explicit" is added to the very top of the code window. This happens whenever you enable this option. This compel you to define each and every variable that you use in the code that you're writing. If you try to run the code without declaring a variable first, it will give you an error message (as shown below).
In the example that was just presented, I made use of the variable Var without first declaring it. Therefore, an error message appears whenever I attempt to run the code.
When you have a large number of variables, using this option can be quite helpful. It has assisted me in locating misspelled variable names on numerous occasions due to the fact that they are considered undeclared and an error is displayed.
Note that activating this option will not have any effect on the modules that are already installed.
Auto List Member
This option is quite helpful since it allows you to obtain a list of the properties and methods that are associated with an object.
If I want to get rid of a worksheet called "Sheet1," for instance, I have to utilize the line that says "Sheet1.Delete."
It will show me all of the methods and properties that are associated with the Worksheet object as soon as I type the dot while I am typing the code (as shown below).
The Auto list feature is wonderful because it enables you to do the following:
- Saves time by allowing for the quick selection of the method and property from the list.
- Displays all of the features and functions, some of which you might not be familiar with.
- Helps you avoid making spelling mistakes.
This setting is already enabled when you create a new profile, and I suggest you keep it that way.
Auto Quick Info Options
When you put a function into an Excel worksheet, the spreadsheet will display some information about the function, including the arguments that it requires.
In a similar manner, when you type in a function in VBA, it displays some information to you (as shown below). However, in order for this to take place, you need to ensure that the Auto Quick Info option is turned on (which it is by default).
Auto Data Tips Choices Available
If you are going through your code line by line and position your cursor above the name of a variable, it will show you the value of the variable at that point in the process.
When I am debugging the code or working my way through it line by line, which includes loops, I find that this feature is incredibly helpful.
In the preceding illustration, when I move the cursor over the variable (var), it displays the value that the variable now stores.
It is recommended that you keep this option enabled by default as it is the default setting.
Auto Indent
The use of indentation makes the code easier to read because it is possible for VBA codes to become lengthy and cluttered.
When you are creating code, you can use the tab key to create an indentation.
If you select this option, when you reach the end of a line that has been indented and hit the enter key, the line that follows won't start from the beginning but will instead have the same level of indentation as the line that came before it.
In the example that was just presented, once I have finished writing the Debug.Print line and have pushed enter, it will begin directly below it (with the same indentation level).
Because I find this option to be helpful, turning it off would require me to manually indent each line of a block of code that I want to have indented.
If you want like, you can alter the value of the indentation. I maintain it at the default value.
Editing of Text Via Drag and Drop
When this option is turned on, you will have the ability to pick a section of code and then move it using drag and drop.
Because you won't have to first cut it and then paste it, this will save you time. You need only make the selection and then move it.
It is recommended that you keep this option enabled by default as it is the default setting.
Default to Full Module View
When this option is turned on, you will be able to view all of the procedures that are contained within a module in a single list that can be scrolled through.
If you turn off this option, you will be unable to view more than one module at a time in the interface. You will need to use the drop-down menu that is located in the upper right-hand corner of the code window to select the module that you wish to view.
This setting is already activated when you create a new profile, and I suggest you maintain it that way.
If you have numerous procedures that are enormous and scrolling over all of them takes a long time, or if you have a lot of procedures and you want to find the one you need fast rather than waste time scrolling, one of the reasons you might want to disable it is so that you can save time.
Procedure Separator
When this option is activated, you will notice that there is a line (essentially a partition) between the two processes.
This is helpful to me since it demonstrates in a visual way when one operation finishes and the next one begins.
It is turned on by default, and my recommendation is that you leave it that way.
Tab for the Editor's Format
You are able to personalize the presentation of your code in the code window by using the settings available in the Editor Format tab.
For my own purposes, I do not change any of the default settings because I am content with how things are. You are free to adjust this to suit your needs and tastes, if you so desire.
In order to make a modification, you will first need to make a selection in the Code Colors box. After you have decided which choice to go with, you will have the opportunity to change the color of the indication, as well as the foreground and backdrop of the selection.
This option also allows you to change the font type as well as the size of the font. Because it makes the code easier to read, you should use a typeface with a set width, such as Courier New. This is suggested.
It is important to take note that the font type and size setting will not change for any of the code kinds (i.e., all the code types shown in the code color box).
In the graphic that follows, I have chosen Breakpoint so that I may demonstrate how the formatting can be modified.
Please take note that when the Margin Indicator Bar option is selected, a little margin bar will appear to the left of the code. It is useful because, when the code is being executed, it displays helpful indicators. When you establish a breakpoint, as demonstrated in the illustration to the right, a crimson dot will immediately appear in the margin bar to the left of the line. You also have the option of setting a breakpoint by simply clicking on the margin bar to the left of the code line you wish to use as the breakpoint. This will accomplish the same thing.
Read Also: 90+ BEST WAYS TO MAKE MONEY FROM HOME
The Margin Indicator Bar is turned on by default, and it is my recommendation that you leave it that way.
Even though she was colorblind, one of the students who took my VBA course found these customization choices to be helpful. With the help of these settings, she was able to customize the color and format in a way that made working with VBA much simpler for her.
General Tab
The General tab provides a wide variety of customization choices, but none of them require your attention at this time.
I strongly suggest that you do not change any of the available options.
Error Handling is a crucial component of this tab's settings, so be sure to familiarize yourself with it.
The option to "Break on Unhandled Errors" is selected by default, and it is my recommendation that you leave it in that state.
If you select this option, it means that if your code runs into an issue and you have not yet accounted for that error in your code, then it will break and stop running. However, if you have already dealt with the problem (for example, by making use of the On Error Resume Next or On Error Goto options), then it will not break (as the errors are not unhandled).
Docking Tab
You can choose which windows you want to dock by making your selections in this tab.
Docking allows you to fix the location of a window (such as the project explorer or the Properties window), which prevents the window from moving around and allows you to view all of the different windows at the same time.
If you choose not to dock your windows, you will only be able to view a single window in full-screen mode at a time before being forced to switch to the other window.
Maintaining the settings in their default state is my recommendation.
I hope this article How to Open VBA in Excel and Use It is useful
Source: https://trumpexcel.com
