Excel VBA - How To Insert Plain Value without Formula?

Question

I have skimmed many Excel VBA topics, however, I have not found any information about inserting plain values - the result of SUM() in this case - not formula. There is a method, PasteSpecial xlPasteValues, but it seems that it is not what I am looking for.

The following code example inserts the formula, not only the value:

Sub test()
    Range("A4").Value = "=SUM(A1:A3)"
End Sub

How do I insert plain values without the formula in Excel VBA?

Answer 1

Here is what I think, you are looking for

range("a4").Value = Evaluate("SUM(A1:A3)")

Answer 2

Are you wanting to the actual result of the SUM? I would try using WorksheetFunction.Sum eg

Dim MyRange as Range
With Worksheets("SheetName")
  Set MyRange = .Range(.Cells(1, 1), .Cells(3, 1))
  .Cells(4,1) = WorksheetFunction.Sum(MyRange)
End With