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:
https://tinyurl.com/y3qhg4j3
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
https://tinyurl.com/y6jtnlk2
Follow Tiger on Facebook:
/ tigerspreadsheetsolutions
Смотрите видео What is an Excel VBA Variable? онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Tiger Spreadsheet Solutions 13 Сентябрь 2018, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 2,310 раз и оно понравилось 60 людям.