VBA循环中如何正确编写含引号的Excel自定义公式？

你的代码有两个关键问题导致无法正常运行，修正方案如下：

核心问题分析 #

• 引号转义错误：在VBA中，要在公式字符串里表示一个双引号，必须用**两个连续双引号（""）**转义，原代码里用单引号是错误的。
• 引用方式错误：你直接拼接了单元格的值（bws.Cells(jr, "AD").Value），这会让公式变成静态文本，而非动态引用单元格地址，失去了公式的动态性。

修正后的代码 #

For jr = 5 To jlRow
    If bws.Cells(jr, "X").Value <> "" Then
        ' 生成跨工作表的单元格地址引用，确保公式能正确指向目标单元格
        Dim targetCellAddr As String
        targetCellAddr = bws.Cells(jr, "AD").Address(True, True, xlA1, True)
        ' 转义双引号，拼接正确的公式字符串
        dws.Range("E1").Formula = "=GetNumeric(TEXTBEFORE(" & targetCellAddr & ",""""))"
    End If
Next jr

代码说明 #

1. Address(True, True, xlA1, True)：生成包含工作表名的绝对引用地址（比如Sheet1!$AD$5），确保公式在不同工作表间也能正确引用。如果bws和dws是同一个工作表，可以简化为bws.Cells(jr, "AD").Address。
2. """"：在VBA字符串里，每两个双引号会被解析为一个实际的双引号，所以这里最终生成的公式里会是TEXTBEFORE(Sheet1!$AD$5,",")，符合Excel公式的语法要求。

更高效的替代方案：直接计算值而非写入公式 #

如果不需要保留公式（只是需要计算结果），可以直接在VBA里调用GetNumeric函数计算，避免公式依赖，效率更高：

For jr = 5 To jlRow
    If bws.Cells(jr, "X").Value <> "" Then
        ' 直接调用GetNumeric函数计算结果，写入单元格
        dws.Range("E1").Value = GetNumeric(Left(bws.Cells(jr, "AD").Value, InStr(bws.Cells(jr, "AD").Value, ",") - 1))
    End If
Next jr

这里用Left和InStr模拟了TEXTBEFORE的功能（提取逗号前的文本），然后直接传入GetNumeric得到结果，不需要生成公式。

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