Excel-DNA keep calculating formulas

[Mateusz K]

Hi,

I've written one formula with Excel-DNA. I'm returning some value not only to cell in which that formula is, but also to 7 other cells. One of that cell is the input for the same formula. 

And it goes 12 times same way. It suppose to work as a cascade. 

1->2->3->4->...->12. 12 is not connected to 1st. It just goes one way. 

I'm having problem when I change any of these input cells. Instead of calculating just one time it keeps to calculate over and over again.

What can I do? Or what am I doing wrong?

[Govert van Drimmelen]

Normally Excel prevents you from writing to other parts of the sheet from inside a UDF in a calculation.

That prevents this kind of problem.

 

If you do use one of the mechanisms to circumvent this restriction, like queueing a macro with the ExcelAsyncUtil.QueueAsMacro mechanism, or using some trick based on the COM object model, then Excel cannot track the relationship between cells changing and updates required for the calculation.

Then it’s like a circular reference that Excel does not detect (and would stop anyway).

 

To get around this is tricky.

One plan might be to check the cells that you’re writing to, and one write if the value is different.

 

But the real answer is to restructure your model and functions so that the writes are never required, and you follow the functional spreadsheet model that Excel works best with, where every UDF just takes some input values and returns a value or array directly from the UDF.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/17bf52b1-2f6a-4014-ac0b-595190964958n%40googlegroups.com.

[Mateusz K]

Thank you.

Hint with checking cell's values and updating only when necessary is worth exploring.

As for "going back to basics" and writing UDF which only returns one value - that would be very, very tricky in my case. I'm doing calculations based on 12 (yes, 12) cells. There are many conditions and calculations within my UDF which depends on what that input is. 

Thanks again,
Mateusz