Author Archives: Chris Mortimer

How To Make Money From Excel

CAN YOU MAKE A CAREER FROM YOUR EXCEL SKILLS?

If you’ve spent time building your Excel / Excel VBA skills, you might be wondering how Excel could push your career forward. How could you monetise your Excel skills? Could you create a part-time income from Excel VBA development, or could you even build a business around Excel? Welcome to How To Make Money From Excel!

In this series, I share my experience of 10 years building a business around Excel VBA. To help you better understand how Excel can push forward your career, we will explore 7 topics, across a 14-day period, from marketing your Excel skills to choosing the right people to work with and scaling a micro-sized business.

Picture

Each topic is supported by a static video and a livestream for you to interact with Chris and video viewers and discuss each topic, live! Yes, this 14-day course is structured around alternating livestreams (7) and static video (7) releases. But there is more: I have prepared forms for you to complete (naturally, these are Excel-based), one for each topic, to force you to commit some of your ideas to paper, which makes it more likely you’ll take action and move towards monetising your Excel skills.

Video 1 – Do I Need To Know Everything In Excel?

I argue that too many people spend too much time trying to learn ‘all’ of Excel. I have two problems with this: first – it’s impossible to master every function in Excel, such is the power and complexity of the software; second, mastering just one area of Excel is more than enough to start to make money from Excel. In fact, it can be counter-productive to know too much: you want to become the go-to guy not just for Excel, but for a particular problem related to its usage.

In the video, I point out five areas that you could focus on to push your career forward using Excel. The truth is, I have not mastered all five of these. In fact, I don’t think I have ‘mastered’ any, but I have developed competence in 3-4. Moreover, I understand that, as we will learn through this course, there are more important skills at play than formulae and coding. Yes, it’s not just about the technical stuff!

Make sure you download the support file and start thinking about which of these five areas could help you start monetising your Excel skills. See you in tomorrow’s livestream!

Click here to download the support file for this video.

Video 2 – How Should I Market My Excel Skills?

No matter how ‘good’ you are at Excel, you have to be able to market yourself in order to freelance successfully or even grow a small business. In this video, I outline 5 things that you have to do in order to start making money out of your Excel / Excel VBA skills.

1) Be The Excel Guy
Tell people you love solving problems in Excel. Start close to home; in fact, start literally in your home by telling your family or housemates that if they have an Excel problem, they should come to you. Then work from there!

2) Create Online Content
Yes, you have to be creating free content. It does not have to be video (podcasts are a big growth area at the moment) and the content does not have to be ‘polished’ – you could document the process of starting as an Excel freelancer on your smartphone, for example.

3) Overdeliver On a Project
If you can complete the first two steps and deploy some patience, enquiries will follow. Yes, a project! This is the most important project you have ever done; make sure you complete it to the best of your ability and that your client is happy.

4) Promote Your Success
Now is the time to tell the world about your Excel project (3). Can you get shareable feedback (if not, why not?) from the client to add realism? Disseminate on the platform you have already established, however modest it may be at present. Tell anybody else who will listen, too!

5) Be Patient And Strategic
This is going to take time and you need to have a strategy for dealing with tough times and negative emotions. If you can deploy patience and take a strategic perspective, however, the rewards are significant. Good luck!

Click here to download the support file for this video.

Video 3 – How To Manage A First Client Meeting?

A prospective client meeting can be nerve-wracking! In this video, I outline 5 points to help you make your first client meeting a success.

1) Be Nice
Unfashionable though it might be in the current business and political climate, being nice is important. In fact, I believe you only have to do two things in business: be competent and be good to be around.

2) It’s Not About You
Yes, it’s great to have a client meeting! But the only way you can succeed from now is to convince the client that the project is about them. Have at least some questions prepared.

3) Translate To Techniques
Probably the most difficult part of a prospective client meeting is live translation: the process of understanding what the customer tells you and matching it to available Excel techniques (that you can actually use!) It’s tricky, but it gets better with experience.

4) Technical Stuff Doesn’t Matter
Yes, we love VBA and the host of other cool functions that Excel offers, but your client probably is not interested. Focus on how the techniques save time, money and stress for their business.

5) Leave Costings Until The End
Know your costing strategy; but, only bring up the subject when it is absolutely clear how the project will help the client organisation.

Click here to download the support file for this video.

Video 4 – How To Manage An Excel Development Project?

Without effective project management, your new client may never feel the benefit of your cool Excel / Excel VBA skills! Here’s five tips for making your Excel development project a success.

1) Keep Commitments
Make a small commitment in the first meeting. This could be sending the customer some notes from the meeting, a project plan or a specification for the new file. Make sure it is an easy commitment to keep and then, crucially, make sure you keep it! This will begin to foster trust in the relationship and confidence in the project.

2) Use Video
Use your capability in video creation (remember, you have already created a lot of online content!) to support the project. A video walkthrough will make the file easy to understand and ensure the customer is using all of its functions.

3) Seek Out Criticism
The client will not like everything about what you have done. Don’t worry, those criticisms are actually gold dust to us as consultants. Responding to criticism properly will not only optimise the implementation for the client, it will build confidence in you as somebody who is easy to work with. Don’t just passively consume criticism, actively seek it out!

4) Be Flexible
One thing you can guarantee is that as the client appreciates the possibilities of Excel / Excel VBA (because of your brilliant work to date!) new ideas will be mooted. Have a strategy for responding to these: yes, the general direction of the project needs to be agreed in advance but showing some flexibility will create better results for the client. And that’s why we do this job, right?

5) Think Long-Term
You want your customer to keep coming to you for Excel work, so how should the relationship work long-term? Many managers view ongoing maintenance contracts with suspicion, though this could be an option. Could you shift to some kind of monthly retainer payment, for example?

Click here to download the support file for this video.

Video 5 – Excel Development: Who Should You Work With?

Yes, it’s important for us to put ourselves in front of people as Excel developers. At the same time, we should apply our own filter to understand if prospective clients are likely to be good, long-term customers.

