UDF - Auto refresh issue

Greetings,

I have a UDF that uses data from one sheet to display result in a second sheet. I added the “Application.Volatile” statement so that the result in the second sheet is automatically refreshed if data in the first sheet changes.

However, I have now noticed that if I run any macro in the workbook, the UDF gives “#VALUE!”. I have to manually click in the cell and hit the ENTER button in order to get the result.

A solution offered was to add the line

Please Login or Register  to view this content.

before the "End Sub" to calculate the workbook before the macro ends.

However, I have several macros in the my workbook and was wondering if something can be done at the workbook level instead of the adding the “calculate” line in every macro?

The UDF is

Please Login or Register  to view this content.

Many thanks
Asha

Asha;

I didn't look at your UDF(), but this will calculate your entire workbook everytime you change anything in the workbook

Please Login or Register  to view this content.

Or if you only want to calculate the sheet that was changed:

Please Login or Register  to view this content.

Correct, Any Subs that have names starting with "Workbook_...." go into ThisWorkbook Module
Any Subs having names starting with "Worksheet_..." go into the module for the worksheet you want then to work on.
There are exceptions, but that's a pretty safe bet.

If you want to calculate all the sheets in the workbook when any change is made on any sheet.

Try this in the workbook module

Please Login or Register  to view this content.

This will calculate all open workbooks

Please Login or Register  to view this content.

Cheers

Asha;

I get a compile error "Method or data member not found" when I run the macro after adding your code.

My mistake. I didn't test it first.
The Line

Please Login or Register  to view this content.

Should be

Please Login or Register  to view this content.

Instead of making the UDF volatile, you could pass the range of interest. Then it would recalculate only when needed.

Or, instead of recalculating all sheets on a change, you could calculate only the cell(s) in which the UDF appears.