Author Archives: Chris Mortimer

7 Essential Excel VBA Beginner Techniques

GET YOUR VBA CAREER STARTED WITH THESE 7 ESSENTIAL BEGINNER 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!

Click here to download the STARTER Excel file for this video.

Click here to download the COMPLETED Excel file for this video.

Essential VBA Beginner Techniques Thumb

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!

Other properties of objects that are commonly used:
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.

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.

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.

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?

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.

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.

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!

———————————

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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

17 Things You Must Know About The Excel VBA Editor

GET TO KNOW THE EXCEL VBA EDITOR, AND BOOST YOUR CODING SKILLS

BY CHRIS MORTIMER

We cannot do anything in Excel VBA without accessing the VBA editor. It is our workbench, our canvas, our playground. So, it surprises me how few people take the time to really get to know it. The Excel VBA Editor supplies clever features to help us code better, and to help us debug when (inevitably!) things go wrong.

In this video and article, I outline 17 things about the Excel VBA Editor that will help accelerate and de-stress you VBA coding career. The tips in the article and the video differ slightly, but I am absolutely sure you will get value out of either or both, whether you are an Excel VBA beginner, or an experienced coder.

Click here to download the Excel file for this video.

Beginner VBA Editor Thumb

First, where is the VBA Editor In Excel?

It’s best to be able to view the Developer tab in the ribbon at the top of Excel – we can access the VBA Editor there and other useful VBA functions. To make the Developer tab visible, go to File / Options (in the bottom-left corner), Customize Ribbon, then ensure ‘Developer’ is ticked on the right-hand side.

Remember you can use the Alt + F11 shortcut on the Windows PC to view the VBA editor with no mouse clicks at all – cool!

1. The Default Layout (And Fixing It!)
When you open the VBA Editor, two panes are likely to be visible with a row of symbols and menus at the top. Let’s concentrate on the two panes on the left side of the main window first. At the top is the Project Explorer, and at the bottom is the Properties Window.

Let’s first deal with a crisis you are likely to encounter early in your VBA career. What to do when these essential panes simply do not appear? This certainly caused me frustration when I was getting started, and I still receive regular emails about it to this day! If you cannot see the Project Explorer or the Properties Window, go to the ‘View’ menu at the top of the VBA Editor, then click on the two windows in the menu. This should return the VBA Editor to our default view – phew!

2. The Project Explorer
The top-left pane is the ‘Project Explorer’. Here, useful Excel ‘objects’ are listed – the sheets in the workbook and the workbook itself. Modules are also listed here when they are created.

A tip – the details of ALL open workbooks appear here; make sure you do not confuse objects in different workbooks, as I have so many times in my career. You could be writing code with one file ‘open’, inadvertently adding it to a different file – which means the code can behave strangely and is easily lost. I recommend making sure all other workbooks are closed, certainly when getting started with Excel VBA; so, go ahead and close other workbooks now, if there are any open.

Code usually exists in modules (though there are exceptions), so let’s try creating one. To do this, click ‘Insert’ at the top and ‘Module’. If no modules are in the workbook already, a module will be created, along with a folder to store them in. You will also see a whole space open up on the right side of the window – this is our main coding area, our playground, our canvas 😊 We’ll move over there in a second!

3. The Properties Window
First, click on the module in the Project Explorer, and notice that a ‘property’ of the module appears in the Properties Window below (bottom-left corner of the VBA Editor.) Let’s try changing the name of the module; in technical language we are working with the name property of the module object.

4. Modules
Click in the name box and change the name of the module to ‘Our_Code’. We will work with a single module only today, but it helps to give modules meaningful names and to group similar routines in modules. I often have a module called ‘Navi’ in my files, for example, where all the macros to do with navigation are stored.

By click on Sheet1 or ThisWorkbook in the project explorer, you can see a better illustration of the value of the properties window. Click on Sheet1 and notice that sheet name (Name property) and visibility (Visible property) can be controlled here. Cool!

5. One File At A Time
Right, let’s go ahead and do some coding! Download the file that accompanies this video, and copy paste the code into the coding window. Notice that two files (two file names), and two sets of objects, are now visible in the Project Explorer window: we know this can confuse, so let’s close the download file now, to keep our stress levels down 😊

6. The Run (‘Play’) Button
The aim of this macro is to ‘draw’ a capital T (for ‘Tiger’!) by putting values in cells on the spreadsheet. Now, it’s time to ‘run’ the macro or execute the code. I like to say ‘play’ the code, however, because the VBA editor includes a ‘play’ icon at the top. Click inside the macro (under the first line ‘Sub …’ will be fine) and hit the play button. Oh no! We have a VBA error. Don’t worry, you’ll get used to these as you go through your career; in my view, a critical skill in VBA development is being able to respond to these properly. So, don’t lose heart, it’s all in the plan!

7. The List of Macros
Before we go into debugging, let’s look at another way to look through and run macros in the VBA editor. Click on the ‘Tools’ menu at the top, and then ‘Macros’. If the file contains more than one routine, you’ll see each listed here, but our file contains only one. Make sure the ‘Debug_This’ macro is highlighted then click ‘Run’. And there you go, the same error! Don’t worry, we were only practising different ways of triggering macros, and were expecting this. So, let’s fix it.

8. Break Mode
This time, hit ‘Debug’ on the error dialogue box. This puts the VBA Editor into what is known as ‘Break’ mode. This mode can be annoying, because as long as the VBA Editor is in break mode, we cannot run any macros. There are three ways to tell you are in break mode: first, code does not execute (though it is not always clear the code is not running); second, the word ‘[break]’ appears at the top of the VBA Editor; third, a line of code is usually highlighted. Yes, Excel is trying to help by highlighting where the error is. Cheers, Excel!

9. The Reset (‘Stop’) Button
Our job now is to ‘debug’ or to fix the code so that it works. At this point, let’s introduce some of the VBA Editor’s awesome debugging tools. First, click the ‘stop’ button (remember there is a ‘play’ button too!) at the top of the VBA Editor; this will reset the code and exit break mode.

10. Step Through Code
Let’s learn how to ‘step into’ the code. This allows us to execute each line individually; at the same time, we can look at the main Excel window and understand what is going on. This really is the best way to learn code, particularly if you like an intuitive ‘trial-and-error’ approach (which I personally recommend.) Click below the ‘Sub …’ line of code so that the cursor is flashing inside the routine; then head to the ‘Debug’ menu at the top and click ‘Step Into’.

11. The Yellow Line
Immediately you’ll notice that the first line of the macro is highlighted in yellow. Stepping into the code automatically puts the VBA Editor into break mode; then, it highlights a line of code. This code is about to be executed; the next time we hit ‘Step Into’, the highlighted code will run. This allows us to understand exactly where we are in the code, and the exact location of a problem. We’re now debugging!

Hit the F8 key on the Windows PC, or click ‘Step Into’ via the ‘Debug’ menu at the top of the VBA editor. Note how the VBA Editor ‘works through’ the code line-by-line, with the Excel window updating after each execution. This is the approach I use for debugging in my day-to-day work – give it a try!

