Most Important Skill in Excel VBA

What’s the Most Important Skill in Excel VBA coding?

It might not be what you think.

VLOOKUP Problem Thumb

BY CHRIS MORTIMER

I have the privilege of teaching university students about Excel and VBA coding about one day a week, at a top-10 university here in the UK. I’ve been doing it for almost 10 years now, and I love it!
It’s usually fun and it’s energising for me to see others get excited about the potential of Excel VBA.

My approach to teaching the subject is simple: to trigger that initial curiosity and help them appreciate the possibilities; once these things happen, students tend to get very enthusiastic about VBA very quickly.

That’s as long as they have one critical skill. It has fascinated me over the years watching people grapple with coding for the first time. What I have found is that those from technical backgrounds are not always the best VBA coders (although they are usually pretty solid!) At the same time, the process throws up some surprise success stories: people from social science or arts backgrounds who excel at coding.

Why this discrepancy? One would expect Excel capability to be correlated to previous experience with other coding languages and logic-based subjects such as mathematics. Though this holds as a generally rule, there are enough notable exceptions to fuel other theories about coding success. So what quality do all successful coders share?

All successful coders have a set of attributes that you could bundle together and describe as ‘resilience’. Yes, you’ve got to be ‘resilient’! These attributes are not earned through study, in fact they are not technical at all. Everybody has them to a greater or lesser extent, and this article might motivate you to strengthen them. My dog has them! Let’s dig into what these attributes actually are…

First, you’ve got to react well when things go wrong. I’m talking about an initial reaction here. You can be sure of this: Excel VBA is going to throw up errors (particularly when you’re less experienced) that will confuse you, and how you react is important.

Naturally you might feel frustrated, tempted to close the laptop, or throw it out the window – I’ve been there! More importantly, you’re probably thinking things like ‘I’m just not a coder’ and ‘I don’t have the background to do this’. Though understandable, you have to avoid this reaction; to avoid it is to demonstrate resilience. So, rather than giving up, say to yourself ‘I can fix this’ (yes, say it out loud, I always talk to myself whilst programming!), then begin the process of debugging.

The ability to respond positively when everything goes wrong can sound like a big deal, but it is actually hard-wired into our psyche; it is just a case of reactivating this part of your brain. All of us have been through tough learning experiences: learning to walk, learning to ride a bike, learning an instrument, a sport, or something else. I compare VBA learning to these things because, for me, Excel is ‘play’; things are going to go wrong – but it doesn’t matter, we’ll fix it. Over time, try to assume this mindset, it might allow you to feel childlike enthusiasm for the topic (I know I do, can you tell? :-))

Second, you have to be able to ‘debug’. What does this mean in a practical sense? At the basic level, you need to be able to arrange the VBA editor and Excel alongside each other so that you can ‘step through’ the code and see what is happening in Excel. It always surprises me how few people can do this quickly – you do it instantaneously use the Windows key + left arrow shortcut on a Windows PC.

Next, you need to know what Excel VBA’s debugging aids are. You can find them at the top of the VBA editor in the ‘Debug’ menu. Excel VBA provides the helpful facility to ‘step through’ the code line by line (F8 key on the Windows PC.) You can also use breakpoints to stop a routine mid-execution and understand what is going on. Don’t forget you can hover the cursor over VBA variables to see what values they hold. Command of such techniques is critically important and you can see me use them in most of my videos.

In the fullness of time, you will begin to enjoy debugging because you know you will always fix it in the end, and some learning will result (it’s play!) This mindset, founded on a bundle of capabilities that I’m calling ‘resilience’, is one sign of a resilient programmer.

How else can you recognise a resilient programmer? Back to teaching, we offer ‘tutorial’ sessions for students where they work through problems with teachers on hand to help them. In the first few weeks, these sessions are quite chaotic because students ask questions all the time. Hands are going up everywhere! The reaction when things go wrong is to disengage and seek outside help.

Through the term, I gently and consistently reinforce messages about dealing with problems. Reacting correctly, using the debugging aids, developing the critical skill of resilience. This sidelines the teacher in the learning process, so why would I advocate this? As a teacher, is that not counter-intuitive? In my view, the best learning a teacher can offer a student is motivation to develop self-reliance and resilience. I want to feel, as a teacher, that I am gradually making myself redundant as students learn to deal with problems on their own.

Over time, the number of questions reduces, despite the fact the topics get more difficult. Then I know at least some students, equipped with right skill and mindset, are off and running on their programming journey…

Get on the Tiger mailing list (click left) for privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta

Contact us here.

This entry was posted in Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized. Bookmark the permalink. Comments are closed, but you can leave a trackback: Trackback URL.