User Defined Function Opens Improperly - Round III

[SteveM]

(This a repost to bubble the query back to the top. Still seeking a
solution.)

I wrote a couple of functions with data links to a second workbook.

The data workbook is already open when I open the workbook with the
functions. However the cells containing the functions open up the
"####" error designator, not the function return value. When I open
the function dialog box attached to a cell, I can see that it
calculates properly and the return value is correct. And I can repair
the problem by simply closing the dialog box by clicking OK.

PLEASE NOTE THAT THIS IS NOT A PROBLEM WITH THE COLUMN WIDTH

If I widen the column the #### changes to this:

First the #Name? error designator appears for a moment then changes to
the #Value! designator. Which I can repair by opening and closing the
associated dialog box for just one cell. I can drag the repaired cell
down the column to repair the other cells with the same formula.

So it must be a lag linking up with the data source. But if it is, I
don't know how to fix it.

Any ideas on how to prevent the fault from appearing at all?

Thanks Much,

 

[Peter T]

Select the cell with the UDF
In the Immediate window, ctrl-g, do
? activecell.value, activecell.value2

In your UDF, declare a temporary variable same as the function, if not
explicitly declared simply
dim vTmp as Variant

just above where you do 'myFunctionName = someResult'
vTmp = someResult
debug.print vTmp

Put a break on your UDF name, F9
Open your immediate window, Ctrl-g
Select a cell with your UDF and Enter
Code will break in the VBE, step through with F8
look for anything unexpected and the debug result

Regards,
Peter T

 

[SteveM]

Peter,

As an FYI, I just solved this problem. Apparently Excel does not
recalculate User Defined functions with F9 or Application.Calculate.

It does work however with Application.CalculateFull.

Regards,

SteveM