1) How Did They Find You?
Knowing how the prospective customer found you tells you a lot about their motivations – make sure you ask them!

2) What Systems Are Currently Used?
What is the current data landscape in the organisation? If a new system is being introduced for example, how might it impact on your project?

3) Do You Have A Champion?
You must have somebody to advocate for Excel (and for you!) in internal meetings. Will your champion work with you on the project? If not, who will?

4) How Will The File Be Used?
We love Excel, but it certainly has its limitations, like any other software platform. Lots of collaborative / remote usage of the file, for example, could be problematic, particularly if you plan to use VBA. Try to uncover these potential issues early on, so that you are not wasting your time!

5) Can They Pay?
The all-important question! Do not shy away from it and bear in mind there are things you can do outside of the meeting to establish if the organisation has money to pay.

Click here to download the support file for this video.

Video 6 – The 3 Seasons Of Your Excel Career – Are You Ready To Move To The Next Level?

You have to move through 3 phases (or ‘seasons’) to make Excel really work for you in your career. In this video, I try to describe the main features of each season and, crucially, how to move to the next season and make more money from your Excel skills.

Season 1: Hobbyist
If you are falling in love with Excel (or your particular area of specialism within Excel) and are just beginning to think about the possibility of monetising your new skills, you are in the hobbyist season. The key question at this stage: do you *really* love Excel development? A ‘project’ means doing an Excel implementation for somebody else – do you love that process too?

Season 2: Part-Time
In this season, you are getting paid to undertake some Excel projects, but the bulk of your income comes from another source – perhaps your regular, full-time job. One critical question at this stage: can you achieve a flexible working arrangement with your current employer, to free up more time to pursue your Excel development ambitions?

Season 3: Full-Time
At this stage you earn the bulk of your income from Excel development. You have to be ready for the tough times too, so you should be working on online content and passive income streams – perhaps you can establish at least one of those before going full-time? If all is going well, how can you scale your business?

Click here to download the support file for this video.

Video 7 – Can You Scale Your Excel (VBA) Consulting Practice?

It’s great to have customers and to be getting paid for Excel projects. But, at some point, you will want more! Is it possible to achieve scale in an Excel VBA consultancy? In this video, we discuss 5 key issues that you’ll have to consider if you wish to transform your job into a business.

1. Can You Segment Your Customers?
Can you group customers by type – repeat customers, one-off projects and occasional clients, for example. Different customer groups have different requirements and place different demands on your time and resources.

2. What Are You Busy Times?
If you have segmented your customers, you can begin to establish when you’ll be busy delivering work, and when you can undertake important strategic work to grow your business.

3. Do You Have Scale?
The holy grail! Do not design yourself a job – design a business. Do not hard wire limits to growth into your business model. Can you generate income without having to physically deliver the work yourself?

4. Should You Take On Others?
Others could be employed or contracted to support the core offering or to assist with other functions of the business. To what extent get you let them ‘get on’ with delivering for your customers?

5. Can You Scale Online?
Online activity probably represents your best opportunity to transform your job into a scaleable business; are you fully exploiting the opportunities online?

Click here to download the support file for this 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

Excel VBA Absolute Beginner Course 30 For 30

YOUR EXCEL VBA ABSOLUTE BEGINNER COURSE

This course, delivered live and interactive, is aimed at absolute beginners in Excel VBA. Building from the basics, we will explore the power of Excel VBA and appreciate the transformational effect it can have on people’s work. I will show you the techniques I use to deliver massive value for my clients in the real-world context using Excel VBA, and give you demonstrations to get you using at least some of them.

Picture

Yes, I cannot make you a computer programmer in 15 hours but I can help you appreciate the possibilities and equip you with the basic tools and concepts. Welcome to the 30 for 30 Excel VBA Absolute Beginner Course!

Video 1

In part 1, we will look at how to set up your computer for Excel VBA and create your first custom-coded Excel macro. Yes, you’re now a computer programmer!

Video 2

In part 2, we will explore how to work with ranges or ‘cells’. These are clearly a fundamental part of Excel so getting VBA to work with them is essential. We will build from the basics to some more advanced examples. Remember to ‘play’ with the examples Chris gives you to consolidate your learning!

Video 3

In part 3, we will get acquainted with ‘variables’ in Excel VBA. First, what on earth is a ‘variable’, and how can they help us in Excel programming? Then, we will define a variable and use to store information and perform some basic calculations.

Video 4

In part 4 we look at position control. Excel gives us cells to work with; ability to navigate these cells is one of the key skills of Excel VBA programming. We will get to know the offset formula and then attempt to transfer this knowledge into VBA to begin to control position like a pro.

Video 5

In part 5 we will up the level of difficulty and of programming power with loops. A loop is a repeated set of instructions. They are an order of magnitude more powerful than what we have learned so far and, as such, have to be handled with care! We will learn the basics in this stream – be prepared for a crash or two but don’t worry, it’s all part of the process!

Video 6

Conditional statements are a key part of Excel VBA and of computer programming languages more generally. Suppose we want the code to go one of two ways depending on something happening in the spreadsheet – a ‘condition’. In the stream we will explore how to do this, building from a simple to a more advanced example. I hope you are feeling the power of Excel VBA!

Video 7

Have you ever used Excel’s macro recorder to record and ‘play’ a macro? The macro recorder is such a useful facility for the Excel VBA learner. In this stream, we will look at how to record some code in the macro recorder, then tweak and scale it up to get a huge amount of work done at the click of a button.

Video 8

We’ve already dealt with and applied one type of loop – the for / next loop; check out the previous streams for that one. Excel VBA offers different types of loops that help us get different jobs done and create supremely powerful routines. In this stream we will look at for / each loops and how they can help us get things done in Excel.

Video 9

Today we’ll deal with the third and final looping technique in Excel VBA. Remember, a loop allows us to repeat a set of instructions any number of times; as such, they allow us to get HUGE amounts of work done in Excel. The Do – Until loop offers new possibilities in terms of flexibility and exit conditions. It’s also more likely to crash – so watch out! Always save the file before running VBA code 🙂

Video 10

In today’s stream I will first show you a real-world application of Excel VBA that should get you excited about the possibilities. Then, we’ll look at manipulating objects such as shapes in spreadsheets, and a tricky problem that we will tackle over the next few streams.

Video 11

I would be short-changing you if I claimed all you need to know is each Excel VBA technique in isolation. In reality, we have to know how the techniques combine together and, crucially, how to apply them to real-world problems. During this phase of the course we will explore more real-world applications (from Chris’ work) and tackle problems that require combinations of techniques. Today, let’s get into the traffic light simulator!

Video 12

In part 12 we explore how to use conditional statements to programme a reasonably complex mechanism – a set of traffic lights! Yes, these traffic lights will not be fit for real-world usage but attempting to programme the sequencing is a great learning opportunity. Let’s see if we can get it working, and don’t forget to show somebody else YOUR complete Excel VBA traffic lights!

Click here to download the file used in the video.

Video 13

Let’s get in to part 13! So, our traffic lights are working well, but we now have a new client requirement to deal with – to conceal the ‘state’ cell. We know variables offer another place to store information away from the user – but how to apply them in this context? In this stream, we learn how to use variables to pass information between macros, and even to store information once a routine has run.

Click here to download the file used in the video.

Video 14

Ok, so it’s been tough going the past few streams. We’re learning some really sophisticated techniques now, and combinations of them, so it’s normal to not understand everything. Give yourself time, remember you can watch the replays AND that you don’t need all the techniques anyway. Today’s stream is reasonably technical but tomorrow’s is not – I promise something different then! In part 14, we look at how to apply application.worksheetfunction to access a worksheet formula from VBA and get a job done at the click of a button.

Click here to download the file used in the video.

Video 15

Halfway! Today we’re doing a more relaxed stream. I will do a short overview of the course so far and answer queries that I’ve received. Hopefully the stream will act as a useful recap and a reminder that you don’t need to know ALL the VBA techniques. Then, we’ll be ready to press on with the second half the of the 30 for 30 Excel VBA Absolutely Beginner Course.

Video 16

Right, the second half of the programme, and the beginning of your Excel VBA consultancy career! Yes, today you will be presented with a short ‘briefing’ to replicate a real-world challenge: will you be able to deliver an Excel VBA-based solution for our ‘customer’? Today, we’ll focus on planning and conceptualisation for this task, and a basic mechanism (and something completely new for 30 for 30) to get us started.

Click here to download the file used in the video.

Video 17

In part 17, we will start coding the task that we planned and conceptualised in part 16. We zoom in on the major coding challenges and deal with at least one of them in this stream. We will focus on how to use constructs such as .end(xldown) to create dynamic routines that will keep you and your customers happy for years to come!

Click here to download the file used in the video.

Video 18

Having dynamically defined a range of cells to work with in part 17, we will continue our application of Excel VBA in part 18 by looping through the range of cells. We’ll explore how to tackle our next coding challenge: how to establish if a worksheet with a particular name already exists in the file. It will involve some kind of ‘loop within a loop’ type setup – cool!

Click here to download the file used in the video.

Video 19

In part 19 we will explore how to add a sheet to a file using Excel VBA in the context of the real-world type task we are undertaking in the second half of the course. This means dealing with the challenge of generating the code – will it be recycled, recorded or organic? Join us at 1600 to find out.

Click here to download the file used in the video.

Video 20

Part 20! Let’s get on with our real-world type Excel VBA task. Yes, we have succeeded in creating a sheet for each spending category; but – this poses a new problem: how to delete previously created sheets, a potentially time-consuming task? We will work out how to do this at the click of a button in this stream, and then consider our next steps …

Click here to download the file used in the video.

Video 21

In part 21 we will explore how to move data around sheets in a powerful and scalable way. We now have a sheet for each category – so how to transfer data onto each sheet? A number of issues must be solved, including how to dynamically position the data on each sheet to get the next empty row. See you in the stream!

Click here to download the file used in the video.

Video 22

So, we’ve managed to transfer some data onto the destination sheets. This gives us some satisfaction as a programmer, but is not particularly useful for the end user. In this stream, we apply advanced VBA position control techniques to transfer data from multiple cells to the destination sheets. The techniques and concepts should not be new – but the application might be! See you at 1600.

Click here to download the file used in the video.

Video 23

Our solution to the real-world type problem we have been dealing with is certainly accurate and functional, but lacks efficiency and ‘slickness’. In this stream, we will consider an alternative approach and look at how to create code to sort datasets. We’ll record some code then explore how to tweak it to get it working perfectly. See you at 1600!

Click here to download the file used in the video.

Video 24

After improving the efficiency of our routine in the previous stream, now we’ll explore how to implement a custom sort button. Sorting data (and interacting with sort dialogue box) is very ‘clicky’ – so why don’t we automate it with Excel VBA? In this stream, we’ll look at how to create a custom sort function in which the customer can choose the sort column. Cool!

Click here to download the file used in the video.

Video 25

Part 25! In this stream we’ll implement a mechanism to choose a sort column. This should (finally) complete our custom sort functionality. Once this is done, we’ll look at how to use Excel VBA to format reports and create a professional look – all at the click of a button, of course. See you at 1600!

Click here to download the file used in the video.

Video 26

In part 26 we’ll explore how to complete our real-world type task by using cell formatting to make the reports look neat and tidy. We’ll look at at least one approach and how to get it working, at the click of a button, in the real-world context. More at 1600 UK time!

Click here to download the file used in the video.

Video 27

Can we get this formatting macro working? In part 26, we recorded some formatting code and created the framework for the formatting macro that should get the reports into a neat-and-tidy format. Can we bring everything together today, and get it all working at the click of a button, with a reasonable level of efficiency? Join us at 1600 UK time to find out.

Click here to download the file used in the video.

Video 28

