1. The Default Layout (And Fixing It!)
When you open the VBA Editor, two panes are likely to be visible with a row of symbols and menus at the top. Let’s concentrate on the two panes on the left side of the main window first. At the top is the Project Explorer, and at the bottom is the Properties Window.

Let’s first deal with a crisis you are likely to encounter early in your VBA career. What to do when these essential panes simply do not appear? This certainly caused me frustration when I was getting started, and I still receive regular emails about it to this day! If you cannot see the Project Explorer or the Properties Window, go to the ‘View’ menu at the top of the VBA Editor, then click on the two windows in the menu. This should return the VBA Editor to our default view – phew!

2. The Project Explorer
The top-left pane is the ‘Project Explorer’. Here, useful Excel ‘objects’ are listed – the sheets in the workbook and the workbook itself. Modules are also listed here when they are created.

A tip – the details of ALL open workbooks appear here; make sure you do not confuse objects in different workbooks, as I have so many times in my career. You could be writing code with one file ‘open’, inadvertently adding it to a different file – which means the code can behave strangely and is easily lost. I recommend making sure all other workbooks are closed, certainly when getting started with Excel VBA; so, go ahead and close other workbooks now, if there are any open.

Code usually exists in modules (though there are exceptions), so let’s try creating one. To do this, click ‘Insert’ at the top and ‘Module’. If no modules are in the workbook already, a module will be created, along with a folder to store them in. You will also see a whole space open up on the right side of the window – this is our main coding area, our playground, our canvas 😊 We’ll move over there in a second!

3. The Properties Window
First, click on the module in the Project Explorer, and notice that a ‘property’ of the module appears in the Properties Window below (bottom-left corner of the VBA Editor.) Let’s try changing the name of the module; in technical language we are working with the name property of the module object.

4. Modules
Click in the name box and change the name of the module to ‘Our_Code’. We will work with a single module only today, but it helps to give modules meaningful names and to group similar routines in modules. I often have a module called ‘Navi’ in my files, for example, where all the macros to do with navigation are stored.

By click on Sheet1 or ThisWorkbook in the project explorer, you can see a better illustration of the value of the properties window. Click on Sheet1 and notice that sheet name (Name property) and visibility (Visible property) can be controlled here. Cool!

5. One File At A Time
Right, let’s go ahead and do some coding! Download the file that accompanies this video, and copy paste the code into the coding window. Notice that two files (two file names), and two sets of objects, are now visible in the Project Explorer window: we know this can confuse, so let’s close the download file now, to keep our stress levels down 😊

6. The Run (‘Play’) Button
The aim of this macro is to ‘draw’ a capital T (for ‘Tiger’!) by putting values in cells on the spreadsheet. Now, it’s time to ‘run’ the macro or execute the code. I like to say ‘play’ the code, however, because the VBA editor includes a ‘play’ icon at the top. Click inside the macro (under the first line ‘Sub …’ will be fine) and hit the play button. Oh no! We have a VBA error. Don’t worry, you’ll get used to these as you go through your career; in my view, a critical skill in VBA development is being able to respond to these properly. So, don’t lose heart, it’s all in the plan!

7. The List of Macros
Before we go into debugging, let’s look at another way to look through and run macros in the VBA editor. Click on the ‘Tools’ menu at the top, and then ‘Macros’. If the file contains more than one routine, you’ll see each listed here, but our file contains only one. Make sure the ‘Debug_This’ macro is highlighted then click ‘Run’. And there you go, the same error! Don’t worry, we were only practising different ways of triggering macros, and were expecting this. So, let’s fix it.

8. Break Mode
This time, hit ‘Debug’ on the error dialogue box. This puts the VBA Editor into what is known as ‘Break’ mode. This mode can be annoying, because as long as the VBA Editor is in break mode, we cannot run any macros. There are three ways to tell you are in break mode: first, code does not execute (though it is not always clear the code is not running); second, the word ‘[break]’ appears at the top of the VBA Editor; third, a line of code is usually highlighted. Yes, Excel is trying to help by highlighting where the error is. Cheers, Excel!

9. The Reset (‘Stop’) Button
Our job now is to ‘debug’ or to fix the code so that it works. At this point, let’s introduce some of the VBA Editor’s awesome debugging tools. First, click the ‘stop’ button (remember there is a ‘play’ button too!) at the top of the VBA Editor; this will reset the code and exit break mode.

10. Step Through Code
Let’s learn how to ‘step into’ the code. This allows us to execute each line individually; at the same time, we can look at the main Excel window and understand what is going on. This really is the best way to learn code, particularly if you like an intuitive ‘trial-and-error’ approach (which I personally recommend.) Click below the ‘Sub …’ line of code so that the cursor is flashing inside the routine; then head to the ‘Debug’ menu at the top and click ‘Step Into’.

11. The Yellow Line
Immediately you’ll notice that the first line of the macro is highlighted in yellow. Stepping into the code automatically puts the VBA Editor into break mode; then, it highlights a line of code. This code is about to be executed; the next time we hit ‘Step Into’, the highlighted code will run. This allows us to understand exactly where we are in the code, and the exact location of a problem. We’re now debugging!

Hit the F8 key on the Windows PC, or click ‘Step Into’ via the ‘Debug’ menu at the top of the VBA editor. Note how the VBA Editor ‘works through’ the code line-by-line, with the Excel window updating after each execution. This is the approach I use for debugging in my day-to-day work – give it a try!

