Passing range to function with access to individual cells

Creating a macro - Writing a Script - Using the API

Passing range to function with access to individual cells

Postby wimbeuk » Sat Nov 23, 2019 5:57 pm

There is a lof of stuff on the forum about this subject, and this confuses me so much that i have no idea if it this is possible or not.
I am quite new to the basic macro language en English is not my mother language.

My problem is that i wat to pass a simple range of cells to a user defined function and in that function i want to inspect te properties of the cells in that range.
The function has to make a total of the cell values in the range depending on the color of the value of the cells.
Cells with an amount in a red color are totalized, cells with an amount in another color not.
The function gives the total of the red cells back in the return value.

The only thing that i do not know how to do is to get acces to the properties of the individual cells in the function.

I have an MS-Excel spreadsheet with a VB-macro in it which does this job very well, but i want to convert it to ibreOffice Calc.
Maybe someone can give me a hint?
Last edited by MrProgrammer on Sun Nov 24, 2019 5:57 am, edited 1 time in total.
Reason: Moved topic from Calc forum to Macors and UNO API
LibreOffice 6.1.4.2
Windows 10
wimbeuk
 
Posts: 8
Joined: Sat Nov 23, 2019 5:11 pm

Re: Passing range to function with access to individual cell

Postby RusselB » Sat Nov 23, 2019 6:44 pm

Welcome to the Forums.
Are you using OpenOffice or LibreOffice? The two suites are similar, but do have significant differences.
Your post states LibreOffice and the version number in your signature indicates LibreOffice, but you do have OpenOffice specified in your signature.
If you are using LibreOffice and if the VB macro isn't too complex, then LibreOffice might be able to run it, as OpenOffice will not be able to.
If it will not run, then you'll need to re-write the code using one of the macro languages.. StarBasic is the one I hear mentioned the most, but macros can be written in other languages.
You can start with Useful Macro Information by Andrew Pitonyak and/or OpenOffice.org Macros Explained
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5791
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Passing range to function with access to individual cell

Postby wimbeuk » Sat Nov 23, 2019 8:32 pm

Thanks for your quick reply RusselB!

I am using LibreOffice ( i will change my signature right away :-))
The macro in MS-excel looks like this:

Code: Select all   Expand viewCollapse view
Function Som_als_kleur(kleur As Range, Range1, Optional Range2) As Double
Dim objCell As Range
Application.Volatile

Som_als_kleur = 0
cellTextColour = kleur.Font.Color
          cellbackground = kleur.Interior.ColorIndex
For Each objCell In Intersect(Range1, _
    Range1.Parent.UsedRange)
    If Application.IsNumber(objCell.Value) And _
    objCell.Font.Color = cellTextColour Then _
  Som_als_kleur = Som_als_kleur + objCell.Value
Next objCell

If Not IsMissing(Range2) Then
    For Each objCell In Intersect(Range2, _
        Range2.Parent.UsedRange)
        If Application.IsNumber(objCell.Value) And _
        objCell.Font.Color = cellTextColour Then _
        Som_als_kleur = Som_als_kleur + objCell.Value
    Next objCell
End If
End Function


I do not know is this VB-macro is to complex.
After removing application.volatile the function stops at the first codeblock in the line
"If Application.IsNumber(objCell.Value) And _"
with the msg: "Basic runtime error '423' IsNumber
Last edited by robleyd on Sun Nov 24, 2019 12:34 am, edited 1 time in total.
Reason: Added Code tags
LibreOffice 6.1.4.2
Windows 10
wimbeuk
 
Posts: 8
Joined: Sat Nov 23, 2019 5:11 pm

Re: Passing range to function with access to individual cell

Postby wimbeuk » Sat Nov 23, 2019 8:40 pm

And thanks for your hint with "Useful Macro Information...." i will study this.

If anyone else has some hints about this problem, i will be very glad to hear form them!
LibreOffice 6.1.4.2
Windows 10
wimbeuk
 
Posts: 8
Joined: Sat Nov 23, 2019 5:11 pm

Re: Passing range to function with access to individual cell

Postby gerard24 » Sat Nov 23, 2019 10:08 pm

If you are using cell style instead of direct formatting, there is a countstyle extension :

https://extensions.libreoffice.org/exte ... countstyle
LibreOffice 6.3.4 on Windows 10
gerard24
Volunteer
 