In part 28, our final ‘technical’ stream, we’ll look at an alternative approach to this data manipulation problem using a ‘Do – Until’ loop. Yes, we have got the job done with other approaches, but ‘Do – Until’ offers new possibilities for the VBA developer, and this example gives us an opportunity to explore at least some of them. See you at 1600!

Click here to download the file used in the video.

Video 29

Here we go, part 29! In this stream I will attempt to inspire you to kickstart your Excel VBA journey by creating some real-life applications. I will show you 2-3 of my real-life projects and point out the key features and some of the interesting VBA-based mechanisms that make it all work in the background. See you at 1600 UK time for the penultimate part of 30 for 30.

Click here to download the Excel offside game.

Video 30

We made it! The final session in the series. In this video, I’ll answer viewer questions about 30 for 30 and Excel consultancy more generally, and show you some of my top comments relating to the series. What a privilege it has been to be your guide for the last 30 days: now you have to find the confidence and motivation to create your own magic using Excel VBA. Let’s try to get you there with this final session!

———————————

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

Members Monday Materials

FIND MEMBERS MONDAY MATERIALS HERE – SCROLL DOWN

Members Monday Content Navigator

MM logo

Click here to download the latest MM Content Navigator.

The content navigator allows you to quickly find a topic in Members Monday, and to navigate to a particular point in a particular video.

You can use it to generate customised training for yourself on particular topics or techniques.

We are looking to improve the Content Navigator all the time, so let Chris know if you have any improvement ideas.

MM19 Files

Click here to download the data file for the stream.

Click here to download the completed file for the stream.

MM20 Files

Click here to download the start file for the stream.

MM21 Files

Click here to download Ian R’s homework

Click here to download Iain L’s homework.

MM22 Files

Click here to download the starter file for session.

Click here to download the end file for session.

MM23 Files

Click here to download Iain’s homework file.

Click here to download Jazfin’s homework file.

Click here to download the starter file for the session.

Click here to download the end file for the session.

MM24 Files

Click here to download Iain’s homework file.

Click here to download the starter file for the session.

The starter file for this session is the same as the end file for the previous session.

MM25 Files

Click here to download Iain’s homework file.

Click here to download Jazfin’s homework file.

———————————-
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 Excel Guidance Articles, Uncategorized | Comments closed

Excel VBA Position Control Mastery

EXCEL VBA POSITION CONTROL IN 13 EASY MACROS

Excel VBA position control is one of the main skills of the Excel VBA programmer. Excel gives us a grid consisting of rows and columns; how well we can control position on that grid is a key determinant of the impact of our Excel VBA implementations.

Click here to download the file used in the video.

Excel VBA Position Control Thumb

From a practical perspective, position control allows us to work dynamically with datasets in Excel; that means being able to work with a dataset that might change in size – as almost all datasets tend to do in Excel! These skills will add the wow factor to your implementations – your colleagues will ask you ‘how did you do that?’

How did I determine the content for this video? I found that this bundle of position control techniques helped move me from intermediate to professional level as a programmer. I have translated them here into 13 one-line macros that might take your Excel VBA to the next level.

We’ll work from simple cell selection using a static reference all the way to dynamically selecting a range of cells. Don’t forget to leave a comment to let me know what you think. Welcome to Excel VBA Position Control Mastery!

Excel VBA Position Control Techniques Breakdown:

00:15 1. Simple Cell Selection
Let’s start with something we know – a simple cell selection with a static reference.

00:32 2. Select Last Cell In Dataset Using .End(xldown)
Here’s our first piece of magic syntax – .End(xldown) allows us to select the last cell in the dataset; in other words, the cell above the next empty row. Look out for its partners in crime .End(xlup), .End(xltoright) and .End(xltoleft).

01:16 3. Select Multiple Cells Using Range(Range,Range)
Ah – a Tiger favourite, the powerful Range(Range,Range) referencing technique to define a range of cells. We begin with static or ‘hard-coded’ references – make sure you ‘play’ with some different cell addresses to get a feel for how it works.

02:07 4. Select Multiple Cells To The End Of A Dataset
Next, we combine Range(Range,Range) with .End(xldown) to dynamically select a dataset. Yes, this is a ‘dynamic’ technique, so if entries are added at the bottom, the code still does the job. As we’ll see, the techniques create powerful synergy as they combine together.

02:44 5. Select The Last Row On A Worksheet With Simple Referencing
How many rows are there in a spreadsheet? It’s something I learned on my Masters! Here I demonstrated the tuition fees did not go to waste…

03:51 6. Use Rows.Count With A Message Box
There is no need to manually input the number of rows (or indeed to remember the number) because Excel already knows – we just have to ask Excel to tell us using VBA. Here we combine the Row object and .Count method to externalise the number of rows in the spreadsheet – make sure you impress your friends with this one.

04:28 7. Use Rows.Count To Select Last Row Dynamically
Now we can combine the previous two techniques together to select the last row dynamically – no hard-coded references needed!

04:57 8. Use Columns.Count With A Message Box
We have seen how to do this with rows – can you do it with columns? What difficulties might counting columns present that counting rows did not? Hhhhmm …

05:20 9. Use .Cells Referencing Technique
Here a new technique arrives to save the day! .Cells referencing uses numbers (as opposed to alphanumeric references) to refer to cells. Let’s understand the basic concept with some simple examples.

06:16 10. Use .Cells To Select Last Column Dynamically
We combine together the previous techniques to once again select a cell far, far away without any ‘hard-coded’ values. Now, the big finale …

06:46 11. Work From Bottom Of Worksheet To Select Last Row
The code here looks tricky but only comprises concepts we have practised above. What does it mean in a practical sense? This approach allows us to work with datasets that might have empty rows in them.

07:56 12. Work From Right Of Worksheet To Select Last Column
This approach also allows us to work with datasets that might have empty rows in them.

08:51 13. Punch The Air – Full Dynamic Dataset Selection
The actual approach I use to work with datasets dynamically using Excel VBA. Make sure you give it a try!

