Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write a function call into an Excel cell from VBA
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write a function call into an Excel cell from VBA
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 -- Regards, Tom Ogilvy "Conceptor" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write a function call into an Excel cell from VBA
Solved it. Thanks!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Column above current cell dynamically using function call | Excel Worksheet Functions | |||
Is there a write-multi-cell-at-once function in VBS for Excel? | Excel Discussion (Misc queries) | |||
Use reference text from one cell in another function call | Excel Worksheet Functions | |||
CALL .NET FUNCTION FROM EXCEL | Excel Worksheet Functions | |||
How do i call a function to a cell? | Excel Worksheet Functions |