How to force a range of cells to be evaluated

Nov 23, 2011 at 7:32 PM
Edited Nov 23, 2011 at 7:33 PM

Hello

I have written an ExcelDNA AddIn which writes values to a range of cells in a worksheet similar to:

                    b1Val                           c1Val

 a2Val           =GetData(A2, B1)          =GetData(A2, C1)

However the UDFs are not evaluated until I click on the formula cells and press return.

Is there a way to make the =GetData( ) cells automatically evaluate?

Kind Regards
Jon

Coordinator
Nov 23, 2011 at 7:40 PM

Hi Jon,

Do you mean you are writing to those cells from a macro, using something like ExcelReference.SetValue(...)?

If so, it sets the text of the cell and not the formula. You'd want to call XlCall.xlcFormula instead. Check these discussion threads:

http://groups.google.com/group/exceldna/browse_frm/thread/7274642a7c86829b

http://groups.google.com/group/exceldna/browse_frm/thread/975e52350674fb4c

Alternatively, if you are using the COM automation interface, you would call MyRange.Formula = "...' instead of MyRange.Value = "...".

Regards,

Govert

Nov 24, 2011 at 4:49 PM

Hi Govert

I was using MyRange.Formula within a macro where the range was something like A1:G10 and the value passed to the method was a string[ , ] 2-dimensional array of the correct size with the =GetData() calls in.

This resulted in the cells being set as =GetData(...).

I ended up iterating over my array and explicitly saying Cells(row, col).Formula = array[row, col] in order to make the cells evaluate automatically.

Regards
Jon