Excel #value! error on opening workbook

Question

I have an Excel Workbook with a User Defined function in it.

When I open the workbook, it fires the Workbook_open event  but on opening, I have a cell with #value! in it.  If I press F2 it goes away.  I then save the Workbook and close it.

When I open it again, the #value! error is back again.

Is there a configuration setting I am missing?

Answer 1

Without testing, it is difficult to say what the problem might be. Really need to see the code for following:

• Workbook_Open  event
• UDF code
• Some values that the UDF uses.

If the UDF relies on data that only becomes valid after Workbook_Open and the UDF is attempting to run before the open event is finished then it could cause it. Inserting a MsgBox "Open event" as the last line of code in the open event and as the first line of code in the UDF insert a MsgBox "UDF" and see which MsgBox displays first.

You could try inserting Application.Calculate as the last line of code in the Open Event to force a re-calculation after the other processing.

Could also try Application.Volatile as the first line of the UDF because then if the open event changes something that the UDF relies on then it should recalculate with the new data.

If instead of pressing F2, what occurs if you just press F9 to force a re-calculation. Also, ensure that you do not have calculation set to manual.

Answer 2

Sorry its taken a while to come back but End of Financial year comes first.

I have tried the following based on your suggestions:

1. Added the two message boxes and found that the Open Event fires first so not the answer
2. Added Application.Calculate as described which did not help.  I even added the line twice as I have found that is needed in other situations.
3. Added Application.Volatile as described which did not help.
4. F9 does absolutely nothing.
5. Calculation is set to Automatic.

I have also commented out the Worksheet Open Event and simply added a Hyperlink Button to one of the worksheet cells which executes the UDF.

If I press F2 first to get rid of the #VALUE! error, the UDF runs fine vi the hyperlink button.

If I save the worksheet with no #VALUE! error showing, when I open it again, the error is back.

If I open the worksheet on a different computer, I don't get the error.