12. Insert A Breakpoint
So, what is the code doing? As you hit the F8, you will notice the VBA Editor repeating a line of code. This is because a ‘loop’ is in place, which, as the name suggests, ‘loops’ through a line or lines of code a certain number of times. Suppose we don’t have time to ‘manually’ step through the loop using the F8 key and step into. Another option is to use a breakpoint – another one of the Excel VBA Editor’s powerful debugging tools. Let’s check it out.

If you look carefully at the coding window (the main window on the right side), you’ll notice a grey border that separates the coding window from the Project Explorer and Properties Window. Locate the line of code that reads ‘For Counter = -1 To -5 Step -1’, and position the cursor in the border alongside this line of code, then click the left mouse button.

If all has gone to plan, a red circle should appear. If so, you have successfully inserted a ‘breakpoint’; this means that the code stops or ‘breaks’ when it reaches the breakpoint, without executing the line of code next to the breakpoint. For us, it is a great way to run a macro ‘to a certain point’ that you wish to test. It is particularly useful when macros take some time to execute eg. if they contain loops – like our example, and the point we wish to test is later in the routine. Let’s give it a try.

Click the Reset (‘stop’) button at the top of the VBA Editor, then hit the Run (‘play’) button to execute the macro. You will notice that the code runs without an error until we reach the breakpoint, when the code stops. So, we know that the code above the breakpoint is robust – it does not cause an error. But what about the rest of the code? This approach allows us to home in on the source of the problem.

Now the code has reached the breakpoint, we are automatically in break mode. From here, we can step through the code once more to until we reach the VBA error we encountered earlier (remember, our mission here is to fix this error, and get the code working.) So, use the F8 key (Windows PC) or go through the Debug menu to ‘step through’ the code. Follow what is happening in the Excel window – can you see what the problem is going to be?

What should occur is runtime error 1004 which means that we are referencing something that, as far as the VBA Editor is concerned, does not exist! In this case, we are referencing a column outside of the spreadsheet, before column A; clearly this is not possible, which is why the error occurred. Can you see how using the tools in the VBA Editor allowed us to work through the code and identify the source of the problem? These techniques are so important in the bigger picture of your VBA skills development.

So, how might we fix the problem, do you have any ideas? VBA is reasonably user-friendly as a programming language; even without much coding experience, perhaps you can identify and fix the issue. As always, trial and error is the way forward – do not be scared of errors (make sure you save the file), and use the tools we have learned to work through the code, and see the result of the changes you make. Make tweaks within the framework of the existing syntax. It is normal to work incrementally towards a solution in this way – in fact this is critical skill in Excel VBA and computer coding more generally.

Try replacing this line of code:
Range(“D3”).Offset(0, Counter) = “O”
With this:
Range(“F3”).Offset(0, Counter) = “O”
Then reset and re-run the code.

The result should be a beautiful capital ‘T’ shape. Did you get it working? Good! You have now understood and debugged a problematic routine using the tools in the VBA Editor – well done!
Before we finish, let’s look at a couple of time-saving features in the VBA Editor.

13. Get The Value of a Variable with the Cursor
You might have noticed that our macro contains what is called a ‘variable’. A variable is simply a place to store information; in our case, the name of the variable is ‘Counter’ (though we could give it any name) and it combines with .offset (a method) to control the top of the ‘T’ shape. Variables are integral to computer programming and, correctly applied, facilitate powerful routines in Excel VBA. Let’s look briefly at how to work with them in the VBA Editor.

Let’s work through the code once more using Debug / Step Into. As you step through the code, hover the cursor over the Counter variable in the VBA Editor. Note how the value of the variable pops up (eg. Counter = 1); this is the easiest way to understand the value of a variable.

14. The Watch Window
If you want something more involved, you could use the ‘Watch Window’. Click the View menu at the top of the VBA Editor, and hit ‘Watch Window’; notice a new pane appears at the bottom of the VBA Editor. Right-click anywhere in this new pane, and click ‘Add Watch’. Then, type in ‘Counter’ (the spelling must be accurate) in the ‘Expression’ box, and hit ‘OK’. Note how you can track the value of the Counter variable in the Watch window. Cool! It’s a great tool for externalising information (such as values stored in variables) that is not immediately visible in the VBA Editor.

15. Option Explicit
A final point about variables: did you spot the very first line of code in the module? It reads ‘Option Explicit’ – what on earth does that mean? Option Explicit means we have to be explicit by declaring variables properly; if we use Option Explicit, Excel checks variable spelling before running any code. This has saved me huge amounts of time through my career, so I highly recommend using it.

16. Options Menu
Click ‘Tools’ at the top of the VBA Editor, and ‘Options’. There are plenty of interesting options here, including control of the appearance of text in the VBA Editor. We will just tick ‘Require Variable Declaration’; this means that, next time you create a module, ‘Option Explicit’ will automatically appear at the top, saving the need to type it in, and delivering the benefits described above. Recommended!

17. Find And Replace
Finally, let’s look at Find and Replace. This facility allows us to quickly substitute one piece of code for another within a routine, a module, or even the whole VBA project. Again, it’s a huge time-saver. Suppose we wish to change the name of the Counter variable. Yes, we could do this manually; but that might be time-consuming with a longer / more sophisticated routine. This is where Find and Replace can help.

Click the ‘Edit’ menu at the top and then ‘Replace’, or use the Ctrl + R shortcut on the Windows PC. Type in Counter in the first box, then an alternative name in the second (I’ll use Chris_Counter); then see how the VBA Editor changes the values for us, and even reports how many occurrences it found. Ah, another time-saver!
———————————

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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

How to Change Grades to Numbers in Excel

How to quickly convert grades to numbers in order to measure student progress in Excel?

How can you change grades to numbers in Excel? It’s something many teachers out there are trying to do in order to measure student progress. And it’s something I have been asked to do on projects numerous times! Learn how to do it, and avoid the need to hire somebody like me. Welcome to Never Hire Me.

Click here to download the STARTER Excel file for this video.

Click here to download the COMPLETED Excel file for this video.

Excel cannot easily understand the difference between two letters. So, the best option is to convert the letters to numbers, and then to work out the difference between the numbers. The concept is simple enough, but how to actually do it in Excel?

First, we build a table to assign a value to a particular grade. It is sensible to position the table on another sheet for clarity; but, referencing other sheets in formulae can be tricky. So, we use a ‘named range’. Using a named range makes formula referencing much easier later – Chris shows you how to quickly set one up in the video.

Match a number to a range in Excel thumb

With the first step complete, we return to the original sheet and implement a VLOOKUP formula. The formula ‘looks up’ a ‘lookup value’ (ie. the student grade) in a table, and returns a value from another column in the table that is on the same row. We work through the formula in the video, step-by-step – make sure you follow along with Chris and try to build it yourself.

The final step is to copy the formula across to the second grades column, and to implement a simple arithmetic formula to establish the difference between the grades. And that’s it! You have combined VLOOKUP with a table and a simple arithmetic formula to find the difference between two student grades.

Did you get the difference between two letters in Excel? Let Chris know in the YouTube video comments, he will get back to you.

———————————

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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

How To Match A Number To A Range in Excel

What is the best way to match a number to a range in Excel?

