Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel forgets Custom VBA Function (#Name error)
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel forgets Custom VBA Function (#Name error)
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 Avery wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel forgets Custom VBA Function (#Name error)
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" wrote in message ups.com... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel forgets Custom VBA Function (#Name error)
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel forgets Custom VBA Function (#Name error)
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel forgets Custom VBA Function (#Name error)
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. R Avery wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel forgets Custom VBA Function (#Name error)
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... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel forgets Custom VBA Function (#Name error)
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#VALUE! error with custom excel vba function | Excel Discussion (Misc queries) | |||
Excel forgets where Names are from. | Excel Discussion (Misc queries) | |||
Custom function returning VALUE error | Excel Discussion (Misc queries) | |||
clipboard forgets on call back | Excel Programming | |||
Adding a custom function to the default excel function list | Excel Programming |