excel - 用于更新命名范围的 Excel VBA 脚本导致 .RefersTo 指向错误的单元格

Question

有一个非常奇怪的问题。我想编写一些代码来查找表中列出的范围，并创建或更新范围 RefersTo 值到该表第二列中的值

所以桌子可能看起来像这样

范围更新	范围公式
RngNme1	=工作表1！C10
RngNme2	=偏移（Rng1,1,1,4,0​​）

在此示例中，代码运行，并且 RngNme1 得到更新或创建，但地址偏移到 Sheet1!F32，并且 RngNme2 按预期更新，但现在引用了错误的区域。

调试时，Rng 确实采用了预期的值，但似乎没有正确更新。也尝试过 .Formula ，但这也没有奏效。

代码如下所示：

Sub ReplaceRanges()


Dim RngNme, RngForm As Range
Dim rName As Variant
Dim i As Integer
Dim chkRng As Boolean
Dim Rng As String

    Application.ScreenUpdating = False
    
    Set RngNme = Range("tblRangeNameControl[RangeToUpdate]")
    Set RngForm = Range("tblRangeNameControl[FormulaForRange]")
    i = 0
    For Each rName In RngNme
        i = i + 1
        chkRng = RangeExists(rName.Value2)
        
        Rng = RngForm(i).Value2
        
        If chkRng = True Then
            ActiveWorkbook.Names.Item(rName.Value2).RefersTo = Rng
        Else
            ActiveWorkbook.Names.Add Name:=rName.Value2, RefersTo:=Rng
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Function RangeExists(rName As String) As Boolean
    Dim Test As Range
    On Error Resume Next
    Set Test = Range(rName)
    
    RangeExists = (Err.Number = 0)
    
End Function

标签： excelvbanamed-ranges