7 Essential Excel VBA Beginner Techniques
GET YOUR VBA CAREER STARTED WITH THESE 7 ESSENTIAL BEGINNER VBA TECHNIQUES
PART 1 – Introduction to 7 Essential Excel VBA Techniques
Tired of manual, time-consuming work in Excel? So are we! With our Excel VBA Beginner techniques, you can get this kind of task done at the click of a button. Yes, some learning is required; but with the video resources and below download file, you are all set. Remember to work along with Chris so you internalise the skill yourself. With that said, let’s get into it!
1. Change Properties
Excel consists of ‘objects’ which are the things you can see on the screen – cells, worksheets, workbooks, charts, shapes etc. Objects have properties which control their appearance and behavior – manipulating objects and properties is an essential Excel VBA Beginner technique, so let’s give it a try!
Excel consists of ‘objects’ which are the things you can see on the screen – cells, worksheets, workbooks, charts, shapes etc. Objects have properties which control their appearance and behavior – manipulating objects and properties is an essential Excel VBA Beginner technique, so let’s give it a try!
Let’s change the number in cell C3 to ‘1’; or, we could say let’s change the value property of the cell object to 1. A simple piece of VBA syntax allows us to do this, as Chris demonstrates in the video. Make sure you download the file and work along yourself, to practise the skill, and feel the excitement!
Other properties of objects that are commonly used:
The colour of a cell
The name of a worksheet
The series range of a chart
The colour of a cell
The name of a worksheet
The series range of a chart
Well done – you’ve now manipulated the property of an object, and you are programming. But, this particular operation is not particularly exciting, so let’s look at the objects and properties that will help us to complete this task.
2. Use the Macro Recorder
Suppose I asked you to use VBA to change the colour of a cell – how would you do that? It’s likely that you don’t know the syntax; so, what should you do? Excel provides a fantastic capability to record code; yes – we can do some stuff in Excel and the VBA Editor ‘writes down’ the corresponding code. Not only is this incredibly cool (right?!), it is an essential Excel VBA beginner technique.
Suppose I asked you to use VBA to change the colour of a cell – how would you do that? It’s likely that you don’t know the syntax; so, what should you do? Excel provides a fantastic capability to record code; yes – we can do some stuff in Excel and the VBA Editor ‘writes down’ the corresponding code. Not only is this incredibly cool (right?!), it is an essential Excel VBA beginner technique.
Let’s see the macro recorder in action. Chris shows how to start the macro recorder in the video; once the recorder is started, change the colour of a cell in the spreadsheet to your preferred colour. Then, stop the macro recorder as Chris does in the video.
Now we can view the recorded code in the VBA editor. Not only does this allow us to understand the syntax better, we can also reuse this code to get things done. This is why the macro recorder is an essential Excel VBA beginner technique.
3. Control Position
Changing the properties (eg. the background colour) of the selected cell is cool, but things get much more exciting when we learn how to control position. This essential VBA technique allows us to change cells anywhere in the spreadsheet file; as we will see, it combines powerfully with variables and conditional statements.
Changing the properties (eg. the background colour) of the selected cell is cool, but things get much more exciting when we learn how to control position. This essential VBA technique allows us to change cells anywhere in the spreadsheet file; as we will see, it combines powerfully with variables and conditional statements.
Follow Chris’s demonstration in the video and learn how you can use the .Offset method to reference cells a number of rows (the first number) and columns (the second number) away from the selected cell. A key skill here is ‘play’; yes, VBA coding is fun – so make sure you try your own combinations! Don’t worry if you get an error, simply reset the VBA editor and keep going.
4. Message Boxes
Yes, all this technical stuff is great, but if your user cannot make sense of it, it means nothing. Message boxes are the foundation of good communication with the user; that’s why they are our fourth essential Excel VBA Beginner technique.
Yes, all this technical stuff is great, but if your user cannot make sense of it, it means nothing. Message boxes are the foundation of good communication with the user; that’s why they are our fourth essential Excel VBA Beginner technique.
Follow along with Chris’s demonstration. First, we create a simple message box; then, we show how a message box can be used to externalize some useful information – in this case, the time and the date. Cool! We will look at more sophisticated applications of message boxes in the next application video.
5. Variables
Ah, variables – so powerful, and so often misunderstood. How should Excel VBA Beginners think of variables?
Ah, variables – so powerful, and so often misunderstood. How should Excel VBA Beginners think of variables?
It’s very simple, a variable is a place to store information. Similar to a cell in the spreadsheet, you might say, with some critical differences: first, a variable is not ‘visible’ and lives in the Excel VBA editor; this means we can use variables to store and manipulate information away from the user, and control macro programming. This makes variables a crucial Excel VBA beginner technique.
Let’s just get used to the idea of storing a number in a variable first. Follow along with Chris’ demonstration in the video and notice how we can quickly externalise the value in a variable using a message box – more on them later!
6. Conditional statement
OK, are you ready for a step up in difficulty? It might require some more concentration, but I promise it will be worth it. A conditional statement sends Excel VBA one of two or more ways, according to ‘conditions’ – whether something is happening (or not) in the spreadsheet.
OK, are you ready for a step up in difficulty? It might require some more concentration, but I promise it will be worth it. A conditional statement sends Excel VBA one of two or more ways, according to ‘conditions’ – whether something is happening (or not) in the spreadsheet.
Let’s combine the conditional statement with the message box to see it in action. Suppose we want Excel to tell us if a cell contains a value or not (yes, this is not particularly useful, but bear with me!) A simple IF, ELSE, END IF conditional statement allows us to get the job done.
For additional credit, try combining .offset into this routine to tell the user if the cell above the selected cell contains a value or not. You are beginning to feel the synergy of multiple VBA techniques combining together. And it’s about to get a lot more interesting as we introduce the most powerful Excel VBA beginner technique.
7. For Next Loop
Like what we’ve done so far? This one is next level. A loop allows us to repeat a certain action. Combined with position control and a conditional statement, we can loop through rows in a column and colour the cells according to the dates in the cell.
Like what we’ve done so far? This one is next level. A loop allows us to repeat a certain action. Combined with position control and a conditional statement, we can loop through rows in a column and colour the cells according to the dates in the cell.
Let’s first look at a simple application of a loop. Once again, we can use a message box to better understand what is going. Then, let’s try integrating the offset method to give us a sense of position control. Next, let’s build in an integer variable; combined with offset, the loop, and our original ‘recorded’ code to change the cell property (eg. the cell colour) a powerful mechanism is created that gets the job done at the click of a button. Awesome!
You have now applied seven essential VBA Beginner techniques – well done! In the next video, we look at how to apply these techniques in combination to a get a typical time-consuming Excel task done at the click of a button. See you in the next video!
PART 2 – Planning and Conceptualisation
Planning?! But we want to code! Yes, this is topic can appear boring, but proper planning and conceptualisation really is the key to effective coding.
Only by being deliberate about how we build a macro can we be sure to avoid technical problems later. That’s the thing: when people complain about VBA syntax, the problem is usually that they do not have a clear idea in their own heads about how the code should work. Clear conceptual thinking not only helps unleash your potential as a programmer, but it also keeps stress levels down!
After a ‘warm-up’ exercise to revise the concepts and get us thinking conceptually (make sure you complete the exercise too!) Chris demonstrates the process of planning and conceptualising a macro. By applying the concepts one-by-one to the real-world problem, Chris creates the shape of the routine using ‘comments’ (text in green that Excel ignores) in the VBA Editor. This vastly simplifies a complicated task: next we can translate these comments into VBA syntax; then, we will be close to a powerful macro to automate a piece of time-consuming work.
What did you make of the planning and conceptualisation video? Did you manage to complete the first exercise too? Let Chris know in the YouTube comments, he will get back to you. See you in the next video!
PART 3 – EXECUTION
Finally – it’s time to build the code and execute the task. This won’t be easy, but the basic concepts and annotations in the VBA editor will certainly help us. Let’s get into it – don’t forget to download the Excel files and work along with Chris.