locked
Refresh all cells containing formulas in Excel RRS feed

  • Question

  • hi forum :-)

    We wrote an application level add-in for Excel 2003 in c# to display financial information in excel. It also provides an UDF to manually specify what information the user wants to see in a specific cell. Now I am looking for a efficient way to update all cells containing this custom formula at once. Basically it's about updating all cells containing a formula at once. I did some research in the net, but couldn't find a good solution.

    Does anybody have experience with that ?

    Thank you all in advance & best wishes
    -ToM-
    Thursday, October 23, 2008 7:14 AM

Answers

  •  Your scenario sounds like you should be using Real Time Data server.

    If you're looking for a technique other than using application.Calculate, then perhaps you can register each cell that has this UDF, which I'm presuming calls into your C# code, by adding it to a Union of Ranges Application.Union and then doing a Calculation on that range of cells.

    Without knowing what your udf looks like, I'm going to guess...

    Sheet1!a2=DisplayData(PriceObject:=objRef,ValueToDisplay:="Price",UpdateRange:=Sheet1!a1)

    This is a lost cause, you might as well calculate the entire sheet, and if it might be cross sheet, then the whole book.

    Sheet1!a2=DisplayData(PriceObject:=objRef,ValueToDisplay:="Price")

    This is less of a lost cause, but still a pain, but at least you can identify a2 as needing a calculation. However you will have to keep track of what a2 contains in the change event because if you delete a2, then your program will still want to calculate a2 unnecessarily.

    Regards
    • Marked as answer by Tim Li Thursday, October 30, 2008 2:03 AM
    Thursday, October 23, 2008 8:46 AM

All replies

  •  Your scenario sounds like you should be using Real Time Data server.

    If you're looking for a technique other than using application.Calculate, then perhaps you can register each cell that has this UDF, which I'm presuming calls into your C# code, by adding it to a Union of Ranges Application.Union and then doing a Calculation on that range of cells.

    Without knowing what your udf looks like, I'm going to guess...

    Sheet1!a2=DisplayData(PriceObject:=objRef,ValueToDisplay:="Price",UpdateRange:=Sheet1!a1)

    This is a lost cause, you might as well calculate the entire sheet, and if it might be cross sheet, then the whole book.

    Sheet1!a2=DisplayData(PriceObject:=objRef,ValueToDisplay:="Price")

    This is less of a lost cause, but still a pain, but at least you can identify a2 as needing a calculation. However you will have to keep track of what a2 contains in the change event because if you delete a2, then your program will still want to calculate a2 unnecessarily.

    Regards
    • Marked as answer by Tim Li Thursday, October 30, 2008 2:03 AM
    Thursday, October 23, 2008 8:46 AM
  • hi incre-d

    thank you for your response. I will check out the real time data server, the application already existed and i have to reprogram it in c# because of new security reasons of the customer and they want more or less a 1:1 copy in c#, but maybe i can somehow convince them to use the RTD server.

    i think i will go the way of registering the cells containing the udf meanwhile, handle the delete/change events and update them regularly.

    thank you very much for your input :-)

    greetings from zurich
    -ToM-
    Thursday, October 23, 2008 9:11 AM
  • Hi,

    I have the same issue, but I am new to Excel and VSTO, how do you register all the cells containing the UDF?

     

    Thanks.

    Tuesday, August 9, 2011 8:53 PM