Inserting value into Excel cell instead of formula

Question

Hi,

I have a VBA script that inserts long strings into Excel cells. In some cases, the string begins with a '='. It seems like Excel interprets this as a formula and I get an 'Out of Memory' error due to the memory limitations of formulas.

How do I tell Excel that I am writing a value, not a formula? Currently, I am doing this:

ws.Range("A" & row) = Mid(xml, first, CHUNK_SIZE)

This doesn't work: ws.Range(...).Value = ....

Thanks in advance!

Answer 1

Add an apostrophe ' to the beginning of the string (that excel thinks is a formula) and excel should interpret it as a string instead of a formula.

Answer 2

Append an ' before the = sign:

Sub Test()

     'This returns 30 in cell Al
      Range("A1").Value = "=SUM(10,10,10)"

      'This shows formula as text in cell A2
      Range("A2").Value = "'" & "=SUM(10,10,10)"

End Sub