Were you able to follow this video? Do you feel you have achieve a measure of position control mastery in Excel VBA? Or was it a bit too much to take in? Either way, let me know in the YouTube comments, I 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

9 Reasons (NOT) To Do An MBA

SHOULD YOU DO AN MBA? AN MBA LECTURER DISCUSSES 9 COMMON REASONS

Should you do an MBA? It’s an important decision that requires serious thought. In this video, I break down 9 reasons for doing an MBA based on my experience as a post-graduate business student and MBA lecturer. I give my verdict on each – I hope it helps you decide if an MBA is for you.

MBA Thumb

1. An MBA Will Make Me An Entrepreneur 00:30
No doubt some of the MBA content (eg. entrepreneurship, innovation) is relevant to aspiring entrepreneurs, but other modules might seem somewhat pedestrian. Might you be better off investing time and resources in actually starting your business (and learning all the tough lessons you’ll have to learn) rather than doing an academic qualification?

2. I Need A Business Conversion Course 01:20
This is why I did my MBA-type qualification (actually a specialist MSc) – to fill knowledge and skills gaps in business and to allow me to build confidence in the professional world. For me, it was a big success! I did find what I ‘got out of the module’ varied greatly from one module to the next. But, a single module can be career-changing; I first learned about Excel on my MSc programme. Make sure you do your research about the specific modules.

3. I Want To Learn The Latest Business Theory 02:33
It’s likely you’ll cover some cutting-edge business theory on an MBA programme. But, the truth is that the precise content and focus of any MBA programme is driven by staff availability in the faculty. No institution can field subject superstars in all areas at any given time. Therefore, expect some more traditional, foundational (filler?) content too – topics that are probably covered in A-Level Business Studies. Porter’s Five Forces, anyone? 🙂

4. I Want To Meet Industry Experts 03:34
I find that the level of industry input varies from one MBA programme to the next and that students can be disappointed with this aspect of MBA delivery. The idea that academics are regularly ‘brought in’ by companies for their expertise is, in my experience, not exactly true – so links with industry may be less strong than advertised. Though plenty of students do their final projects for a company (often their own,) I recommend asking some direct questions of the institution to establish the facts about industry involvement.

5. I Want To Learn About Business 04:51
Yes, you should learn something about business on an MBA! Just bear in mind that the modular structure of an MBA is not necessarily reflective of how organisations work in the real world. This ‘siloed’ delivery of content has been criticised by management scientists such as Russell Ackoff and, in my experience, can frustrate more holistic and creative thinkers who naturally think laterally from one MBA topic area to the next.

6 I Want To Learn Technical Skills 05:46
The truth is that there is little technical content on MBA programmes. This is because entry requirements need to be low to generate demand and appeal to a broad audience. I am not surprised some students complain about a complete lack of ‘hard’ skills on the MBA programmes. If you want to learn programming or financial modelling, for example, a specialist MSc programme is probably a better choice.

7. I Want To Build My Network 06:37
You’ll meet lots of people on the programme and I am absolutely sure an MBA will create networking benefits for you. But, it’s a rather expensive way to network! If networking is your sole objective rather than a side-benefit, might there be more efficient ways to achieve this? I have spoken to plenty of students who do an MBA primarily for the networking, however.

8. I Want The MBA On My CV / Resume! 07:27
Plenty of people do the MBA simply to get it on their CV. I know, as a lecturer, they are easy to spot in the classroom – the student who turns up at the end of the module and only wants to know about the assessment! Yes, you are entitled to do an MBA for this reason, but your fellow students and lecturers will detect your lack of genuine engagement. At least question your assumptions – do you really need an MBA?

9. I Want To Meet Leading Academics 08:27
You’ll meet a good range of academics on your programme and you’ll have to develop relationships with at least a few of them – particularly your dissertation supervisor. Bear in mind that teaching is not the ‘glory job’ for many academics who would rather be publishing papers or speaking at conferences. Don’t expect all of them to be super enthusiastic about the teaching.

———————————

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 Use Excel VBA Code – Real Example S2

How To Use Excel VBA Code To Speed Up Complex Manual Work

Welcome to the Excel VBA Real Example Series – season 2. If you are interested in using Excel VBA to speed up manual work, this series is for you.

In the Excel VBA Real Example Series, we take a file from a viewer of the channel and demonstrate how to use Excel VBA to get an annoying manual task done ‘at the click of a button’. Yes, with Excel VBA you can get those jobs (that you are spending minutes, hours or days completing) done at in just a few seconds, such is the power of Excel VBA. So, let’s get into it!

Click here to download the ‘New Data’ file.

Click here to download the ‘Old Data’ file.

Click here to download the ‘Product Data’ file.

Excel VBA Example Picture

Thank you to channel viewer Chahin for sending in this example. We’ve selected it because it’s typical of so many examples of Excel-based work that we have seen over the years.

We have an old and a new data file, and the question is simple: which entries appear in the old file, which entries appear in the new, and which entries appear in both? Then, the entries should be checked against a product data file for any inaccuracies in the record. It’s typical of manual work in Excel that can be frustrating. We’re using a small dataset for learning purposes, but this kind of task would be a nightmare with a larger dataset, so let’s learn how to use Excel VBA to get it done.

Part 1 – Planning and Conceptualisation

In part 1, we discuss the task at hand, and do the all-important planning and conceptualisation. This may not sound very exciting and many people miss out this stage altogether in their programming; to do so often creates problems later. We argue that conceptual rather technical issues account for most of the problems people experience with VBA code.

Do you get the concept? Can you explain in plain English (or your own language) how the code should work? If you cannot, you are likely to run into problems later.

In the first video, therefore, we make sure the concepts are clear by using ‘pseudo-code’ (descriptive comments in the VBA editor) to work through the task ‘step-by-step’, making notes as we go. This lays an important foundation: when we begin coding (which we promise we will start in the next video!) these comments (or ‘annotations’) act as an important guide, saving mental bandwith for the technical component – in other words – the actual VBA coding.

