How to make cell formula dirty (mark for recalculation)?

[Arty]

subj

[Govert van Drimmelen]

Hi Arty,

This is actually a complicated topic.

Best plan is to create an RTD server which you call from a function in
the cell.
Have a look at how FinAnSu (http://code.google.com/p/finansu/)
implements live streaming web quotes.

Some other ideas:
* For the active sheet you can call Range.Dirty.
* Set the formula to the current cell formula.
* From another thread you could
Run the macro from another thread by calling
Application.Run("MyMacro") with error checking etc.

Regards,
Govert

On Sep 26, 8:11 am, Arty <testing...@hotmail.com> wrote:
> subj

[Arty]

> * For the active sheet you can call Range.Dirty.

How to make this via ExcelDna? Why active worksheet only?

[Govert van Drimmelen]

Hi Arty,

Range.Dirty is a bit quirky. It seems if you set Sheet1!A1 to dirty,
then cell A1 on the active sheet (maybe Sheet2) will be set dirty.
This is a long standing Excel bug. Some recent discussion here:
http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/9e8362f3-e9f4-4642-bda6-a8fa3d893b04.

For doing this from Excel-DNA - you can experiment with the .dna file
below. (It requires .NET 4, just because I don't use the interop
assembly but the C# 4 dynamic support.)
It has a function GetNow() that will always return something
different, and a macro with menu (under the Add-Ins menu for Excel
2007/2010) that will set Sheet1!A1 dirty. Now add the same to Sheet2
and press the button while Sheet2 is active. Instead of Sheet1!A1
updating it will be Sheet2. I'm not sure how different Excel versions
work with this.

From the macro I guess you could active the right sheet...

Load it up (save to RangeDirty.dna and put a copy of ExcelDna.xll
called RangeDirty.xll next to it). Then put =GetNow() in cells A1 and
A2 and click the menu to update the cell - only A1 should update.

My understanding is that Range.Dirty is not a great solution for this
problem, so I don't have much experience with it and would appreciate
if you could share whatever you figure out.

Regards,
Govert

<DnaLibrary RuntimeVersion="v4.0" Language="C#">
<![CDATA[
using System;
using ExcelDna.Integration;

public class MyAddIn
{
// Note this function is not volatile,
// so shouldn't recompute unless the cell is set dirty.
// To make it volatile, add this attribute:
// [ExcelFunction(IsVolatile=true)]
public static string GetNow()
{
return DateTime.Now.ToString();
}

[ExcelCommand(MenuName="Range Dirty Test", MenuText="Set A1
Dirty")]
public static void MyMacro()
{
dynamic app = ExcelDnaUtil.Application;
dynamic range = app.Range["Sheet1!A1"];
range.Dirty();
}
}
]]>
</DnaLibrary>