Hi,
let’s say I’m at Sheet1 cell A1. This cell takes the value of Sheet2, cell B2. Is it possible for me to go to Sheet 1, cell A1 and ‘jump’ to Sheet2, cell B2 automatically? If not, is there anyway that I could create a function that would allow me to do that? I know in Excel, I could write a VBA program to do that (although I’d rather not).
Thanks!
NN
A macro solution would be straight forward, if the required API function would work as expected.
https://bugs.documentfoundation.org/show_bug.cgi?id=159973
Found a not so straight forward solution:
gotoPrecedents.ods (26.2 KB)
It is far from perfect because I do not fully understand how to parse formula tokens correctly, i.e. named references or references to database-ranges are disregarded (not listed)
You may import the library to MyMacros, so it can be used with all spreadsheet documents:
Tools>Macros>Organize…
button [Organizer…]
tab “Libraries”
Location “My Macros”
Button [Import…]
Point to my file and select library “GoToPrecedents”.
Nor do I.
But as chance would have it:
For a completely different reason, I tried to get a little more familiar with the FormulaParser service the day before yesterday. From parts of the corresponding “research code”, I have now quickly and somewhat sloppily created a function that generates a list of all references that occur in the formula of a cell.
The function and an example how to use it ist contained in the attachment.
FormulaParser_playful.ods (13.1 KB)
Did somebody try the workarund I attached above?
I would be interested in comments.
Yep. Letting the formula parser do the work is very clever. I had no idea, how to do something like that and wrote my own formula token parser.
One thing: ((qO=0) OR (qO)) is obsolete.
P.S. Your code lists external references as well. Mine doesn’t because I forgot about those, therefore my parser does not handle them.
In case of array formulas =A1:B5, your function returns #NAME? for any cell but the first one, whereas my parser returns a reference to the first cell instead of the #NAME? error.
Option Explicit
cell = pCell
REM fillListArray tks, a()
s = listReferences(ac)
a() = split(s, "/")
Now we need some change in my dialog’s ItemListener in order to select the referenced ranges. This will fail where named references are relative, which is why I resolved the names recursively.
There was a typo. The expression should read ((qO=0) OR (qO=4)).
I simply didn’t know if there are additional OpCode values for which (qT="Object") would come out true.
(Assumed 0 for direct references, 4 for named ones as far as I understood. I didn’t find a doument telling me clearly.)
This is why I used the type names of the structs.
Function getTypeNameOfStruct(v)
getTypeNameOfStruct=Split(v.dbg_properties, """")(1)
End Function
… together with OpCode 19 which seems to indicate a database range.
I have to admit that I didn’t study your solution (.oxt). I can’t simply accept the defaults of exchange “services” as they are, and I lack the patience to go through everything they offer in a bypass.
I tried to recognize the needed references the way I did because OpCodes 0 and 4 also are used for direct (literal) values. DB ranges and DB...() functions I never used myself.
(The original reason for what I studied the formula parser recently wasn’t in any way related to precedents.)
The .oxt has been created by the Basic organizer (tab “Libraries”, button “Export”). It contains a single Basic library. You can extract the library folder from the oxt and import the library without extension manager.
OpCode
FormulaMapGroupSpecialOffset
Great job.
Particularly liked the idea of bringing in a formula expert like FormulaParser instead of directly analyzing tokens.
2 similar lists but not the same, adding more confusion.
The first link is the full list.
I gave the second link (maybe in vain) because it contains explanations for some special codes (don’t look at the number, but at the name: PUSH, WHITESPACE, …).
@Villeroy, thanks for the interesting development!
The following macro adds an item with a call to your function to the Cell context menu.
Sub AddMenuContextItem
Dim oSupplier as Object, oUIConf As Object, oSettings As Object, settingsURL As String
Dim oItems(1) As New com.sun.star.beans.PropertyValue
oSupplier=GetDefaultContext.getValueByName("/singletons/com.sun.star.ui.theModuleUIConfigurationManagerSupplier")
oUIConf=oSupplier.getUIConfigurationManager("com.sun.star.sheet.SpreadsheetDocument")
settingsURL="private:resource/popupmenu/cell"
oSettings=oUIConf.getSettings(settingsURL, true)
oItems(0).Name = "CommandURL": oItems(0).Value = "vnd.sun.star.script:ShowReferencesLib.Module1.Main?language=Basic&location=application"
oItems(1).Name = "Label" : oItems(1).Value = "Show refetences"
oSettings.insertByIndex oSettings.count, oItems
oUIConf.replaceSettings(settingsURL, oSettings)
' Uncomment the following line to make the changes permanent
oUIConf.store
End Sub
Interesting as the times we are living in. This little project revealed so many flaws in the spreadsheet API, that I wonder how I finally got to the point where I dared to publish it.
It started with:
[quote=“Villeroy, post:12, topic:39770, full:true”]
A macro solution would be straight forward, if the required API function would work as expected.
https://bugs.documentfoundation.org/show_bug.cgi?id=159973
[/quote].
Thanks to @Lupp and your pointer to https://opengrok.libreoffice.org/xref/core/include/formula/compiler.hxx?r=fd0cc5b2d352dc4d2c1fd406ec2998e91adbd3a1#SC_OPCODE_PUSH, I began to understand some relevant formula tokens with their OpCodes. Nevertheless, I had to fix broken tokens and loop through all kinds of collections in order to get selectable range objects from tokens.
We can have hyperlink jump marks selecting named ranges in the current document like #SomeName or #Sheet1.SomeName or #DatabaseRange, but I don’t know any way how to follow such links by means of API methods.
The API can resolve string addresses like Sheet1.A1:B5 in all their absolute/relative variants. Either you split the sheet name from the address, and then query getCellRangeByName from the sheet OR you query getCellRangesByName from the sheets collection. This was the first project where I tried the second method and found out that the controller can’t select the return value of that method. I had to write another helper function getRangesFromArray.
Until now, I could not figure out any case where getCellRangesByName returns more than one range. The returned array always consists of one range. Even if the input is a 3D-reference Sheet1.A1:Sheet3.B7, the returned array of cell ranges consists of a single range, converted to a collection of SheetCellRanges, the outcome is a collection of one range Sheet1.A1:B7.
I did write another work-around converting Sheet1.A1:Sheet3.B7 into a collection Sheet1.A1:B7, Sheet2.A1:B7 and Sheet3.A1:B7, but when my spreadsheet controller successfully selects this collection, the sheet tabs do not visually indicate the multiple selection. So I considered, it might be safer sticking with the simple code performing a single selection.
My problem with the context menu wasted some time of try-and-error, but this problem is secondary. I’ll see how I can integrate your code into the next version. First, I’ll have to add a test if the menu item exists already. Then the menu item will be added after the macro has been called for the first time because there is no way to run any code at installation time.
' There should be Sheet1 and Sheet2
Sub TestGetCellRangesByName()
Dim arr
arr=ThisComponent.Sheets.getCellRangesByName("$Sheet1.$A$1;$Sheet2.$B$2")
For Each v In arr
msgbox v.AbsoluteName
Next v
End Sub
The commonly used aggregation functions (sum, count, min, max etc.) support these lists of disjunct ranges. My FormulaParser will always split them into single tokens. I could not find multiple ranges because I turned blind towards anything outside the context of this project.
Maybe we write a generic function that returns a SheetCellRanges object that joins the data of all parameters?
The function can have a variable number of parameters, which can be of the following types (both scalar values and arrays are allowed):
SheetCellRanges interfaceSheetCellRange interfaceThisComponent
aRange of getCellRangesByName methodCellAddress or CellRangeAddress
True, False - are interpreted as the bMergeRanges parameter of the addRangeAddress method when adding subsequent ranges.Or is it already written? 