How can you match a number to a range in Excel? It’s a common reason I get hired! Learn how to do it, and avoid the need to ask the IT department, or to hire somebody like me.

Click here to download the Excel file for this video.

Thank you to channel viewer Phillip for sending in this example. Phillip is recording results of physical tests for the military which involves awarding scores for different exercises, including press ups. The scoring system allocates different points for different age groups; so, our spreadsheet must match an age to an age group. Difficult! So many people find themselves trying to do this, and do not know the techniques required.

Match a number to a range in Excel thumb

Phillip’s setup is typical, the age ranges are defined in a cell using upper and lower bounds, and a hyphen, eg. ’17-21′. The problem is that Excel simply cannot make sense of ranges articulated in this way. So, the first step is to express these ranges in a way that Excel understands; this means extracting the lower bounds and listing them in a row.

With this set up, we can think about applying formulae to get the job done. In the video, Chris covers two possible approaches, one using HLOOKUP and the other using MATCH. Both applications are non-typical: HLOOKUP uses approximate match as its 4th component, for example, and MATCH uses ‘less than’ as opposed to ‘exact match’ in its 3rd. Go through the video and work along with Chris to learn how to apply these interesting and useful applications yourself. You will be matching values to ranges in Excel in no time!

Did you manage to match a number to a range in Excel? Let Chris know in the YouTube video comments, he will get back to you.

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

How To Use Excel VBA Code – Real Example

How To Use Excel VBA Code To Speed Up Manual Work – Real-World Example

Click here to download the STARTER Excel file for this series.

Click here to download the COMPLETED Excel file for this series.

How can we use Excel VBA code to speed up manual work in Excel? In this series we explore an example Excel VBA project sent in by Eric, a viewer of the channel. We aim to build an Excel VBA application to move data around a file at the ‘click-of-a-button’, saving hours of frustrating manual work. The application should be flexible and scaleable enough to handle changes in business circumstances – a challenge indeed! Let’s get into it…

How To Use Excel VBA Thumb

Part 1 – Planning and Looping Through Worksheets

In part 1, we consider the task at hand and complete a simple task that moves us in the right direction. This may seem to lack ambition, but it is important to plan any VBA task and to move forward in easy steps; if you simply jump into the VBA editor, you are likely to get into trouble. We have all been in a coding tangle before!

We know we have to take data from four sheets in the file. So, a sensible first step would be to set up a mechanism to loop through sheets in the file. It’s simple enough, and it sets us off in the right direction, and allows us (and our client?) to build trust and confidence. In the video, Chris uses a particular sheet referencing technique (using sheet index numbers) that allows us to quickly exclude sheets from the loop – since some of the sheets in the file do not contain data. We also look at how to set up the loop to cater for additional sheets in the file – remember our solution has to be flexible and scaleable.

With this relatively simple task complete, we are on our way! See you in part 2. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

Part 2 – Dynamic Range Referencing and With … End with

In part 2, we create a mechanism to loop through the data on each worksheet. Looping through ‘static’ data is simple enough – we have done that in other videos on the channel – but Eric’s briefing presents an additional challenge: how to loop through a variable amount of data?

What is meant by ‘variable’ in this case? As you can see in the download file, each sheet contains a different number of rows; further, Eric wishes to be able to add new sheets with any number of rows. What is required is a solution that can handle these different situations without the need for additional coding; what is needed is a *dynamic* solution.

The video explores how to create such a solution. We learn about the powerful .end(xldown) construct which selects to the end of a dataset and drives the dynamic mechanism. We use the ‘step into’ facility in the VBA editor to work through the code whilst looking at the Excel window to understand how it works, and to test the new mechanism.

Finally, we look at how to integrate With …. End With to simplify the code and to avoid having to select sheets. Selecting objects such as sheets and cells is a ‘direct referencing’ approach; routines built in this way can take some time to execute, and there is a more efficient approach.

We would much rather have a fast routine built on ‘remote’ referencing; ie. a routine that does not select sheets but uses sophisticated referencing to get the job done faster.

Part 3 – Loop within Loop and Countif

In the third video, we first implement a ‘loop within a loop’ – the powerful mechanism that allows us to get the first part of this job done. Conceptual thinking always comes first, so what exactly are we trying to do, and can we explain it clearly in normal language? Let’s try…

We already have one loop set up which loops through the worksheets in the file. The next loop, which operates ‘one level down’ and is embedded within the first, will loop through the name cells on each sheet. With the conceptual understanding clear, we can move onto the programming.

Chris uses a ‘for each object in the collection’ / ‘for – each’ loop to work through the name cells on each sheet. This type of loop could be applied to open workbooks, shapes on a sheet, sheets in a file, or another of Excel’s ‘collections’ of objects. It shows how, if we can navigate Excel’s object hierarchy (simply the ‘architecture’ of Excel), we can make coding easier.

In this case we loop through each cell in the range of cells that contains customer names, as defined by the dynamic range reference we created in the previous video. We test the mechanism using message boxes to extract information from the VBA editor during code execution. Job done – next!

The next step is to understand if a particular name appears in the list of names we are assembling. Remember, Eric has requested a list of ‘unique’ names, so names must not be duplicated. So, how can we check if a name already appears in the list? We set up a thing of beauty: worksheet formulae working together with Excel VBA to get a job done. In this case we create a ‘COUNTIF’ formula to establish if a value appears in a range; if the formula returns a value of zero, we know the name needs to be added to the list.

Onwards!

See you in part 4. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

Part 4 – Importance of Frequent Testing and a Conditional Statement

In part 4, we discuss the importance of frequent testing of computer code, and test what we have done so far to build confidence in its accuracy and robustness. It might appear that Chris is obsessive about testing … and you might have a point! But, this mindset is the result of hours spent working with Excel VBA.

The truth is that frequent, small-scale testing eliminates the need for large-scale, stress-inducing testing later in the process. Testing should imbue the development process, rather than being bolted on at the end. We again use a message box and the ‘step into’ facility to work through the code and understand what is going on. Don’t forget to view the VBA editor and Excel worksheet side-by-side, if possible; an additional screen can really help if you have access to one.

With the testing done, it’s time to transfer data from the four worksheets into the mechanism on the Analysis sheet. This is fairly routine (only because we have already done the hard work!) but we wish to do more; we wish to tell Excel to do something if the name appears in the list we are compiling. We wish to tell Excel to do something if a condition is met.

With this in mind, we implement a simple conditional statement, followed by an If-Then-Else statement to direct the code in one of two directions. With this mechanism in place, we are almost ready to assembly our list of unique entries … all at the click of a button.

See you in part 5. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

Part 5 – Combining A Worksheet Formula with VBA Code

Some more (yes, more!) testing is required at the beginning of part 5 to ensure the file is working as we expect. With a quick test completed, we set about creating a mechanism to ‘stack up’ entries in a list. This mechanism moves us towards our initial goal of a list of unique entries in the file.

