#VALUE! Error

[VincentCrimmins]

Hi,

I have a spreadsheet in which I've created my own functions.

There are about 6000 cells per sheet (over four sheets) that contain the four functions I wrote in VB.

When I open the workbook, sometimes an entire sheet contains the #VALUE! error. Then I click into one cell, press return and they all fill out correctly, removing the errors. Is there away to remove this error?

Also, I've noticed three of my functions are in lowercase in the cell, yet in the VB code section they're in title case, is this significant?

Thanks!

 

[Chance1234]

Do you have application volatile in your custom functions ?

Chance,

F, G + Its official, its even on a organisation chart et all

 

[VincentCrimmins]

Hi Chance,

Sorry, I don't understand what "application volatile" is!

Can you explain it to me?

 

[Chance1234]

From the help file

Volatile Method
See AlsoApplies ToExampleSpecificsMarks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

expression.Volatile(Volatile)
expression Required. An expression that returns an Application object.

Volatile Optional Variant. True to mark the function as volatile. False to mark the function as nonvolatile. The default value is True

Example
This example marks the user-defined function "My_Func" as volatile. The function will be recalculated whenever calculation occurs in any cells on the worksheet on which this function appears.

Function My_Func()
Application.Volatile
'
' Remainder of the function
">
End Function




Chance,

F, G + Its official, its even on a organisation chart et all

 

[VincentCrimmins]

Hi Chance,

The 'Application.Volatile' doesn't appear to be having an effect.

However, I came across the 'AutoLoad Property', could that help?

Thanks

 

[Chance1234]

what happens when you go to Tools/Options/Calculation Calculate now ? do all the VALUES# dissapear ? does the smae happen when you press F9

I would loko at adding calculate on the workbook open and turning off screenupdating and using doevents

Application.ScreenUpdating = False
Application.Calculate
DoEvents

Application.ScreenUpdating = True

Finally sometimes help is to change the maximum number of iterations on the calucation script from the tools/options

Chance,

F, G + 3MSTG

 

[VincentCrimmins]

Hi Chance,

The F9 wasn't working earlier but is now (I actually think putting the Application.Volatile into each Function allows it to work now... would that make sense?).

I'm trying to get it to automatically press F9 once the sheet is updated, but the Workbook_SheetChange isn't working, is there a different event I need to run?

Thanks for all your help...

 

[Chance1234]

F9 triggers the calculate method

The VBA help will show you how you can use it

If i have a worksheet with a lot of custom functions that take a long time, i normally include a refresh button, either as a custom button or a form control o na sheet.

The refresh just carries out the code posted above

Chance,

F, G + 3MSTG

 

[VincentCrimmins]

Hi Chance,

It seems that whichever worksheet I've currently selected, it updates correctly, but then when I look into the other three sheets they all have the #VALUE! error. Then when I go into one of the other worksheets and press F9, the previously correct one now has the #VALUE! error!

 

[Chance1234]

try changing the application.calculate to the sheet(s) names ie one for each sheet

is there an order they need to be done in ? ie sheet 2 refers to sheet 1 etc

Chance,

F, G + 3MSTG