Page 1 of 1
Basic runtime error property or method not found: currentcon
Posted: 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
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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
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
Re: Basic runtime error property or method not found: curren
Posted: 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
Posted: Thu May 28, 2020 11:42 am
by Bidouille