Basic runtime error property or method not found: currentcon

Creating a macro - Writing a Script - Using the API

Basic runtime error property or method not found: currentcon

Postby Cherylm999 » Thu May 21, 2020 10:16 pm

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
Last edited by Cherylm999 on Fri May 22, 2020 2:57 am, edited 2 times in total.
Openoffice 4.1.5
Windows 10
Cherylm999
 
Posts: 4
Joined: Thu May 21, 2020 9:53 pm

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

Postby Villeroy » Thu May 21, 2020 11:43 pm

Without the faintest programming background you can't. What you try to do is a popular mistake anyway.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Lupp » Fri May 22, 2020 12:04 am

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.
On Windows 10: LibreOffice 6.4 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2866
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby Cherylm999 » Fri May 22, 2020 2:51 am

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.
Openoffice 4.1.5
Windows 10
Cherylm999
 
Posts: 4
Joined: Thu May 21, 2020 9:53 pm

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

Postby JohnSUN-Pensioner » Fri May 22, 2020 5:33 am

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
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 823
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

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

Postby Cherylm999 » Fri May 22, 2020 5:36 am

Thanks JohnSun. I will try that.
Openoffice 4.1.5
Windows 10
Cherylm999
 
Posts: 4
Joined: Thu May 21, 2020 9:53 pm

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

Postby JeJe » Fri May 22, 2020 10:46 am

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
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1038
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby Cherylm999 » Sat May 23, 2020 2:17 am

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.
Openoffice 4.1.5
Windows 10
Cherylm999
 
Posts: 4
Joined: Thu May 21, 2020 9:53 pm

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

Postby Bidouille » Thu May 28, 2020 11:42 am

User avatar
Bidouille
Volunteer
 
Posts: 344
Joined: Mon Nov 19, 2007 10:58 am
Location: France


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests