The Excel VBA .Cells technique is incredibly powerful - and slightly confusing! Learn it and use it with this series of four videos.
Link to download file:
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.
"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!
EXCEL VBA .CELLS SERIES RELEASE DATES
Video 1 - 08/03/19 - https://youtu.be/x_PCdMlO41M
Video 2 - 22/03/19 - https://youtu.be/tlmFmcimIwU
Video 3 - 05/04/19 - https://youtu.be/oW2ZqqArIhA
Video 4 - 19/04/19 - https://youtu.be/rf8rK7rzoU0
These introductory videos will be supported by 'long-play' application videos showing how to apply the technique to real-world problems. These will be viewable in the series playlist when released.
Get in touch with Tiger using the platforms below!
Also on Insta :-)