Ask Your Question

Calc - reference cells that have been merged in basic macro

asked 2020-09-28 10:54:40 +0100

stabiloboss gravatar image

updated 2020-09-28 13:34:57 +0100

Hi, I want a macro that merges manually selected cells, then applies a style, then writes text in the merged block, and then centers the text. I found a way to perform all the individual tasks, but the complete macro fails (without errors) after merging and applying the style. I think this is because after merging the cells, I have no longer a valid reference to the merged cells. If I manually select the merged cells and then run the macro a second time, then the rest of the macro executes as expected and the text is written to the merged block.

So the question is how I can reference the newly merged block from within the macro.

sub applyStyle(strStyle, strText, blnCentered)


//'split the selection just in case it already contains merged cells
//'merge the cells

//' apply the style
dim document   as object
dim dispatcher as object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("")

dim args1(1) as new
args1(0).Name = "Template"
args1(0).Value = strStyle 
args1(1).Name = "Family"
args1(1).Value = 2

dispatcher.executeDispatch(document, ".uno:StyleApply", "", 0, args1())

//'enter the text   <== fails in the first run
// 'center the text
if blnCentered = true then
end sub


edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2020-09-28 12:15:10 +0100

igorlius gravatar image

updated 2020-09-28 12:19:30 +0100


you just have to get the cell at position (0,0) of the seleciton and write to it instead to the selection itself.

Minimal example:

sub mergeAndSet
    mergedCell = theSelection.getCellByPosition(0,0)
    mergedCell.String = "newContent"
end sub

Hope that helps.

edit flag offensive delete link more



Worked! Thanks! Not sure what to do now, I never had multiple working answers before, and the answers are basically the same (mergedCell = theSelection.getCellByPosition(0,0)). How does this work? Do I accept both, do I have to choose one over the other?

stabiloboss gravatar imagestabiloboss ( 2020-09-28 14:21:11 +0100 )edit

That is kind of up to you. In general i would say upvote all helpful answers and accept the one you think helped you out the most.

igorlius gravatar imageigorlius ( 2020-09-28 14:45:26 +0100 )edit

answered 2020-09-28 12:20:23 +0100

Zizi64 gravatar image

updated 2020-09-28 17:35:19 +0100

You need study the API function if you want to write your macros instead of recording them. The macro recorder has a very limited capability. And install and use one of the object inspection tools (XrayTool, MRI).

Sub Merge_and_style

    oSelection = ThisComponent.CurrentSelection
    if oSelection.ImplementationName = "ScCellRangeObj" then
        if oSelection.GetIsMerged then
        end if
        oCell = oSelection.getCellByPosition(0,0)
        oCell.CellStyle = "MyMergedCells"
    end if
End Sub

The Cell style named MyMergedCells (with adjusted vertical and horizontal alignment properties) must be created before you launch the macro.

image description

edit flag offensive delete link more


Yeah, I hear MRI is really helpful, but I haven't figured out yet how it works and how to use it. But the code works, thanks for your input!

stabiloboss gravatar imagestabiloboss ( 2020-09-28 14:22:47 +0100 )edit

I am using the Xray install it with the Extension manager, and then put this commant into your macro code:

oSelection = ThisComponent.CurrentSelection
xray oSelection

You must load the Xray Library before you launch the macro. You can load the xray by a macro code:

If (Not GlobalScope.BasicLibraries.isLibraryLoaded("XrayTool")) Then
End If

...or it is enough to click on the name of the Xray in the Library list in the Basic IDE.

See the enlargened panel of the working Xray at my answer.

Zizi64 gravatar imageZizi64 ( 2020-09-28 17:32:09 +0100 )edit

Thanks a lot for that, Zizi, I have it working...

stabiloboss gravatar imagestabiloboss ( 2020-09-29 05:56:38 +0100 )edit

answered 2020-09-28 12:12:55 +0100

keme gravatar image

updated 2020-09-28 12:14:10 +0100

If theSelection no longer holds a valid reference after merging, I guess an error should be thrown. Anyway, you could try to refresh theSelection from ThisComponent.CurrentSelection (which should now be the merged block) before setting content.

On my phone now, so I can't test this myself.

edit flag offensive delete link more


Hey, tnx for replying! Not sure why, but this approach didn't work for me.

stabiloboss gravatar imagestabiloboss ( 2020-09-28 14:16:39 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-09-28 10:54:40 +0100

Seen: 105 times

Last updated: Sep 28 '20