Author Archives: Chris Mortimer

Merry Christmas! Excel VBA Advent Calendar

Merry Christmas! Let’s build an advent calendar using Excel VBA…

Click here to download the starter file used in the video

Click here to download the completed file (version 1)

Click here to download the completed file (version 2)

VLOOKUP Problem Thumb

BY CHRIS MORTIMER

Need to look seasonal but don’t wish to invest in a ‘physical’ advent calendar?

Want to take your VBA coding to the next level?

If either (or both!) apply, this full Excel VBA lecture could be for you.

Our Excel VBA advent calendar dispenses sweetness in the form of words, not chocolate (so it is healthy too!) It takes lyrics from the popular Christmas tune ‘Stay Another Day’ by UK pop group ‘East 17’, and presents a lyric (or some lyrics) from the song when the user clicks on a day icon. This song is Chris’ personal favourite, but you could substitute lyrics from any other song.

The calendar comprises basic and advanced functionality. In basic mode, the calendar displays a single lyric; in ‘multi’ mode, the calendar displays all lyrics up to the day clicked. And there is more. A validation control checks to see if the user is attempting to ‘open’ a day too early! We grant the user the option to switch off this control to allow for glorious ‘full song’ lyric viewing from the beginning of December. Well, it is the season of good will…

Seriously, this kind of creative application provides a perfect learning space away from the pressures of deadlines, customer expectations and file specifications. In the video, I explain how to apply variables, loops, conditional statements and more to get this job done. You’ll see me get into a coding tangle on more than one occasion, and work around the issues using VBA’s debugging aids.

I hope this video is a fun diversion from more serious matters, or another step in your Excel learning journey. Either way, me and the team at Tiger wish you a Merry Christmas. I hope you’ll be back with us in 2019 for more Excel fun.

Take care,
Chris

Get on the Tiger mailing list (click left) 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:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta

Contact us here.

Posted in Advanced Articles, Beginner Articles, Excel Guidance Articles, Intermediate Articles, Uncategorized | Comments closed

VLOOKUP Formula Problems / Fixes

Let’s Fix Your VLOOKUP Formula

Click here to download the file used in the video

VLOOKUP Problem Thumb

VLOOKUP formula problems? There is no doubt about it, VLOOKUP can be infuriating. But, it is an incredibly powerful formula. For many, it is the first ‘non-trivial’ Excel formula learned: those who master it tend to go on to learn many other powerful techniques; those who do not may give up serious Excel learning. In this way it can have a kind of strategic importance in the Excel learning journey. So, let’s fix yours, ASAP!

PROBLEM 1: SPELLING (EXTRA SPACES?)

A simple problem accounts for most problems with VLOOKUP. Spelling generally has to be 100% accurate. Make sure the entries in the database are accurate, as well as the value you are looking up. Also, do you have extra spaces after your entries? This classic mistake is difficult to spot and can be infuriating. Precise spelling clears up a lot VLOOKUP formula problems.

PROBLEM 2: LACK OF ABSOLUTE REFERENCES IN TABLE REFERENCE

It is natural to want to ‘drag down’ a VLOOKUP formula to apply it to multiple cells. To do so can create problems if the correct referencing technique is not used. When copying the formula to other cells, we wish to ‘fix’ the table reference, so that all cells refer to the same table. This necessitates use of absolute references or ‘the dollar signs’. See how Chris integrates them in the video.

PROBLEM 3: NOT REFERENCING THE ‘FURTHEST LEFT’ COLUMN

VLOOKUP requires a table reference and does only one thing with it: it tries to find the look up value in the leftmost column of the table. It cannot find a lookup value in the ‘second-from-left’ or any other column! Make sure the leftmost column of table contains the lookup values – you may have to adjust the table reference, or the layout of the data.

PROBLEM 4: LOOKUP COLUMN OUTSIDE OF TABLE RANGE

You might wish to adjust the VLOOKUP formula to return data from another column, or add an additional VLOOKUP formula to do the same thing. It is important to remember that VLOOKUP can only find columns within the table range, however. An #REF! error may mean the proposed column index number exceeds the number of columns in the table. To resolve this, review and enlarge the table reference to encompass more columns.

PROBLEM 5: LACK OF UNDERSTANDING OF ‘APPROXIMATE MATCH’

