xlcFormula

[Chezky]

Hi,

I have a method in my xll that sets up cells to call a UDF, using
XlCall.Excel(XlCall.xlcFormula, ...), and they populate cells
correctly. The problem is that they UDFs aren't actually called until
I trigger it, by hitting F9, or by hitting enter in any cell. Is there
a way to programatically do this? I've tried following up these
xlcFormula calls with a call to XlCall.Excel(XlCall.xlcCalculateNow);,
but this didn't seem to do the trick.

Thanks,
Chezky

[Chezky]

Another related question: How can I reference a cell from within the
formula I pass as a reference? Right now, all I can do is pass in
strings, but if I try to pass in a cell reference, I get an error.
For example, if my formula is:
"=Function(\""+variable+"\"")"
but it fails if I try:
"=Function("+A2+")"

Thanks,
Chezky

[Govert van Drimmelen]

Hi Chezky,

I think you need to use R1C1-style references when setting the
formula. So instead of A2, you put in "=Function(R2C1)".

Maybe you can convert from A1-style to R1C1-style like this:
formulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert,
formula, true, false, ExcelMissing.Value, firstCell);
where "firstCell" is a reference from which the offsets are taken.

About your original recalc question - if you make a small self-
contained .dna file with an example of what you are trying to do, I
could try to have a closer look.

Regards,
Govert

[Patrick O'Beirne]

Does "Calculate" not go away in the status bar?

Excel will not recalculate if there are too many dependencies. I forget how many, 65000 at a guess.
Check Charles Williams' FastExcel site  decisionmodels.com

Try Ctrl+Alt+F9 to recalc all
or Ctrl+Alt+Shift+F9 to rebuild calc chain
 

On 29/06/2012 04:20, Nikee wrote:

Chesky 

Were you able to fine answer for this recalculation ? 

In Excel it does not recalculate even with F9; i have to go to formula bar and then hit enter or change dependant cells; then and then only it recalculates for me...

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To view this discussion on the web visit https://groups.google.com/d/msg/exceldna/-/Mrnbm60vrPoJ.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

[Govert van Drimmelen]

Hi there,

I paste below from my answer to your question on StackOverflow (http://
stackoverflow.com/questions/11255548/excel-dna-refresh-all-data-source-
and-formula-calculation)

xlcCalculate will only calculate formulae that Excel knows have to be
recalculated. You can mark an Excel function as 'Volatile' for it to
behave like Excel's NOW() or RAND() functions, which are recalculated
every time the sheet calculates. With Excel-DNA you can do it like
this:

[ExcelFunction(IsVolatile=true)]
public static string MyVolatileNow()
{
return DateTime.Now.ToString("HH:mm:ss.fff");
}
and compare with the default non-volatile case:

[ExcelFunction]
public static string MyNow()
{
return DateTime.Now.ToString("HH:mm:ss.fff");
}

Another way to push-based data for Excel is to create an RTD server or
even use the new Reactive Extensions for Excel (RxExcel) support in
the latest Excel-DNA check-ins. Some initial info here -
http://exceldna.codeplex.com/wikipage?title=Reactive%20Extensions%20for%20Excel.

Kind regards,
Govert

On Jun 29, 3:10 pm, Nikee <ocean.ind...@gmail.com> wrote:
> I have ExcelDna -  ExcelFunction which i want to call (i.e. recalculate) on
> F9

[Patrick O'Beirne]

In VBA there is a CalculateFull & CalculateFullRebuild method. Is
either available?

[Govert van Drimmelen]

Hi Patrick,

xlcCalculateNow is "Equivalent to choosing the Calculation tab from
the Options dialog box and then choosing the Calc Now button."

I don't think there's a way to do CalculateFullRebuild via the C API.
Of course you can always use the COM interface to call those methods
on the Application object.

Regards,
Govert

On Jun 29, 3:23 pm, "Patrick O'Beirne" <obeirne....@gmail.com> wrote:
> In VBA there is a CalculateFull  & CalculateFullRebuild method. Is
> either available?
>
> On 29/06/2012 14:18, Govert van Drimmelen wrote:
>
>
>
>
>
>
>
> > Hi there,
>
> > I paste below from my answer to your question on StackOverflow (http://
> > stackoverflow.com/questions/11255548/excel-dna-refresh-all-data-source-
> > and-formula-calculation)
>
> > xlcCalculate will only calculate formulae that Excel knows have to be
> > recalculated. You can mark an Excel function as 'Volatile' for it to
> > behave like Excel's NOW() or RAND() functions, which are recalculated
> > every time the sheet calculates. With Excel-DNA you can do it like
> > this:
>
> > [ExcelFunction(IsVolatile=true)]
> > public static string MyVolatileNow()
> > {
> >      return DateTime.Now.ToString("HH:mm:ss.fff");
> > }
> > and compare with the default non-volatile case:
>
> > [ExcelFunction]
> > public static string MyNow()
> > {
> >      return DateTime.Now.ToString("HH:mm:ss.fff");
> > }
>
> > Another way to push-based data for Excel is to create an RTD server or
> > even use the new Reactive Extensions for Excel (RxExcel) support in
> > the latest Excel-DNA check-ins. Some initial info here -

> >http://exceldna.codeplex.com/wikipage?title=Reactive%20Extensions%20f....

[aberglas]

Well, there is always the SendKeys hack, Crtl-Alt-F9 forces a full
recalc.

Anthony