Excel forgets Custom VBA Function (#Name error)

[R Avery]

I have an addin which contains many custom functions. These functions
work perfectly most of the time, but often whenever i open a workbook
which calls these functions, there are #Name errors rather than values.


The functions are well-behaved... they are not VOLATILE and only return
values that they retrieve from a database (using ADO recordsets).

This is an annoyance because Excel seems to forget all or most of the
functions, and F9 will not make Excel remember. I have to manually go
to a cell containing the formula and re-enter it. If i do that, the
#Name error for that cell goes away, and an F9 fixes the rest of the
cells that call that function. However, i have to do this for every
single custom function i call, so i end up repeating the above for 25
functions each time i open the spreadsheet and sometimes at random
other times. It's almost as though re-entering a custom function
manually makes Excel remember that that function exists... but i need a
way for Excel to remember all of them at once, or never forget them to
begin with.

The issue has nothing to do with compiling the addin, since I haven't
changed it in months.

Has anyone ever had this problem? Does anyone know how to solve it?

 

[Dave Peterson]

Just some questions--maybe it'll help, maybe not...

What's the name of your UDF?

Have you ever had a workbook name with that same name?

Do you have any modules with that same name?

 

[Robin Hammond]

Robert,

I've seen it happen before but never figured out why. Have you tried
Application.CalculateFull rather than just F9?

I have this hanging around in an add-in to force a recalc if needed for just
this reason:

Sub FullRecalc()
If Val(Application.Version) < 10 Then
Application.Calculate
Else
#If VBA6 Then
Application.CalculateFull
#End If
End If
End Sub

Robin Hammond
www.enhanceddatasystems.com

 

[R Avery]

What's the name of your UDF?
ForwardDatesGrid

Have you ever had a workbook name with that same name?
no

Do you have any modules with that same name?
no

 

[R Avery]

CalculateFull is not an option for me because this spreadsheet is so
large that it would be a disaster if it tried to recalc itself...

I have found a solution to my problem. I bound to SHIFT-CTRL-F the
following sub:

Public Sub FixFormulas()
Dim r As Excel.Range

For Each r In ActiveWindow.RangeSelection.Cells
r.Formula = r.Formula
Next
End Sub

 

[Dave Peterson]

It might be quicker to just
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Record a macro when you do it if you really need a macro solution.

 

[awaters]

Isn't this equivalent to doing a CalculateFull?

You might try loading the workbook with AutoCalc turned off - but it is
difficult to guarantee that since it Excel sets this value to that in
the first workbook (which is a right pain).

You might also look at where the xla is stored. Is the problem
happening on the same machine the xla was written on? Has the XL ibrary
path changed etc..?

By the way CTRL+ALT+F9 forces a complete recalc. I use it all the
time...

 

[TT]

Any of the MVP's or other Tech guru's who know the cause (and remedy
pls!!) of this behavior? I posted a similar question last week or so,
but didn't get any answer.
At least now, thanks to you R. Avery, I know I'm not alone in this one!

With kind regards,
Ton Teuns