Setting relative reference for repeatedly used macro

English
,
#1

Hi there,

I have an operation I perform in spreadsheets that are generated for me. Basically what I do is I copy a template of sorts beside the tables, highlight the template of cells I copied in, find and replace some hardcoded variables in formulas with the cell numbers that the table takes up and then drag down specific columns to fill the cells. In the example I will post it will be 6 and 17, the table I am working with has 12 rows in total.

This is the macro recorder’s interpretation of what I do. I understand the macro recorder is inefficient and bloated but it’s the only way I can properly give an idea of what is going on.

REM  *****  BASIC  *****

sub UK12
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$F$6:$AC$9"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())'

rem ----------------------------------------------------------------------
dim args2(20) as new com.sun.star.beans.PropertyValue
args2(0).Name = "SearchItem.StyleFamily"
args2(0).Value = 2
args2(1).Name = "SearchItem.CellType"
args2(1).Value = 0
args2(2).Name = "SearchItem.RowDirection"
args2(2).Value = true
args2(3).Name = "SearchItem.AllTables"
args2(3).Value = false
args2(4).Name = "SearchItem.SearchFiltered"
args2(4).Value = false
args2(5).Name = "SearchItem.Backward"
args2(5).Value = false
args2(6).Name = "SearchItem.Pattern"
args2(6).Value = false
args2(7).Name = "SearchItem.Content"
args2(7).Value = false
args2(8).Name = "SearchItem.AsianOptions"
args2(8).Value = false
args2(9).Name = "SearchItem.AlgorithmType"
args2(9).Value = 1
args2(10).Name = "SearchItem.SearchFlags"
args2(10).Value = 71680
args2(11).Name = "SearchItem.SearchString"
args2(11).Value = "888"
args2(12).Name = "SearchItem.ReplaceString"
args2(12).Value = "6"
args2(13).Name = "SearchItem.Locale"
args2(13).Value = 255
args2(14).Name = "SearchItem.ChangedChars"
args2(14).Value = 2
args2(15).Name = "SearchItem.DeletedChars"
args2(15).Value = 2
args2(16).Name = "SearchItem.InsertedChars"
args2(16).Value = 2
args2(17).Name = "SearchItem.TransliterateFlags"
args2(17).Value = 1073743104
args2(18).Name = "SearchItem.Command"
args2(18).Value = 3
args2(19).Name = "SearchItem.SearchFormatted"
args2(19).Value = false
args2(20).Name = "SearchItem.AlgorithmType2"
args2(20).Value = 2

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Visible"
args3(0).Value = false

