Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)

Chapter 5

R1C1-style formulas

In this chapter, you will:

• Understand A1 versus R1C1 references

• Toggle to R1C1-style references

• Witness the miracle of Excel formulas

• Examine the R1C1 reference style

Understanding R1C1 formulas will make your job easier in VBA. You could skip this chapter, but if you do, your code will be harder to write. Taking 30 minutes to understand R1C1 will make every macro you write for the rest of your life easier to code.

We can trace the A1 style of referencing back to VisiCalc. Dan Bricklin and Bob Frankston used A1 to refer to the cell in the upper-left corner of the spreadsheet. Mitch Kapor used this same addressing scheme in Lotus 1-2-3. Upstart Multiplan from Microsoft attempted to buck the trend and used something ...