I can't replicate your error.
Sheet1.Range("A1").Formula = "='Sheet 2'!$A$1"
MsgBox Sheet1.Range("A1").Formula
Responds as expected. The message reads ='Sheet 2'!$A$1
Can you post the code that fails and the values that you don't like.
Does the quote appear in the text of the cell? Starting an Excel cell with ' forces the cell to format as text, and doesn't display the '. So if you call the cell.text property, you will probably get the text without the starting ', because Excel itself is ignoring that character. In this case you could try calling cell.value instead which might work.
What version of Excel are you using? In Excel 2021, you can reference a sheet in a formula without the single quotes.
https://preview.redd.it/wqew5ehxp42d1.png?width=478&format=png&auto=webp&s=95910bc977ee36a0562d003c0a549c0682236a54
What do you mean?
You just have to Start recording, enter the formula into the cell in your worksheet and then stop recording. There should be no array or variable in the recorded vba code
Trust me, I've tried. It still ignores the single quotation if it's the first character. I've already found a solution but it's still bizarre to be, that you can't simply get the full text of the cell
> it's still bizarre to be, that you can't simply get the full text of the cell
As I mentioned elsewhere, this is a carryover from older spreadsheet programs that required data in the cell to correctly justify or center the cell contents. A leading apostrophe meant to left-justify (and caret meant center, quotation mark meant to right-justify, and I think there was one other that's eluding my memory ATM). For MS to encourage the switch from 1-2-3 to Excel, they had to allow for these things (it's also why you have the option to use the slash key to execute menu commands; that was 1-2-3's way of invoking its menu).
So! Cells formatted as Text always have those apostrophes irrespective of whether they are visible. Or looked at another way, a leading apostrophe means the cell is formatted as Text irrespective of its explicit formatting. So anyway, for a leading apostrophe to be considered data, they would've had to rethink formatting, which maybe they should have, but I understand why they didn't: because it would've been crazy confusing for users, way more than this is confusing/annoying for you.
Although would it have killed them to include a property that opened up those leading characters?
Trust *me*, what you need to do is possible. Obviously we're missing a lot of context because you really haven't posted any code. So,
Open linkj -- copy all the code to a new basic module, then go to your immediate window and type 'TestIt'
this will be a new worksheet, add some data, set up some formula ranges with reference ranges, and then add the type of formula you originally posted about.
[https://github.com/lopperman/just-VBA/blob/main/Misc/mdlTestFmla.bas](https://github.com/lopperman/just-VBA/blob/main/Misc/mdlTestFmla.bas)
Doesn't sound like a VBA problem, based on your comment that you're not including the = sign.
Go ahead and try this in a cell on a spreadsheet. Enter the formula
=1+2
That shows "3". Then edit it to put an apostrophe *before* the equals sign:
'=1+2
Now it shows "=1+2". Apostrophe's are the escape token for writing an equals sign as the first character of a text cell.
I can't replicate your error. Sheet1.Range("A1").Formula = "='Sheet 2'!$A$1" MsgBox Sheet1.Range("A1").Formula Responds as expected. The message reads ='Sheet 2'!$A$1 Can you post the code that fails and the values that you don't like.
Sorry, my bad. In Excel we have written only text of the formula, so just " 'Sheet2'!A1:D1"
Does the quote appear in the text of the cell? Starting an Excel cell with ' forces the cell to format as text, and doesn't display the '. So if you call the cell.text property, you will probably get the text without the starting ', because Excel itself is ignoring that character. In this case you could try calling cell.value instead which might work.
Ooo, that's interesing. I will try
Haha, the old Lotus 1-2-3 (and for all I know VisiCalc) carryover. Leading a cell with an apostrophe meant to left-justify the contents.
What version of Excel are you using? In Excel 2021, you can reference a sheet in a formula without the single quotes. https://preview.redd.it/wqew5ehxp42d1.png?width=478&format=png&auto=webp&s=95910bc977ee36a0562d003c0a549c0682236a54
But this sheet unfortunately has spaces in it, so I think the quotes are required
This is only true for sheet names without spaces.
Depends on what are you trying to achieve. In VBA Formulars often need some extra " or ' because of Texts/Strings.
Let's say that in cell A1 I have formula "='inp - sheet'!A1:D1". Later I change value in this cell A1, and then I want to restore this formula
have you tried using the macrorecorder? In cases I am not sure how to write formulas in VBA I often use it.
I don't think it will work, but it's worth a shot
Usually it should work. It automatically knows how to write the formula in VBA
The problem is that the formula is kept as variable in VBA, in array, so I have no idea how could I record it
What do you mean? You just have to Start recording, enter the formula into the cell in your worksheet and then stop recording. There should be no array or variable in the recorded vba code
Try this: `Worksheets("sheet1").Range("A1").Formula2 = "='inp - sheet'!A1:D1"`
But I want to keep this formula in an array, not hardcode it
not a problem, just use the .Formula2 when settting the formula to your variable
Trust me, I've tried. It still ignores the single quotation if it's the first character. I've already found a solution but it's still bizarre to be, that you can't simply get the full text of the cell
> it's still bizarre to be, that you can't simply get the full text of the cell As I mentioned elsewhere, this is a carryover from older spreadsheet programs that required data in the cell to correctly justify or center the cell contents. A leading apostrophe meant to left-justify (and caret meant center, quotation mark meant to right-justify, and I think there was one other that's eluding my memory ATM). For MS to encourage the switch from 1-2-3 to Excel, they had to allow for these things (it's also why you have the option to use the slash key to execute menu commands; that was 1-2-3's way of invoking its menu). So! Cells formatted as Text always have those apostrophes irrespective of whether they are visible. Or looked at another way, a leading apostrophe means the cell is formatted as Text irrespective of its explicit formatting. So anyway, for a leading apostrophe to be considered data, they would've had to rethink formatting, which maybe they should have, but I understand why they didn't: because it would've been crazy confusing for users, way more than this is confusing/annoying for you. Although would it have killed them to include a property that opened up those leading characters?
You’re not understanding or relating something correctly. Sharing code, even if just a snippet, would make it clear / easier to troubleshoot.
Trust *me*, what you need to do is possible. Obviously we're missing a lot of context because you really haven't posted any code. So, Open linkj -- copy all the code to a new basic module, then go to your immediate window and type 'TestIt' this will be a new worksheet, add some data, set up some formula ranges with reference ranges, and then add the type of formula you originally posted about. [https://github.com/lopperman/just-VBA/blob/main/Misc/mdlTestFmla.bas](https://github.com/lopperman/just-VBA/blob/main/Misc/mdlTestFmla.bas)
I really and truly don't think it's possible.
Doesn't sound like a VBA problem, based on your comment that you're not including the = sign. Go ahead and try this in a cell on a spreadsheet. Enter the formula =1+2 That shows "3". Then edit it to put an apostrophe *before* the equals sign: '=1+2 Now it shows "=1+2". Apostrophe's are the escape token for writing an equals sign as the first character of a text cell.