The problem that is most difficult to explain! People often avoid dealing with the last component of the VLOOKUP formula that determines the match type, which we can set to exact or approximate, by excluding it altogether. It is best to include it, to make things clear to Excel and to other spreadsheet users, and for your own learning benefit! Before we go further, it’s worth noting that, in most cases, exact match (FALSE or ‘0’) is most appropriate; this is because people are usually trying to match ‘exact’ text values (see the example in the video). If in doubt, set this value to false (‘0’).

So, what is approximate match for? VLOOKUP with approximate match helps is slot a number into a range of numbers. The values we input in the leftmost column of the table act as ‘lower bounds’; Excel constructs ranges and slots the look up value into the appropriate one. This can be difficult to envisage: check out the example in the video, and download the example file.

So, did you manage to fix your Excel VLOOKUP problem? Thanks for reading and leave a comment on the video.

Get on the Tiger mailing list (click left) 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:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta

Contact us here.

Posted in Advanced Articles, Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel VBA to Copy Data to Sheets

Speed Up Onerous Copy / Pasting in Excel Using VBA

Click here to download the STARTER file used in the video.
Click here to download the COMPLETED file used in the video.

Excel VBA Copy Data to Sheets Chris Only

BY CHRIS MORTIMER

Have you ever had to copy / paste data in Excel from one sheet to another? Perhaps you’ve had to copy / paste certain rows from a dataset to separate sheets. I am pretty sure you are not the only one who has found themselves frustrated in this situation. It takes SO much time! Surely there is a better way…

We can use Excel VBA to copy data to sheets in Excel. In the video tutorial, we take an example (sent in by a subsriber to the Tiger mailing list) from the world of horse racing. The workbook contains details of horses in races from the past few years; the user wishes to be able to copy the horses from each race onto a separate sheet. Sounds simple enough, right? It’s a typical example and I’m absolutely sure you will have tried to do something similar at some point in your working life – I know I certainly have…

… in fact, I tried to do it for this tutorial (see the beginning of the video!). I copy / pasted a few entries onto separate sheets and was genuinely shocked at how much time (not to mention, energy!) this relatively simple task used up, and I soon gave up! I started thinking about how to exploit Excel VBA to copy data to sheets; the result was an awesome VBA routine that executes in just a couple of seconds. Yes, this manual task that takes up hours of your time can be reduced to just seconds.

In the video, I walk you through the code, step-by-step, using the same systematic coding approach I use in my day-to-day work. Some of the ‘usual suspects’ from the Tiger videos are there – variables, a loop, conditional statements, use of the macro recorder. The code is quite complex but, with some conceptual understanding, you will be able to make sense of it, and hopefully apply it in your work. If this video is too advanced, work through our ‘Excel VBA for Beginners’ playlist on YouTube first.

Good luck and let us know how you get on.

Get on the Tiger mailing list (click left) 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:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel Frequency Formula: Common Problems / Fixes

Excel Frequency Formula: Excel’s Most Difficult Formula?

Click here to download the file used in the video.

BY CHRIS MORTIMER

I think of Excel learning as a journey, and I have certainly been on a journey with the Excel Frequency formula!

From being intrigued by it, confused by it, ignoring it altogether and then relearning it step-by-step, I have come to appreciate its unique power. We have to go through a number of steps to get it working properly – without these steps, it doesn’t quite work, which creates frustration and a reputation of being ‘difficult’. In fact, I think it is the most difficult Excel formula to get to grips with. It’s a real shame that many people, like me, have struggled with it: we are missing out on an Excel gem that creates powerful, visual analyses that quickly ‘tell the story’ of datasets.

Frequency Formula Chris Only

In this article and accompanying video, we explore five common issues with the frequency formula, and look at how to fix them. We explore the key concepts and critical technical issues. An appreciation of both will position you to take advantage of this incredibly powerful Excel formula. So, what are those five issues? Let’s dig in to number 1.

Excel Frequency Formula: Five Common Issues

1. CONCEPT NOT UNDERSTOOD (DATASET / BINS)

It is easy to blame Excel when things are not working – I have done so many times. The formula / coding are too complicated, the interface is not user-friendly etc. etc. In fact, when people struggle with Excel, the cause is usually conceptual, not technical. By that, I mean that we lack a clear idea in our own heads of what we are trying to do, or how the functions actually work.

