R1C1 formula references are garbled unless worksheet is active

Question

I am trying to avoid activating worksheets as I do calculations in my spreadsheet. However, I am running into a problem. This description reproduces the problem exactly on my version of Excel 2016 (32-bit).

Create a new workbook with 2 sheets: Sheet1 and Sheet2. Sheet1 is blank. In Sheet2, in cells A2:A21, I put some integer numbers. I then added a module that uses R1C1 formula format to put some formulas in Column G of Sheet2, with this code:

Option Explicit
Sub Test()
Dim osht As Excel.Worksheet
Dim NumRows As Integer

Set osht = ThisWorkbook.Worksheets("Sheet2")

NumRows = 20
osht.Range("G1").Offset(1, 0).Resize(NumRows, 1).FormulaR1C1 = "=RC[-6]*5"

End Sub

I added a chart sheet to plot what is in Sheet2, with a chart. If you run the code with either Sheet1 or Sheet2 as the active sheet, it will work fine, and the formulas in Sheet2!G2:G21 are correct:

=RC[-6]*5

Run the code with the CHART sheet active, and this does not work. The formulas in Sheet2!G2:G21 look like this:

=R[-1]C[16372]*5

This happens in Excel 2013 (32-bit) as well.

Where am I going wrong here? Or is this an Excel bug?

one of the formulas has "Manual Other", the other has "MANUAL OTHER" .... i would say that you have two pieces of code at play here, depending on which worksheet is active. — jsotola
Changed my post to illustrate the problem with simple code fragment. — Peter B
If you always want the calculation to look at column A try RC1*5. — Darren Bartrup-Cook
I can confirm the behaviour, including the fact that it only applies to FormulaR1C1, and not to Formula or FormulaLocal . I would guess this is a bug. One way around it is to Activate one of the worksheets before applying the formula, and then re-activate the originally active sheet after applying the formula. (But, obviously, just using Formula will be easier.) — YowE3K
Agree this looks like a bug. And, I was trying to avoid the management of the active object as I switched from one sheet to another. I did read that fully qualifying references would make switching between sheets, and active object management, unnecessary, but I thought the issue I was having meant it would not work. Now I have learned that R1C1 formulas are not needed to maintain relative references. And, I no longer have any "active object" tracking code in my VBA. Can anyone point me to how MS receives bug reports from the public? — Peter B

Peter B Peter B · Accepted Answer · 2017-11-12T22:28:18

OK, solved this. I dont need to use R1C1 referencing to get the formulas correct in the range (which I thought I did). Going back to regular Formula vs. FormulaR1C1 fixed the issue.

R1C1 formula references are garbled unless worksheet is active

1 Answers