Calculation of an UDF

[Werner Rohrmoser]

Hello,

I've made an UDF to determine, whether a cell has a formula or not,
see listing below:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function CellHasFormula(Cell As Range) As Variant

' Error trapping
On Error GoTo FuncFail:

' Exit, if cell has formula and cell isn't recalculated.
If IsEmpty(Cell) And Len(Cell.Formula) > 0 Then Exit Function

' determines whether cell has formula
CellHasFormula = Cell.HasFormula
Debug.Print Cell.Row & " / " & Cell.Column
Exit Function

' Error trapping
FuncFail:
CellHasFormula = CVErr(xlErrNA)
End Function
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The tested cell is referenced by the argument of the UDF, so it should
calculate whenever the referenced cell is changed and in general it
works as it should.

But now, the part I don't understand.
I have a cell which is no precedent to the cell with the udf and I can
change it in two ways:
1. I select a value from the validation dropdown
2. I enter a valid value manually into the cell.

When I do it the first way, the UDF is calculated and when I do it the
second way it is not calculated.

Is it a difference to use validation drop down or not?

Regards
Werner

Excel XP SP 3
WIN XP SP 3

 

[Spiky]

Hello,

I've made an UDF to determine, whether a cell has a formula or not,
see listing below:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function CellHasFormula(Cell As Range) As Variant

' Error trapping
On Error GoTo FuncFail:

' Exit, if cell has formula and cell isn't recalculated.
If IsEmpty(Cell) And Len(Cell.Formula) > 0 Then Exit Function

' determines whether cell has formula
CellHasFormula = Cell.HasFormula
Debug.Print Cell.Row & " / " & Cell.Column
Exit Function

' Error trapping
FuncFail:
CellHasFormula = CVErr(xlErrNA)
End Function
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The tested cell is referenced by the argument of the UDF, so it should
calculate whenever the referenced cell is changed and in general it
works as it should.

But now, the part I don't understand.
I have a cell which is no precedent to the cell with the udf and I can
change it in two ways:
1. I select a value from the validation dropdown
2. I enter a valid value manually into the cell.

When I do it the first way, the UDF is calculated and when I do it the
second way it is not calculated.

Is it a difference to use validation drop down or not?

Regards
Werner

Excel XP SP 3
WIN XP SP 3

I think using the automatic entry from the drop-down triggers a
calculation. But entering does not, because it's a UDF.

Excel supposedly has 2 modes: Autocalc & Manual Calc. But clearly,
UDFs generally need the user to press F9, even when in Autocalc mode.
So it apparently has a dual-mode in some situations.

You could add a Worksheet_Change event code to make sure it calculates
no matter what.

 

[Werner Rohrmoser]

Hi,

my problem is not that my UDF does not calculate. It works fine,
whenever a precedent has changed my UDF calculates. The case I don't
understand is, that it calculates also when I use the validation
dropdown, even
when the precedent is unchanged.

Regards
Werner

 

[Werner Rohrmoser]

Hi,

my problem is not that my UDF doesn't calculate.
Whenever a referenced precedent changes the UDF is recalculated - as
it should.
My problem, a performance problem, is, that the UDF calculates and it
shouldn't.
When I use the dropdown of cell with validation and this cell or
dependents of this cell
aren't precedents of the cell with the UDF it still calculates.
That extends the calculation time.

Regards
Werner

 

[Spiky]

it calculates also when I use the validation

dropdown, even
when the precedent is unchanged.

You may not wish it, but what I'm saying is that it is normal. I'm
guessing the drop-downs are classified among Worksheet_Change events
or something, so the whole sheet is calculated.

I don't know, maybe the UDF can be tweaked to prevent this?

 

[Werner Rohrmoser]

Hi Spiky,

yes, it looks like the sheet is calculated when I use the validation
drop down.
Thanks.

Werner