Inserting value into Excel cell instead of formula

Question

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)

I have tried the following code, but it doesn't work...

ws.Range(...).Value = ....

Solution 1

Append a ' 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

Solution 2

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.