Make sure you download the three files and work along with Chris – can you get a clear idea in your head how this routine is likely to work? What are the key features – loops, conditional statements etc.? Try applying this deliberate planning approach to your day-to-day VBA coding – we are confident you will see improvements, and feel lower stress levels!

Part 2 – Interacting With Multiple Files With Excel VBA

In video 2 of the Excel VBA Real Example series (season 2), we look at how to interact with multiple files using Excel VBA. Things tend to get very complicated very quickly when working with multiple files. So, it’s sensible to implement checks before the code runs to check that the right files (and the right number of files) are open. This *should* help us avoid complete chaos later!

We look at how to do this by harnessing the Workbooks collection. First, we ask the VBA editor how many files (or objects) are in the collection; in other words, how many files are open? Then, we loop through the collection, checking against the cells in the spreadsheet and the name of the file that contains the code, to ensure the correct files are open.

Only if these conditions are met do we proceed with the coding to do the analysis on the files. At all times, we make sure the user is informed by using (dynamic) message boxes to convey important information; which file is problematic, for example? We highlight this as one way to make yourself stand out as a programmer: effective communication with the user.

Did you manage to get the code working too? See you in the next video!

Part 3 – A Sophisticated File Validation Mechanism

In video 3 of the series, we look at how to create a sophisticated validation mechanism to check the right files are open – and no others! We use VBA to check through the open files and ensure each (that is ‘supposed’ to be open) is open in order to complete the task. This involves some advanced coding: we have to implement an IF statement with multiple conditions, for example, and use another dynamic message box to inform the user of what is wrong should there be a problem. Remember – sophisticated user communication is a great way to make you stand out as a programmer.

The structure we set up is what Chris uses on all Excel VBA projects involving multiple files. These projects can be complicated; one way to manage the complexity is to ensure that the right files are open (and closed!) This should keep the stress levels down and allow us to focus on creating value for the client rather than ‘getting things to work’.

Part 4 – Time To Tidy Up!

In the fourth installment in the series, we deal with the non-glamorous but essential job of ‘tidying up’. Yes, alongside planning and a bundle of other skills, tidying up is one of the key competencies that nobody really talks about – but which determine your effectiveness as a coder. Don’t worry, you can learn all of these skills, either implicitly or explicitly, on the Tiger channel!

The key idea is that, by keeping stress levels down, coding sessions can be longer and more productive. Yes, we need to stay below the critical point where you want to throw the laptop out of the office window (we’ve all been there!) To do that, we need to be proactive. Let’s do ourselves a favour and take the chance to ‘tidy up’ right now.

In the video, Chris first shows how to use With … End With to economise on code; fewer characters to read means lower cognitive load and lower stress levels, so integrating With … End With represents a quick win for us. Just remember to add the full stops (.) where necessary.

Then we look at re-arranging the annotations (remember, the ones we created in the first video of the series?) so that each aligns with the correct line of code. Being able to understand what the code is doing in your native language is a real bonus for the coder – so let’s take the time now to get those annotations up-to-date.

A small test confirms everything is still working as required.

See you in the next video when we will tackle some more exciting coding ideas…

Part 5 – A Modular Approach

In recent years, I have experimented with a more ‘modular’ approach in my Excel VBA programming, getting good results. A more modular approach can mean easier-to-understand code and a lower risk of crashes – but how to actually realise it in practice? Let’s check it out in this video…

It seems we’ve reached the end of the first ‘phase’ of this task – checking the open files are present, open, and correctly named. We could call this phase ‘validation’ and make it ‘distinct’ or discrete by separating it from the main programming. We could then trigger this first phase from a separate button (as we will later in the series) to create a sense of a ‘pre-departure check’. Yes, this phase does not do much in terms of analysis; but it does set things up for us, and a phase completed successfully builds user confidence.

This approach requires a ‘module-level’ variable. These variables, declared at the top of the module, are not cleared after a routine executes; they continue to store their values and can be used to pass information between routines within the same module – cool! In the video, we create a module level variable, test it, and then use it to check a condition before triggering another routine – the modular approach in action.

As always, we include lots of testing as we go along. Our classic ‘flash up a message box’ approach comes in handy more than once; at the same time, we take the opportunity to improve a message box by changing its title to something that directs the user more effectively. Remember – user communication is everything!

So, are you a fan of the modular approach? Perhaps you can’t feel the benefits fully yet, but Chris does mention some in the video. As we work through the task, the approach will take shape and hopefully you’ll be able to feel its full power.

See you in the next video in the Excel VBA Real World Example series – season 2! Don’t forget to leave Chris a comment below (he answers all comments personally.)

Part 6 – Step-By-Step

In the previous video we explored how to apply a modular approach in this implementation. In video 6 in the series, we first add buttons to the interface, to allow us to exploit the modularity of the code, and to allow the user to ‘work through’ the process step-by-step. It takes a little more time to implement a modular approach, but we will soon begin to feel the benefits: modulated code is easier to debug (it’s bound to go wrong at some point!)

Moreover, it allows your client to gradually build confidence in the application, and in you as a programmer. No more clicking the button then closing your eyes and hoping everything works!

Let’s not forget more practical matters. In the video, Chris shows how to create the buttons, align them with the spreadsheet gridlines, assign macros to them and change the text that displays on the button. Don’t forget to use the Alt key to make sure those buttons are aligned! (Chris’s bugbear.)
With our button in place, let’s take our communication with the user to the next level by including text on the interface to let them know when the file was last validated. We use some cool VBA syntax to get this done – NOW to give the current date and time, and FORMAT to display the text exactly as we require.

The step-by-step approach we apply in this series may seem unnecessarily time-consuming (Chris calls it ‘ponderous’ in the video.) Hopefully you are feeling some of the benefits already, however, and more will come apparent as we work through the series. Years of working on real-world projects has taught Chris this really is the best way!

See you in the next video and don’t forget to leave a comment on YouTube, Chris will get back to you.

