how to pass cellrange to a user defined macro paramenter

Question

i would like to work with cellranges within my macro.

Function SumIfColor(SumRange)
    Dim oRange as object
    Dim oSheet as object

    ' Get Access to the Active Spreadsheet
    oSheet = ThisComponent.CurrentController.ActiveSheet

    ' Get access to the Range listed in Sum Range
    oRange = oSheet.getCellRangeByName(SumRange).RangeAddress
End Function

The question is how can I call this function with real cellRange object instead of String. Because getCellRangeByName works only with String variable. Because when I call the function like this

sumifcolor(B1:B3)

I got the following error: "Object variable not set"

I read some hint here but it did not helped me.

score 2 · Accepted Answer · answered Jan 21 '17 at 19:11

It is not possible to pass an actual CellRange object. One solution is to pass the row and column number, similar to the second part of @Axel Richter's answer in the link:

Function SumIfColor(lcol1, lrow1, lcol2, lrow2)
    sum = 0
    oCellRange = ThisComponent.CurrentController.ActiveSheet.getCellRangeByPosition(_
        lcol1-1,lrow1-1,lcol2-1,lrow2-1)
    For lCol = 0 To oCellRange.Columns.Count -1
     For lRow = 0 To oCellRange.Rows.Count -1
        oCell = oCellRange.getCellByPosition(lCol, lRow)
        If oCell.CellBackColor > -1 Then
            sum = sum + oCell.Value
        End If
     Next
    Next
    SumIfColor = sum
End Function

To call it:

=SUMIFCOLOR(COLUMN(B1:B3),ROW(B1),COLUMN(B3),ROW(B3))

The sum will be recalculated whenever a value in the range B1:B3 is changed, because of COLUMN(B1:B3). However, apparently changing only the color of a cell does not cause it to be recalculated.

how to pass cellrange to a user defined macro paramenter

1 Answers1