Excel VBA转换公式绝对引用为相对引用时发生右移该如何修复

• 核心错误：调用Application.ConvertFormula时未指定RelativeTo参数。该方法转换引用类型时，必须以指定单元格为坐标基准计算相对引用的偏移量，未指定时默认以当前活动单元格为基准。你批量处理选中区域时，除第一个和活动单元格位置重合的单元格外，其他单元格的转换基准与自身位置不匹配，最终生成的相对引用就会出现偏移，完全符合你遇到的错位情况。
• 次要缺陷：逐单元格写入公式的实现方式，在部分场景下会触发Excel的自动引用调整逻辑，进一步放大偏移问题，同时处理大量单元格时性能较差。

修复后的代码适配任意选区的转换需求，不管是单行、单列还是不规则的多选区域，都不会出现引用偏移：

Sub convertFormulaToRelative()
    Dim calcOld As Long, screenOld As Boolean, eventsOld As Boolean
    Dim selRng As Range, formulaArr As Variant
    Dim i As Long, j As Long
    
    ' 缓存Excel原有设置，避免处理过程中卡顿、触发无关事件
    calcOld = Application.Calculation
    screenOld = Application.ScreenUpdating
    eventsOld = Application.EnableEvents
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set selRng = Selection
    If selRng.Cells.Count > 1 Then
        ' 多单元格场景批量读取到数组，提升处理效率，避免中途引用自动调整
        formulaArr = selRng.Formula
        For i = 1 To UBound(formulaArr, 1)
            For j = 1 To UBound(formulaArr, 2)
                ' 仅处理带公式的单元格
                If Left(formulaArr(i, j), 1) = "=" Then
                    formulaArr(i, j) = Application.ConvertFormula( _
                        Formula:=formulaArr(i, j), _
                        FromReferenceStyle:=xlA1, _
                        ToReferenceStyle:=xlA1, _
                        ToAbsolute:=xlRelative, _
                        RelativeTo:=selRng.Cells(i, j) _
                    )
                End If
            Next j
        Next i
        ' 批量回写处理后的公式
        selRng.Formula = formulaArr
    Else
        ' 兼容单个单元格选中的场景
        With selRng
            If .HasFormula Then
                .Formula = Application.ConvertFormula( _
                    Formula:=.Formula, _
                    FromReferenceStyle:=xlA1, _
                    ToReferenceStyle:=xlA1, _
                    ToAbsolute:=xlRelative, _
                    RelativeTo:=selRng _
                )
            End If
        End With
    End If
    
    ' 恢复Excel原有设置
    Application.Calculation = calcOld
    Application.ScreenUpdating = screenOld
    Application.EnableEvents = eventsOld
End Sub

核心修复点 #

1. 显式指定RelativeTo参数为当前正在处理的单元格本身，确保相对引用的转换基准完全正确，从根源解决偏移问题
2. 采用数组批量读写的方式，既提升了大量数据的处理效率，也避免了逐单元格写入时Excel自动调整其他单元格公式的问题，适配所有通用场景

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