如何用VBA为XLookup设置动态路径？宏运行异常求助

问题背景 #

要制作动态VBA宏，读取单元格B1（路径）、B2（工作簿名）、B3的值，将路径和工作簿名填充到XLOOKUP公式中，但运行时变量未被替换，生成的公式包含占位符dir和FRLU，导致返回#N/A错误。

原错误代码 #

变量定义部分：

Dim FRLU As String
Dim LU As String

dir = Range("B1").Value
FRLU = Range("B2").Value
LU = Range("B3").Value

公式写入部分：

lngLastRow = Cells(Rows.Count, "H").End(xlUp).Row

Range("N4").Formula = "=XLOOKUP(G4,'dir[FRLU]ColumnG'!$G$2:$G$1000,'dir[FRLU]ColumnE'!$E$2:$E$1000)"
Range("N4").Copy Range("N5:N" & lngLastRow)

错误原因 #

VBA不会自动识别字符串中的变量名，原代码直接将dir和FRLU作为字符串内容写入公式，没有通过字符串拼接把变量的实际值嵌入进去。另外dir是VBA的关键字，用作变量名可能引发潜在问题，建议重命名。

修正后的代码 #

Dim FRLU As String
Dim LU As String
Dim strPath As String ' 替换原dir变量，避免关键字冲突

strPath = Range("B1").Value
FRLU = Range("B2").Value
LU = Range("B3").Value

Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, "H").End(xlUp).Row

' 通过字符串拼接嵌入变量值
Range("N4").Formula = "=XLOOKUP(G4,'" & strPath & "[" & FRLU & "]ColumnG'!$G$2:$G$1000,'" & strPath & "[" & FRLU & "]ColumnE'!$E$2:$E$1000)"
Range("N4").Copy Range("N5:N" & lngLastRow)

验证结果 #

修正后生成的公式为：

=XLOOKUP(G4,'C:\Users\User1\Documents\Test1[workbook1.xlsx]ColumnG'!$G$2:$G$1000,'C:\Users\User1\Documents\Test1[workbook1.xlsx]ColumnE'!$E$2:$E$1000)

符合预期，可正常执行XLOOKUP查询。

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