Excel UDF

[nitekram]

I am having an issue - on the excel sheet (working in excel) the Formula works, but when I try to insert it via code no luck.

>Running: (3.2.1.12):C:\Program Files\AutoIt3\beta\autoit3.exe "F:\excel stuff\x_test.au3"

F:\excel stuff\excelcom_udf.au3 (407) : ==> The requested action with this object has failed.:

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).FormulaR1C1 = $sFormula

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).FormulaR1C1 = $sFormula^ ERROR

>AutoIT3.exe ended.

>Exit code: 0 Time: 1.307

I have been able to get $formula2 to work so what gives?

expandcollapse popup

#include "excelcom_udf.au3"
$forumla = "=VLOOKUP(B18,'X:\!All Customers\[Certificate_of_Liability_Insurance.xls]Sheet1'!$B$2:$V$500,5,TRUE)"
$forumla2 = "=SUM(B1:B16)"


If FileExists("F:\excel stuff\test.xls") Then FileDelete("F:\excel stuff\test.xls")


$oExcel = _ExcelBookOpen("F:\excel stuff\excel_test.xls",1,False) ; false - ready to write - true not able to write

_ExcelRowInsert($oExcel, 20,1)
If @error Then MsgBox("",'@error',@error)

_ExcelWriteCell($oExcel, "CERT", 20,1)
If @error Then MsgBox("",'@error',@error)

_ExcelWriteFormula($oExcel, $forumla, 20, 2)
 If @error Then MsgBox("",'@error',@error)

_excelBookSaveAs($oExcel, "F:\excel stuff\test.xls")
  If @error Then MsgBox("",'@error',@error)

_ExcelBookClose($oExcel,0) ; no save
  If @error Then MsgBox("",'@error',@error)


;ADDED THE FOLLOWING EDIT
#cs
; Initialize COM error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")


$objWSC = ObjCreate("Demo.Scriptlet")

$MethodWSC_0 = $objWSC.Method1 = 0
$MethodWSC_1 = $objWSC.Method1 = 1

Msgbox(0,"Your Own COM object TEST", $MethodWSC_0)
Msgbox(0,"Your Own COM object TEST", $MethodWSC_1)

Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

#ce
; need to see if this will show any more errors

Edited January 5, 2007 by nitekram

[nitekram]

*bump*

[Locodarwin]

Ah, sorry - I'm slow to get to this one.

There's a slight issue with _ExcelWriteFormula() that might be causing your problem. It's something I'm meaning to get around to at some point.

When using A1 references in the formula you wish to write, you need to use the A1 reference scheme in the _ExcelWriteFormula() function.

This may solve the issue for you:

_ExcelWriteFormula($oExcel, $forumla, "B20")

If it does not, then you have a different problem that I will be unable to help you solve without more info. I can't run your example because in it you're referencing files I don't have.

-S

[nitekram]

Ah, sorry - I'm slow to get to this one.

There's a slight issue with _ExcelWriteFormula() that might be causing your problem. It's something I'm meaning to get around to at some point.

When using A1 references in the formula you wish to write, you need to use the A1 reference scheme in the _ExcelWriteFormula() function.

This may solve the issue for you:

_ExcelWriteFormula($oExcel, $forumla, "B20")

If it does not, then you have a different problem that I will be unable to help you solve without more info. I can't run your example because in it you're referencing files I don't have.

-S

I will try the cell reference on Monday - when I get back to work.

The cell in question just pulls a value from another excel file. Any file will do for the test - meaning the cell is not calculating any value it has its own value. It is a date - which means I will be formatting the cell later for the date to show correctly, but it all started with this formula. By the way - like I stated, it appears to be able to write the formula if you write to the cell - have you tried this?

[Locodarwin]

By the way - like I stated, it appears to be able to write the formula if you write to the cell - have you tried this?

I'm afraid there were too many pronouns in the above quote. Have I tried what, exactly?

-S

[nitekram]

I'm afraid there were too many pronouns in the above quote. Have I tried what, exactly?

-S

Sorry about that. I guess when I reread what I wrote I got confused too. But I knew what I was talking about - lol.

I have been able to _ExcelWriteCell() the $formula to the cell that was used in my code - it worked - have you tried?

So rather than using _ExcelWriteFormula() to write the formula - I wrote my $formula to the cell with your _ExcelWriteCell() and it worked, it was able to calculate the formula.

[Locodarwin]

Right, I get you now.

Excel was able to infer after the write that what you wrote was a formula, even though it was technically entered as a value. Most of the time Excel will infer a formula correctly when you enter it as a value. _ExcelWriteFormula() was introduced to force Excel to always consider the write string as a formula. When told to do this, you have the choice of writing it using R1C1 or A1 referencing. If you don't choose the one that matches the reference format of the formula you're writing, Excel will loudly exclaim blasphemous remarks about your mother. Well, my code doesn't yet take that into account.

_ExcelWriteFormula() attempts to infer which reference scheme you're using by how you've referenced the range in the formula parameters. If you used "A1" as your range, it'll presume you're using A1 referencing in your accompanying formula. If you use the row and column parameters instead (which you did in your script), then it'll presume your formula is R1C1 (which it isn't in your case).

It's a bug, and my intention is to fix it.

-S