12. Insert A Breakpoint
So, what is the code doing? As you hit the F8, you will notice the VBA Editor repeating a line of code. This is because a ‘loop’ is in place, which, as the name suggests, ‘loops’ through a line or lines of code a certain number of times. Suppose we don’t have time to ‘manually’ step through the loop using the F8 key and step into. Another option is to use a breakpoint – another one of the Excel VBA Editor’s powerful debugging tools. Let’s check it out.

If you look carefully at the coding window (the main window on the right side), you’ll notice a grey border that separates the coding window from the Project Explorer and Properties Window. Locate the line of code that reads ‘For Counter = -1 To -5 Step -1’, and position the cursor in the border alongside this line of code, then click the left mouse button.

If all has gone to plan, a red circle should appear. If so, you have successfully inserted a ‘breakpoint’; this means that the code stops or ‘breaks’ when it reaches the breakpoint, without executing the line of code next to the breakpoint. For us, it is a great way to run a macro ‘to a certain point’ that you wish to test. It is particularly useful when macros take some time to execute eg. if they contain loops – like our example, and the point we wish to test is later in the routine. Let’s give it a try.

Click the Reset (‘stop’) button at the top of the VBA Editor, then hit the Run (‘play’) button to execute the macro. You will notice that the code runs without an error until we reach the breakpoint, when the code stops. So, we know that the code above the breakpoint is robust – it does not cause an error. But what about the rest of the code? This approach allows us to home in on the source of the problem.

Now the code has reached the breakpoint, we are automatically in break mode. From here, we can step through the code once more to until we reach the VBA error we encountered earlier (remember, our mission here is to fix this error, and get the code working.) So, use the F8 key (Windows PC) or go through the Debug menu to ‘step through’ the code. Follow what is happening in the Excel window – can you see what the problem is going to be?

What should occur is runtime error 1004 which means that we are referencing something that, as far as the VBA Editor is concerned, does not exist! In this case, we are referencing a column outside of the spreadsheet, before column A; clearly this is not possible, which is why the error occurred. Can you see how using the tools in the VBA Editor allowed us to work through the code and identify the source of the problem? These techniques are so important in the bigger picture of your VBA skills development.

So, how might we fix the problem, do you have any ideas? VBA is reasonably user-friendly as a programming language; even without much coding experience, perhaps you can identify and fix the issue. As always, trial and error is the way forward – do not be scared of errors (make sure you save the file), and use the tools we have learned to work through the code, and see the result of the changes you make. Make tweaks within the framework of the existing syntax. It is normal to work incrementally towards a solution in this way – in fact this is critical skill in Excel VBA and computer coding more generally.

Try replacing this line of code:
Range(“D3”).Offset(0, Counter) = “O”
With this:
Range(“F3”).Offset(0, Counter) = “O”
Then reset and re-run the code.

The result should be a beautiful capital ‘T’ shape. Did you get it working? Good! You have now understood and debugged a problematic routine using the tools in the VBA Editor – well done!
Before we finish, let’s look at a couple of time-saving features in the VBA Editor.

13. Get The Value of a Variable with the Cursor
You might have noticed that our macro contains what is called a ‘variable’. A variable is simply a place to store information; in our case, the name of the variable is ‘Counter’ (though we could give it any name) and it combines with .offset (a method) to control the top of the ‘T’ shape. Variables are integral to computer programming and, correctly applied, facilitate powerful routines in Excel VBA. Let’s look briefly at how to work with them in the VBA Editor.

Let’s work through the code once more using Debug / Step Into. As you step through the code, hover the cursor over the Counter variable in the VBA Editor. Note how the value of the variable pops up (eg. Counter = 1); this is the easiest way to understand the value of a variable.

14. The Watch Window
If you want something more involved, you could use the ‘Watch Window’. Click the View menu at the top of the VBA Editor, and hit ‘Watch Window’; notice a new pane appears at the bottom of the VBA Editor. Right-click anywhere in this new pane, and click ‘Add Watch’. Then, type in ‘Counter’ (the spelling must be accurate) in the ‘Expression’ box, and hit ‘OK’. Note how you can track the value of the Counter variable in the Watch window. Cool! It’s a great tool for externalising information (such as values stored in variables) that is not immediately visible in the VBA Editor.

15. Option Explicit
A final point about variables: did you spot the very first line of code in the module? It reads ‘Option Explicit’ – what on earth does that mean? Option Explicit means we have to be explicit by declaring variables properly; if we use Option Explicit, Excel checks variable spelling before running any code. This has saved me huge amounts of time through my career, so I highly recommend using it.

16. Options Menu
Click ‘Tools’ at the top of the VBA Editor, and ‘Options’. There are plenty of interesting options here, including control of the appearance of text in the VBA Editor. We will just tick ‘Require Variable Declaration’; this means that, next time you create a module, ‘Option Explicit’ will automatically appear at the top, saving the need to type it in, and delivering the benefits described above. Recommended!

17. Find And Replace
Finally, let’s look at Find and Replace. This facility allows us to quickly substitute one piece of code for another within a routine, a module, or even the whole VBA project. Again, it’s a huge time-saver. Suppose we wish to change the name of the Counter variable. Yes, we could do this manually; but that might be time-consuming with a longer / more sophisticated routine. This is where Find and Replace can help.

Click the ‘Edit’ menu at the top and then ‘Replace’, or use the Ctrl + R shortcut on the Windows PC. Type in Counter in the first box, then an alternative name in the second (I’ll use Chris_Counter); then see how the VBA Editor changes the values for us, and even reports how many occurrences it found. Ah, another time-saver!