Excel forgets Custom VBA Function (#Name error)

R

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?
 
D

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?
 
R

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

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

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
 
D

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.
 
A

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...
 
T

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


*** Sent via Developersdex http://www.developersdex.com ***
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top