If you’re looking to hire someone and want to know whether they know how to code in Excel using Visual Basic (VBA), it’s not too hard of a task to quickly evaluate whether they know it or not. The first step is to ask them to send you a sample of something that they’ve done that includes coding and that is not password protected.
Then you’ll want to test to see what it does. So you’ll want to ask how it works so you can see for yourself. If that code works and the macro does what it’s supposed to do, you might be thinking that will be enough. However, someone could simply use a macro recorder to try and generate the code. This is not the same as coding and anyone can do this with no knowledge of code whatsoever. But there’s an easy way to uncover this.
Finding the code
In the file that someone’s sent you, hit ALT+F11. This will send you into Excel’s backend and open up VBA. You should see something like this on the left-hand side:
Double click on each of those items – sheets, workbook, and any modules. Code can reside in any and all of those areas so you might need to cycle through to see where it is.
Once you find the code, that’s when you can start evaluating it.
Reviewing the code
Below, I’ll show you the same macro, how it might look in VBA compared to how it looks using the macro recorder:
There are three things that should be clear from comparing the two examples above, which will help to identify whether someone’s just using the macro recorder or whether they’re actually coding properly using VBA.
1. Organization and spacing.
The macro recorder doesn’t care for spacing out the code and each line of code will come after the other. Especially when you’re looking at longer lines of code, it’ll get real messy real quick. Organization is important because if it looks like one big block of text it’s going to make it very difficult to audit or review later should you want to make changes.
2. No comments.
In the first example, there were lines in green that started with an apostrophe, called comments. They are optional but it ties back to the organization and putting notes along the way to help remind you what you were trying to do. It doesn’t have to accompany every line, but if you don’t see any comments at all, it could be a hint that someone just used a recorder. For a quick macro that’s only a couple lines long it probably wouldn’t be necessary, but for a lot of code you would certainly expect to see at least some comments.
In the second example, you’ll notice .select showing up multiples times. This makes it obvious that someone’s used the macro recorder. If you want to insert a column or bold it, you can just code it right away, you wouldn’t need to actually select it and then make the change. The macro recorder, however, records everything, including those selections. So seeing this should tell you right away that someone’s just used a recorder rather than coding it themselves.
There are other ways you could see whether the macro recorder was used or not but these three should suffice in helping you identify whether someone knows how to code or not.
Why does this matter?
If someone knows how to use the macro recorder, that’s good, but it’s not knowing how to code. The problem is that the macro recorder could do a small fraction of what is possible through actual coding. Coding through VBA opens up a lot more opportunities for automation and improving a spreadsheet. A macro recorder can be used by anyone but it lacks the sophistication to build much logic into it.