.Cells For Beginners
Let’s learn the Excel VBA .cells technique
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 … 🙂