Basic runtime error property or method not found: currentcon

[Cherylm999]

I am new to writing function in openoffice. I wanted a function that would sum a column based on the values font color. I have researched and found from other posts this is not possible the way I want it with a dynamic range selection. There was a post of how to do it with text of the range in a function. It had to suffice. It works the first time as I put it in. When I reopen the spreadsheet I get ...Basic runtime error
Property or method not found; currentcontroller

I ok past the error message page shows and I have to force recalc and it works.

Code: Select all

REM Usage is Function AddIfColor("a1:a23") note quotes around designated range

Function AddIfColor(oRange as string)AS Double
dim oCell
colorred = rgb(255,0,0)
AddIfColor = 0
sheet=thiscomponent.currentcontroller.activesheet
rangeaddress = sheet.getCellRangeByName(oRange).getrangeaddress

with rangeaddress
       for col=.startcolumn to .endcolum
             for row = .startrow to .endrow
                  oCell=sheet.getCellByPosition (col,row)
                  if oCell.charcolor = colorred then
                         AddIfColor =AddIfColor + ocell.value
                   end if
                next
        next
end with

End Function

[Villeroy]

Without the faintest programming background you can't. What you try to do is a popular mistake anyway.

[Lupp]

There is no problem with the CurrentController, and I don''t know what actually caused the error you got the message about.

The fundamental mistake is that you tried to use the function name as an accumulator. Only on the left side of an assignment you can use the function name like a variable. Used in expression position it causes a (recursive) call to the function.

(The naming of variables is inconsistent.)

Otherwise the function is written ugly but it's workable.

Concerning the design of your sheet it is a very bad news if you think to need such a function. Never use attributes as a surrogate for real data.

By the way: If you want to get the sum of negative values this way because you used a NumberFormat displaying them in red you will fail anyway.

[Cherylm999]

Like I said I tried something that was already out there. I do know some macro writing in Excel. It seems not everything works the same or is available in Openoffice. These are not negative numbers. If they were I could use built in functions. Oh well, I will use some of the things you pointed out and clean this up.

[JohnSUN-Pensioner]

Please do not be surprised. This is not a mistake, this is the logic of Calc's work - when you open a spreadsheet, the program creates “images” of sheets that will be displayed on the screen. To do this, recalculates the values ​​of all formulas, including your function. Only after Calc is sure that there is something to display (the spreadsheet has loaded, data and formats are not within acceptable limits), a controller will be created that will begin to draw the sheet on the screen. This is a very crude description, greatly simplified, to bring you one simple idea - at the moment when your function starts to be recounted, the controller does not exist yet. The easiest and fastest way to get rid of the message (do not fix the problem, but only remove the annoying message!) - add at the beginning of your code On Error Resume Next

[Cherylm999]

Thanks JohnSun. I will try that.

[JeJe]

Lupp:

The fundamental mistake is that you tried to use the function name as an accumulator. Only on the left side of an assignment you can use the function name like a variable. Used in expression position it causes a (recursive) call to the function.

This (same as used by the OP) is fine, it uses the function return like any other variable, return is 1.

Code: Select all

Sub Main
msgbox fishing
End Sub

function fishing()
fishing = fishing +1
end function

Adding the function brackets as below calls the function recursively and gives an out of stack space message.

Code: Select all

Sub Main
msgbox fishing
End Sub

function fishing()
fishing = fishing() +1
end function

[Cherylm999]

Here is what I had to do. I had to turn off autocalculate. Once the sheet was fully loaded I could then use F9 and calculate. It just didn't like calculating the cells with that function in it as it was opening the workbook. It couldn't find currentcontroller.

[Bidouille]

Already posted: viewtopic.php?f=45&t=90364