What is an Excel VBA Variable?

In this beginner Excel VBA lecture, Chris explores an instructive example of the power of Excel VBA. Download the Excel file and work along with Chris.

Download file link:

BY CHRIS MORTIMER

From time to time I come across an application of Excel VBA that perfectly sums up our message: that a bundle of critical VBA techniques, applied correctly, can transform the way you work. Yes, transform the way you work. We are not talking about saving a few minutes here and there, we are talking about hours and days saved, and a shift in mindset towards data and analytical work in general.

It's a message I try to communicate in my work and on the YouTube channel; but, to be honest, I don't feel that people often understand the potential impact of Excel VBA. If they did, they would be as excited as I am about the applications! No matter. All I can do is keep presenting examples of applications, and chipping away at popular misconceptions such as 'code is for coders!'

Today's example comes from a real-life Excel development project focused on an HR issue. Yes, as with most of the examples on the YouTube channel, this application is based very closely on a real-life example from my own work.

The Excel VBA I created converts time in hours and minutes to a decimal figure, in order to provide accurate aggregates of hours worked. For example, a four-hour-fifteen-minute shift would previously display as 4.15; when 'decimalised', this translates (inaccurately) to 15% of one unit. We know that there are 60 minutes in an hour; so, in this case, the decimalised value should equal 4.25. In the same way, 4.30 should be converted to 4.50, and 4.45 to 4.75. It's a potentially time-consuming task that can frustrate; with the right knowledge, it can be quickly automated using Excel VBA.

A SYSTEM IS THE PRODUCT OF ITS INTERACTIONS

What techniques are required? Fans of the channel will be familiar with those used: (different types of) variables, loops and a conditional statement. It is the power of the techniques working together that I wish to emphasise. To do so, let's use the concept of 'cool points'. We have all seen, and felt 'cool' things in Excel. Remember when you first applied VLOOKUP, for example? VLOOKUP scores cool points! (Note: I have made up the concept of 'cool points' to try to illustrate this point, they cannot be found in the VBA editor!)

Let's arbitrarily assign 7 cool points to loops and 8 cool points to conditional statements. So - when used in concert, how many 'cool points' are generated? 15? No!

Many more cool points are generated; in fact, the coolness of VBA techniques combined together is on a higher order of magnitude. To paraphrase the great management scientist Russell Ackoff - a system is not the sum of its parts, it is the product of its interactions. So, the above combination would be worth (7 x 8) 56 cool points. My argument is that things get a lot more exciting as you combine techniques together. Things move to another level. I have seen the reaction again and again in people I work with: 'Cooooooooool!'

Download the example file, work along yourself, and feel the power of Excel VBA in action. Can you see potential time-saving applications in your work? And, how many 'cool points' would you give this application? :-)

Get on the Tiger mailing list 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:

[henrikijonkoping]

Tiger is brilliant! When I decide that Excel VBA will be a critical part of my future skills, I know who to learn from

[maurocastagnera]

Hi Chris! It's a pleasure to see you again. Your videos are always interesting and clear. Cheers 😊

[praveene]

Great video. But please can u do a practical about why and where we use integer, long, string etc., I was new for VBA.

[ruleoflaw]

Thanks teacher for your useful explanations
I have a problem to reuse my Excel VBA folder, it tells me that the folder is secured,
I want to you explain me how to take off the security so i can modify codes after saving