Determining if a string has been entered with a leading single quote

Question

This question arose in my attempt to answer this question:

In a blank workbook, format A1:A2 as text. In A1 type 123 and in A2 type '123. Both result in the string "123" being the cell value:

But -- the formula bar displays the text '123 when A2 is selected but only displays 123 when A1 is selected, so Excel retains the information about how the strings were entered. Can this information be accessed from VBA?

A quick test which shows what doesn't work:

Sub test()
    Dim R As Range, S As Range
    Set R = Range("A1")
    Set S = Range("A2")
    Debug.Print R.Value = S.Value
    Debug.Print R.Value2 = S.Value2
    Debug.Print R.Text = S.Text
    Debug.Print R.Formula = S.Formula
    Debug.Print R.FormulaLocal = S.FormulaLocal
    Debug.Print R.FormulaR1C1 = S.FormulaR1C1
    Debug.Print R.FormulaR1C1Local = S.FormulaR1C1Local
End Sub 

This does nothing but repeatedly print True. I can't think of any other range properties that would be relevant. I don't see any application or worksheet level object which allows one to access the text displayed in the formula bar. Any ideas?

Answer 1

You can use the PrefixCharacter property of Range to access the apostrophe.

Debug.Print [A1].PrefixCharacter ' prints blank line
Debug.Print [A2].PrefixCharacter ' prints apostrophe

Answer 2

Consider:

Sub SingleQuoteTest()
    MsgBox ActiveCell.PrefixCharacter = "'"
End Sub