Can't set cell.formula in Excel XP but Excel 2000 is OK

[Lars Gustavsson]

Hi!

I have created a VB 6.0 app that read/writes to Excel Templates via
OLE using late binding.

The main problem is that it seems impossible to write a formula to a
cell in sheet in Excel XP, but it works OK for one formula (or
reference) in Excel 2000.

The VB code looks like below,

'---
xlWB.Sheets("Report").Activate
xlWB.Sheets("Report").Range("B5").Select
xlWB.Sheets("Report").Range("B5").Formula = _
IIf(Left$(sFormula, 1) = "=", sFormula, "=" & sFormula)
'---

The formula in the example is like, sFormula = "='1'!B1"
and will only work in Excel 2000.

The formula in the example is like, sFormula =
"=Average('1'!B1;'2'!B1)"
it will won't work in either Excel versions.

How come? I would appreciate any comment on this.

TIA

Rgds

Lars, SWEDEN

 

[Tom Ogilvy]

the first formula should work in either version.

the second is using your regional list separator, so you need to use
FormulaLocal rather than Formula or you need to replace the semicolon with a
comma.