Posts: 953
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: Passing range to function with access to individual cell

Postby wimbeuk » Sun Nov 24, 2019 12:34 pm

Thanks for your posting gerard24. This can be a solution. The only disadvantage is that the extension needs to be installed on every pc which accesses the sheet.

I thought I had found an example in OpenOffice.org Macros Explained by Andrew D. Pitonyak. This is what I thought that could be useful, but it gives me an error.
(the example is from page 455 – 456, listings 407 and 408) and goes like this:

Code: Select all   Expand viewCollapse view
Function prettyRangeAddressName(oRangeAddr)
Dim s1$, s2$
Dim oConv
Dim oCellAddr As New com.sun.star.table.CellAddress
d = oRangeAddr.StartColumn
print d
s1 = AddressString(oRangeAddr.Sheet, oRangeAddr.StartColumn, oRangeAddr.StartRow, True)
s2 = AddressString(oRangeAddr.Sheet, oRangeAddr.EndColumn, oRangeAddr.EndRow, False)
prettyRangeAddressName = s1 & ":" & s2
End Function

Function AddressString(iSheet As Long, iCol As Long, iRow As Long, bWwithSheet As Boolean)
Dim s$
iCol = iCol + 1
Do
iCol = iCol - 1
s = CHR$((iCol MOD 26) + 65) & s
iCol = iCol \ 26 - 1
Loop Until iCol < 0
If bWwithSheet Then
AddressString = "Sheet" & CStr(iSheet + 1) & "." & s & CStr(iRow + 1)
Else
AddressString = s & CStr(iRow + 1)
End If
End Function

When I place a ‘=prettyRangeAddressName(A3:A10)’ formula in a cell of my sheet and execute this function, it results in a 'BASIC-runtime-error: Objectvariabele not set' in the following line: s1 = AddressString(oRangeAddr.Sheet, oRangeAddr.StartColumn, oRangeAddr.StartRow, True)
What can be wrong here? What is missing?

The case is that I am looking for something like this (in pseudocode):
In a cell of my sheet I place: =AddIfColor(a3:a10)

The user defined function that goes with it looks like this:

Code: Select all   Expand viewCollapse view
Function AddIfColor(oRange)
  For each cell in oRange
     If textcolor in cell is red then
            AddColor = AddColor + cell.value
    End if
  Next cell
End function


My problem is that I do not know how to get access to the color of the cells in the function.
Any help is welcome! And if possible with a code snippet, because I am not so familiar with starbasic.
Thanks in advance!
Last edited by robleyd on Sun Nov 24, 2019 10:29 pm, edited 1 time in total.
Reason: Added Code tags
LibreOffice 6.1.4.2
Windows 10
wimbeuk
 
Posts: 8
Joined: Sat Nov 23, 2019 5:11 pm

Re: Passing range to function with access to individual cell

Postby JeJe » Sun Nov 24, 2019 2:00 pm

The text color is .charcolor

msgbox thiscomponent.sheets(0).getcellbyposition(0,0).charcolor

Edit:

If you install mri, you can type mri thiscomponent.sheets(0).getcellbyposition(0,0) , or mri any_other_object, and it will give you a list of all the properties.

https://extensions.openoffice.org/en/pr ... ction-tool

Edit2: should have put getcellbyposition(0,0), changed
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 694
Joined: Wed Mar 09, 2016 2:40 pm

Re: Passing range to function with access to individual cell

Postby JeJe » Sun Nov 24, 2019 2:47 pm

I'm not that familiar with spreadsheets... but something like this maybe:

Code: Select all   Expand viewCollapse view
colorred = rgb(255,0,0) 'can't use the word red as its a function name
sheet = thiscomponent.sheets(0)
for col = 0 to 1
for row = 0 to 2
cell = sheet.getcellbyposition(col, row)
if cell.charcolor = colorred then
'do whatever
end if
next
next


Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 694
Joined: Wed Mar 09, 2016 2:40 pm

Re: Passing range to function with access to individual cell

Postby wimbeuk » Mon Nov 25, 2019 6:57 pm

Many thanks JeJe for your reply! .charcolor solves indeed one part of my problem: how to find out the color of the text oin a cell.

