7 Essential Excel VBA Beginner 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!
Download file link
https://tinyurl.com/yxz5h7fa
Series Playlist
https://tinyurl.com/yy9kge63
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!
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!
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.
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.
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.
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.
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.
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? It’s very simple, a variable is a place to store information. 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
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.
7. For Next Loop
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! 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!
---------------------------------------------
🎥🎁YOUR EXCEL CHEATSHEET
🎁1 Page Summary Of What You Need To Know
https://tinyurl.com/yc7jlmbs
/ tigerspreadsheetsolutions
/ tigspreadsheets
http://tigerspreadsheetsolutions.co.uk
Daily video on Instagram
Watch video 7 Essential Excel VBA Beginner Techniques online without registration, duration hours minute second in high quality. This video was added by user Tiger Spreadsheet Solutions 01 January 1970, don't forget to share it with your friends and acquaintances, it has been viewed on our site 9,880 once and liked it 252 people.