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

Creating a macro - Writing a Script - Using the API

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

Postby sokolowitzky » Tue Dec 18, 2018 7:43 pm

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   Expand viewCollapse view
function thysformula()
thysformula= "=address(row();column();4)"
end function
Attachments
pseudo-pseudo-pseudo.ods
(9.38 KiB) Downloaded 33 times
Last edited by sokolowitzky on Wed Dec 19, 2018 7:56 am, edited 2 times in total.
Ocasionally; Windows 10 & Open Office 4.1.3//LibreOffice 6.0
sokolowitzky
 
Posts: 62
Joined: Mon Sep 15, 2014 7:34 pm

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

Postby Zizi64 » Tue Dec 18, 2018 7:49 pm

Pass the results of the SHEET(), ROW() and the COLUMN() functions to your user defined macros function.
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: A function to tell the address of the cell whereit's wri

Postby Zizi64 » Tue Dec 18, 2018 7:51 pm

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; 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: A function to tell the address of the cell whereit's wri

Postby sokolowitzky » Tue Dec 18, 2018 8:14 pm

Like this one?

Code: Select all   Expand viewCollapse view
function thysformula()
dim y as long
y = "=row()"
thysformula = y
end function
Ocasionally; Windows 10 & Open Office 4.1.3//LibreOffice 6.0
sokolowitzky
 
Posts: 62
Joined: Mon Sep 15, 2014 7:34 pm

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

Postby Zizi64 » Tue Dec 18, 2018 8:26 pm

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; 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: A function to tell the address of the cell whereit's wri

Postby Zizi64 » Tue Dec 18, 2018 8:32 pm

Please upload your full sample code here.
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: A function to tell the address of the cell whereit's wri

Postby Lupp » Tue Dec 18, 2018 9:51 pm

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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2610
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

Postby JeJe » Tue Dec 18, 2018 10:25 pm

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

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

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

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

Postby Villeroy » Tue Dec 18, 2018 11:29 pm

Hello_URL.ods
Hyperlink formula reporting the cell address
(29.73 KiB) Downloaded 39 times
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.x
User avatar
Villeroy
Volunteer
 
Posts: 27565
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Villeroy » Tue Dec 18, 2018 11:50 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27565
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby sokolowitzky » Wed Dec 19, 2018 5:48 am

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.
Ocasionally; Windows 10 & Open Office 4.1.3//LibreOffice 6.0
sokolowitzky
 
Posts: 62
Joined: Mon Sep 15, 2014 7:34 pm

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

Postby Lupp » Wed Dec 19, 2018 2:46 pm

@ "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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2610
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: MSN [Bot] and 13 guests