Write a function call into an Excel cell from VBA

[Conceptor]

Hi,

I am trying to write a custom function call into an Excel
cell from VBA. The string formula that contains this
function call is:

=Finance("Cube1";"01";"Amount
Budget";"AUTREC,AVANCE,AVOIR,CAP";"2002,2003";"1,2,3,4,5,6"
)

If I go into excel and manually type this formula into any
cell, Excel accepts it without problems and even returns
the correct value.

If I try to put the formula into the cell from VBA code,
like in:

sFormula = "=Finance(" & Chr(34) & "Cube1" & Chr(34) & ";"
& Chr(34) & "01" & Chr(34) & ";" & Chr(34) & "Amount
Budget" & Chr(34) & ";" & Chr(34)
& "AUTREC,AVANCE,AVOIR,CAP" & Chr(34) & ";" & Chr(34)
& "2002,2003" & Chr(34) & ";" & Chr(34) & "1,2,3,4,5,6" &
Chr(34) & ")"

ActiveCell = sFormula

Excel says something like "Object or application error" (I
have a french error message, so this is the closest
translation I could come up with).

I got a more meaningful error message when I tried this

'Remove the "=" from the formula string
sFormula = "CRAPCRAPCRAP" & Right(sFormula, Len
(sFormula) - 1)

ActiveCell = sFormula

'put back the "=" into the formula cell
Call ActiveSheet.Cells.Replace("CRAPCRAPCRAP", "=")

Excel still sends an error message but more meaningful
(again, from french...):

Execution Error 1004: The formula contains an error

I have verified the following:
1) the formula is valid
2) the problem ain't the quotes (")
3) the problem isn't the space between "Amount"
and "Budget" in the function call

The code is put into the same module as the Finance
function and into the same Excel file and project. I
tried to simplify things as much as I could. The formula
doesn't even contain references to other cells.

Why won't it work? Am I missing something?

Is there a way to make it work?

Thanks!
C.

 

[Tom Ogilvy]

when you enter the formula using the formula method, the formula should be
in the US format with a comma separator. If you want to enter it as you
would in the worksheet you should use
FormulaLocal =


But you could just use application.substitute to change your string:

Sub Tester4()
sFormula = "=Finance(" & Chr(34) & "Cube1" & Chr(34) & ";" _
& Chr(34) & "01" & Chr(34) & ";" & Chr(34) & "Amount " _
& "Budget" & Chr(34) & ";" & Chr(34) _
& "AUTREC,AVANCE,AVOIR,CAP" & Chr(34) & ";" & Chr(34) _
& "2002,2003" & Chr(34) & ";" & Chr(34) & "1,2,3,4,5,6" & _
Chr(34) & ")"
sFormula = Application.Substitute(sFormula, ";", ",")
Debug.Print sFormula
End Sub

 

[Conceptor]

Solved it. Thanks!