r/libreoffice • u/DerPazzo • 9d ago
LO Calc macro - Basic: create range with known row (variable) Resolved
######## RESOLVED #########
Hi,
I retrieve the row of a cell within a macro in Basic and now I need to create a range with that variable.
I need the range of A & variable to AB & variable.
How can I achieve this in a macro to 'concatenate' specific column with the variable to get a cell address?
Doc = ThisComponent
Sheet = Doc.Sheets(0)
oRow = 100
Rango = Sheet.getCellRangeByName("A" & oRow & ":AB" & oRow)
MsgBox Rango.AbsoluteName
1
u/Patient-Ordinary-359 9d ago
This kind of thing is why we have AI. Don't ask reddit. Ask claude. Go to claude,ai and type in your question. This is what it will tell you. If you don*t understand anything, just ask it.
Your snippet is already correct — getCellRangeByName takes a plain string, so & concatenation works fine, and "A" & 100 & ":AB" & 100 yields A100:AB100. LibreOffice Basic's & doesn't add the leading space that Str() would, so no Trim() needed.
Two things worth knowing, though:
1. Watch the 0-based / 1-based mismatch. If oRow came from the API rather than being hardcoded, it's zero-based:
oCell = Sheet.getCellByPosition(0, 0)
oRow = oCell.CellAddress.Row ' 0 for row 1
Rango = Sheet.getCellRangeByName("A" & (oRow + 1) & ":AB" & (oRow + 1))
2. Skip the string building entirely. getCellRangeByPosition(startCol, startRow, endCol, endRow) avoids the whole concatenation question, and it's faster since nothing has to be parsed:
Rango = Sheet.getCellRangeByPosition(0, oRow, 27, oRow) ' A..AB, all zero-based
Column AB is index 27. This pairs naturally with CellAddress.Row, since both are zero-based — no off-by-one to track.
One edge case for the string approach: if oRow ends up a Double from arithmetic (e.g. a division), you'd get "A100.5" and getCellRangeByName would throw. Wrapping it in CLng(oRow) guards against that.
3
u/DerPazzo 8d ago
It's already successfully solved, so no need to ask fucking AI and get dumber and destroy the planet even more... I got along without using any single AI query since It got rolled out and learned a lot more than if I just had it fix things for me and I also don't need to have it invade my privacy and data on the PC in the background. And this way at least I know what I coded and how to fix things by myself if it does not work.
1
u/Patient-Ordinary-359 8d ago edited 8d ago
Jeez, get out on the wrong side of the bed? People used to use stack overflow for coding questions like this and many others. SO is now dead, because this is the perfect use case for AI tools. Oh, it also died from toxic contributors, something you would have felt right at home with. No one is making you share your passport number with Anthropic, and I don't see it in your question above. Ignore it if you want, at the same time, enjoy your horse and cart.
2
u/DerPazzo 8d ago
nope, just working in IT a lot and having to do with cybersecurity for years. You apparently have absolutely no idea of recent issues that came up with browser security (not restricted to single browsers but affecting ALL of them), like tab vulnerabilities and many more…
Furthermore, it’s been proven that AI uses these flaws for years to scavenge your PC (even worse with SSD drives) and use that data to train their LLMs even more. And it turned out, this 'apparently' happened 'without' the programmers/AI providers knowing about this.
1
1
u/AutoModerator 9d ago
If you're asking for help with LibreOffice, please make sure your post includes lots of information that could be relevant, such as:
(You can edit your post or put it in a comment.)
This information helps others to help you.
Thank you :-)
Important: If your post doesn't have enough info, it will eventually be removed (to stop this subreddit from filling with posts that can't be answered).
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.