I encourage the people I work with to ‘speak out loud’ what they are trying to do with formulae, and / or to articulate in words what a formula does. Technical proficiency (ie. being able to build the formula) comes when conceptual understanding is clear. In the first part of the video, Chris explains the two key concepts, datasets and bins, with reference to an example from the world of running!

2. BINS NOT UNDERSTOOD / ACCURATELY DEFINED

The Excel Frequency formula relies on the concept of ‘bins’ that we define in a single column as single values. Single values? How on earth does that work?! Surely Excel would need to know two values, the upper and lower bounds of the ‘bin’?

Fair question! Excel actually interprets the single values as ‘upper’ bounds and uses this idea to construct the bin ranges. The best way to think of the single values in the bins column is ‘upper bounds’; and, the best way to understand this idea is to download the Excel file and check out the ‘Explainer’ sheet. Bear in mind that the formula looks at the single values in column B, not the ranges as defined in column C – these are included as a learning aid.

This bit can be difficult – it took me years to work out. Take your time and expect some frustration.

Now we have grappled with the conceptual side, let’s consider the technical points…

3. FORMULA ENTERED INTO ONE CELL / ABSOLUTE REFERENCES ($) NOT USED

4. CTRL + SHIFT + ENTER ENTRY TECHNIQUE NOT USED

Points 3 and 4 refer how to construct the formula and to ‘enter’ it into Excel. There are a number of critical points here and, if any one is ignored, the formula is unlikely to work. The only way is to step back, take a steady and systematic approach, and be ready to start again! Follow along with Chris’ detailed explanation in the middle of the video.

5. DIFFICULT TO AMEND / TWEAK = FRUSTRATION!

The final humiliation! Once entered into Excel, the formula can be difficult to remove / amend. This is because it is an ‘array’ formula and, as with data tables, we cannot delete only part of it. In order to remove the formula, we have to select all of the cells in which it is entered. If you need to amend the formula, I recommend deleting all of the formulae and ‘starting again’. I told you it might not be straightforward!

Download the example file, work along yourself, and feel the power of the Excel Frequency formula. If you can get it working, the rewards are immense: the formula helps you to quickly understand the main features of a continuous numerical dataset. As Chris demonstrates, the formula can be used as the basis for chart that is bound to create value for your clients. Good luck!

How did you get on with the Excel Frequency formula? Is it really ‘Excel’s most difficult formula’? Leave a comment on the YouTube channel.

Get on the Tiger mailing list (click left) 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:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel VBA to Convert Minutes to Decimals

An instructive example of the power of Excel VBA

Click here to download the file used in the videos.

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.

Excel VBA Min Dec Thumb

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 (click left) 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:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Subscribe To The Tiger Mailing List

Enter Your Details Below…

On the mailing list, you will receive:
– Details of the livestreams in advance
– Information about Tiger Spreadsheet Solutions products including special offers for mailing list subscribers
– Details of new YouTube video releases

Once subscribed, you will receive a confirmation email.  If you do not receive a confirmation email, get in touch via the contact form in the ‘About Us’ tab above.  You can unsubscribe at any time by getting in touch via email, or via the contact form in the ‘About Us’ tab above.

* indicates required



/

( mm / dd )


Posted in Excel Guidance Articles, Excel Guidance Videos & Articles, Uncategorized | Comments closed

What are Excel VBA Variables?

By Chris Mortimer

CLICK HERE to download the EXAMPLE FILE used in the video.

I like to think of an Excel VBA variable as a place to store information. Now, this definition is not particularly exciting! But, thinking about variables in this way can help understand them and maximise their impact in your work.

Excel VBA Message Boxes

Let’s explore this definition a little more. You could compare an Excel VBA variable to a cell in a spreadsheet – which is, of course, where we usually store information in Excel. Indeed, you could substitute a VBA variable for a cell in many Excel VBA routines and still get the job done; but, it would not be as slick or efficient. Nevertheless, ‘a place to store information’, a bit like a cell, is a reasonable conceptual starting point.

How do we typically use Excel vba variables in coding? Let’s look at the example of a loop. A loop is a repeated set of instructions that allows us to get mind-boggling amounts of work done. However, they require careful control (all of us have been stuck in an endless loop at some point!) VBA variables allow us to achieve control in a neat-and-tidy way.

We could, theoretically, reference any object in Excel to control a loop. For example, we could say ‘keep running the loop until the value in cell A1 = 10’. This would work perfectly well, if the value in cell A1 incremented up or down towards 10 as the loop ran. The obvious downside is that we would have to update an object in the spreadsheet (in this case, a cell) which diminishes the efficiency of the code. Further, a spreadsheet cell is usually visible to the user, which may not be desirable.

