在Libre Calc中用Basic宏实现可动态更新的公式粘贴

问题说明 #

复刻Excel宏过程中，复制指定区域公式到目标区域时，使用原代码仅能复制公式文本，但相对引用无法随目标单元格位置自动调整——例如原公式=SUMIF(F8:$F$13,"<>#VALUE!")*C8中的C8不会根据目标行更新为对应行的单元格引用。

原代码问题 #

destRange.FormulaArray = srcRange.FormulaArray 是将源区域作为数组公式整体赋值，不会自动解析并调整单个单元格的相对引用，因此无法实现类似手动复制粘贴的引用自适应效果。

修复方案 #

方案1：逐个单元格复制Formula属性（高效推荐） #

通过循环遍历源区域的每个单元格，将其Formula属性赋值给目标区域对应单元格，LibreOffice会自动处理相对引用的调整：

Dim srcRange As Object
Dim destRange As Object
Dim i As Integer

' 设置源区域（参数：startCol, startRow, endCol, endRow，对应I8:I10）
srcRange = OutputSheet.getCellRangeByPosition(8, 7, 8, 9)

' 设置目标区域（从Row行开始的3个单元格，对应I列的Row到Row+2行）
destRange = OutputSheet.getCellRangeByPosition(8, Row, 8, Row + 2)

' 循环复制每个单元格的公式，自动调整引用
For i = 0 To srcRange.Rows.Count - 1
    destRange.getCellByPosition(0, i).Formula = srcRange.getCellByPosition(0, i).Formula
Next i

方案2：模拟手动复制粘贴（贴近Excel宏逻辑） #

使用LibreOffice的调度器（DispatchHelper）模拟手动复制、粘贴公式的操作，完全复刻手动粘贴时的引用调整行为：

Dim srcRange As Object
Dim destRange As Object
Dim dispatcher As Object

' 设置源区域和目标区域
srcRange = OutputSheet.getCellRangeByPosition(8, 7, 8, 9)
destRange = OutputSheet.getCellRangeByPosition(8, Row, 8, Row + 2)

' 获取调度器服务
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

' 选中源区域并复制
OutputSheet.CurrentController.Select(srcRange)
dispatcher.executeDispatch(OutputSheet.CurrentController.Frame, ".uno:Copy", "", 0, Array())

' 选中目标区域并粘贴公式
OutputSheet.CurrentController.Select(destRange)
dispatcher.executeDispatch(OutputSheet.CurrentController.Frame, ".uno:PasteFormula", "", 0, Array())

关键说明 #

• Formula属性：单个单元格的公式属性，赋值时LibreOffice会自动识别相对引用（如C8）并根据目标单元格位置调整；
• FormulaArray属性：仅用于数组公式场景，赋值时会保留源区域的所有引用结构，不做自适应调整。

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