vba - 用 VBA 中的另一个单元格引用替换依赖项

Question

我编写了一些 VBA 代码，它采用单个单元格并在工作簿中标识其所有依赖项(通过 NavigateArrow 分析)并将它们的范围位置添加到数组中。从这里开始，我希望能够更新每个依赖项并将对原始单个单元格的引用更改为另一个指定的单元格。

我在这里遇到的特别困难是，虽然我知道每个依赖项在哪里，但对原始单元格的引用可能位于公式的开头、中间或结尾，并且可能未 anchor 定、行/列/两者都 anchor 定，可能在不同的工作表上，因此在它之前有一个工作表引用，等等。因此，由于这些潜在的差异，我无法在每个相关单元格中轻松查找和替换，另外我想保持原来的 anchor 定每个单元格引用。

是否有一个优雅的——甚至不优雅的——VBA 解决方案来解决这个问题？

Answer 1

我认为正则表达式或 Regexp 是您正在寻找的东西。

以下模式

([A-Z0-9]*)!(\${0,1})([A-Z]{1,3})(\${0,1})([0-9]*)

将匹配 "Sheet1!A1", "Sheet1!$A$1", "Sheet1!$A1", "Sheet1!A$1"

解释:

([A-Z0-9]*)!  =  Find anything that is before "!"
(\${0,1}) = $ or nothing
([A-Z]{1,3})  = between one and three letters
([0-9]*)  = Any number

您应该能够轻松修改该模式以仅匹配您想要的内容。特别是，([A-Z0-9]*)!(\${0,1})B(\${0,1})1，只会匹配里面有 B($)1 的东西......
用字符串操作构造正则表达式模式，应该很好。

您需要引用(工具 > 引用)“Microsoft VBScript 正则表达式 5.5”

尝试以下代码，这应该为您提供实现目标的所有工具

Sub ReplaceReference()
' Reference: Microsoft VBScript Regular Expressions 5.5

Dim RegEx As Object
Set RegEx = New RegExp

Dim s As String

' Here I have hardcoded the reference to the original cell for demonstration purposes
s = "Sheet1!$AB$2"


' Replacement: New sheetname, New Column, new row number
Dim NewCol As String, NewRow As String
NewCol = "C"
NewRow = "10"

Dim NewSheet As String
NewSheet = "Sheet2"


With RegEx
    .Pattern = "([A-Z0-9]*)!(\${0,1})([A-Z]{1,3})(\${0,1})([1-9]*)"
    .IgnoreCase = True
    .Global = True
End With


Debug.Print RegEx.Replace(s, NewSheet & "!" & "$2" & NewCol & "$4" & NewRow)


End Sub

干杯，
于连