Let’s learn the Excel VBA .cells technique

Click here to download the Excel files for this video

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

TUTORIAL VIDEO 1

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

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

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

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

TUTORIAL VIDEO 2

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

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

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

Ah, Poetry in Motion … 🙂

TUTORIAL VIDEO 3

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

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

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

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

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

TUTORIAL VIDEO 4

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

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

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

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

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

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

EXCEL VBA .CELLS SERIES RELEASE DATES

Video 1 – 08/03/19 –

Video 2 – 22/03/19 – 

Video 3 – 05/04/19 –

 Video 4 – 19/04/19 – 

Playlist link:
?list=PLpOAvcoMay5QiWBy4FyahlI2uqSRA23Mp

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

APPLICATION VIDEO 1

Click here to download the Excel files for this video

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

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

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

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

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