PDA

View Full Version : Solved: How do I force recalulation of custom function on Workbook_Open?



Wizard
09-11-2007, 06:57 AM
I have a custom function that, when the workbook is opened, shows a #VALUE! error.

If I force a recalculation by any of a number of methods, the function behaves itself and displays properly. But I don't want the users having to take an extra step to fix something that should be automatic... not to mention that the apparent errors are going to confuse users on two continents, and lets just not go there.

I've tried adding "Application.Volatile = True" to both the Workbook_Open code and the Custom Function itself (first line in both cases) and "Application.Calculate" (or Recalculate) to the Workbook_Open code, but it hasn't worked. Events are Enabled, of course.

Any clues as to what I'm missing?

thanks
Wizard

Bob Phillips
09-11-2007, 07:20 AM
I think you need to examine the UDF and see why it is returning the 'VALUE rather than trying to frig it. What does it and the data look like?

unmarkedhelicopter
09-11-2007, 07:45 AM
Strip down the workbook to the minimum size you can and still have the error occur, then post it.

Wizard
09-11-2007, 07:53 AM
Of course, once I get the file stripped down to the safely-anonomized leven, the problem has vanished. :bug: Of course, it still lives on the copy I can't post... will keep you posted...

unmarkedhelicopter
09-11-2007, 11:04 AM
It 'may' be a corrupted WB, reintroduce stuff bit by bit till you get the problem then remove the last bit and build it from scratch.

Wizard
09-11-2007, 11:15 AM
This is a quite complex report, with internal macros, entirely built by VBA. It works fine as it's built, but after it's completed, saved, closed & then re-opened, that's when it gives the #VALUE error on those custom-function cells.

To top it off, I can't manage to anonymise the data without making the spreadsheet recalculate (even with it on Manual Recalc) so the problem goes away.

I'm going to have to let this one go for now, somebody just handed me a hot potato.

Thx
Wizard

geekgirlau
09-11-2007, 08:36 PM
It's probably due to the time required to actually perform the calculation in the full-sized workbook. What if you had the formula containing the UDF in a hidden column, and then another formula referring to that column displaying something like this for the user:

=IF(ISERROR(E2),"Calculating …",E2)

Wizard
09-12-2007, 05:48 AM
It's probably due to the time required to actually perform the calculation in the full-sized workbook. What if you had the formula containing the UDF in a hidden column, and then another formula referring to that column displaying something like this for the user:

=IF(ISERROR(E2),"Calculating ?",E2)

Tried it, and no, it doesn't work. :( I'm sure it's not a time issue... It's not really that large a spreadsheet - it recalcs in <1sec when I force it to, and I've waited 3 minutes after opening to see if it finally finds its backside with both hands and a flashlight, and it just doesn't do.

Thanks for the suggestion.....

Wizard
09-12-2007, 11:35 AM
Solved:
I said in the initial post that
"I've tried adding 'Application.Volatile = True'... "

Yup, I sure did. And it sure didn't work.

Problem was the correct syntax is 'Application.Volatile(True)'.

:banghead:

Why VBA didn't halt on the syntax error is beyond me.

Thanks for your responses & sorry I wasted your time.

Wizard (the Mortified)

geekgirlau
09-12-2007, 06:02 PM
And why is there a difference in syntax? Why do we have

Application.EnableEvents = True
and
Application.Volatile(True)

rory
09-13-2007, 02:43 AM
It's because EnableEvents is a property whereas Volatile is a method.

geekgirlau
09-13-2007, 03:17 PM
Don't go getting all technical on me Rory - the girl needs to vent!!! :tease:

rory
09-13-2007, 03:26 PM
Ah:
Application.EnableVents = True
:)

geekgirlau
09-13-2007, 03:41 PM
:rotflmao:

unmarkedhelicopter
09-14-2007, 02:28 AM
I have some code I use when debugging :-

If Application.Code(NotWorking) Then
Application.SitInCorner.Cry = True
End If

rory
09-14-2007, 02:32 AM
Simple solution:
If Application.Code(NotWorking) Then Application.AutoCorrect

:)

unmarkedhelicopter
09-14-2007, 02:49 AM
Yeah but then it's like the guy who complains "I hate it when my computer does what I ask, rather than what I want !" So I'd only be interested in that option if it could take some parameters e.g. :-

Application.AutoCorrect(WishList1, WishList2, WishList3, IfAllElseFails: = ALargeBeer)

YellowLabPro
09-14-2007, 02:58 AM
Can I get a copy of the vba that runs that code, it does not work on my computer as written....

rory
09-14-2007, 03:29 AM
Must be a glitch in the compiler - hit it with a spanner...

johnske
09-14-2007, 03:36 AM
If Application.Code(NotWorking) Then :igiveup: