Macro error "object variable not set" // Pass range reference to UDF // SUMIF by color

English
,
#1

I found this code in the web, it seemed to be useful, so I inserted this function in the calc spreadsheet.


Function SumByColor(oCellRef As Range, oSumRange As Range) As Double
    Dim oSheet As Object
    Dim oCell As Object
    Dim targetColor As Long
    Dim total As Double
    Dim r As Long, c As Long
    Dim uR As Long, uC As Long
    
    ' Get target background color from the reference cell
    targetColor = oCellRef.CellBackColor
    total = 0.0
    
    ' Parse the target range dimensions
    uR = oSumRange.Rows.Count - 1
    uC = oSumRange.Columns.Count - 1
    
    ' Loop through all cells in the range
    For r = 0 To uR
        For c = 0 To uC
            set oCell = oSumRange.getCellByPosition(c, r)
            If oCell.CellBackColor = targetColor Then
                If IsNumeric(oCell.Value) Then
                    total = total + oCell.Value
                End If
            End If
        Next c
    Next r
    
    SumByColor = total
End Function

Unfortunately, when I try to call this function writing in a cell
=SUMBYCOLOR(E340; D2:D341)
the code fails immediately (apparently on the function definition line) with tjhe error “Object varioable not set”. I am a real beginner with libre office, and I really don’t know what is wrong with the code. I found a lot of similar entries in this forum, but none of the solutions proposed works.
Thank you for your understanding

Libre office version 26.2.5.2 (x86,64) on windows 11.

#2

:face_with_thermometer:

→ Basic pass a range to function to create - #2 by Zizi64

#4

Thank you for your kind interest. I must admit that the answer from Pierre created more doubts than before (in my ignorance, the range appears to be passed as “range”, not as “object”,but maybe this is not allowed either)… More practical and to the point was the answer from Lupp. Adding the VBASupport 1 and changing something in the code seems to do the trick.

option explicit
option vbasupport 1

Function SumByColor(oCellRef As Range, oSumRange As Range) As Double
    Dim oSheet As Object
    Dim oCell As Object
    Dim targetColor As Long
    Dim total As Double
    Dim r As Long, c As Long
    Dim uR As Long, uC As Long
  
	oSheet = ThisComponent.CurrentController.getActiveSheet()

    ' Get target background color from the reference cell

    targetColor = oCellRef.interior.color
    total = 0.0
    
    ' Parse the target range dimensions
    uR = oSumRange.Rows.Count 
    uC = oSumRange.Columns.Count 
    
    ' Loop through all cells in the range
    For r = 1 To uR
        For c = 1 To uC
            set oCell = osumrange.cells(r,c)
            If oCell.interior.color = targetColor Then
                If IsNumeric(oCell.Value) Then
                    total = total + oCell.Value
                End If
            End If
        Next c
    Next r
    
    SumByColor = total
End Function

this code seems to work OK. This does not mean I understood everything, I need a lot of study here, but at least it works. Thank you again.

#5

There are very many subtypes in the LibO API (ApplicationProgrammingInterface) which are summarized under the as-if-typename “Object”, among them complex services and simple structures.
The object subtype only used under VBA support is labelled “A-Range”, and its relevant highly specialised pseudo-service is “ooo.vba.excel.Range”, which isn’t documented like true LibO-API services, and interactions may fail.
LibO Basic and VBA follow extremely different concepts, and if you use VBAsupport, it’s always on your own risk.
Therefore: Try to keep code needing VBAsupport separated from LibO Basic code in an extra module. (My very simple example attached below is disregarding this rule.)

#6

OK, fine. So, if I understand correctly, the best thing I could do is re-coding the SumByColor function (having as input the sample cell and the sum range) as pure LibBasic with no VBA support. Is this feasible without driving one nuts?

#7

Trying to adapt the code for LO Basic…
So how do I check array’s parameter dimensions?

Function SumByColor(celula_ref As Variant, aSRg As Variant) As Double
	GlobalScope.BasicLibraries.loadLibrary("MRILib")
    Dim oSheet As Object, Cref As Object, SumRg As Object
    Dim targetColor As Long, r As Long, c As Long, lri As Long, lci As Long
    Dim total As Double : total = 0.0
    On Error GoTo Erro
    oSheet = ThisComponent.Sheets.getByName("Planilha1")
    print UBound(aSRg())
    print "valor célula referência: " & celula_ref

