rangelibreoffice-calclibreoffice-basic

How to store in a variable, the range address from a user selected range


I want a variable to store the "range address" (e.g. "B2:E4") of a range that was selected by a user.

I'm trying things along these lines:

Sub print_selected_range

Dim oSheet as Object
Dim MyChoice as Object

oSheet = ThisComponent.CurrentController.getActiveSheet()
MyChoice = oSheet.getRangeAddress()
    
Print MyChoice
    
End Sub

This results in an error:

enter image description here

What method will yield this range? The code below that I found elsewhere gets a bit close to what I want:

Sub get_range_address

    oActiveCell = ThisComponent.getCurrentSelection()
       
    oConv = ThisComponent.createInstance("com.sun.star.table.CellRangeAddressConversion")
    oConv.Address = oActiveCell.getRangeAddress
    
    msgbox  oConv.UserInterfaceRepresentation & _
      "  " & oConv.PersistentRepresentation

End Sub

However, I don't see how to extract the range address and dump it into a variable, plus it's complicated and I really want simple code (like the first code block above), so that later I quickly remember / understand what's going on and rapidly apply it elsewhere.


In case it's helpful to anyone, based on JohnSUN's answer (Tq!), Here's what I did to operate on my level...

Sub print_selected_range

    Dim sAddressOffSelection As String
    Dim A1CellData as String ' Holding variable if needed.
    Dim A2CellData as String ' Holding variable for just the range if needed.

    Dim oSheet as Object
    Dim oCell as Object
    Dim occurances as Integer
    occurances = 0
    oSheet = ThisComponent.CurrentController.getActiveSheet()
    
    sAddressOffSelection =  ThisComponent.getCurrentSelection().AbsoluteName
 
    oCell = oSheet.getCellRangeByName("A1") ' sets up sheet to reference cell A1
    oCell.setString(sAddressOffSelection) ' dumps a string in cell A1 - for visual reference/debugging
    A1CellData = oCell.getString() ' Assigns the string in A1 to the variable called A1CellData
    
     StrippedRange = Split(A1CellData, ".") ' chops up A1CellData into an array of characters we can call 'Words"
                                                                        ' delimited via "."
                                                                        ' Note: each Word is an element of StrippedRange
    For Each Word in StrippedRange
        occurances = occurances+1
    Next Word

    oCell = oSheet.getCellRangeByName("A2") ' sets up sheet to reference cell A2 - for visual reference/debugging
    oCell.setString(StrippedRange(occurances-1) ' dumps last part of Range address into cell A2

End Sub

Solution

  • I hope this code is short enough and understandable without further explanation:

    Sub print_selected_range
    Dim sAddressOffSelection As String 
        sAddressOffSelection =  ThisComponent.getCurrentSelection().AbsoluteName
        MsgBox "Current selection is " & sAddressOffSelection
    End Sub