How to View the Visual Basic Editor
Before a Visual Basic for Applications (VBA) program can be written, you need to know where to put it. Within Microsoft Excel, VBA code is written in the Visual Basic Editor (VBE). You can access the VBE on the main Excel Ribbon Menu, or by hitting ALT+F11 on the keyboard.
The User Interface
The Visual Basic Editor may be a screen you’re unfamiliar with. The user interface is relatively simple and is split into 5 key sections:
Project Window: All VBA programs form part of a larger project which encompasses the parent Excel workbook, worksheets, charts etc. together with code modules which contain the actual VBA programs. The Project window is a visual overview of the structure of the project, allowing quick access to the objects and modules within it.
Code Window: This is the principle coding area. All files and modules that you open in the Visual Basic Editor appear in the code window. This is where you’ll likely spend a lifetime debugging VBA code!
Properties: Window: Some objects (shown in the project window) can have properties or attributes. If we ever need to set them, we can do this in the properties window.
Immediate Window: This is not enabled by default, but is very handy to switch on from the start. It enables more effective debugging. It can also be used to test code before introducing it into your VBA module. We’ll cover the many use-cases in depth in a later lesson.
Locals Window: As your VBA code is running, and you start changing properties or storing data in variables it’s sometime hard to keep track of what’s going on. The locals window enables your to view how the various objects you’re manipulating via code are being influenced. This will be very useful when debugging code and ensuring it’s doing what you think it is.
VBA Code Modules
As discussed above, the Project Window organizes our VBA work. Most VBA programs that you’ll create will be placed in a code module. Therefore, we should probably learn how to create them right?
- Right Click anywhere in the Project Window
- Then click Insert
- Lastly, click Module
Congratulations! You just created ‘Module1’, this is your first step into the depths of VBA. You should have seen a new blank Code Window pop up, this is where you’ll soon be writing your own VBA.
Each project can have as many modules as required. Consequently, more complex projects are broken down into many modules; whereas small projects can often be stored in a single module.
Can I Start Writing Some VBA Code Now?
There are a few more things we really need to learn before we start writing our own code… but seeing as we’ve found the Visual Basic Editor (World’s Worst Hide & Seek Contestant) using out handy keyboard shortcut (ALT+F11)…. why don’t we try writing our first program?
Now in the programming community, it is convention for your first code to be a ‘HELLO WORLD’ program (basically just a pop-up box that says ‘Hello World!’). However that’s pretty boring… and conventions are not legally enforceable. So try copy/pasting the code below into your Code Window instead and then hit F5 on your keyboard (shortcut to run your program)
'// Source: www.ExcelWTF.com
'// Purpose: My first VBA program
MsgBox "I can now claim to be a programmer on my CV! Release the Kraken!!!"
Woah! Woah! Keep your Kraken where it is… nobody want to see that. Don’t worry if you don’t understand the code or the syntax we just used, these will be covered in detail in later tutorials as part of this VBA course.
If every day is a gift… can someone tell me where I can return Mondays?!?!— Unknown