For UBound(aSRg()) I get 16.
:ok: because range = D3:D18 (from default index = 1).
But must check if there is a 2nd dimension, of course.
Also, how to get the address of celula_ref?
MRI only returns its value, as it should be.
:thinking:

#8

→ UBound Function
... [, Dimension])

maybe check Passing cell reference to basic function

#9

:+1:

So it may be impossible as per

:grey_question:

#10

@CRDF: Did you actually have a look into the example I attached to my answer below?
The “BAD” in its name doesn’t mean the provided code, but the idea to use comparison of color values as a condition.
The code works correctly, and the alternative to describe a reference by passing numeric arguments was also mentioned.

#11

No.
Give the information you try to evaluate based on colors explicitly as cell contents or formula results. That’s data. If you want to emphasize the used cells with the help of colors, you can define a conditional format.
If you urgently want to select with the help of an example cell, use a named cell style, and check for that name.
The needed code to get the example cell and the range where to search in, is the same.

#12

My goodness, I feel like i am drowning in the deluge of info you are sending me. As I said, I am really a rookie, I need time to digest all that stuff. Anyway, dear Lupp, i understand that comparing cell colors is not a good practice, but as long as the function does what I expect… I did look at your code, but I could not find the reference to passing numeric arguments.
Now in LibO basic if I wanto to call my function specifying two ranges (the sample cell and the sum range), it seems I get the contents (number or strings) of the cells, but I need the cell coordinates to check the color and the sum range limits for the for loop. Can I get these info?

No question, I need to study…

#13

But in

Function listByCellStyle(pFullRangeName, pStyleName)
    [...]
    sheet = calcDoc.Sheets.getByName(sheetName)
    [...]

