Lesson 11Programming Formulas
Spreadsheets are a popular choice for managing information because mathematical calculations and data analysis are, and always will be, a requirement of education, business, and personal record-keeping. If there were no need to compile numeric data with formulas, there'd be no need for spreadsheets as we know them—an unfathomable thought in our information-ravenous, digital world.
As you've seen, VBA enables you to programmatically manipulate Excel's objects, methods, and properties. You can interact with users to make decisions and establish conditions. Just as importantly, you need to understand how to program formulas, starting with how Excel regards locations of cells and ranges by their row and column references.
Understanding A1 and R1C1 References
Most people who use Excel—most being around 99.9 percent—view Excel worksheets with rows headed from the top as numbers 1, 2, 3 and continuing downward, and columns headed from the left as letters A, B, C and continuing to the right. The top-left cell address on the Excel grid is commonly seen as cell A1. The cell immediately below A1 is A2, the cell to the right of A2 is B2, and so on.
Behind the scenes, Excel does not refer to its rows and columns in A1 style; that is, not in the sequence of column letter and row number. Rather, Excel regards rows and columns as numbers, in R1C1 style, expressing a cell address in the sequence of its intersecting row number and column number.
Get Excel VBA 24-Hour Trainer, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
