How to prevent VBA function from re-executing inside the code

Question

I'm calling VBA function from an Excel worksheet. When I change a cell inside the code of the VBA function, Excel tries to re-execute that function again (and again in the second iteration and ...)

Example: if you have the code:

Function test() As Variant
    Range("A1") = 1
    test = "test"
End Function

When you use "=test()" anywhere, it will return #VALUE!. A debug will show that when you update A1, it will try to re-execute test().

Can you prevent Excel from doing this? E.g. saying 'don't update any of my numbers until I'm done with this function'? I've tried the Application.Calculation flag, or doing some external concurrency checks, but that doesn't seem to work ...

Don't attempt to change a worksheet cell from a UDF, only return a value thru its header. — Gary's Student
– Gary's Student, Commented Nov 6, 2013 at 23:35
@Gary'sStudent valid point. Generally, you cant modify OTHER cells than the one the function is being called from. Change it to a Sub then youll be fine — user2140173
– user2140173, Commented Nov 6, 2013 at 23:39
It's returning the error because of what @Gary'sStudent mentions. The UDF is not allowed to manipulate the worksheet, and the line Range("A1") = 1 causes it to terminate. A debug will show you that this line fails. — David Zemens
– David Zemens, Commented Nov 7, 2013 at 2:02
What Gary mentions above is the reason for your #Value error. A debug will show that when you update A1, it will try to re-execute test(). Now I am curious as to why your code is being reexecuted again. Do you have a Worksheet_Change Code in your workbook? — Siddharth Rout
– Siddharth Rout, Commented Nov 7, 2013 at 6:38

Gary's Student · Accepted Answer · 2013-11-06 23:39:01Z

0

Try:

Function test() as Variant
test="test"
End Function

answered Nov 6, 2013 at 23:39

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

1 Comment

tb189 Over a year ago

The goal was not to return "test", that was just the easiest example I could think of to change a value inside a function.

tb189 · Accepted Answer · 2018-03-05 14:45:07Z

0

As Gary's student says in his comment: UDF's should only change worksheet entries of the cells that call the function, and this value should be returned as a result of the function, not by changing a cell value of an explicit address.

In other words, this does not work:

Function test() As Variant
    Range("A1") = 1
    test = "test"
End Function

Instead call test() from cell A1 and do this:

Function test() As Variant
    test = 1
End Function

If the desired behaviour is to edit multiple cells (the return value of the function AND another cell), this should be implemented through a sub rather than a function.

edited Mar 5, 2018 at 14:45

answered Jan 10, 2015 at 14:15

tb189

2,0723 gold badges24 silver badges39 bronze badges

Comments

Charles Williams · Accepted Answer · 2013-11-07 08:35:18Z

-1

To answer your specific question of how to stop the Function re-executing when you change A1 or your function, and stop it returning #Value:
1. Use the Excel user interface (Formulas--> calculation options) to change calculation mode to manual
2. add some error trapping to your function like this.

Function test() As Variant
    On Error Resume Next
    Range("A1") = 1
    test = "test"
End Function

as has already been said the function will not change the value of A1

edited Nov 7, 2013 at 8:35

answered Nov 7, 2013 at 0:10

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

3 Comments

tb189 Over a year ago

Like I said: I've tried the Application.Calculation flag, or doing some external concurrency checks (I guess that's what you mean with error trapping), but that doesn't seem to work. Do you have some working code that shows what you mean?

David Zemens Over a year ago

this won't work, the OP is attempting to implement code that is specifically prohibited from executing.

Charles Williams Over a year ago

The OP's question was how to stop the function returning #Value and executing every time he changed the function or the cell it referred to. That is what I answered.

Collectives™ on Stack Overflow

How to prevent VBA function from re-executing inside the code

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related