ExcelReference.SetValue with Formula

[Fred]

Hi,

I don't find how to set a formula in a cell.
Ex with a ExcelReference if i do xlRef.SetValue("=SUM(A1:A3)"); i show
in the cell the text formula and not the result.

Anyone has a response ?

[Govert van Drimmelen]

Hi Fred,

At last! - I have a suggestion for one of your questions:
ExcelReference.SetValue internally calls XlCall.Excel
(XlCall.xlSet, ...) which just sets the cell contents. To set the
formula you need xlcFormula:
Try this:
bool result = (bool)XlCall.Excel(XlCall.xlcFormula, xlRef,
formula);
I think you need to use R1C1 references in your formula.

For array formulae, you use XlCall.xlcFormulaArray.

Let us know if it works.

--Govert--

[Fred]

Hi

thanks for your reply.
I have already test it with :
XlCall.Excel(XlCall.xlcFormula, new ExcelReference(5,5), "=TODAY
()");
with or without "=" before the function, but it doesn't work
I have the error "Invalid reference".

Thanks

[Govert van Drimmelen]

Hi Fred,

Sorry, the arguments are the other way around. Try:
XlCall.Excel(XlCall.xlcFormula, "=TODAY()", new ExcelReference(5,5) );

In some cases you might like to use
XlCall.xlcFormula | XlCall.xlIntl
which I think allows you to use English formula names in international
versions.

--Govert--

[Fred]

Do you have a sample for the FormulaArray use ?

Thanks

[Fred]

Thanks it works !

[Jack Woodward]

SetValue is extremely fast is there any way to get excel to evaluate the formula after the fact? 

[Govert van Drimmelen]

Hi Jack,

You can check this thread: https://groups.google.com/d/topic/exceldna/l15SNQZ0-0w/discussion

ExcelReference.SetValue sets the value, and not the formula of a cell. So it's like putting a ' in front of the formula text in the cell.

To set the formula, you need to use XlCall.xlcFormula. After using this to set the formula, Excel should automatically recalculate the cell (if the book is not in Manual calculation mode.)

Regards,

Govert

[Jack Woodward]

Hi Govert,

Is there an overload of xlcFormula that takes a multi-cell ExcelReference and an array of forumlas similar to how xlSet works?  The com api has this functionality through range.Formula so I was thinking there might be a C equivalent.

xlcFormula just puts the array in a single cell.

Thanks,

Jack

[Govert van Drimmelen]

Hi Jack,

Nothing that I know of. 

There is xlcFormulaArray to set array formulas, but I don't think that's what you're asking.

Regards,

Govert