Is it possible in VBA to write an invalid formula to a range?

Question

I have the following code in a VBA macro:

Option Explicit

Sub WriteFormula()
    Dim rng As Range
    Set rng = Range("A1:B2")

    ' Works
    rng.Formula = "=ROUNDUP(1, 1)"

    ' Doesn't work
    rng.Formula = "=ROUNDUP(1)"
End Sub

The second write contains an invalid formula and causes a runtime exception:

Is there any way to disable error checking so that the second write succeeds?

I think the problem is that it isn't a range. VBA doesnt know where you are looking as you've only added half a range in. You could put in an On Error resume Next, but that wouldn't allow it to "succeed" it would just be skipped. — Kyoujin, Mar 09 '18 at 08:49
Since the formula won't work, why do you even want to write it? — Chronocidal, Mar 09 '18 at 09:11

score 1 · Answer 1 · answered Mar 09 '18 at 08:53

1

A bit tricky, but you could write your 'wrong' formula as a string in one cell, then, in another cell, use the hidden evaluate function (see https://stackoverflow.com/a/4472470/78522)

answered Mar 09 '18 at 08:53

iDevlop

24,841
11
90
149

score 1 · Answer 2 · answered Mar 09 '18 at 08:55

1

Well, cheeky answer.

Option Explicit

Sub WriteFormula()
    Dim rng As Range
    Set rng = Range("A1:B2")

    ' Works
    rng.Formula = "=ROUNDUP(1, 1)"

    ' Doesn't work
    rng = "'=ROUNDUP(1)"
End Sub

answered Mar 09 '18 at 08:55

adam

414
2
8
21

well, this is not a formula but text string. I have tried remove this apostrophe with replace, but it throws an error. Funny thing, considering that you can type wrong formula in the spreadsheet without difficulties. – MarcinSzaleniec Mar 09 '18 at 10:14

score 1 · Accepted Answer · answered Mar 09 '18 at 08:58

No, you cannot bypass error control and write the bad formula but you could change the formula to text and write the text. This would give you the opportunity to view what was attempted to be written and correct it on the worksheet before applying the modifications to the VBA code.

Option Explicit

Sub WriteFormula()
    Dim str As String
    Dim rng As Range
    Set rng = Range("A1:B2")

    On Error GoTo forceWriteText

    ' Doesn't work
    str = "=ROUNDUP(1)"
    rng.Formula = str
    Exit Sub

forceWriteText:
    rng.Value = Chr(39) & str

End Sub

score -2 · Answer 4 · answered Mar 09 '18 at 08:53

-2

The Second formula is wrong syntax, = Round(number,num_digits) so its throws an error. Either remove it or correct it. Thank You.

answered Mar 09 '18 at 08:53

arun v

852
7
19

score -2 · Answer 5 · answered Mar 09 '18 at 09:05

-2

If you want to avoid the error for second line then you use "On Error Resume Next" statement. First statement will be succeed.

answered Mar 09 '18 at 09:05

Parveen Saroha

34
6

Is it possible in VBA to write an invalid formula to a range?

5 Answers5