With this example in mind, what advantages does a variable offer? First, it is quick and easy for Excel to work with, because Excel can change the value that is ‘assigned’ to the variable very efficiently. Remember, a variable is a place to store information; it is faster for Excel to store and recall information using a variable, than to interact with the spreadsheet to change a cell value.

Second, a variable is not usually visible to the user. This means variables are easier to control because, most of the time, they are ‘hidden’. It is not that we don’t trust the user! Rather, variables control processes that should not be easily changeable, such as looping through code. Variables offer superior control to the programmer, which helps create robust applications that do not break.

With these obvious advantages, it intrigues me that people tend to shy away from using VBA variables. Why is this? As is often the case with Excel, it is problem of communication. The language of variables is not easy to understand, so people think ‘they must be for computer programmers only!’ String? Integer? Long? What is the point of these terms, apart from confusing and often deterring the layperson? Let’s dig into it…

In a practical sense, variables help Excel organise its memory – where Excel compiles information to display what we see on the screen. In an efficient application, you could compare Excel’s memory to a beautifully designed wardrobe – it has differently sized spaces for different items, making everything easy to store, and to find. This is the point of variables – they tell Excel the kind of information that is coming; Excel then allocates an appropriately sized space to store it. This is why we ‘declare’ variables (‘Dim counter as integer’ for example), to tell Excel the type of information (‘integer’), and to create a name (‘counter’) to refer to the variable.

What are some common variable types? An Integer variable can store whole numbers up to a value of approximately 32,000 and requires a small amount of memory (just two bytes!) A Long variable can store larger whole numbers but requires twice as much memory. Other variables store decimals (Single, Double), text (String), and other data types. You can find comprehensive lists online. The main variable types I use are Integer, Long, Single (for decimals), String (for text) and Boolean (true or false).

When declaring a variable, we must also name it. A simple letter (Dim ‘x’ as integer) would do the job; however, fans of the channel know that I prefer informative variable names. An informative variable name tells the programmer what the variable does. For example, ‘counter’ is an informative variable name because it counts the number of times a loop has run. I use ‘ChrisCell’ when looping through a collection of cells, because the variable (in this case a ‘range’ variable) represents a cell. We can use any name apart from words that Excel reserves for its own use (‘row’, ‘cell’ etc.) An informative variable name requires more typing but allows us to understand what is going on much better; for me this is a worthwhile trade-off.

So, why not try out some VBA variables? You can find examples of all the above variables in our tutorial videos. They are central to the cool macros we create. Developing basic confidence in their use should lead to slicker and more robust Excel implementations. Good luck and let us know how you get on!

For regular spreadsheet hints and tips and more on the #ExcelRevolution:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Use Excel VBA to Separate Worksheets to Files

Create any number of Excel files at the click of a button using Excel VBA

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

We receive many emails from people who spend too much time moving Excel worksheets to separate files. This is a common task: if you are creating reports for students in a school or payslips for employees in a company, for example, you have to do this or something similar. It’s time-consuming, takes lots of mouse clicks and leaves us thinking ‘there must be faster way!’.

Excel VBA Message Boxes

One of the key themes of our training is that a small set of VBA techniques can transform how you go about your work. We say they deliver ‘disproportionate benefit’. Some of these techniques are: using the the macro recorder; using VBA variables and applying loops. With this video series, you will learn how to get this task done at the click of a button and improve these critical skills.

So, how can we use Excel VBA to separate worksheets to files? Let’s go! Don’t forgot to download the Excel file (above) and work along with Chris…

Video 1 – Using the Macro Recorder to Get Started

‘INTELLIGENCE IS KNOWING WHAT TO DO WHEN YOU DON’T KNOW WHAT TO DO’.

We like this aphorism at Tiger. For many professionals, applying code to this task is a situation in which they would not know what to do – ‘how should I get started? I have no idea’. To avoid giving up, we have to tap into ‘metaskills’ – mental resources that guide our actions when we are stuck, and that enhance or limit our capacity to learn. How does this translate into Excel? If you have no idea how to begin a coding task, what is the required metaskill? The macro recorder, of course!

The macro recorder allows us to do something in Excel and record the corresponding VBA code. So, for this task, we hit ‘play’ to start the recorder, and then copy the first worksheet to a new file; once we stop the recorder (Chris almost forgets!) we can review the code in the VBA editor. This is pure gold for the amateur coder; even if you have no coding knowledge, you can ‘create’ code for whatever you are trying to do, then try to tune it up and get it working. Try it.

Video 2 – Tweaking Recorded Code, Introducing a Variable

In the second video, we review the code in the VBA editor and tweak it to get it working for our purposes. One problem often encountered when creating multiple files is that of file names; we cannot save two files with the same name to the same location, as Excel helpfully tells us!

We resolve this by introducing a VBA variable to store the desired file name – generated in the original, origin workbook – and to apply it to the new workbook. By the end of the video we have a routine to separate a single worksheet to a single file; though this in itself is not very exciting, it is an important intermediary step that allows us, in the next video, to apply powerful Excel techniques to quickly ‘scale up’ the code to work for multiple worksheets and files. We think ‘get it to work once, accurately and robustly, then apply the loop’.

Video 3 – Applying a Loop to Scale Up the Code

We are dealing with a ‘repeated’ set of instructions since we want Excel to do the same thing to multiple worksheets. The concept of ‘repetition’ can only mean one thing – loops. A loop is a powerful programming technique that repeats a set of instructions any number of times. But, like any powerful beast, loops require careful control!

In the final video, we look at how to set up and control the loop so that it ‘works though’ the database, pointing to the next row each time the loop iterates. This involves another Tiger favourite – the Excel VBA offset command. Loops and offset really are like fish and chips: they work beautifully together. Master their interaction to get a mind-boggling amount of work done at the click of a button. In this example, we set up the loop to work through the worksheets in the file, copy each to a new file, and to save each under an appropriate file name. A task that would take a few minutes, and cause some frustration, is now complete in just a few seconds.

What did you think about the Use Excel VBA to Separate Worksheets to Files videos series? Did you manage to save some time, and did you feel the power of the VBA techniques? We would love to know how you get on – contacts below.

For regular spreadsheet hints and tips and more on the #ExcelRevolution:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Working with Excel VBA Message Boxes

Tell the user what is going on and build their confidence in your spreadsheet

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

A true story…

We are currently working on a spreadsheet development project here in the UK. The project involves updating a ‘legacy’ spreadsheet – one that has been around for a while. It’s a typical situation in which we might intervene: the file has bits and pieces ‘bolted on’, and the person who was in charge of it has left the business. It’s causing anxiety.

The spreadsheet contains various buttons and macros that run on workbook events including opening the workbook, some of these macros are very powerful. Here is the thing: nobody actually knows what happens when the buttons are pressed. People are aware that some code runs, but the consequences of the macros are mixed at best: some reports are created but, elsewhere, inputs disappear and formats changed for no apparent reason. The client team is frustrated.

Excel VBA Message Boxes

What is missing here is helpful user interaction. The applications we develop must communicate with users to build their confidence in the file. One example is asking for permission to run a macro, and making sure the the user is aware of any adverse consequences such as losing pre-existing data; another example is notifying the user that a macro has run, and providing some helpful summary information from the analyses generated. Both are examples of a critical success factor in spreadsheet development: helpful user interaction. So, how do we use Excel VBA message boxes to create helpful user interaction?

This video series teaches you the basics of Excel VBA messages boxes. With the building blocks provided, you will be able to create powerful, dynamic message boxes that provide the user with the information they need, when they need it. First, we cover a simple message box. Then, we look at two possible applications of Excel VBA message boxes: first, to seek permission from the user to run a macro; second, to report back to the user after a macro is run. So, let’s get into it!

Video 1 – Simple Excel VBA Msgbox

In the first video, we look at how to create a simple message box. From the outset we deploy powerful coding concepts – we use activecell.value, for example, to make the message box display what is in the selected cell; this lends the message box a dynamic quality – it responds to different situations and provides appropriate feedback. We will harness this dynamic quality later. We also look at how to change the title of the message box from ‘Microsoft Excel’ – we don’t want Bill Gates taking all the credit!

Video 2 – Excel VBA Message Box for Confirmation

