BEYOND THE MACRO RECORDER
BEYOND THE MACRO RECORDER 1/4 - Dynamic Position Control With Excel VBA
The purpose of the first video in the Beyond The Macro Recorder series is to create what’s called ‘dynamic quality’ in a recorded VBA macro. “Chris, what on earth does that mean?!”, I hear you say.
Well, let me try to explain simply. Recorded code always contains cell references. Our recorded code, for example, contains the reference B6:J386 which refers to the dataset we want to sort. This is perfectly normal but brings with it a particular issue that we need to ‘address’ to move to the next level. 😉
This is kind of reference is called a ‘hard-coded’ reference, since it does not cater for a changing dataset. In other words, if we added additional data, the routine might sort only up to row 386, with the additional data remaining unsorted. This is clearly problematic and is a typical problem with ‘hard-coded’ VBA code that the macro recorder gives us. You might encounter the same problem filtering data using VBA, and in any number of other applications. It’s a key challenge we need to surmount to move beyond the macro recorder!
What’s the alternative? A dynamic approach. In the video, I demonstrate how to use Excel VBA variables to dynamically define the dimensions of our data. Yes, this is a ‘dynamic’ approach – the code works for any size dataset, within reason, as I demonstrate in the video. Look out for powerful pieces of syntax such as .end(xlup), and usage of variables to express ranges dynamically. Make sure you download the Excel file and work along with me to ensure you develop this critical skill that should help move you beyond the macro recorder.
Have you worked along with the video, and did you have any ‘punch-the-air’ moments? I would love to hear about them in the YouTube comments, I’ll get back to you there!
CLICK HERE TO WATCH THE VIDEO
Topics :
00:00 Who this video is for
00:35 Get the download files
01:07 Review the recorded code
03:14 Identify critical parts of the code
03:55 Hard-coded vs. dynamic code
06:24 What’s a variable?
07:30 Declare a variable
09:50 Create a named range
11:42 Use .end(xldown)
12:20 Testing VBA code
13:55 Dealing with 1004 error
15:05 Adding data to the dataset
17:18 Use .end(xlup) as an alternative
18:30 .Cells referencing technique
19:50 Dealing with 6 error (overflow)
23:00 Concept of concatenation
23:20 Using a string variable
26:50 Substituting variables for references
30:22 Variable not defined error
31:40 Tweaking code that’s almost there!
32:45 Final testing
34:03 Session review
34:48 Our Members’ Monday learning community
Members' Monday Learning Community
Beyond The Macro Recorder Playlist
BEYOND THE MACRO RECORDER 2/4 - Loops In Excel VBA
In session two of Beyond The Macro Recorder, we explore how to apply the most powerful technique in Excel VBA and computer programing more generally: loops.
GET THE EXCEL FILE Here
If you’ve used the macro recorder in Excel, perhaps you’ve thought:
‘I can do that to one sheet with recorded VBA code, how do I do it to multiple sheets? Could I apply it to two sheets? Or twenty?”
If you’ve ever wondered how to quickly ‘scale up’ code you’ve generated in the macro recorder, this video is for you!
In the video demonstration, I start by creating a separate macro to use as a ‘safe space’ for testing a for-each loop. We learn how to easily loop through all sheets in the file at the click of a button.
CLICK HERE TO WATCH THE VIDEO
With the basic concept understood and tested
, we try to integrate the for-each loop around the sort code we created in session one. As we’ll see, this is not easy, but can be done without too much stress with a steady-and-systematic approach, testing at every step to build confidence. You’re now experiencing the power of loops in Excel VBA …
The session features a bonus topic and something of a preview for the next session in Beyond The Macro Recorder. The truth is, we don’t want to sort ALL the sheets in the file, since one contains the series summary information.
So, how could we tell Excel to only apply the code to certain sheets in the file? This introduces the concept of ‘conditionality’ and the next technique you’ll need to move beyond the macro recorder. Catch all the action in the second half of session two!
TIMESTAMPS
00:00 The four key skills to move beyond the macro recorder
00:38 Get the Excel download files
01:00 Session 1 review and goal for session 2
01:42 Conceptual explanation of a loop in Excel VBA
02:12 Create a safe space for VBA experimentation
03:08 Concept: how a for-each loop works in Excel VBA
04:00 Create simple for-each loop with VBA
04:04 Declare an object variable
06:15 Show names of all worksheets in file with a loop
06:47 Debug and test a simple macro
08:50 Tidying up a macro in the VBA editor
09:30 Integrating a loop with recorded code
10:38 Properly indenting VBA code
11:50 Replacing hard-coded references
15:20 Use find and replace to update VBA code
16:00 Performing a ‘sense check’
18:00 Concept of conditional statement
18:49 Integrating IF – END IF statement
24:58 Tweaking and testing to build confidence
25:16 Sort Z-A rather than A-Z with Excel VBA
27:26 About our Members’ Monday Community
I would love to hear about your struggles and ‘punch-the-air’ moments in the YouTube comments – I’ll get back to you there.
Members' Monday Learning Community
Beyond The Macro Recorder Playlist