Excel vba formula string doesn't calculate: #NAME? error

Question

I have this sub that types in a sum formula in A1 cell:

Sub test_string()
    ThisWorkbook.Worksheets("test").Cells(1, 1) = "=СУММ(B1:D1)"
End Sub

At first it looks like it has worked, but when I open a worksheet there's a #NAME? error in A1 cell:

The error disappears when I manually calculate the formula (put cursor in formula's text and click Enter).

Why does this happen and is there a way to fix it? I tried

ThisWorkbook.Worksheets("test").Cells(1, 1).Calculate

But to no result.

You forgot an = so "=SUM(B1:D1)" (Although I would not think that would result in #NAME ...) — Alex K.
– Alex K., Commented Jul 11, 2018 at 11:09
@Alex K. Sorry for confusion, it's the result of retyping. The error isn't because of it. — Ans
– Ans, Commented Jul 11, 2018 at 11:16

Chris Melville · Accepted Answer · 2018-07-11 11:21:11Z

3

You're not specifying which property of Cells you want.

The default property of Cells is Value. Therefore when you write:

Cells(1,1) = "=SUM(B1:D1)"

... what you're actually saying is:

Cells(1,1).Value = "=SUM(B1:D1)"

You need to use the .Formula property:

Cells(1,1).Formula = "=SUM(B1:D1)"

answered Jul 11, 2018 at 11:21

Chris Melville

1,5161 gold badge14 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CLR Over a year ago

or Cells(1,1).FormulaLocal = "=СУММ(B1:D1)" if you prefer.

Ans Over a year ago

@CLR I heard it wouldn't work on Excels with a different language version. I have a Russian Excel version, so I guess FormulaLocal wouldn't work for someone with an English Excel isntalled on?

CLR Over a year ago

That would make sense. I hadn't realised you were looking to use this on other set-ups.

Collectives™ on Stack Overflow

Excel vba formula string doesn't calculate: #NAME? error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related