如何在LibreCalc中用StarBasic宏计算给定单元格区域的列位置差值

核心逻辑 #

连续单元格区域的列差值 = 区域结束列号 - 区域起始列号，计算结果天然为整数，无需额外转换。

完整实现代码 #

REM 计算单个单元格区域的列差值，返回整数结果
Function GetSingleRangeColumnDiff(oRange As Object) As Integer
    Dim nStartCol As Integer, nEndCol As Integer
    nStartCol = oRange.RangeAddress.StartColumn
    nEndCol = oRange.RangeAddress.EndColumn
    GetSingleRangeColumnDiff = nEndCol - nStartCol
End Function

REM 计算当前选中的所有区域的列差值，弹窗输出结果
Sub CalcSelectedRangesColumnDiff
    Dim oSelection As Object, oRanges As Object, oRange As Object
    Dim i As Integer, nDiff As Integer, sResult As String
    Const MB_OK = 0
    
    oSelection = ThisComponent.CurrentSelection
    sResult = "各区域列差值计算结果：" & Chr(10) & Chr(10)
    
    REM 兼容单个/多个选中区域的情况
    If oSelection.supportsService("com.sun.star.sheet.SheetCellRange") Then
        nDiff = GetSingleRangeColumnDiff(oSelection)
        sResult = sResult & "区域 " & oSelection.AbsoluteName & "：" & nDiff & Chr(10)
    ElseIf oSelection.supportsService("com.sun.star.sheet.SheetCellRanges") Then
        oRanges = oSelection
        For i = 0 To oRanges.getCount() - 1
            oRange = oRanges.getByIndex(i)
            nDiff = GetSingleRangeColumnDiff(oRange)
            sResult = sResult & "区域 " & oRange.AbsoluteName & "：" & nDiff & Chr(10)
        Next i
    Else
        sResult = "请先选中有效的单元格区域后再运行宏"
    End If
    
    MsgBox sResult, MB_OK, "列差值计算完成"
End Sub

REM 固定计算示例中三个指定区域的列差值
Sub CalcDemoRanges
    Dim oSheet As Object, aRangeNames() As String
    Dim i As Integer, oRange As Object, sResult As String
    Const MB_OK = 0
    
    REM 默认取当前文件第一个工作表，可根据需求修改索引
    oSheet = ThisComponent.Sheets(0)
    aRangeNames = Array("B17:C28", "E17:G28", "L17:O28")
    sResult = "示例区域列差值计算结果：" & Chr(10) & Chr(10)
    
    For i = LBound(aRangeNames) To UBound(aRangeNames)
        oRange = oSheet.getCellRangeByName(aRangeNames(i))
        sResult = sResult & aRangeNames(i) & "：" & GetSingleRangeColumnDiff(oRange) & Chr(10)
    Next
    
    MsgBox sResult, MB_OK, "示例计算完成"
End Sub

使用方法 #

• 打开目标LibreCalc文件，按Alt + F11唤起宏编辑器
• 左侧选中当前文件，右键点击「插入」-「模块」
• 将上述代码粘贴到模块编辑区后保存
• 如需计算手动选中的区域：选中目标区域后运行CalcSelectedRangesColumnDiff宏即可
• 如需直接验证示例效果：运行CalcDemoRanges宏，将直接输出你给出的三个区域的计算结果：1、2、3

注意事项 #

• 列差值计算不受区域行范围影响，仅和区域覆盖的起止列有关
• 工作表索引默认从0开始，如需计算其他工作表的区域，修改ThisComponent.Sheets(0)中的索引即可

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