Part 7 – A Remote Reference To Another File

We begin by testing the validation mechanism created in the previous video and refreshing our minds about how the module level variable works. Ultimately, we want the customer to be able to click through a few buttons to get this task done. Yes – it’s more ‘click-of-a-few-buttons’ than ‘click-of-a-button’ but we are arguing that the ‘modular’ approach delivers benefits for the user (by building confidence in the process) and the programmer by making the code easier to debug.

This takes us to the next phase of the task. So, what to do? Here, the time we took to talk through the task at the beginning (see video 1 in the series) and create pseudo code (the green annotations or comments) pays dividends. We can simply read through the annotations to find what to do next. No shifting between conceptual thinking and coding. Cool!

One thing we will have to do is remotely reference the other open files. ‘Remotely’ (as opposed to ‘directly) means referencing the files without activating them. It involves more sophisticated coding but results in far more efficient execution.

In the video, Chris talks about the value of the ‘ThisWorkbook’ construct – meaning ‘the workbook that contains this code’. It comes in useful when working with multiple open workbooks. As with multiple sheets, we have to make sure that Excel understands the object we are talking about when working with multiple workbooks. So, let’s try the syntax and test it using Msgbox, as usual.

In the video, Chris also shows how to integrate the With … End With construct to economise on code and improve efficiency. Are you using it in your coding practice?

We end the video by remotely referencing a cell in another open file to flash up the contents of a cell in a message box. It’s proof that our remote and modular coding approach works, at least for this simple example. In the next video, we’ll look at scaling up this approach to begin to create some insights for our client.

See you in the next video and don’t forget to leave a comment on YouTube, Chris will get back to you.

——————————————

Are you following season 2 of the Real Example VBA series? We would love to hear from you, and Chris answer all comments personally, so leave us your questions below in the comments.

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

Excel Dashboards For Beginners

Excel Dashboards For Beginners

Want to get started with Excel Dashboards? You are in the right place. Excel Dashboards can look cool, but putting one together can be tricky. In the Excel Dashboards for Beginners series, Chris explains his simple approach for putting together powerful, dynamic and visually-pleasing Excel dashboards that you and your customers are going to love. Let’s get into it!

Click here to download the Excel Dashboards For Beginners download file.

Excel Dashboards For Beginners Picture

Part 1 – 4 Cool Features of Excel Dashboards

The best way to tackle this complex topic is first to develop an appreciation of what a good Excel Dashboard looks and feels like. Chris has built a demo file that you can download and click through – check out the download link above. This dashboard contains four features that all good Excel dashboards share: (1) a helpful layout (2) consistent spacing (3) layering (4) good use of colour.
Let’s go through these features one-by-one, noticing how the are implemented. Make sure you try to implement these in your Excel Dashboards too! Let us know in the YouTube comments how you get on.

(1) A Helpful Layout
How on earth do you get started with an Excel dashboard? An empty spreadsheet can be daunting, so try applying Chris’ ‘three-by-two’ default layout to get things started. In the video, Chris shows how to set up this layout, and how the layout can create a cool Excel Dashboard.

With the layout in place, how do you know what should go where? This depends on your situation; make sure you prioritise by discussing requirements with your customer and establishing the what the most important information is. Since the Western reader’s eye traces from the top-left to the bottom-right corner of the spreadsheet, it is important to place the most important data (Eg. ‘profit’?) in the top-left – don’t hide it away somewhere else!

(2) Spacing
Neat-and-tidy presentation is another feature of a good Excel dashboard. ‘Tidiness’ can be subjective (ask your mother about when you were a teenager), so what rules can we apply here? Consistent row heights and columns are a must. In the video, Chris shows how all columns in the example Excel dashboard are one of two column widths, creating a super clean look and helping the user assimilate the data. Try this approach in your Excel dashboard, you and your client will love it.

(3) Layering
This might come as a surprise, but all cool Excel dashboards showcase this third key feature. When a lot of data is visible (check out your car dashboard), layers help the user navigate and make sense of the information. How can we do this in Excel? Shapes with gradient fill allow us to lift the main dashboard pane from the background. Then, shading around charts and other objects can make them stand out. Check out Chris’s demonstration in the video and have three layers in mind for your next Excel dashboard.

(4) Colour
What constitutes helpful colouring? This is to some extent a matter of opinion, but some general principles can really help. The most important is that colour should be used sparingly, or (at least) judiciously. Too many colours confuse and mean that individual colours lose impact and resonance. Stick to 2-3 key colours and use 1-2 highlight colours (yellow is helpful here) to make the important information stand out. Remember you can use an online colour picker to match your colours to a company brand identity. Chris demonstrates his approach in the video.

———————————

Part 2 – 17 Tips For Formatting Excel Dashboards

1. Consistent Row (Heights) and Column (Widths)
a. Display columns (8/10) and spacer columns (2)

2. Use Subtle Background Formatting (Not A Distraction)
a. Dotted pattern fill works well

3. Shapes Help Create Layers (Use a gradient fill)

4. Switch Off Gridlines (But Horizontal Borders Are Important!)
a. Creates ‘Clean’ Look (but gridlines do support readability)
b. Do you need ‘all borders’? Horizontal only improves readability

5. Get Rid Of The Units (Write It Once)

6. Get Rid Of The Zeroes (With Special Formatting)
a. Some interesting ones to try (working with thousands):
i. 0.00,
ii. 0,”K”

7. Use Colour Sparingly (Within A Scheme!)
a. Same formatting in tables and charts (to highlight important items)
b. Consider conditional formatting
c. White font on dark colour provides a nice contrast
d. Match to client brand identity?

8. Position Output Cell In the Top-Left (Not The Bottom Left)

9. Highlight User Input Cells (Top-Left)
a. Position in the top-left for easy access
b. Use a highlight colour (yellow works well)

10. Differentiate Font Size (Make Output Stand Out)
a. Like on your car dashboard