Regular viewers of the channel will be familiar with the components: first, we use COUNTA to count the number of entries currently in the dataset. Then, we add the .offset method in the VBA editor; .offset allows us to move away from an ‘anchor point’ – the top-left corner of the dataset – by a certain number of rows and columns (anchor.offset(x,y).

By pointing Excel to the cell in the workbook that contains the COUNTA formula, we can make magic happen: a powerful interaction between .offset and COUNTA creates dynamic position control which allows us to create a list of unique entries at the click-of-a-button. A powerful mechanism indeed – check it out in the video!

As the thrill of this instant functionality subsides, we set about testing the accuracy of the new mechanism. Chris implements three (yes, three!) different tests to establish its accuracy, including some manual checking and an IF formula to check our dataset against a pre-prepared one. Only then are we ready to move onto the next step.

Hopefully you are understanding the importance of a methodical approach in Excel VBA coding, characterised by small steps and almost constant testing. This is the approach Chris applies in his actual Excel VBA development work – we hope it helps you shape your own practice.

See you in part 6. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

Part 6 – Dynamic Positioning with MATCH

We managed to loop through the file and build up the name list in the previous video, a feat in itself. But, what we are doing is more sophisticated than that! We know a name may appear multiple times; each time the name appears, we have to count it in the correct column in our table. So, we have to establish: “how far down the list does an entry appear?” Only with this knowledge can we count in the correct row in the table. Got the logic? Good!

What formula tells us ‘how far along’ a row or ‘how far down’ a column a particular value appears? It’s a Tiger favourite. The MATCH formula helps us establish position in this way. Chris implements the formula in the video – make sure you work along and implement the formula yourself.

Chris also discusses a design issue – the balance between the scope of the formula and worksheet efficiency: it is possible for the formula to reference thousands of cells which allows for lots of data; but, referencing large ranges is inefficient and will eventually slow down workbook calculation. So, there is a balance to be struck.

With the row position established, we now have to think about how to use this information in the code. Chris assigns the row position to a variable to make it ‘portable’; we then deploy the variable in combination with the offset method to create a powerful and dynamic position control mechanism – cool! We step through the code to test it and correct a slight inaccuracy with the anchor point of the offset mechanism.

Next, we have to think about column position – how to ensure we mark the correct column? Remember each column represents a sheet in the workbook. We can solve this one elegantly and efficiently without additional formulae or excessive VBA code, using the existing mechanisms – can you figure out how?
Chris demonstrates in the video and, initially, chaos ensures! We regroup and intentionally ‘get it wrong’ first, as an intermediary step, then fix the code to create glorious dynamic functionality. Ahhhh….

But let’s not forget about testing. At the end of the video, Chris shows how to use the Find and Replace facility to double check the counting mechanism we created. But that is not enough – how else might we test the accuracy? We review the ‘Use Indirect’ sheet to show that a formula-based approach generates the same results. Achieving the same result in three (yes, three!) different ways gives us some peace of mind. Onwards!

See you in part 7. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

Part 7 – Sum Values Using VBA

In part 7 of the series, we first look at ‘totalling up’ the entries, to tell us how many times they appear in the file. This is simple enough using an =SUM formula, but formulae are static and including them would detract from the dynamic quality of our solution – what if additional sheets were added to the file? The total column should ‘move across’ as sheets are added. Therefore, we opt for a VBA-based approach: this is fast, more dynamic and means no formulae ‘hanging around’ in this area of the worksheet.

After this initial discussion, we set about implementing the VBA-based approach. It involves an offset driven by the number of sheets in the file – this gives it ‘dynamic’ quality. Chris shows how to use ActiveWorkbook.Sheets.Count in combination with the offset method to get the job done. This setup should cater for additional sheets in the file. Cool!

With the position control tested, we consider how to ‘increment’ up the value in the total cell, so that it increases each time an entry is found in the file. The key concept here is ‘X = X + 1’. We apply this concept to the mechanism created previously to achieve the required effect.

Finally, we deal with the problem of ‘clearing out’ existing data from the file. We have now done this numerous times during the testing process, and it is getting onerous! Though a dynamic solution that would cater for very large datasets, would be ideal, Chris decides, for the sake of brevity, that a static solution is appropriate in this case.

We use the .clearcontents method (that retains cell formats), and create a new routine. It is sensible to ‘modulate’ code in this way (ie. create multiple, short routines) to allow us to access pieces of code from other macros. Chris compares the modulation to Ikea furniture!

An unwanted intervention from Siri completes the video 😊

See you in part 8. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

Part 8 – The Rhythm of Excel VBA Development

Excel development has … a *rhythm*?

Yes that’s right – Excel VBA development has a rhythm and, by understanding its rhythm, we can improve our coding practice. At the beginning of the video, Chris argues that progress through any computer-related task is not ‘linear’; in other words, rather than by incremental units of work, an Excel VBA task is characterized by periods of conceptualization, planning or tidying up, interspersed with periods of heavy coding and rapid development. These different phases that demand different skills and mindsets make up the ‘rhythm’ of Excel VBA development.

We did a fair amount of coding in the previous video. So, now is a good time to step back, take stock and do some tidying up. We can think of the VBA editor as our ‘workspace’, like a potter’s wheel, carpenter’s workbench or seamstress’s sewing machine (yes, Chris thinks of computer programming as a ‘craft’, like the above). An untidy workspace means a muddled mind and more frustration, just as a tidy one can inspire creativity.

So, how can we tidy up the workspace, in our case, the VBA editor?

First, we call on a technique that helped us earlier in the series. With … end with allows us to simplify code by minimizing text; this helps our mental processing – and the computer’s! Yes, code tidied up in this way is not only easier to read, it also runs faster. Cool! We look at two opportunities to apply with … end with, integrate the syntax, and do the testing.

With the simplification done, what else can we do to tidy up? After the speedy development of the last video, we need to catch up on annotations. These in-code comments help us understand, in English, exactly what is going on. Chris goes through the code line-by-line and describes what each is doing in layman’s terms. The result: a ‘commentary’ of what the code is doing. This might help your colleague or your client understand what is going on; as Chris explains, it also helps us when we come back to the code after some time away from it. Consider these annotations essential for stress-free coding.

See you in the next video! Don’t forget to leave a comment for Chris on the YouTube channel – Chris will get back to you.

Part 9 – Sheet Naming Conventions in Excel

In part 9 of the real-world VBA series, we tackle the next part of the briefing from Eric, our ‘client’ for this project. We have to list the customers in four groups, on four different sheets, according to how many times the customer appears in the file. For example, all customers that appear on two sheets should be listed together.

This involves creating new sheets to store this information. Chris talks about the value of concise sheet names; they are helpful because shorter sheet names mean more tabs visible at the bottom of the Excel window, and more efficient navigation through the file. With this in mind, we shorten the existing sheet names, and create new sheets with concise names and a consistent naming convention – that will help us with the coding later.

Here’s a challenge: can you spot the problem the new sheet names create with the analysis we created using the INDIRECT formula, earlier in the series? Not covered in the video!

With the new sheets created, named and re-ordered, it’s time to think about how to get the right names on the right sheets! As usual, we take a step back and try to get the concept, before starting the coding.
Our idea is to loop through the names on the Analysis sheet once again, and then to move the name to the correct sheet according to the value in the ‘total’ column integrated previously in the series – we told you it would come in useful later!

With the concept clear, we set about creating the required code. We ‘concatenate’ the value in the ‘total’ column for the customer with the letter ‘L’ to give us the sheet name, and then store the name in a variable. Now, it is a small step to transfer the data to the correct sheet.

The video is completed with a small-scale test to prove that the code positions the names on the correct sheets. In the next video, we will look at how to ‘order’ or ‘stack up’ the names to create the lists specified Eric’s brief. We probably won’t use any new coding – so how about trying to do that step yourself, before Chris’s video demonstration is released? Let us know how you get on in the YouTube comments!

See you in part 10.

Part 10 – Tidying Up Again!

Welcome to part 10 of the Excel VBA real-world example series. The setting is new, but the task is the same, and our client Eric is waiting. So, let’s get into it.

We first discuss a common occurrence in Excel VBA development – the creation of a new macro interferes with the execution of existing ones, causing problems. A rigorous testing approach includes not only testing of new code but also existing functions; so, our first job in this video is to get into the VBA editor to identify the source of the problem, and to get the previous functions working again. Thank you to channel viewers BlvdMinSS and Paul for pointing out the issue in comments under the last video (why don’t you leave a comment too, Chris will get back to you!)

The first issue is simple enough: because we changed sheet names in the previous video, the INDIRECT formulae on the ‘Use Indirect’ sheet are no longer accurate. Remember, we use these formulae to confirm the accuracy of the VBA code, since these formulae to the same job as the VBA code, but in a different way. Simple amendments to the entries at the top of the sheet resolves this one, and our powerful counting formulae are back. Cool!

The next issue is to update the original routine to make it work with the new setup. In the previous video, we added new sheets to the file; so, lines of code such as ‘activeworkbook.sheets.count’ require our attention, since they reference the number of sheets in the file. Some clear, logical thinking and a quick tweak to this line of code allows us to move on.

Finally, and most complicated, we have to find a way to exclude the new sheets from the analysis. Remember, our code loops through sheets that contain original data form Eric; so, we need the VBA editor to ‘ignore’ sheets that do not contain original data. A number of approaches are possible; Chris quickly implements one in the video.

With these three issues resolved, it’s time to test the code; then, we will be ready to move on!

Though this video does not seem particularly exciting in terms of content, it covers an important point in Excel VBA development, and coding more generally. Put simply, we are not always coding. Important phases in the development process involve planning, conceptualisation or, in this case, ‘tidying up’ after a spurt of coding. It’s all part of the rhythm of VBA development – try to embrace each phase and understand that these ‘metaskills’ will help improve your practice. See you in the next video!

Part 11 – Towards Completion?

We did not do a huge amount of ‘coding’ in part 10, so let’s get into some powerful VBA techniques this time. In part 11 of the VBA Real World Example series, we create a dynamic mechanism to make names ‘stack up’ on each sheet. This means that names that appear once in the file are listed on the 1L sheet, names that appear twice on the 2L sheet, and so on. We will use some of VBA’s most powerful constructs to get it done, so let’s get into it!

The key construct here is .end(xldown). This awesome VBA syntax takes us to the next empty cell; or, in other words, to the bottom of a column of data. If we can tell Excel to go to the correct list sheet and to add the name to the end of the column, we can get this task done for Erik. At least, that’s the concept. So, let’s try to implement it.

Chris works through the syntax in the video, combining the offset (remember, we are looking at position control here) method with .end(xldown) to get the job done. Multiple errors occur as we work through the code; no matter, this is completely normal and an important part of the development process, just be sure that your file is saved and backed up properly.

After some ‘manual’ testing to check the results against those generated on the Indirect sheet, we feel ready to move on. Remember, the best way to test something is to do it in two different ways and achieve the same result.

Some jobs remain. First, we have to clear data out from the ‘lists’ sheets. A dynamic approach – that would cater for a changing dataset – would be possible here; but we opt for simplicity and programme a sensible range (approximately 2000 rows) to be cleared using the .clearcontents method. Finally, we add a button to trigger the macro – since Erik would not want to have to enter the VBA editor to run the code!

The task is close to complete. In the next video in the series, we will perform some final (hopefully!) testing and discuss the strengths and weaknesses of our approach – see you then, and don’t forget to leave a comment below the YouTube video to let us know how you are getting on.

Part 12 – Hand over a file with good client communication

How to be a better Excel consultant? People ask me this question all the time! The obvious answer is ‘improve your Excel skills’, but this answer is not, in fact, the best – at least in my view. If you really want to boost the impact of your Excel consultancy, concentrate not on technical stuff but more on the soft skills that wrap around your coding and make it relevant to the real world. One of these soft skills is client communication. Client communication is particularly important at the end of the project to ensure a smooth handover and guarantee that the customer can get value out of the application.

We explore two ways in which you can communicate better with your client in this video. First, how to build client confidence in the code we have implemented in this project? Macros can be powerful but users, particularly inexperienced ones, can find them troublesome; when does the macro finish, for example? Let’s make this crystal (Christal?) clear for the user by flashing up a message to tell them the routine is finished. Let’s also use a dynamic mechanism to tell the user how much data has been processed. There, we have probably multiplied the real-world impact of this macro by communicating effectively and putting client needs front and centre – cool!

What else do we have to think about before handover to the client? It’s sensible to note the assumptions you have made during development and to be transparent about the weaknesses of the file. Customers can be amenable to some limitations if it makes the coding easier; most customers are impressed by the power of the coding and are prepared to make sacrifices elsewhere in return. So, ask yourself the question, do you need maximum flexibility? More and more coding may not create more and more value; it’s more likely to be a case of diminishing returns.

In the video, we look at how to interrogate code, identifying weaknesses and noting assumptions. This is where neat-and-tidy code that is well-annotated really helps (we told you it would!) I recommend actually listing the limitations and assumptions in the file to remind you to discuss them with the client. This should build confidence in the implementation (and in you as a person), and avoid annoying bugs and crashes later on.

This brings us to the end of the VBA real-world example series. I have done my best to communicate the approach that I use in my projects to create value for real people, in the real world! I would love to hear your impressions of the series. Also, let me know in the YouTube channel comments what else you would like to see on this channel.

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel Indirect Function Tutorial

Welcome to our Excel Indirect Function Tutorial!

BY CHRIS MORTIMER

Welcome to Tiger’s Excel Indirect function tutorial. The Indirect formula has super powers that could be a game-changer for you. This example is inspired by a recent work situation where the formula saved me a huge amount of time.

Click here to download the START file for the Excel Indirect Function Tutorial.
Click here to download the COMPLETED file for the Excel Indirect Function Tutorial.

How could we summarise Indirect’s ‘magical powers’. Well, let’s suppose you wish to apply a formula to multiple sheets. In my example, I wish to see how many times data appears on datasets from different US states. We can use Countif to do this, as Chris demonstrates in the video. And, voila! The analysis is complete.

Excel Indirect Function Tutorial Thumb

So, what’s the problem? The problem is that we need to apply the same analysis to more than one sheet. In the example we have four sheets, but you might have fifty, or more. Can you imagine building the formula fifty (fifty!) times, in order to apply it to different sheets. This would be intensely frustrating!

The fact is that there are too many people out there trying to do this kind of manual task in Excel. I wish a few more of them watch the Tiger channel 🙂

This is where Indirect changes the game. Indirect converts the contents of a cell into a text string and allows us to use the text string in a formula. Such a technical exposition does not sound exciting; so, how else can we express it? How about ‘Indirect allows you to input sheet names into column headers, and to directly reference the sheet names in formulae’?

Perhaps this sounds more exciting. No matter, Chris demonstrates how the formula works in the video. Correctly implemented (the formula is notoriously tricky to work with), Indirect allows us to control the sheet that a formula looks at by simply changing the value in a cell. Crazy! And incredibly cool.

The implications are significant. I managed to speed up a task that would have taken most an afternoon, and complete it in just a few minutes.

Did you manage to complete the task in the video? What have you managed to accomplish using the Indirect formula? We hope the tutorial was helpful for you.

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel Data Table Tutorial

Welcome to our Excel Data Table Tutorial!

Welcome to Tiger’s Excel Data Table Tutorial Series – the only Excel videos you will need to get you up to speed with this powerful – and often misunderstood – technique.

Click here to download the start file for Video 1.
Click here to download the completed file for Video 1.
Click here to download the completed file for Video 2.

What is a data table? Like so many things in Excel, its name does not help us understand its role. An Excel data table does much more than store ‘data’; it actually presents outputs from an Excel model. Applied correctly, it allows us to instantly test any number of model inputs, and identify the input(s) that create(s) the best, or ‘optimal’ output. It’s quick and powerful ‘what-if’ – analysis: what would the output be if this were the input value? So, I suggest it would be more accurately called an ‘Outputs’ table, or even an ‘Optimisation’ table.

Excel VBA Move Data Thumb

That’s exactly what an Excel data table allows us to do – quickly optimise a model. There is an important point to note here, we certainly need a ‘model’ to use a data table! A model consists of an input cell (or cells) linked to some calculations and an output. The 1st video features what Chris calls ‘the simplest possible model’; but, it is still a model because it consists of input, process and output (eg. 4 x 3 = 12).

Simply put, a data table automates the process of finding the best output from lots of inputs. It’s something we could do manually – inputting values into the model and noting down the outputs in Excel or somewhere else as we move towards the optimal solution. A data table does this for us at the click of a button, instantly tabulating outputs for any number of ‘candidate values’ that we choose ourselves.

EXCEL DATA TABLE TUTORIAL VIDEO 1 – CONCEPT AND SIMPLE EXAMPLE

As we see in the video, we can use a one-way data table to test a single input, or a two-way data table to test two inputs. You may also hear the terms ‘one-variable data table’ or ‘two-variable data table’ used. Both can be tricky to set up: a particular layout is required, the table must be properly linked to the model’s output cell, and we must understand the meaning of ‘row’ and ‘column input’ in the data table dialogue box (it’s confusing!)

Further, data tables are difficult to tweak / delete, you might be familiar with the ‘you cannot change part of a data table’ error message, for example. We deal with how to quickly delete a data table in the second video in the series.

Armed with this simple foundational example, you are ready to move onto video two where we explore a powerful real-life application of an Excel data table.

EXCEL DATA TABLE TUTORIAL VIDEO 2 – REAL-LIFE APPLICATION

First, thank you to channel viewer Milan for sending this one in. Milan is working in the garage door industry and wishes to find the best combination of two panels (of different lengths) for a specified door height. Without data tables, this kind of problem could take hours to solve!

In the video, Chris takes you through the model he built to give Milan an instant optimal answer for any garage door height. The secret: the power of data tables.

We know from part 1 that we cannot use data tables without a ‘model’. A model consists of input, process and output. In this case, inputs are a combination of the two panels, and the output is the garage door height that this combination creates. Simple arithmetic formulae comprise the ‘process’ because they do the working out. It’s a simple Excel model – they do not have to be complex!

Then, Chris goes through the setting up of the data table, emphasising the need to be steady and systematic in your approach. We also deal with the tricky issue of editing and deleting data tables; once implemented, they never seem to go away! Chris demonstrates how to do it.

With the two variable data table in place, it is possible to ‘optimise’ by simply looking at the table and doing some simple workings manually. But, we want more than that: we want a fully automated solution that makes the best combination absolutely clear.

To do so, we implement more formulae (=abs(), =min() and =match()) to identify the specific combination of panels that provide the best door height. Moreover, we display the combination at the top of the spreadsheet (everybody hates scrolling down to find important information), and even include useful information like excess height.

It’s an illustrative example of a simple and powerful Excel model, with data tables deployed to powerful effect.

So, did you manage to get the data table working? Can you feel the power of this awesome optimisation technique? Have you found an application in your work? Leave a comment on the YouTube video, Chris will get back to you.

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel VBA to Move Data Around a File – My Approach

A super powerful approach to use Excel VBA to move data around a file

Click here to download the Excel file for this series.

The super powerful Excel VBA technique I use to quickly and flexibly move data between Excel worksheets.

Moving data around an Excel file? It’s a topic we’ve covered before. And it’s worthy of further attention because so many people out there are trying to do it. This kind of manual, repetitive task takes time and can frustrate!

Excel VBA Move Data Thumb

First, I recommend checking out our Excel VBA for Post-Beginners series where we apply a basic approach to move data from a database to a report template.

This approach certainly works and is a great starting point for beginners. But what are the drawbacks? Well, if we wanted to tweak the code (for example, to add another piece of data to the report), we would have to access the VBA Editor to update the macros. This is not ideal – it is unlikely a typical client would be confident amending the process in this way. Is there a better way?

The approach I teach in this video series offers superior speed, flexibility and robustness. It is the approach I actually use on my Excel VBA projects, and it allows both programmer and customer to quickly update the report if required, without going into VBA. Sounds cool, right? Let’s get into it.

Excel VBA to Move Data Around – TUTORIAL VIDEO 1

In the first video, I explain the mechanism, and begin to set up a new mechanism from scratch, for demonstration purposes. Yes, it takes some time to set up (we actually do not do any exciting coding in this video), but the rewards are significant. The first step is to create a table on a supporting sheet in the file; the table displays information about where we want the data to go (destination) and where the data is located initially (origin). Sheet names and cells are required – you can simply type them in.

Yes, I am asking for some viewer patience here! But, this rather routine task creates magic later on. VBA will actually interact with this table to move data round as we require. This means that building up the process is as a simple as adding new lines to the table.

Further, any tweaks required later require just a simple adjustment to the table data. This is why programmers and clients alike should love this approach: after the initial code setup (we cover that in the next video) it facilitates powerful report configuration without having to open the VBA editor.

(If you really cannot wait for the next video, all the code is included in the download file – see if you can work it out for yourself!)

Excel VBA to Move Data Around – TUTORIAL VIDEO 2

In the second video, we begin to set up the short but powerful routine that makes the magic happen. It’s a concise macro with some tricky lines of code; that’s why it’s important to be steady and systematic in your approach, to avoid getting in a coding mess! (We’ve all been there…)

Crucial to the mechanism is a dynamic selection – a selection that expands as new rows of data are added. The first part of the video deals with this ‘dynamic’ code using an established Tiger favourite – Range(range,range). This construct, combined with .end(xlDown), creates the functionality we need.

Next we test the routine we have created. Yes, it’s tempting to simply plough on with the coding, but that might create problems later. We take the time to test the code, using a message box to flash up the cell value; we even add new rows of data to prove the routine is ‘dynamic’. Cool!

Excel VBA to Move Data Around – TUTORIAL VIDEO 3

In the third video in the series, we complete the mechanism and ‘play’ with it to understand its unique power. Before we get there, however, there is some intricate coding work to do. With this kind of complex syntax that interacts with objects in the file, the likelihood of errors (and stress!) is high. Therefore, or first step is to implement a simple line of code that ‘does a similar thing’, as an intermediary step. When faced with difficult coding always simplify, test, then build up.

With the simpler line of code in place, we can substitute in the more powerful constructs. In this case we replace normal references for the variable name; using a variable in this way can feel unnatural – but remember that this is a range variable, so it stores a cell address. That means we can use it to reference cells in the file. Cool! Chris incorporates this new element and tests. Finally, we ‘recycle’ a line of code that we know is functioning to complete the syntax; some adjustments to the offset method to get Excel looking at the correct cells completes the process.

What we have created is supremely powerful and flexible. No more editing VBA when we need to add a new item to the report; rather, we can simply add a new line of code to the backend table. Sweet!

Excel VBA to Move Data Around a File – Series Release Dates
Video 1 – 03/05/19 – https://youtu.be/XStFwJW-oCQ
Video 2 – 17/05/19 – https://youtu.be/v2FZcYcOSwA
Video 3 – 31/05/19 – https://youtu.be/156KqQZ-pgA

What did you make of the Excel VBA to Move Data Around video series? Have you used the approach yourself? Leave a comment on the YouTube channel and let’s start a conversation!

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel Spreadsheet Projects: Your Complete Guide

Here is your complete guide to Excel spreadsheet projects!

Click here to download PDF file from the video.

Contact us here.

Posted in Uncategorized | Comments closed

Your Weird Skillset is Your Best Asset

Let’s talk about unconventional career paths and how they can create magic!

Weird Skillset Thumb

If you haven’t taken a conventional career path you might feel frustrated from time to time. Are things really coming together? Are all these things I have learned actually useful? Am I a jack of all trades and master of none?

Such feelings can be particularly intense in social situations, around people with ‘normal’ jobs and those who are conventionally ‘successful’. They appear to take an interest in your funky project; but you feel they are just being polite. I personally dread the question ‘What do you do?’ It makes me shudder!

Though it’s still there, the social anxiety is less acute these days. Over the years I have come to realise that skills and experiences that seem varied, disparate or completely unrelated can synergise to give you unique competitive advantage. This synergy emerges over time and it is difficult to feel it happening. But it definitely happens, in the background, and can put you in a powerful position career-wise.

You might have watched Steve Jobs’ famous Stanford Commencement address. If not, look it up on YouTube. In the video, Jobs talks about his career and how he got his business started. One part in particular resonated with me.

Apple pioneered a unique and beautiful typography in its early systems. It’s one of the features that contributed to a long tradition of elegant design. The typeface created a point of differentiation between Apple and its competitors (until they copied it!)

Jobs is such an interesting character. I was fascinated to find out he took a caligraphy class in College, which is why he integrated the new lettering into Apple’s systems. Without the class, the typeface might not have been conceived, and Apple may never have carved out its market niche for premium products – who knows?

Why on earth was Jobs, a computer programmer, in a calligraphy class? It turns out Jobs got bored of his college programme and withdrew, but not entirely; he remained on campus and picked and chose the classes the interested him, following his instincts, interests and passions.

His aim was self-actualisation rather than anything more practical, so it is funny to think that he eventually helped Apple become the world’s most valuable company. He went about developing an unconventional and varied skillset, a weird skillset … that turned out to be his greatest asset.

Coming back to my humble career, I can say that things certainly got easier once I started the YouTube channel – it creates interest in the company, some enquiries about projects, and provides a very modest income stream. It’s now at the centre of the company’s long-term strategy. But, not everybody can be successful on YouTube (I myself have achieved very minor success); it requires a unique set of skills.

In the feedback I receive on the channel, people say that I speak clearly and that they can understand. I learned how to speak slowly and clearly, and in a manner that people from other countries can understand, through teaching in a high school in northern Japan for two years. In the context of the YouTube videos, it is something that makes the channel distinctive, at least to some extent.

I could not have started the channel if I had not learned VBA programming on the MSc I undertook after returning from Japan. Programming requires attention to detail; I was able to foster real precision in my thinking through studying Japanese for four years when I was resident there.

It’s funny that by teaching English and learning Japanese in Japan I was actually crafting a skillset that would allow me to run a YouTube channel about computer programming years later! It’s impossible to tell how your apparently disparate skills will synergise, and how your unique skillset will interact with the demands of the economy (who would have predicted a business model like YouTube 20 years ago?)

I like to think that somewhere deep in our subconscious we are guided by a force that sees how everything might fit together in the future. This is why, in my view, it’s a good idea to put the conventional career on the back burner, periodically and if circumstances permit, to pursue a hobby, interest or passion that you love, if only as a side project. Things might make sense later on – and you might end up with your dream job, or make a massive contribution to society with something unique (or both!)

Coming back from Japan I felt a little bit lost career-wise. I distinctly remember a couple of social situations where my insecurities were definitely exposed and I responded defensively to people about not wanting to do something conventional. I do regret this. I could not feel everything coming together; but, in the background it was, it’s just the circumstances were not right for it to bear fruit – but it did eventually and I am extremely grateful for it. A weird skillset turned out to be the best asset.

What did you make of this article? Leave a comment on the YouTube channel, we would love to hear from you!

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 Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

.Cells For Beginners

Let’s learn the Excel VBA .cells technique

Click here to download the STARTER file used in the series.

Click here to download the COMPLETED file used in the series.

The Excel VBA .Cells technique is incredibly powerful – and slightly confusing! Learn it and use it with this series of four videos.

TUTORIAL VIDEO 1

In the first video, we deal with the basic concept and apply .cells to select a single cell. The .cells technique uses numbers to reference cells in the spreadsheet; this makes sense for rows (we are used to using numbers to refer to them) but less so for columns. So, we first use an =column formula to show how Excel can reference columns using numbers. Cool!

After opening up the VBA editor, we attempt a simple application of .cells. Chris explains the basic cells(a,b) concept where ‘a’ determines the number of rows down, and ‘b’ the number of columns across. Armed with this concept, you can use .cells to select a cell in the spreadsheet. Remember to try different combinations – it should feel like you are ‘playing’ with the code; this really is the best way to build confidence.

Finally, we introduce the idea of the ‘anchor point’. By integrating a cell reference before .cells (eg. Range(“A1”).cells(1,1)), we can ‘anchor’ the cells technique anywhere on the spreadsheet. Chris demonstrates this by using A1 as an anchor point. Those experienced with .offset will notice a subtle but important difference in the referencing here, which Chris explains in the video.

Hopefully you are beginning to feel the power of the .cells technique! How did you get on, and what applications have you found for .cells in your work? Please leave a comment.

TUTORIAL VIDEO 2

Having learned the basic concept in video 1 (above), we explore a more complex application in video 2. We witness what Chris terms ‘Poetry in Motion’ – spreadsheet cells and VBA code working together to create dynamic functionality. A profound capability indeed – but how does it work in this context?

We are familiar with the .cells(rows, columns) basic construct. In the first video, we used ‘hard-coded’ values, inputted in the VBA editor, in the rows / columns components, to select cells. But we can do better than that – nobody wishes to access the VBA editor every time they need to tweak a function! In the second video, we substitute the ‘hard-coded’ values (which are best avoided in Excel VBA) for cell references.

What does this do? It means that we can control the selected cell *from the spreadsheet*, with no need to open the VBA editor at all! This interplay between the spreadsheet and VBA editor is what Chris terms dynamic functionality; it is a feature of many powerful VBA routines. In the video, Chris sets up the mechanism and does some testing and experimentation (‘play’) to build confidence in its robustness.

Ah, Poetry in Motion … 🙂

TUTORIAL VIDEO 3

So far in the .cells series, we have dealt with how to work with a single cell selection. No doubt this is useful (and fun!), but selecting ranges consisting of multiple cells presents new possibilities: it might allow us to work with datasets that change in size, for example – a common requirement in Excel projects. So, how would we select a range of cells using the .cells technique?

The key syntax is Range(Cells(a,b),Cells(a,b)) where the first cells construct represents the top-left cell of the range, and the second the bottom-right cell. This incredibly powerful construct allows us to do so much – but is very tricky to code. If you try to write it out without a steady and systematic approach, you are likely to run into trouble and get frustrated! Chris has been there so many times… 🙁

To avoid this, Chris builds up the code step-by-step, starting with a conventional range reference in VBA (eg. range(“A1:A10”)) that you are probably familiar with. After testing this, we can substitute in the more complicated syntax, once piece at a time, testing at each step, whilst steadily building up the final construct. Chris demonstrates in the video – work along with him, and don’t forget to do your own experimentation! ‘Play’ is key.

Quotable:
“Whenever I deal with complicated syntax, I simplify it; write something simpler first, then build it up.”

In the final video, we will aim to substitute the hard-coded values in the VBA editor for cell references. Can you imagine the kind of functionality that might result? The possibilities are mind-boggling. See you in the final video!

TUTORIAL VIDEO 4

Hopefully you are getting to grips with the Excel VBA .cells technique. You’ve learned the basics of one of VBA’s most powerful constructs. But, we’re not done yet!

We have previously used the syntax Range(Cells(a,b),Cells(a,b)) to select a range of cells. Though fun to play with, this in itself is not particularly useful. The reason? We are using ‘hard-coded’ values in the VBA editor. What is meant by this?

Put simply, any value in the VBA editor is ‘hard-coded’. This means that it cannot be changed without accessing the VBA editor and making manual tweaks. As such, it is hardly a satisfactory solution – can you imagine having to access the VBA editor to change a spreadsheet input, every time? It would be very onerous; we certainly cannot ask our clients to do this (because they think macros are magic!)

Rather than inputting ‘hard-coded’ values in VBA, we can point VBA to a cell in the spreadsheet and ask it to take a value from there. This is a profound capability that creates powerful functionality, as Chris tries to explain the video. In the video, we substitute the numbers in the VBA for cell references, and then adjust the cell references by changing values in the spreadsheet. Cool! When we run the macro, the selection is determined by the values in the cells we choose…

We could take things still further. We could point VBA to a cell in the spreadsheet that contains a COUNTA formula, for example. This kind of set up would allow us to dynamically define and update a range – the size of a database, for example. All using the .cells technique.

We hope you enjoyed the four Excel VBA .cells tutorial videos. Let us know what applications you have found, and what other Excel VBA tutorials you would like to see on the YouTube channel.

EXCEL VBA .CELLS SERIES RELEASE DATES

Video 1 – 08/03/19 – https://youtu.be/x_PCdMlO41M
Video 2 – 22/03/19 – https://youtu.be/tlmFmcimIwU
Video 3 – 05/04/19 – https://youtu.be/oW2ZqqArIhA
Video 4 – 19/04/19 – https://youtu.be/rf8rK7rzoU0

Playlist link:
https://tinyurl.com/y46hxw5h

These introductory videos will be supported by ‘long-play’ application videos (below) showing how to apply the technique to real-world problems. These will be viewable in the series playlist when released.

APPLICATION VIDEO 1

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

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

This video is a viewer request (thank you to Hugo) and an example of the power of the VBA .cells technique.

In the video, we combine .cells with two different kinds of loops to get a mind-boggling amount of work done at the click of a button.

The briefing asks for every sixth row to be copied to a different sheet for analysis. At the beginning of the video, Chris puts together the main elements of the routine (.cells, a loop, conditional statement), and introduces the VBA Mod function to identify every sixth row on the worksheet. Two integer variables are used to control position: they synergise beautifully with .cells to create the required mechanism.

Next, we explore how to delete ‘unwanted’ rows. Chris gets in a coding tangle and, in the end, implements two different loops to get the job done. The second loop uses Do Until to repeat an instruction until an empty cell is found, ie. until the bottom of the dataset. Though the code is functional, it is CPU-intensive, and takes a few seconds to execute. We conclude that moving target data to another area can be preferable to asking VBA to delete rows.

We hope this application video helps you feel the power of the VBA .cells technique. What have you managed to do using .cells? Leave a comment on the YouTube channel, we would love to hear from you!

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, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Your First Pivot Table

Let’s create your first pivot table

Click here to download the file used in the video.

Work along with Chris to create your first pivot table.

Excel Pivot Thumb

Pivot tables can be scary! But, if you are not using pivot tables, you are missing out on Excel’s most powerful data analysis facility. Not only can you produce powerful analyses with a few button clicks, but pivot tables will reduce sorting, filtering and fragmentation of data across sheets.

In the video, Chris shows you how to create a simple pivot table. The data comes from the YouTube channel and Chris wishes to examine two metrics at the same time: traffic sources and countries.

The first step is to visualise the pivot table: what should appear in the rows, what should appear in the columns, and what data should appear in the table? At this point, it is a good idea to write down the specific question you are looking to answer. Chris recommends using ‘good old pen and paper’ to formulate the key questions and (literally) sketch out the table layout.

Next, we can select the target data and insert the pivot table. A critical mistake at this point is to exclude the column headers from the selected data; make sure you include the column headers in the selection. The create pivot table dialogue box allows you to position the table on a new or existing sheet – we recommend using ‘new sheet’ for the timebeing.

Now, time to create the table! It can be confusing when you click ‘ok’, yet no table appears. Rather, we are confronted with a blank space labelled ‘pivot table x’, and an unfamiliar interface. Chris shows how to quickly manipulate the interface to get the analysis into the layout you require. Cool!

Did you manage to create the pivot table? What is your application of pivot tables, and how have they helped? Are you experiencing any particular problems? Leave a comment below 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 Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Uncategorized | Comments closed