Can i use a function doing macros?

jfca283 · May 23, 2023, 7:02am

Can you guide me?
I know how to use vba and call functions as sumifs or vlookup coding.
But I can’t find examples in BASIC performing that idea.
Suppose I have numbers in A1:A10 and gender (M o F) in B1:B10.
I’m trying to obtain the result in H3 doing: sumifs(a1:a10;H1;b1:b10;H2)
And H1 and H2 are the criteria for each range.

On VBA you can receive the result directly in H3 or write the formula in the cell.
Here in BASIC I’ve read some old posts saying that It is not possible doing that.
Am I right?

Thanks for your ideas and time, folks.
Have a nice day.

Lupp · May 23, 2023, 9:14am

(Leaving aside the explicit error already named regarding the FunctionAccess service).

Now that you have clarified that the example was invented with the intention of learning from it, and @MikeKaganski has pointed out the essential restriction for UserDefinedFunctions, I would like to make an attempt to describe more generally the actual problem that exists even after the obviously needed correction:

What means are there to circumvent the said restriction?
How to call a subroutine with parameter values without using a UDF?

Do you agree?
If necessary, perhaps each of the two questions should be asked separately, even if they are factually related.

Don’t expect very simple answers of a few words. (And don’t expect me to answer the questions immediately.)

mikekaganski · May 23, 2023, 7:05am

Using the under-rated built-in help, there is a topic named “Using Calc Functions in Macros”

But generally, your question is so much unclear, that the proper answer could be completely different.
E.g., one obtains the result of “sumifs” in H3 by typing the SUMIFS formula in H3 without any macro - you never explained why a macro is needed.

One might use “multiple operations” feature to get results of the same formula for a set of input values, for a “what-if” analysis.

One can use macros to fill any cell in a sheet; but one should not try to use a user-defined spreadsheet function (i.e., a macro that is called as a Calc function) to fill cells, because UDFs are expected to not produce side effects, only to return a value that Calc will put into the proper cell itself… It would be good if you provided some references, when wrote “I’ve read some old posts saying…”.

jfca283 · May 23, 2023, 7:17am

I tried this:

Sub SumarSiConjuntoMacro
    Dim oSheet As Object
    Dim oFunction As Object
    Dim oRange As Object
    Dim oResultCell As Object
    

    oSheet = ThisComponent.CurrentController.ActiveSheet
    

    oRange = oSheet.getCellRangeByName("A1:A10")
    

    oResultCell = oSheet.getCellByPosition(1, 0)
    

    oFunction = CreateUnoService("com.sun.star.sheet.FunctionAccess")
    

    Dim aParams(1) As New com.sun.star.beans.PropertyValue
    aParams(0).Name = "Criteria"
    aParams(0).Value = ">500"
    

    oResultCell.Value = oFunction.callFunction("SUMIFS" ,Array(oRange, oRange, aParams()))
    
End Sub

But I get :

Type: com.sun.star.lang.IllegalArgumenException
Message...funcuno:cxx:645.

The idea of coding this is learning.
That’s why the idea is a bit strange.
And sometimes, you don’t want use pivot tables.
Thanks

mikekaganski · May 23, 2023, 7:25am

As someone who had put some effort to avoid user mistakes, could I ask you: you used a Suggest a solution button to post a clarification to your question, while the name of the button was specifically chosen to tell user that it is not a proper thing to use for clarifications (one best edits the question, or at least uses comments). Could you please describe, how could we improve the site to make it more obvious?

Why do you think that you use PropertyValue array to pass arguments to functions? You just pass a sequence of arguments the same way as you pass them to the function on the sheet, without naming them. Like

   oFunction.callFunction("SUMIFS", Array(oRange, oRange, ">500"))