Jump to content
Sign in to follow this  
NewtonScripter

Array Formulas with ExcelCOM_UDF?

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by NewtonScripter

Share this post


Link to post
Share on other sites
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 by junkew

Share this post


Link to post
Share on other sites
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"

Share this post


Link to post
Share on other sites
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 by NewtonScripter

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×