CODEWITHSUNDEEP
vbaexcelvstouser-defined-functions
surfen
surfen

Reputation: 4692

VBA Excel UDF retaining original value

I have a UDF non-volatile function in VBA, which requires a global variable (callback to VSTO) initialized. This function resides in xla or xlam (same behaviour)

Is there a way to cancel calculation of the formula if the variable not yet available?

In Excel Automatic recalculation mode (I don't want to change that) Excel recalculates cells with my function every time I open a workbook, replacing value saved in a cell with error: "#ARG!".

I know how to return empty cell if my variable is not initialized, but is it possible to return original value? I checked with a debugger that it has been saved in the workbook. Trying to return Application.Caller.Value2 causes cyclic reference.

Also, the function isn't called (expected behaviour) if the VBA code is embedded in the workbook (xlsm), or if the workbook is in xls format. However, converting a workbook to xlsx causes the cells to be recalculated after workbook is opened as described.

Upvotes: 2

Views: 1006

Answers (2)

surfen
surfen

Reputation: 4692

I eventually went with the xlsm approach.

Upvotes: 0

Charles Williams
Charles Williams

Reputation: 23505

You can do this in XLM or a C XLL by flagging the function as a macro function. In VBA the only easy way I know is an ugly hack : return Application.Caller.Text if the variable is not initialised. This suffers from the major flaw that it gives you the formatted value of the cell rather than the actual value. Otherwise you have to build a way of persisting the value of the cell in the closed-but-saved workbook: possible schemes include using Defined Names, Cell Comments, the registry, external files etc, but I don't think there is a clean VBA solution.

Upvotes: 2

Related Questions