function is called as
LISTBYCELLSTYLE(A1;
where A1 = <string with the reference cell for the color>
But the goal is to be able to insert directly this cell as the function parameter.
Also to get the target range origin (as the dimensions we have).

Option Explicit
Function SumByColor(celula_ref As Variant, aSRg As Variant) As Double
	'GlobalScope.BasicLibraries.loadLibrary("MRILib")
    Dim oSheet As Object, Cref As Object, oCell As Object
    Dim targetColor As Long, r As Long, c As Long, upper_1index As Long, upper_2index As Long
    Dim total As Double : total = 0.0
    On Error GoTo Erro
    oSheet = ThisComponent.Sheets.getByName("Planilha1")
    'Cref = oSheet.getCellRangeByName(celula_ref)
    REM parse target range dimensions:
    upper_1index = UBound(aSRg()) - 1 'last index of dimension 1
    upper_2index = UBound(aSRg(), 2) - 1 'last index of dimension 2
    REM get reference cell color:
    Cref = oSheet.getCellRangeByName(celula_ref)
	targetColor = Cref.CellBackColor
    REM loop through all cells in the range
    REM TARGET RANGE ORIGIN MANUALLY INSERTED! 
    For r = 2 To upper_1index + 2
    	For c = 3 To upper_2index + 3
        	oCell = oSheet.getCellByPosition(c, r)
        	If oCell.CellBackColor = targetColor Then
            	If IsNumeric(oCell.Value) Then total = total + oCell.Value
            End If
        Next c
    Next r    
    SumByColor = total
    Exit Function
    Erro: MsgBox "ERRO " & Error & Chr(10) & "na linha " & Erl
End Function

:grey_question:

#14

This is what I’m trying to figure out.

Solved by @fpy above for the range dimensions:

Option Explicit
Function SumByColor(celula_ref As Variant, aSRg As Variant) As Double
    Dim oSheet As Object, Cref As Object, SumRg As Object
    Dim targetColor As Long, r As Long, c As Long, upper_1index As Long, upper_2index As Long
    Dim total As Double : total = 0.0
    On Error GoTo Erro
    oSheet = ThisComponent.Sheets.getByName("Planilha1")
    'Cref = oSheet.getCellRangeByName(celula_ref)
    REM get Sum Range data:
    upper_1index = UBound(aSRg()) - 1 'last index of dimension 1
    upper_2index = UBound(aSRg(), 2) - 1 'last index of dimension 2
    print "range is (" & upper_1index & ", " & upper_2index & ")"

SumRangeIndexes
SumRangeIndexes769×366 31.9 KB

 
But question remains for range origin.
E.g. D3 = (3, 2)
:thinking:

#15

Sorry, I don’t get it. The range is 16 x 3, why do you get “range is (15,2)”?

#16

Tried to run this code with
=sumbycolor(D341;D2:D341)
fails at line 14
Cref = oSheet.getCellRangeByName(celula_ref)
to be expected, since the debugger shows celula_ref = 2972,15. Do we have to translate 2972,15 to D341?

Don’t understand the REM TARGET RANGE ORIGIN MANUALLY INSERTED! i Is related to
For r = 2 To upper_1index + 2
For c = 3 To upper_2index + 3
??

#17

It’s a frequent error to disregard:
3 posts ==> 2 gaps
5 gaps ==> 6 posts

#18

Range indexes = 0 to…

Because at this time the range origin question is not solved yet! So inserted coordinates manually to get the function “working”.
And

in LO realm :thinking:

Of course: [14] is commented out.
In my “test” I did the trick of inserting “B1” at the reference cell B1
At this point I think the (logical!) code you want is impossible in LO realm (without MS VBA of course).
Tricks

#19

The discussion seems to have stopped. What I understood up to now is that you cannot pass a range to an UDF. If you try, you get the contents of the cell(s) without any indication of the actual address. This sentence

remains a mystery: how do you “identify” what you need if you do not have the necessary data? I may be completely wrong here, probably i did not understand correctly, or you need to call the function with something other than the simple range, but this obviously makes the call harder.

The only solution I found in the discussion is to use the VBA support. Indeed this solves the problem.
Is this conclusion correct? in this case, there is no use in looking for a solution in “pure” libO basic.

Sorry for the naive question, I can only repeat I am a complete ignorant beginner. Thank you for every comment.

#20

This is the correct conclusion.
There’s nothing inherently wrong with using the “VBASupport 1” option. For UDF functions, it’s more the norm.
The “VBASupport 1” option was introduced to support macros written in VBA. The developers have done a great job of emulating the properties and methods of Excel objects.
In UDF functions, by receiving a cell range as a parameter, you can immediately switch to the standard LO object.
Here’s your first macro, modified this way.

Option VBASupport 1
OPtion Explicit

Function SumByColor(oCellRefVBA As Object, oSumRangeVBA As Object) As Double
    Dim oCellRef as Object, oSumRange as Object
    Dim oSheet As Object
    Dim oCell As Object
    Dim targetColor As Long
    Dim total As Double
    Dim r As Long, c As Long
    Dim uR As Long, uC As Long
    
    oCellRef = oCellRefVBA.CellRange       ' Supports SheetCellRange Service 
    oSumRange = oSumRangeVBA.CellRange
    
    ' Get target background color from the reference cell
    targetColor = oCellRef.CellBackColor
    total = 0.0
    
    ' Parse the target range dimensions
    uR = oSumRange.Rows.Count - 1
    uC = oSumRange.Columns.Count - 1
    
    ' Loop through all cells in the range
    For r = 0 To uR
        For c = 0 To uC
            set oCell = oSumRange.getCellByPosition(c, r)
            If oCell.CellBackColor = targetColor Then
                If IsNumeric(oCell.Value) Then
                    total = total + oCell.Value
                End If
            End If
        Next c
    Next r
    
    SumByColor = total
End Function
#21

You have the necessary information, but you can’t pass it to a UDF the way you prefer without VBAsupport.
In place of a simple reference you need 5 numeric arguments which in turn call standard functions capable of evaluating ranges not for contents but for position (meta information).
With VBAsupport it’s simple, but there are also disadvantages.
The now attached example should be my final contribution to this thread.
disask_137185_offTopic_fullRangeAddress.ods (16.8 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.