Page 1 of 1

Basic runtime error property or method not found: currentcon

PostPosted: Thu May 21, 2020 10:16 pm
by 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   Expand viewCollapse view
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

Re: Basic runtime error property or method not found: curren

PostPosted: Thu May 21, 2020 11:43 pm
by Villeroy
Without the faintest programming background you can't. What you try to do is a popular mistake anyway.

Re: Basic runtime error property or method not found: curren

PostPosted: Fri May 22, 2020 12:04 am
by 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.

Re: Basic runtime error property or method not found: curren

PostPosted: Fri May 22, 2020 2:51 am
by 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.

Re: Basic runtime error property or method not found: curren

PostPosted: Fri May 22, 2020 5:33 am
by 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

Re: Basic runtime error property or method not found: curren

PostPosted: Fri May 22, 2020 5:36 am
by Cherylm999
Thanks JohnSun. I will try that.

Re: Basic runtime error property or method not found: curren

PostPosted: Fri May 22, 2020 10:46 am
by 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   Expand viewCollapse view

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   Expand viewCollapse view

Sub Main
msgbox fishing
End Sub

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

Re: Basic runtime error property or method not found: curren

PostPosted: Sat May 23, 2020 2:17 am
by 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.

Re: Basic runtime error property or method not found: curren

PostPosted: Thu May 28, 2020 11:42 am
by Bidouille