如何将用户选中的单元格区域地址存储到变量中

需求：将用户选中的单元格区域地址（例如"B2:E4"）存储到变量中。

尝试的报错代码 #

最初尝试以下代码时出现报错：

Sub print_selected_range

Dim oSheet as Object
Dim MyChoice as Object

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

找到的复杂实现代码 #

找到一段可实现类似功能的代码，但逻辑复杂，无法直接提取地址存入变量：

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

自行实现的复杂版本 #

基于他人答案实现的代码示例，但步骤繁琐：

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

简洁易记的解决方案 #

可以基于CellRangeAddressConversion组件简化代码，直接提取A1格式的地址到变量：

Sub GetSelectedRangeAddress()
    Dim oSelection As Object
    Dim oConv As Object
    Dim sRangeAddress As String ' 存储地址的变量
    
    ' 获取当前选中区域
    oSelection = ThisComponent.getCurrentSelection()
    ' 创建地址转换实例
    oConv = ThisComponent.createInstance("com.sun.star.table.CellRangeAddressConversion")
    ' 设置要转换的区域地址
    oConv.Address = oSelection.getRangeAddress()
    
    ' 获取A1格式的地址字符串，存入变量
    sRangeAddress = oConv.UserInterfaceRepresentation
    
    ' 可验证变量内容（可选）
    MsgBox "选中区域地址：" & sRangeAddress
End Sub

这段代码直接通过地址转换组件获取用户界面上显示的A1格式地址，一步存入变量，逻辑清晰易记。

内容的提问来源于stack exchange，提问作者Micklos