How to VBA change cell's value (display text) without changing its formula?

Question

I've a problem with this VBA macro.

Sub demoChangeText()
    Application.Range("B2").Value = "Overidden text"
End Sub

My test is here. To run this macro, open it in Excel, press Ctrl+F8 and choose demoChangeText.

As the result, this macro changes the value of cell B2 (the text displayed to us) but clear its formula. I need to change B2's value BUT also need the formula to be remained.

So my question is How to change the display text of cell without changing its formula?

UPDATE

I ask this question because I'm trying to solve this problem

Solution

I'm not sure if this will help, as it is a bit hard to tell what your underlying requirement is, but here goes anyway:

Several things affect the display of a cell:

1. the entered value, if its a constant
2. the result of a calculation, if its a formula
3. the format of the cell
4. the conditional format(s) of the cell, if any

In the example sheet provided you have a formula of =ROW()&COLUMN() which returns a string result of 22

You can make this display something else by applying a cell format,
eg a format of 0;0;0;Ov\e\r\ri\d\d\e\n t\ext will display any string value as Overridden text

This can by applied with VBA with

Range("B2").NumberFormat = "0;0;0;Ov\e\r\ri\d\d\e\n t\ext\s"

or

Range("B2").NumberFormat = "0;0;0;""Overridden texts"""