User Defined Functions NOT recalculating_问答_开发者

I recently took a large, stable XLSM file, and split it apart into an XLAM and XLSX. Thousands of cells in the XLSX call (udfs) functions in the XLAM, and each such udf begins with the statement "Application.Volatile" (overkill,开发者_Python百科 to force recalc).

The XLSX will NOT recalc with F9 thru Ctrl-Alt-Shift F9, nor with Cell.Calculate thru Application.CalculateFull. The XLSX cells are simply "dead" ... but ... I can reawaken them one by one if I hit F2 to edit the formula and then hit ENTER. Cells reawakened this way seem to stay awake, and recalc normally thereafter.

Has anyone encountered this strange behavior and are there any additional ways to force Excel to reconstruct the calc graph from scratch that I should try ?

One additional note in case it matters: I opened the XLAM and the XLSX via File Open, and have not installed the XLAM using the File ... Options ... Addins route - because in the past when I have done so, the minute you "uncheck" and installed XLAM then all the UDF references get replaced by full pathname links - pretty ugly. Alternatively if someone can outline a workaround for installing XLAM addins that doesn't create broken links everywhere I'll go with that.

This works:

Sub Force_Recalc()
    Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Figured it out - not sure why Microsoft has this "feature":

The condition arises when a virgin XLSX that uses an XLAM function is opened / created prior to opening the XLAM. In this case no amount of cajoling will cause the XLSX formulas to bind to and execute those XLAM functions, UNLESS you go into each cell & touch the formula bar & hit ENTER (or, as I discovered, do so en masse via a global replace - in my case all the funcs began w a "k", so globally replacing "k" with "k" fixed the error). The problem does not occur if the XLAM is opened first.

For UDFs who can access to Application instance could use:

Application.CalculateFull()

MSDN source here

You can force recalculation in this situation by search and replace on the = which is at the start of all formulae. You can also make this into a macro and map it to a key combination.

Edited to add

See the macro Greg Glynn has in his answer.

One possible solution: Set calculation mode to manual, then back to automatic

    Application.Calculation = xlCalculationManual
    Application.Calculation = xlCalculationAutomatic

Press CTRL+ALT+SHIFT+F9

This may recalc more than wanted, but it updated my UDF.

(Source)

Here is what I found. I haven't tested it but I believe there may be a work-around.

This is a direct quote: "Excel depends on analysis of the input arguments of a Function to determine when a Function needs to be evaluated by a recalculation. "

from http://www.decisionmodels.com/calcsecretsj.htm

Here's what I'm going to try later today. I am going to generate a specific address of a table, dynamically within my function. Based on why we're here, I should not get an update should the value at the calculated address change.

By including the whole table as a parameter, even without using the parameter, the function should update if anything in the table changes.

In this way, your function hits the dependency tree regardless if you actually process the whole table.

My screen shot:

User Defined Functions NOT recalculating

I had the same problem. Find and Replace works, but not very nice. My solution is:

go to Data tab > Edit links > Click Open Source will resolve this

Excel will monitor the range mentioned in the formula for any change, I faced this today and I was thinking and realized that. So to fix it, make a dummy argument in your function which takes the range you want to monitor or create a dummy function. In my case I called it monitorRange which returns nothing

Function monitorRange(rng As Range)
End Function

and I mentioned it in my formula example

=myfunction(a,b) & monitorRange(RANGE_TO_MONITOR)

This worked very well and it should work with any other function

Ice ages after the original question, but ran into a similar problem just today where I had created a UDF to determine which values of a pivot table filter were selected. The UDF would work fine in when running in macro editor as well as when directly updating the field where the UDF was used, but would throw a "#VALUE!" when updating the sheet or pivot table.

It was killing me until I incrementally added content from my original UDF to Ali's simple monitorRange function. I then found that my UDF had a pivot table refresh statement that when removed eliminated the "#VALUE!" error. Below is the specific offending lines from my UDF, but I the general rule is that the UDF cannot have any code in its call chain that updates other workbook content. That is, the UDF must only GET values, NOT SET values.

I verified this with the following two scenarios, which both cause this error:

Refresh a pivot, e.g.: pt.PivotCache.Refresh
Change a value of another cell, e.g.: Range("AA1").Value = "Test"

Strangely, I tested and found that it is absolutely okay (kinda cool, actually) that the UDF can include a MsgBox call to display a dialog without problem. This would allow a UDF to monitor a range, then popup a msgbox dialog for any condition you wish to include in the UDF. I will keep that in mind for other situations.

Hope this helps others running afoul of this nasty litter issue.

I've tried the above solutions. The problem is, calculating all the formulae in the file takes too long when you have thousands of formula.

I tried the following solution, and it works.

In VBA Editor, set the object to Workbook, and procedure to SheetChange, and paste the following.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Target.Calculate
End Sub

I was having the same issue...I found (in another post) that adding Application.Volatile to the function code made it calculate with the spreadsheet (f9)