How can you (re-) build user confidence in your spreadsheet? One way is to ask the user to ‘commit’ to decisions using a confirmation message box before running code. We are all familiar with this idea from online forms and pieces of software – we are told what will happen, and given the opportunity to proceed, or to reverse the decision. This is particularly important in the spreadsheet environment where users may not be used to macros doing work for them. Correctly implemented, this kind of feature can build user confidence and supports user interaction – strong user interaction is one of the features of professional Excel applications. The important mechanism here is a ‘Yes / No’ message box combined with a conditional statement to take the code in one of two directions. Cool!

Video 3 – Excel VBA Message Box for User Feedback

The final application focuses on using message boxes to provide user feedback. We think that ‘involved’ macros (ones that take some time / do a lot of work) should be ‘book-ended’ by two message boxes – one to seek confirmation (above), and the other to indicate the macro has finished. The second message box, moreover, is an opportunity to provide some timely feedback for the user. Why make the user look for a critical piece of information in a spreadsheet when you could flash it up in a message box? This kind of user feedback further improves user interaction and should build user confidence in your application.

What did you make of the Excel VBA messages boxes series? These techniques should allow you to improve user interaction in your spreadsheet application, and create that ‘wow’ factor. Good luck! We would love to know how you get on – contacts below.

For regular spreadsheet hints and tips and more on the #ExcelRevolution:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel VBA IF Conditional Statements for Beginners

Excel VBA IF statements can take your VBA coding to the next level

If you’ve learned the basics of VBA coding (perhaps you’ve watched our popular VBA for Beginners series), you’re ready to move to the next level. What is next? Excel VBA IF statements move you towards more advanced coding, and more powerful macros. You may have wondered how we tell Excel to execute only part of a routine, according to a value in a cell, or something else. This is the basic idea behind an Excel VBA IF (‘conditional’) statement.

Excel VBA IF

This series teaches you three techniques for dealing with a ‘conditional’ situation. These techniques are the techniques we use in our Excel development projects. But first, what is an Excel VBA IF statement? Why ‘if’?

Conditional statements allow us to instruct Excel to execute code only if a certain condition is met. A condition could relate to any object in Excel, but typically a condition relates to a value in a cell or VBA variable. For example, you might wish to count how many entries in a database are ‘male’, or write the word ‘male’ next to all the ‘male’ entries. In each case, we would ask Excel to do something only if a certain condition is met, in this case if the letter ‘M’ appears in a cell.

Got the idea? OK, let’s get into the first video! Don’t forget to download the files and work along with Chris.

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

Video 1 – Simple Excel VBA IF Statement

In the first video, we learn how to create a simple if statement. A simple if statement involves just a single line of code, and is relatively easy to set up. In this example, we ask Excel to flash up a message box to tell us if an entry in a database is ‘male’ or not. You’ve just created your first Excel VBA IF statement. Well done, onwards!

Video 2 – IF – ELSE – END IF

So, we know how to ask Excel to do something if a condition is met. But, what if a condition is not met? What if we want Excel to notify us if the entry in the database is not a ‘male’? The next technique is IF – ELSE – END IF.

This technique allows us to direct the code in two directions. But, the required coding construct is more complicated and often confuses beginners; when using IF – ELSE – END IF, we have to remember that the instructions must begin on a new line. A simple if statement requires just a single line of code; IF – ELSE – END IF incorporates another condition and requires at least 5! One for if, else, end if, and at least one line for the instructions if the condition is met, and the instructions is the condition is not met. Don’t worry too much about it at this stage – just follow along with the video.

This can seem ‘code-heavy’; but the power of the routines, in our view, justifies the effort required to learn the syntax. In the video, Chris creates a routine to count up the number of males and non-males and in the database. The loop / conditional statement combination is VBA at its best; you can see how these techniques combine together beautifully to get a job done quickly. Chris even has time to validate the routine by using spreadsheet formulae to reach the same outcome. Nice!

Video 3 – Select Case

Oh no, more new syntax! Yes, select case is a new construct for us. But, this technique opens up a new world of possibilities. So far, We’ve dealt with conditional statements that direct the code in one or two directions. Select case allows us to direct the code in any number of directions, and even includes a ‘catch-all’ condition too. It is supremely flexible and powerful, and regularly features in our Excel development projects.

Chris implements select case in the video to count up the number of entries in three age categories (eg. 30-59 years old) in the database. But, he does not complete the report. Can you combine select case with a loop to get the information that the ‘client’ requires? Reference techniques from video 2, and let us know how you get on.

What did you make of the Excel VBA IF Conditional Statements for Beginners series? With these three techniques, we are confident you can deal with most situations where a conditional mechanism is required. Good luck!

For regular spreadsheet hints and tips and more on the #ExcelRevolution:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel Learning Tips: My Story

Chris’s Spreadsheet Story and 3 Excel Learning Tips

By Chris Mortimer

There can be no doubt about it, Excel is difficult!

You’re on an Excel learning website, so you’ve probably felt that difficulty yourself. In the below video, I talk about my spreadsheet story – how I got into spreadsheets, the difficulties I encountered and how I got over them. I share three Excel learning tips that I hope will push your spreadsheet skills forward.

Spreadsheet Story Still

In the below video, I talk about (1) finding an access point, (2) the importance of a mentor, and (3) the impact of good learning resources. What is your spreadsheet story? Tell me – leave a comment below the video on the YouTube channel.

Excel Learning Tips 1/3 – FIND AN ACCESS POINT

When people think about Excel, they tend to think about numbers. This leads to the misconception that spreadsheets are the preserve of statisticians and computer programmers. In the video, I argue that a spreadsheet is actually a social and visual problem as much as a technical, numbers-based one. This is because, to my knowledge, a spreadsheet has never created itself! A spreadsheet is created by a human being to help them do something. Further, a spreadsheet that cannot be understood is not useful, regardless of how powerful it may be; it must be well-presented. Therefore, spreadsheet development actually calls on visual and social skills as well as numbers-based ones.

I find this point of view intriguing (well, it is my own ;-)) and incredibly empowering. It opens up a topic that is considered ‘difficult’ to most people – because most people are confident with some aspects of social, visual or technical problems. With this in mind, you have to find your access point. Are you ‘artistic’? If so, you can arrange the elements of the spreadsheet in a way that helps the user assimilate the information. Do you like interacting with customers? If so, you can get started by understanding user needs and creating a detailed specification. After you have identified your access point, you can build other skills from this enclave of competence and confidence.

Excel Learning Tips 2/3 – FIND A MENTOR

Excel is a piece of technical software that, without specialist skill, is difficult to use. Too many people are asked, wrongly in my view, to ‘get Excel to work’ without the proper training. A critical factor for many successful Excel users is finding somebody to help. I was lucky to have helpful colleagues on my Masters programme, and some excellent lecturers. These people humanised a complicated topic and helped me through the early stages. So, try to find your mentor. If you’re looking for somebody to help you, bear in mind that skilled Excel users often like talking about spreadsheets! They may be happy to share their knowledge. So, approach somebody you work with or somebody you know, and ask them to help you. You may find they are flattered by the invitation.

Excel Learning Tips 3/3 – GET SOME LEARNING RESOURCES

‘Excel’ as a learning topic consists of multiple sub-topics: modelling, data analysis, VBA programming etc. You need a plan to navigate these sub-topics and organise your learning. I have often seen people get lost in ‘rabbit holes’ – obscure topics that don’t deliver much value. Now, this article is not a sales pitch for my YouTube channel, but it might be a starting point. I organise the video series so that these sub-topics can be easily understood, and so that the learner can work along with me and develop the skills. There is a multitude of Excel learning websites out there – find one that suits your learning style, and use the resources to navigate Excel as a learning topic.

I hope these three Excel learning tips help you with your learning journey. Good luck, and let me know how you are getting on.

What is your spreadsheet story? What stage are you at with your Excel learning? Leave a comment below the video on the YouTube channel.

Posted in Advanced Articles, Advanced Videos, Beginner Articles, Beginner Videos, Excel Guidance Articles, Excel Guidance Videos, Excel Guidance Videos & Articles, Intermediate Articles, Intermediate Videos, Uncategorized | Comments closed

Excel Database Formulae For Beginners

Create cool database analysis quickly with Excel Database Formulae for Beginners!

Excel is great for logging data and keeping small databases. But, very few people know how to get the analysis they need from the data quickly and without too much stress! Too often, people resort to sorting data and splitting up the database to simplify the problem; but, this ‘critical mistake’ complicates the workbook and is often counter-productive. What are the alternatives?

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

Excel DB Pic

Correctly applied, Excel database formulae allow us to get the analysis we need quickly and without too much stress. A few hours learning the skills could have a dramatic impact on your work. Make sure you download the exercise files (above) and work along with Chris. Enjoy!

Video 1 – Introduction to Excel Database Formulae

