When writing string to a cell, VBA inserts extra single quotation marks

Question

I am trying to write this formula using VBA:

ActiveCell.Value = "=f(R[-1]C[0],Sheet1!" & ColumnLetter & i & ")"

Where ColumnLetter is some variable letter which my macro computes earlier, and f is some function, and i is some number.

The problem is that when I run this, the cell is given this instead: (if ColumnLetter = F, i = 16):

=f(R[-1]C[0],Sheet1!'F16')

but I want:

=f(R[-1]C[0],Sheet1!F16)

Why is VBA or Excel putting those single quotation marks around F16? It does not insert these extra quotation marks if I do not include R[-1][0] as an argument in my formula, but I need to include this.

Help much appreciated!

Answer 1

Its the combination of R1C1 and A1 addressing. You need to pick one method and use it for both parts.
Note that if you type =f(R[-1]C[0],Sheet1!F16) into a cell you will get an error for the same reason.

You say you need to use R1C1 style for the first address, but (assuming this is because you don't want absolute address) you can use .Offset instead

ActiveCell.Value = "=f(" & ActiveCell.Offset(-1, 0).Address(False, False) _
 & ",Sheet1!" & ColumnLetter & i & ")"

Answer 2

The Apostrophe means to Excel that it should interpret it as text.

Write it to ActiveCell.Formula. That way it is recognized as Formula.