11. Use Dynamic Headers (Highlight Dynamic Quality!)
a. Use formulae to create this effect

12. Declutter The Axes

13. Cell Comments – An Additional Layer?
a. Option to switch on / off (can annoy the user)

14. Use A ‘Catch-All’ Category In Charts / Tabulation (To Simplify)

15. Consider Role Of Company Logo And Titles (Are They Needed?)
a. Can use up prime ‘real estate’

16. Make (Most) Fonts Consistent
a. Can be considered ‘lazy’ to use the ‘default font’
b. Arial is recommended (obscure fonts may not be supported)
c. Fit to company brand identity?

17.
Golden Rule 1: Formatting Should Support Assimilation

Golden Rule 2: Remember To Some Extent It’s A Matter Of Opinion – Whose Matters?

Part 3 – Create Dynamic Quality In Excel Dashboards

Click here to download the simplified file for this video.

What do you we mean by ‘dynamic quality’ in Excel Dashboards? Let’s take a typical dataset such as the (fictional) personal spending data our example. Without a dynamic mechanism, the dashboard displays analysis for ALL the data. This is useful enough, but soon the user will wish to ‘slice and dice’ the data to include and exclude certain values. How much money did I spend in May, for example? How much do I spend on that particular expenditure item?

A dynamic dashboard allows the user to adjust settings and to display ONLY data that conforms to a particular criteria; in our case a month in the year. Not only does this give the customer greater power to get insight, it also gives the dashboard an interactive feel. The user can ‘play’ which makes the dashboard more insightful, and fun! This is why dynamic quality is so cool; but how on earth do you build it into an Excel dashboard?

There are a number of different ways, each with its pros and cons. One way is to use pivot tables; there is no doubt pivot tables are supremely powerful, but their layout can be difficult to predict (since it is auto-generated), and they often require an additional button click to make them update. A VBA-based solution is another option; this creates the slickest, most bespoke functionality; but, on the downside, the programmer must know sophisticated coding to make everything work.

The approach I demonstrate in this video offers a nice balance between powerful functionality and complexity of implementation. The approach uses INDIRECT to isolate ranges to look at in the dataset, determined by user selections (in this case, the month) on the dashboard. A notable benefit is that it updates instantly (because it is formula-based), so no additional button clicks are required to update the dashboard. Over the years it has worked very well for me on client projects.

Download the file, follow the demonstration and let me know what you think. Did you get the mechanism work for you, and have you created your own dynamic dashboard? How do you think it compares to other possible approaches? Will you be adding this approach to your Excel development toolkit? I would love to hear your thoughts in the YouTube comments and I promise I will get back to you!

Did this series help you get started with Excel dashboards? We would love to hear what you think, and to answer your questions in the comments – so go ahead and leave us a query right there. More to come soon from the Excel Dashboards for Beginners series – thank you for watching!

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 Get Better At Excel: The Excel Metaskills

How To Get Better At Excel? It’s Not About Fancy Formulae!

People often overlook the crucial skills that bind everything else together. These are not fancy formulae or obscure coding techniques, they are much more fundamental than that.

In this livestream, Steve and Chris explore what the Excel Metaskills are, why they are important, and how to implement them in your practice.

Click here to download the Excel Metaskills Notes file

Excel Metaskills Picture

Metaskills are founded on metacognition – the capacity to think about how you are thinking about your work. Yes, it’s thinking about thinking, which is why it’s ‘meta’!

A reflective practitioner practices metacognition. Don’t worry about the big words: if you are thinking about your strengths and weaknesses in Excel, you are already practising the metaskills. So, let’s get into it!

Video begins @ 15:30

The Metaskills are as follows:

1. Know What To Do When Things Go Wrong
– fix formulae
– debug VBA code
– do an internet search to find information

2. Appreciate Pros / Cons Of Different Approaches
– Understand pros and cons of
* formulae
* pivot tables
* VBA

– Understand your own personal biases towards particular techniques

3. Understand Main Applications of Excel
– data analysis
– modelling
– data input
– others

4. Understand Effective Workbook Structure
– data sheets
– input sheets
– backend sheets
– others

———————————

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

7 Essential Excel VBA Beginner Techniques

GET YOUR VBA CAREER STARTED WITH THESE 7 ESSENTIAL BEGINNER VBA TECHNIQUES

PART 1 – Introduction to 7 Essential Excel 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!

PART 2 – Planning and Conceptualisation

Planning?! But we want to code! Yes, this is topic can appear boring, but proper planning and conceptualisation really is the key to effective coding.

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

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

Only by being deliberate about how we build a macro can we be sure to avoid technical problems later. That’s the thing: when people complain about VBA syntax, the problem is usually that they do not have a clear idea in their own heads about how the code should work. Clear conceptual thinking not only helps unleash your potential as a programmer, but it also keeps stress levels down!

After a ‘warm-up’ exercise to revise the concepts and get us thinking conceptually (make sure you complete the exercise too!) Chris demonstrates the process of planning and conceptualising a macro. By applying the concepts one-by-one to the real-world problem, Chris creates the shape of the routine using ‘comments’ (text in green that Excel ignores) in the VBA Editor. This vastly simplifies a complicated task: next we can translate these comments into VBA syntax; then, we will be close to a powerful macro to automate a piece of time-consuming work.

What did you make of the planning and conceptualisation video? Did you manage to complete the first exercise too? Let Chris know in the YouTube comments, he will get back to you. See you in the next video!

PART 3 – EXECUTION

Finally – it’s time to build the code and execute the task. This won’t be easy, but the basic concepts and annotations in the VBA editor will certainly help us. Let’s get into it – don’t forget to download the Excel files and work along with Chris.

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

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

This brings the Excel VBA Essential Beginner Techniques series to a conclusion. We have covered basic concepts with simple examples, explored how to conceptualise and plan a VBA macro, and finally implemented and tested the code to complete the task. Try applying this approach to your work, and see if you can speed up those annoying manual tasks.

Don’t forget to let us know how you get on!

———————————

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