But.... there is still another part which is unsolved: How can you get access in a user defined function to the range itself which is passed as argument (for instance the "A5:A10") ?
I have seen some postings that range arguments are only passed by value and not by reference. Which could mean that you can have only access to the values and not to the properties of tof the cells containing these values.
Or am I wrong about this? (I hope that I am wrong....)
LibreOffice 6.1.4.2
Windows 10
wimbeuk
 
Posts: 8
Joined: Sat Nov 23, 2019 5:11 pm

Re: Passing range to function with access to individual cell

Postby hubert lambert » Mon Nov 25, 2019 9:25 pm

Hi,

You're right, it is not possible to access range properties from a user defined function. This is possible only when using function defined by extension (aka "add-in"), like in Countstyle.
The workaround is to provide a textual range representation, that would be interpreted by the code, but you'll loose most benefit of a user function...

Regards
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
hubert lambert
 
Posts: 134
Joined: Mon Jun 13, 2016 10:50 am

Re: Passing range to function with access to individual cell

Postby wimbeuk » Mon Nov 25, 2019 9:53 pm

Ok Hubert, in that case i will stop my quest and start thinking about using an extension like Countsyle. Many thanks for your posting!
LibreOffice 6.1.4.2
Windows 10
wimbeuk
 
Posts: 8
Joined: Sat Nov 23, 2019 5:11 pm

Re: Passing range to function with access to individual cell

Postby Zizi64 » Tue Nov 26, 2019 12:08 am

My opinion: Never use the visual properties of a cell as some data.

Othervise you can pass the range reference as a string (with or without the sheet name). Then you can get the Range object, and the Cell objects with all properties in your user defined function. (The normal passing will pass only the array of the cell content in the passed range.)

https://wiki.openoffice.org/wiki/Docume ... _Documents
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.4; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8656
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Passing range to function with access to individual cell

Postby JeJe » Tue Nov 26, 2019 12:16 am

=ADDIFCOLOR("A3:A10")

Code: Select all   Expand viewCollapse view

function AddIfColor(st) as long
dim ocell
colorred = rgb(255,0,0)
AddIfcolor = 0
sheet = thiscomponent.currentcontroller.activesheet
rangeaddress =sheet.getCellRangeByName(st).getrangeaddress
with rangeaddress
for col= .startcolumn to .endcolumn
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


Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 694
Joined: Wed Mar 09, 2016 2:40 pm

Re: Passing range to function with access to individual cell

Postby wimbeuk » Tue Nov 26, 2019 12:32 am

Wauw...what a surprise! It looks like this is what i am looking for! I will test this in a couple of days. Many thanks Jeje. I will let you know the results....
LibreOffice 6.1.4.2
Windows 10
wimbeuk
 
Posts: 8
Joined: Sat Nov 23, 2019 5:11 pm

Re: Passing range to function with access to individual cell

Postby JeJe » Tue Nov 26, 2019 12:51 am

Might be less error prone to include the sheet as Zizi64 suggested

=ADDIFCOLOR("Sheet1,A3:A10")


Code: Select all   Expand viewCollapse view
function AddIfColor(st) as long
dim ocell,sts() as string
sts = split(st,",")
colorred = rgb(255,0,0)
AddIfcolor = 0
sheet = thiscomponent.sheets.getbyname(sts(0))
rangeaddress =sheet.getCellRangeByName(sts(1)).getrangeaddress
with rangeaddress
for col= .startcolumn to .endcolumn
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

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 694
Joined: Wed Mar 09, 2016 2:40 pm

Re: Passing range to function with access to individual cell

Postby wimbeuk » Tue Nov 26, 2019 9:21 pm

I have tried the example above. It works, but at first i did not noticed the quotation marks in the function call.
It took some time before i noticed that here also the function is called by value (i.e. the string "A3:A10").
Of course this works, but i had hoped that this code example would be a way to pass the real range to the function.

In the sheets where i want to use ADDIFCOLOR, drag and drop and changing ranges is a well common task.
therefore it is necessary that you can use the "normal" way to define function calls.

Anyway, i was close near the target.
Everybody thanks a lot for the effort taken in the replies to my postings.
I think i will go and take a look for the Counstyle extension.

Greetz,
wimbeuk
LibreOffice 6.1.4.2
Windows 10
wimbeuk
 
Posts: 8
Joined: Sat Nov 23, 2019 5:11 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 26 guests