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

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

"r" wrote:

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

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.

--
Kind regards,

Niek Otten


"r" wrote in message
...
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

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

cr