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!
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.
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.
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.
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!
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!
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.
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 …
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!
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.
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!
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!
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!
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!
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.
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!
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.
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!