In the first ‘in-car’ video, Chris talks about the real-world motivation for the series, and the value of the techniques covered. Excel offers a vast array of functions; but, we believe, not all are required to reach a good level of competence. Rather, a subset of techniques learned effectively can deliver disproportionate benefit. The powerful Excel database formulae covered in this series fall into this subset of important techniques. Chris also talks about a ‘critical mistake’ in spreadsheet development – how users tend to split up databases across multiple sheets in order to make the analysis easier. This actually serves to complicate matters and will discourage the user in the long run. Command of Excel database formulae allows you to get the analysis you need without complicating workbook structure.

Video 2 – COUNTA and COUNTIF

The first techniques video covers two basic formulae that are essential for working with databases. The COUNTA formula tells us how many entries are in a database, and the COUNTIF formula allows us to answer ‘single criteria’ questions such as ‘how many females are there in the database?’ Remember to download the spreadsheet files (link at the top of this article) and work along with Chris. Throughout this series, we use PC keyboard shortcuts to navigate Excel and speed up / de-stress the formula-building process. Try the keyboard shortcuts yourself and get Excel working for you like a pro!

Video 3 – SUMIF and AVERAGEIF

COUNTIF allows us to identify how many records conform to a particular ‘criteria’, or piece of information that interests us. The SUMIF and AVERAGEIF formulae allow us to perform an operation on records that conform to a criteria. What do we mean by an ‘operation’? The operation that SUMIF performs is to aggregate the data, whereas AVERAGEIF returns an average of data that interests us. So, you can get important summary measures such as aggregates and averages for subsets of the data without having to sort or split up the database. Cool! Throughout the series, Chris checks the accuracy of the Excel database formulae by sorting the database and applying a simple formula, effectively getting the same result a different way. It is good practice to ‘validate’ your analysis like this – it should help build confidence in the values that the database formulae return.

Video 4 – COUNTIFS

In this video, the Excel database formulae start getting really interesting! We move onto the topic of multiple criteria questions – analysis in which we wish to include data that conform to two or more pieces of information. How many males from the North region are in the database, for example? We deal first with the COUNTIFS formula. The COUNTIFS formula is similar to its close friend the COUNTIF (singular) formula, but allows us to include any number of criteria, whereas COUNTIF allows just one. The formula returns the number of records in the database that conform to all of the criteria we specify. With this level of sophistication, the formula can be tricky to build. Particular attention has to be paid to references and use of absolute, relative, and partial absolute references. Chris discusses references in the video and provides his tips for getting them right throughout the series. Trial-and-error is key!

Video 5 – SUMIFS and AVERAGEIFS

In the fifth video in the series, we deal with more ‘multiple criteria’ questions. In this case, we wish to do more than simply count entries that conform to multiple criteria; we actually want to perform an operation on the data – calculate an aggregate (SUMIFS), or a mean average (AVERAGEIFS). Chris works through each of these formulae step-by-step, building two analysis tables that provide powerful insights on the dataset. All in just over 15 minutes! Hopefully you’re now feeling the power of the formulae and can see applications in your real-life situation…

Video 6 – DSUM and DAVERAGE

The sixth and final techniques video in the series marks a departure from the previous formula-building approach. We examine two powerful Excel database formulae – DSUM and DAVERAGE. These formulae offer new possibilities: rather than having to edit a complicated formula to get the analysis we need, we can simply enter values into a table and watch the outputs change, cool! There is a trade-off however, setting up these formulae requires some preparatory work, and maximum precision is needed to get the formulae working. Once set up and validated, however, they provide quick, powerful, user-friendly analyses. We have found that on our real-world projects, the DSUM and DAVERAGE formulae can have a dramatic impact. Make sure you work along with Chris and get these Excel database formulae working for yourself.

Video 7 – Excel Database Formulae – Reflection and Discussion

In the final video of the series, Chris discusses an important issue in applying these formulae in the real world – how to account for a database that is changing in size. It is unlikely that your database is ‘static’; rather, you would want to add data to it over time. How can we set up the Excel database formulae so that the analysis encompasses the new data? Chris discusses three possible approaches and highlights his preferred option.

What did you make of the Excel database formulae for beginners series? Have you managed to get the formulae working yourself? Share your experience with the Tiger community on the platforms below:

For regular spreadsheet hints and tips and more on the #ExcelRevolution:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed