Custon fucntion applies to entire workbook instead single sheet

Question

I use popular custom function which I found on the internet on one of vba blogs. It reads text cell as if it be formula (i.e. "=A2+B2" or "=ABS(A2+B2)", I created them by using CONCATENATE function, in workbook they are without quotation marks). The code goes:

Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function

In my workbook I have multiple sheets which are exact same copies except 2 columns of data, which calculate some descriptive statistics (i.e. sum, average, standard deviation etc.), the formulas are in my source sheet, copied sheet have formula like: Eval(sourceSheet!A1) and A1 contains text of formula like above. The problem is that to apply macro after loading it, I need to pres F9 to refresh (sometimes several times). It makes refreshing ALL my copied worksheets with the data I have in worksheet I am refreshing. So for example: if i refresh in worksheet 3 and sum of data was 5 it changes sum to 5 in all other sheets. I guess somehow my code makes the function applied to entire workbook instead of single worksheets like every other excel function.

So I have 2 questions:

Is there any way to change the code so my custom function will only apply to worksheet I put it in?

Can you post me a macro code for refreshing entire workbook with every single click of left mouse button?

Thank you in advance

No, your code is not changing anything. It is the F9 that recalculates every formula in the workbook. Try Shift+F9 to recalculate only the active sheet. So for Q1: nope, your code doesn't need to be changed, it doesn't "apply" to other sheets. Q2: not really, we don't do free coding at StackOverflow. We help other programmers with problems, but we don't do free work. — vacip

Charles Williams Charles Williams · Accepted Answer · 2016-10-17T11:38:30

Thats a rather buggy UDF: Evaluate always takes unqualified references as referring to the active sheet. You really need to use Worksheet.Evaluate. Try something like this that assumes that any unqualified references in Ref are to the sheet that Ref is on

Function Eval(Ref As range)
Application.Volatile
Eval = ref.parent.Evaluate(Ref.value)
End Function

Or if you want it to refer to the sheet that the UDF is being called from try this

Function Eval(Ref As variant)
Application.Volatile
Eval = Application.Caller.Parent.Evaluate(Ref)
End Function

There are also a number of strange things/Quirks/bugs with evaluate you should be aware of: see my blog post https://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/

Custon fucntion applies to entire workbook instead single sheet

1 Answers