Array Formulas with ExcelCOM_UDF?

[NewtonScripter]

I am currently using the ExcelCOM_UDF.au3 and I was looking through the list and didn't see an array formula function.

I just learned about array formula function and I think I need it.

So just to make sure, is there a function to add in array formulas?

Or is it possible to create one? and if so, how.

[junkew]

I am currently using the ExcelCOM_UDF.au3 and I was looking through the list and didn't see an array formula function.

I just learned about array formula function and I think I need it.

So just to make sure, is there a function to add in array formulas?

Or is it possible to create one? and if so, how.

What do you mean with the array formula function? Sample?

If you check the ExcelCom_UDF.AU3 file you can see the logic and most likely then are able to add functionality yourself

[NewtonScripter]

What do you mean with the array formula function? Sample?

array formulas have to be entered with CTRL+SHIFT+ENTER

so it's a lil weird

A thing I want to do is add the number of 8 numbers in a row that are greater than a specified number in an another cell

so it looks like this

{=SUM(1*($C2:$C9>$A$8)}

You don't enter it in with the {} though, or it doesn't work

and you can't enter it like a reg formula also

you type in =SUM(1*($C2:$C9>$A$8) and then CTRL+SHIFT+ENTER

If you check the ExcelCom_UDF.AU3 file you can see the logic and most likely then are able to add functionality yourself

I took a look at the file. I don't understand where you find all the variables and functions. Like this line:

$oExcel.Activesheet.cells($RangeorRow).Formula = $sFormula

is there a website that lists the properties of excel?

i am having trouble finding it.

Edited July 21, 2008 by NewtonScripter

[junkew]

You should use property "formula"

You should enter a proper formula (you missed closing brace)

Below sample should work

$oExcel = ObjCreate("Excel.Application")
If NOT IsObj($oExcel) Then consolewrite("Something wrong creating excel instance")
$oExcel.Visible=1

$wb = $oExcel.workbooks.add

$wb.worksheets(1).cells(1,1).formula="=SUM(1*($C2:$C9>$A$8))"

Edit: Fixed formula to be english instead of dutch (SOM instead of SUM)

Edited July 22, 2008 by junkew

[NewtonScripter]

Where do you find information about the COM for excel? (Is it COM?)

Like how did you know to fill out a cell you use something like this

$o_Excel.Activesheet.Range("B1").Value = "hello"

[NewtonScripter]

You should use property "formula"

You should enter a proper formula (you missed closing brace)

Below sample should work

$oExcel = ObjCreate("Excel.Application")
If NOT IsObj($oExcel) Then consolewrite("Something wrong creating excel instance")
$oExcel.Visible=1

$wb = $oExcel.workbooks.add

$wb.worksheets(1).cells(1,1).formula="=SUM(1*($C2:$C9>$A$8))"

Edit: Fixed formula to be english instead of dutch (SOM instead of SUM)

I just tried your code... well, kinda

I created a spreadsheet with column A goes from 1 to 100

And then i used this line of code:

$o_Excel.Activesheet.Range("B3").Formula = "=SUM(1*(A1:A9>5))"

which is basically the same format

what it does is for the numbers 1 to 9, it prints out how many are greater than 5

when i just use that code it outputs 0

but when i do it manually and CTRL+SHIFT+ENTER it gives the correct answer of 4

Okay I just read about the OLE/COM Object Viewer

Now the next step for me is to find the one with Excel.Application right?

I don't know how to find the one with the VersionIndependentProgID = Excel.Applciation

can someone point me in the right direction?

Edited July 23, 2008 by NewtonScripter