dispatcher.executeDispatch(document, ".uno:SearchResultsDialog", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(20) as new com.sun.star.beans.PropertyValue
args4(0).Name = "SearchItem.StyleFamily"
args4(0).Value = 2
args4(1).Name = "SearchItem.CellType"
args4(1).Value = 0
args4(2).Name = "SearchItem.RowDirection"
args4(2).Value = true
args4(3).Name = "SearchItem.AllTables"
args4(3).Value = false
args4(4).Name = "SearchItem.SearchFiltered"
args4(4).Value = false
args4(5).Name = "SearchItem.Backward"
args4(5).Value = false
args4(6).Name = "SearchItem.Pattern"
args4(6).Value = false
args4(7).Name = "SearchItem.Content"
args4(7).Value = false
args4(8).Name = "SearchItem.AsianOptions"
args4(8).Value = false
args4(9).Name = "SearchItem.AlgorithmType"
args4(9).Value = 1
args4(10).Name = "SearchItem.SearchFlags"
args4(10).Value = 71680
args4(11).Name = "SearchItem.SearchString"
args4(11).Value = "999"
args4(12).Name = "SearchItem.ReplaceString"
args4(12).Value = "17"
args4(13).Name = "SearchItem.Locale"
args4(13).Value = 255
args4(14).Name = "SearchItem.ChangedChars"
args4(14).Value = 2
args4(15).Name = "SearchItem.DeletedChars"
args4(15).Value = 2
args4(16).Name = "SearchItem.InsertedChars"
args4(16).Value = 2
args4(17).Name = "SearchItem.TransliterateFlags"
args4(17).Value = 1073743104
args4(18).Name = "SearchItem.Command"
args4(18).Value = 3
args4(19).Name = "SearchItem.SearchFormatted"
args4(19).Value = false
args4(20).Name = "SearchItem.AlgorithmType2"
args4(20).Value = 2

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Visible"
args5(0).Value = false

dispatcher.executeDispatch(document, ".uno:SearchResultsDialog", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$F$6"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "$F$6:$H$6"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "EndCell"
args8(0).Value = "$H$17"

dispatcher.executeDispatch(document, ".uno:AutoFill", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$F$6:$H$17"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "ToPoint"
args10(0).Value = "$O$6:$AC$6"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args10())

rem ----------------------------------------------------------------------
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "EndCell"
args11(0).Value = "$AC$17"

dispatcher.executeDispatch(document, ".uno:AutoFill", "", 0, args11())

rem ----------------------------------------------------------------------
dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name = "ToPoint"
args12(0).Value = "$O$6:$AC$17"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args12())


end sub

Basically what I want to do is make it so the current cell selected acts as the start point, F6 was in the above example, and then the hardcoded variables are changed up with the current row number for the 888 and then the current row number + 11 for the 999 placeholders. I’m guessing these can be done with some string splitter once the cell address variables are set.

So say if the row number I start on is 213, the range will be 213 and 224. The start point will ALWAYS be in the F column.

I have played around with ThisComponent.getCellAddress type of commands to no success.

Any help would be appreciated, thank you.

#2

You macro has been recorded by the Macro recorder. The MacroRecorder uses the Dispatcher. You can modify the code manually as you want and if it is possible to achieve the desired working state.

Otherwise you need WRITE your macros based on the API functions instead of the Dispatcher.

#3

Yes, the API functiony are some similar things.
There are thousands of API functions.
I suggest you to install one of the excellent Object Inspection Tools (MRI or XrayTool) to examine the existing properties of the programming objects, like the ThisComponent.

#4

The parametrization of user code (except UDF) is generally non-trivial.
What do you want to parametrize?
First step:
Is the reference you are talking of the recorded range address $F$6:$AC$9? In what way do you want to get it “relative”. Will the range you want to work with always be in the selected sheet?
Second step:
Recording a Search may result in a hardly interpretable mess. I suppose the only setting you actually entered were the SerachString “888” and the ReplaceString “6”. Do you want to parametrize this input? If so, in what way?
Third step:
What do you intend with it? I don’t know the command.
Fourth step:
Next Search command. What’s the searched range now? May I suppose the original range?
Fifth step:
As already commented.
Sixth and seventh step:
$F$6 is selected. Selection then extended to $F$6:$H$6 What … (CF first step. Suppose inadvertent interim release of the mouse button?)
What? How?
And so on. You will need to clearly describe what you actually want to achieve, and you probably won’t find a contributor here accepting the role of the next-better macro recorder.

Who generates the sheets for you? The person should know about the purposes, and be able to help you with your problem.

#6

Thank you for this MRI tool, very intimidating to navigate but this gives a lot of great info.

#7

The sheets I get are auto generated from a website, they allow users to download the sheets and then the users can do what they want with them, they have no obligations to help me write macros for this. This is a hobby, not work/school.

So here is a sample workout of what I do, hopefully it is rendered in a good resolution once you happen to see it.
I take a block of cells which have formulas that will return results about the tables in the generated spreadsheets. To rank the data I use feature scaling normalisation, I’m unaware of LibreOffice Calc having a math procedure that does this so I wrote the formula in for myself. The range placeholders in the formula are 888 and 999, I find and replace 888 and 999 with the top and bottom rows of the table, which in the video are 6 and 17, 29 and 40, and 51 and 62. Then I drag down and fill the cells.

I should note it doesn’t strictly have highlight 3 rows like my original said that was just recorded, only the first row needs to be highlighted.

Ideally, selecting the top row in the F column would serve as the base number and then from that the the base number would be be the replace with variable in the first find and replace and then “base+11” to be in the second find and replace. This would make sure it works on any row.

#8

I believe I have solved it myself.

REM  *****  BASIC  *****

sub UK12
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

 oActiveCell = ThisComponent.getCurrentSelection()
       
    oConv = ThisComponent.createInstance("com.sun.star.table.CellRangeAddressConversion")
    oConv.Address = oActiveCell.getRangeAddress
      
    Dim oSelection As Object
    oSelection = ThisComponent.getCurrentSelection()
    Dim oCellAddress As Object
    oCellAddress = oSelection.getRangeAddress()
    Dim baseRow As Integer
    baseRow = oCellAddress.StartRow + 1
    endRow = baseRow+11
    Dim finalBaseRow as String
    Dim finalEndRow as String
    finalBaseRow = baseRow
    finalEndRow = endRow
    msgbox "Base row = " & finalBaseRow & " and end row =" & finalEndRow
    
    

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = oConv.Address
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())'

rem ----------------------------------------------------------------------
dim args2(20) as new com.sun.star.beans.PropertyValue
args2(0).Name = "SearchItem.StyleFamily"
args2(0).Value = 2
args2(1).Name = "SearchItem.CellType"
args2(1).Value = 0
args2(2).Name = "SearchItem.RowDirection"
args2(2).Value = true
args2(3).Name = "SearchItem.AllTables"
args2(3).Value = false
args2(4).Name = "SearchItem.SearchFiltered"
args2(4).Value = false
args2(5).Name = "SearchItem.Backward"
args2(5).Value = false
args2(6).Name = "SearchItem.Pattern"
args2(6).Value = false
args2(7).Name = "SearchItem.Content"
args2(7).Value = false
args2(8).Name = "SearchItem.AsianOptions"
args2(8).Value = false
args2(9).Name = "SearchItem.AlgorithmType"
args2(9).Value = 1
args2(10).Name = "SearchItem.SearchFlags"
args2(10).Value = 71680
args2(11).Name = "SearchItem.SearchString"
args2(11).Value = "888"
args2(12).Name = "SearchItem.ReplaceString"
args2(12).Value = finalBaseRow
args2(13).Name = "SearchItem.Locale"
args2(13).Value = 255
args2(14).Name = "SearchItem.ChangedChars"
args2(14).Value = 2
args2(15).Name = "SearchItem.DeletedChars"
args2(15).Value = 2
args2(16).Name = "SearchItem.InsertedChars"
args2(16).Value = 2
args2(17).Name = "SearchItem.TransliterateFlags"
args2(17).Value = 1073743104
args2(18).Name = "SearchItem.Command"
args2(18).Value = 3
args2(19).Name = "SearchItem.SearchFormatted"
args2(19).Value = false
args2(20).Name = "SearchItem.AlgorithmType2"
args2(20).Value = 2

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Visible"
args3(0).Value = false

dispatcher.executeDispatch(document, ".uno:SearchResultsDialog", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(20) as new com.sun.star.beans.PropertyValue
args4(0).Name = "SearchItem.StyleFamily"
args4(0).Value = 2
args4(1).Name = "SearchItem.CellType"
args4(1).Value = 0
args4(2).Name = "SearchItem.RowDirection"
args4(2).Value = true
args4(3).Name = "SearchItem.AllTables"
args4(3).Value = false
args4(4).Name = "SearchItem.SearchFiltered"
args4(4).Value = false
args4(5).Name = "SearchItem.Backward"
args4(5).Value = false
args4(6).Name = "SearchItem.Pattern"
args4(6).Value = false
args4(7).Name = "SearchItem.Content"
args4(7).Value = false
args4(8).Name = "SearchItem.AsianOptions"
args4(8).Value = false
args4(9).Name = "SearchItem.AlgorithmType"
args4(9).Value = 1
args4(10).Name = "SearchItem.SearchFlags"
args4(10).Value = 71680
args4(11).Name = "SearchItem.SearchString"
args4(11).Value = "999"
args4(12).Name = "SearchItem.ReplaceString"
args4(12).Value = finalEndRow
args4(13).Name = "SearchItem.Locale"
args4(13).Value = 255
args4(14).Name = "SearchItem.ChangedChars"
args4(14).Value = 2
args4(15).Name = "SearchItem.DeletedChars"
args4(15).Value = 2
args4(16).Name = "SearchItem.InsertedChars"
args4(16).Value = 2
args4(17).Name = "SearchItem.TransliterateFlags"
args4(17).Value = 1073743104
args4(18).Name = "SearchItem.Command"
args4(18).Value = 3
args4(19).Name = "SearchItem.SearchFormatted"
args4(19).Value = false
args4(20).Name = "SearchItem.AlgorithmType2"
args4(20).Value = 2

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Visible"
args5(0).Value = false

dispatcher.executeDispatch(document, ".uno:SearchResultsDialog", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$F$"&finalBaseRow

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
rem args7(0).Value = "$F$"&finalBaseRow&":$H$6"
args7(0).Value = "$F$" & finalBaseRow & ":$H$" & finalBaseRow

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "EndCell"
args8(0).Value = "$H$"&finalEndRow

dispatcher.executeDispatch(document, ".uno:AutoFill", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
rem args9(0).Value = "$F$6:$H$17"
args9(0).Value = "$F$" & finalBaseRow & ":$H$" & finalEndRow

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "ToPoint"
rem args10(0).Value = "$O$6:$AC$6"
args10(0).Value = "$O$"  & finalBaseRow & ":$AC$" & finalBaseRow

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args10())

rem ----------------------------------------------------------------------
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "EndCell"
rem args11(0).Value = "$AC$17"
 args11(0).Value = "$AC$"&finalEndRow

dispatcher.executeDispatch(document, ".uno:AutoFill", "", 0, args11())

rem ----------------------------------------------------------------------
dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name = "ToPoint"
rem args12(0).Value = "$O$6:$AC$17"
args12(0).Value = "$O$"  & finalBaseRow & ":$AC$" & finalEndRow

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args12())


end sub

Not the most elegant solution but it works after having tested it a bunch of different rows. Will make separate macros for tables with differing rows.

Thank you to all who took the time to read and respond to this topic.

#9

I feel sure you believe wrongly.
Did that code actually work for you as expected in different situations?
I don’t think (e.g.) that

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = oConv.Address
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())'

where oConv.Address is a CellRangeAddress structure can work.

#10

A sample workout would be a .ods exemplifying a (not too much) simplified starting situation and explaining the intended workflow. Then would follow one or more sheets exemplifying (probably in steps) the achieved results.
…
I surely will not start to watch videos in such a case.
Well, fortunately you already found your solution.
Unfortunately I wasted some time studying misleading code with unexplained “placeholders” and the like.

#11

Just tested it on some sheets I generated today, worked perfectly on all tables. Spent about 10-15% of the time on it than I usually would have.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.