[Solved] A function to tell the address of the cell

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
sokolowitzky
Posts: 103
Joined: Mon Sep 15, 2014 7:34 pm

[Solved] A function to tell the address of the cell

Post by sokolowitzky »

Hello,
I ask so many noob questions, like an hobby. Here is a newer one.
I'm trying to make independent functions with help of macros.
For example, I'm trying to make a "=FILE PICKER()" formula.
And that's why I'm trying to figure out how to make a makro to give a cell's address where it is written.
I know that I can do this without a makro, this is something calc can do it self, but as I have mentionned, I will use this information for other makros.
Please excuse me if this is something simple and I could not find an answer in website.
Here below, is a pseudo code that I made up. It can write "address(row();column();4)" anywhere it is written. But gets written as string not as a formula.

Code: Select all

function thysformula()
thysformula= "=address(row();column();4)"
end function
Attachments
pseudo-pseudo-pseudo.ods
(9.38 KiB) Downloaded 132 times
Last edited by sokolowitzky on Wed Dec 19, 2018 7:56 am, edited 2 times in total.
Win10-OpenOffice 4.1/LibreOffice 7.4
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: A function to tell the address of the cell whereit's wri

Post by Zizi64 »

Pass the results of the SHEET(), ROW() and the COLUMN() functions to your user defined macros function.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: A function to tell the address of the cell whereit's wri

Post by Zizi64 »

For example:

=FILE PICKER(SHEET();ROW();COLUMN();and your other parameters)

The result os the functions SHEET(), ROW(), COLUMN() are numeric values (integer, long type)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
sokolowitzky
Posts: 103
Joined: Mon Sep 15, 2014 7:34 pm

Re: A function to tell the address of the cell whereit's wri

Post by sokolowitzky »

Like this one?

Code: Select all

function thysformula()
dim y as long 
y = "=row()"
thysformula = y
end function
Win10-OpenOffice 4.1/LibreOffice 7.4
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: A function to tell the address of the cell whereit's wri

Post by Zizi64 »

Not.
You need pass the sheet, row and column number of the cell where the main function is called from - if you want to use some property of the source cell.
Then you will able to ge the actual sheet, the actual column the actual row and the actual cell in your macro function.

The SHEET(), ROW(), COLUMN() are Calc cell functions. You can not call them by such simple method as you wrote above.


There is a method to call cell functions from your macro. You will found sample codes in this forum.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: A function to tell the address of the cell whereit's wri

Post by Zizi64 »

Please upload your full sample code here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: A function to tell the address of the cell whereit's wri

Post by Lupp »

sokolowitzky wrote:I ask so many noob questions, ...
That's not the problem. The problem is that you don't make any progress. You started to ask your questions mid of september if I am right, and you got answers. Today's question again looks as if it is the first one asked by someone who never looked into any texts about Basic generally, or about OpenOffice Basic specifically. It also doesn't look as if you tried to learn the basic concepts concerning spreadsheets.

You won't get anywhere this way within the next few decades. You need to learn first things first.

And: You cannot expect a contributor to a forum to write another personalised tutorial for you every other day. There may be some people who get along with "learning by doing" (which is a questionable idea with most subjects anyway), but you are not among them, at least not if programming for OpenOffice Calc is the subject.

Your next question should be posted in the beginners branch and read "What texts should I read to understand Calc, and what might help me to understand the basics of Basic and its usage in programming for Calc?" or similar.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: A function to tell the address of the cell whereit's wri

Post by JeJe »

If you download the free pdf of OpenOffice.org Macros Explained By Andrew Pitonyak and look at Chapter 15 it deals with accessing sheets:

http://www.pitonyak.org/book/

This bit is possibly close to what you're asking?

Code: Select all


I had difficulty with the CellAddressConversion service, so I opted to write my own. The following method
accepts the sheet number, column, and row, and returns an address. The “cheat” that I used, is to assume that
the sheet name is formatted as Sheet1, Sheet2, etc. This is trivial to change, but I opted to not do it. The only
tricky part is to understand that columns are labeled using Base 26 where the digits are A-Z and not 0-9; and
being enough of a technical person to understand what that means.

Listing 407. Format a single cell address assuming all sheets are named Sheet.

Function AddressString(iSheet As Long, iCol As Long, iRow As Long, bWwithSheet As Boolean)
Dim s$
455
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

Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: A function to tell the address of the cell whereit's wri

Post by Villeroy »

Hello_URL.ods
Hyperlink formula reporting the cell address
(29.73 KiB) Downloaded 153 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: A function to tell the address of the cell whereit's wri

Post by Villeroy »

Same concept of accessing cells by positional parameters: viewtopic.php?f=21&t=2762
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
sokolowitzky
Posts: 103
Joined: Mon Sep 15, 2014 7:34 pm

Re: A function to tell the address of the cell whereit's wri

Post by sokolowitzky »

Thank you everybody.
@Lupp, I have a very limited time to learn things during the day. Maybe 1-2 hours. That's why I tried to see the results first, so I can understand concepts.
I know that I ask very simple questions that's why I begin with apologize and if nobody would answer then I would deduce that the question shouldn't be asked anyhow.
Win10-OpenOffice 4.1/LibreOffice 7.4
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] A function to tell the address of the cell

Post by Lupp »

@ "sokolowitzky":
Well, not being a native speaker of English, I only can hope you didn't regard my post above as intending to be offensive.
However, even if you have little spare time, and factually the more in this case, you should use your time efficiently. I will not try to give stringent evidence, but I am sure you need to abandon the way you tried to date to get more efficient.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply