SumProduct Function in Excel

Question:

I am trying to get the SumProduct() from an Excel Application Object. But I am getting the error
3069 Assignment not legal here. (Use == for equality testing) on line v = oXL.SUMPRODUCT((RANGE="BOOKS")*(REGION=6101)*(UNITS))

Answer:

What is the meaning of those parameters?

Stuff to try...

v = oXL.SUMPRODUCT((::RANGE="BOOKS")*(::REGION=6101)*(UNITS))

v = oXL.SUMPRODUCT(::RANGE="BOOKS") * oXL.SUMPRODUCT(::REGION=6101) * oXL.SUMPRODUCT(UNITS)

user response:

No that code doesn't work! Note: I can get it to work with a wsc by substituting , for * but the values have to be changed to logical array elements, and VBA has a N() function, or the Evaluate() function. Do a google search for application.sumproduct...

Answer:

I looked the stuff up on Google. Interesting function. But it uses different rules of behavior.

Do you have an example of a VB program that can access the info (that is not just a straight Excel Macro in VBA)?

I would almost suggest packing the parameters in a single string and pass the whole string in.

Basically, as the rules are that parameters should be resolved "inside out, as from the most nested innermost parenthesis working out...and this function does not work that way, there will be issues. So yes, and "Evaluate" kind of function supported by the target app might be what is needed.

;Courtesy of Jay Halverson
;;;;;;;;;;;;;;;;;; save this puppy! ;;;;;;;;;;;;;;;;;;;;;;;;;;; this one took me 2 hours to figure out...

; value = sc.Eval(`ExcelDB.Sum(Worksheet.Range("TestRange"))`)
; message("Debug", value)

I'm not sure about "REGION" but range can just be referred to in a different manner (the above is VBScript).

I know this works in Excel. In Winbatch can't you just pass the equivalent WBT object for the range? or am I missing your point?


app.Function(Worksheet.Range("TestRange1")*Worksheet.Range("TestRange2"))

user response:

SumProduct and its partner SumIf are used to perform conditional calculations. They can both be called from the Excel application object. Unlike functions such as Average, Count, Min, Max... which can be called as application.worksheetfunction.function, using the interim worksheetfunction. blows up.

One can always find an empty cell, input a SumProduct formula, and retrieve the value, however I am looking at situations where the spreadsheet is read-only, so the calculation needs to be performed from the application object.

Answer:

Basically I think that, in altered form, WinBatch has no hope of dealing with the SumProduct as originally posted.

However embedded in some kind of


xxx.Eval("SumProduct this that other")
or 
xxx.Evaluate("SumProduct this that other")

then it has a chance of working since the statement itself is passed to the target application for execution.

Article ID:   W16626

File Created: 2005:02:18:12:21:40

Last Updated: 2005:02:18:12:21:40

Database Search

TechHome