Cannot include a cell-formula into VBA code in MS Excel

Question

I am trying a simple VBA program, that looks like this:

Function NotWorking() As Single

 If Range("C3") = 0 Then
 NotWorking = "=0.6*7"
 Else
 NotWorking = 1.2
 End If

End Function

If cell C3 has 0 as a value, the function NotWorking has to return in the current cell the result of *=0.6*7*, which is 4,2. If the value in C3 is not 0, then the current cell has to return 1,2.

In my case, things aren't the way I expected. In the current cell I type =NotWorking(). If C3 is anything but 0, I obtain 1,2, but when I appropriate 0 to C3, the current cell returns #VALUE.

Why does this code not work?

Best regards and thanks in advance!

Brian Wren · Accepted Answer · 2017-12-19 22:48:14Z

1

I notice that "=0.6*7" is a string, not a number, but 1.2 is a number.

You have specified that the function returns a single, but "=0.6*7" isn't a single.

answered Dec 19, 2017 at 22:48

Brian Wren

3776 silver badges15 bronze badges

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

1 Comment

ecclesiastes Over a year ago

How do you include a cell formula into a VBA?

Brian Wren · Accepted Answer · 2017-12-20 19:09:27Z

0

Don't try to return it from the routine, but reach out to the sheet from within the routine.

Sub NotWorking()

   Dim src As Range
   Dim dst As Range

   Set src = Range("C3")
   Set dst = Range("E3")

   If src.Value = 0 Then
       dst.Formula = "=0.6 * 7"
   Else
       dst.Value = 1.2
   End If

End Sub

answered Dec 20, 2017 at 19:09

Brian Wren

3776 silver badges15 bronze badges

4 Comments

ecclesiastes Over a year ago

Very well. This works really nice. Have an upvote. I still need something slightly different - a formula, that could be called on every cell and every sheet of the file. I guess this little video will help me (as well as your code) youtube.com/watch?v=317P-hH0QwM. Greetings. :)

Brian Wren Over a year ago

You said "Have an upvote," but the voting stands at zero ... :(

ecclesiastes Over a year ago

That is, unfortunately, correct. I have rating under 15. Every upvote I give is written down in the database and after (if) I reach rating of 15 or more, the upvotes, I have given, will appear. :)

Brian Wren Over a year ago

Ahhh. Thanks for the explanation. So all is NOT vanity!

Collectives™ on Stack Overflow

Cannot include a cell-formula into VBA code in MS Excel

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related