Custom function does not fire

[r]

I have a spreadsheet that collects temperature data via a DDE link to
another program. I want to be able to record the highest and lowest values
that I get each hour. My solution was to create a custom VBA function
(actually two separate functions for high and low) that simply declared a
module level array to hold the data. I call the function from a cell in the
spreadsheet. The function is passed the current value of another cell (the
cell containing the temperature) and compares it to the value currently
stored in the array. If the new value is higher (or lower) then it is
stored in the array. If not, it is ignored. The function returns the
current value of the array element so I can see the highs or lows on the
spreadsheet in an adjacent cell.

This works fine when I MANUALLY change the temperature values and the Hi and
Low cell values change as they should. However, it does not seem to "fire"
when I am connected via DDE and getting the values that way. My hi and low
never changes regardless of the values that I have gotten via DDE.

Automatic calculation is turned on. Pressing F9 does not fix the problem.

Can anyone tell me why my custom function does not fire when I am getting my
data via the DDE link?

Thanks

 

[JNW]

Where is the function located in your code? Is it in a worksheet change event?

 

[Niek Otten]

What is the code of your function? What are the input values? How do you
call the function?

All input cells should be in the argument list of the function definition
and call, otherwise Excel is not aware of the need for recalculation.

 

[C. Roenbaugh]

Is the problem fixed if you double click the cell with your function?
Try adding Application.Volatile to the code.

cr