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.
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
On May 17, 10:56 pm, Chezky <chezkyr...@gmail.com> wrote:
> 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.
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
On May 18, 10:51 pm, Chezky <chezkyr...@gmail.com> wrote:
> 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
> On May 17, 10:56 pm, Chezky <chezkyr...@gmail.com> wrote:
> > 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.
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...
On Tuesday, May 17, 2011 10:56:50 PM UTC-4, Chezky wrote:
> 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.
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
> 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...
> On Tuesday, May 17, 2011 10:56:50 PM UTC-4, Chezky wrote:
> 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
> -- > 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 exceldna@googlegroups.com.
> To unsubscribe from this group, send email to > exceldna+unsubscribe@googlegroups.com.
> For more options, visit this group at > http://groups.google.com/group/exceldna?hl=en.
On Friday, June 29, 2012 3:21:38 AM UTC-4, sysmod wrote:
> 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...
> On Tuesday, May 17, 2011 10:56:50 PM UTC-4, Chezky wrote:
>> 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
> -- > 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 exceldna@googlegroups.com. > To unsubscribe from this group, send email to > exceldna+unsubscribe@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/exceldna?hl=en.
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");
> I have ExcelDna - ExcelFunction which i want to call (i.e. recalculate) on
> F9
> On Friday, June 29, 2012 3:21:38 AM UTC-4, sysmod wrote:
> > 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...
> > On Tuesday, May 17, 2011 10:56:50 PM UTC-4, Chezky wrote:
> >> 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
> > --
> > 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 exceldna@googlegroups.com.
> > To unsubscribe from this group, send email to
> > exceldna+unsubscribe@googlegroups.com.
> > For more options, visit this group at
> >http://groups.google.com/group/exceldna?hl=en.
> 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:
> 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
>> On Friday, June 29, 2012 3:21:38 AM UTC-4, sysmod wrote:
>>> 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...
>>> On Tuesday, May 17, 2011 10:56:50 PM UTC-4, Chezky wrote:
>>>> 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
>>> --
>>> 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 exceldna@googlegroups.com.
>>> To unsubscribe from this group, send email to
>>> exceldna+unsubscribe@googlegroups.com.
>>> For more options, visit this group at
>>> http://groups.google.com/group/exceldna?hl=en.
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:
> > 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....
> > 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
> >> On Friday, June 29, 2012 3:21:38 AM UTC-4, sysmod wrote:
> >>> 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...
> >>> On Tuesday, May 17, 2011 10:56:50 PM UTC-4, Chezky wrote:
> >>>> 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
> >>> --
> >>> 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 exceldna@googlegroups.com.
> >>> To unsubscribe from this group, send email to
> >>> exceldna+unsubscribe@googlegroups.com.
> >>> For more options, visit this group at
> >>>http://groups.google.com/group/exceldna?hl=en.
> 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:
> > > 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....
> > > 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
> > >> On Friday, June 29, 2012 3:21:38 AM UTC-4, sysmod wrote:
> > >>> 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...
> > >>> On Tuesday, May 17, 2011 10:56:50 PM UTC-4, Chezky wrote:
> > >>>> 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
> > >>> --
> > >>> 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 exceldna@googlegroups.com.
> > >>> To unsubscribe from this group, send email to
> > >>> exceldna+unsubscribe@googlegroups.com.
> > >>> For more options, visit this group at
> > >>>http://groups.google.com/group/exceldna?hl=en.
