Excel VBA转换英文单元格公式为波兰语区域公式的问题

我在TemplateMap工作表的C15单元格中粘贴了美式英语版本的公式：

'=IF(AND($H11="H",L11>=1,ISNUMBER(L11)),TRUE,IF(AND($H11<>"H",L11>=0,ISNUMBER(L11)),TRUE,FALSE))

公式内部分列为绝对引用、行为相对引用，需要通过VBA代码将其应用到Base_Price工作表的单元格中。

针对波兰用户，对应的波兰语公式应为：

'=IF(AND($H11="H";L11>=1;ISNUMBER(L11));TRUE;IF(AND($H11<>"H";L11>=0;ISNUMBER(L11));TRUE;FALSE))

• 公式中的逗号,未转换为波兰语所需的分号;
• 使用.RefersToLocal时，会出现行列引用错误（除非将原公式的行列全部设为绝对引用）
• .RefersToLocal会自动在公式中添加活动工作表名称

Sub Tester()
    Dim V As Variant
    
    V = FormulaToLocal(ThisWorkbook.Worksheets("TemplateMap").Range("C15"))
    Debug.Print V
End Sub

Function FormulaToLocal(ByRef rCell As Range) As Variant
    Dim x As Variant
    
    ThisWorkbook.Names.Add "myFormula", RefersTo:=rCell.Formula
    FormulaToLocal = ThisWorkbook.Names("myFormula").RefersToLocal
    
    ThisWorkbook.Names("myFormula").Delete
End Function

激活Base_Price工作表时输出：

=IF(AND(Base_Price!$H16="H";Base_Price!U16>=1;ISNUMBER(Base_Price!U16));TRUE;IF(AND(Base_Price!$H16<>"H";Base_Price!U16>=0;ISNUMBER(Base_Price!U16));TRUE;FALSE))

激活TemplateMap工作表时输出：

=IF(AND(TemplateMap!$H25="H";TemplateMap!M25>=1;ISNUMBER(TemplateMap!M25));TRUE;IF(AND(TemplateMap!$H25<>"H";TemplateMap!M25>=0;ISNUMBER(TemplateMap!M25));TRUE;FALSE))

无需激活任何工作表，也不会添加工作表名称，输出如下：

'=IF(AND($H11="H";L11>=1;ISNUMBER(L11));TRUE;IF(AND($H11<>"H";L11>=0;ISNUMBER(L11));TRUE;FALSE))

可以通过直接操作单元格的本地化公式属性来解决问题，修改后的VBA代码如下：

Sub ApplyLocalFormula()
    Dim sourceCell As Range
    Dim targetSheet As Worksheet
    Dim targetCell As Range
    Dim localFormula As String
    
    ' 定义源单元格和目标工作表/单元格
    Set sourceCell = ThisWorkbook.Worksheets("TemplateMap").Range("C15")
    Set targetSheet = ThisWorkbook.Worksheets("Base_Price")
    ' 可根据实际需求调整目标单元格位置
    Set targetCell = targetSheet.Range("A1")
    
    ' 获取源单元格的本地化公式，自动转换分隔符
    localFormula = sourceCell.FormulaLocal
    
    ' 移除源工作表名称前缀，保留原始单元格引用
    localFormula = Replace(localFormula, sourceCell.Parent.Name & "!", "")
    
    ' 将处理后的公式写入目标单元格
    targetCell.FormulaLocal = localFormula
End Sub

代码说明 #

• 使用FormulaLocal属性直接获取本地化公式，Excel会自动根据系统区域设置将逗号转换为分号
• 通过Replace方法移除不必要的工作表名称前缀，避免公式中出现多余的工作表引用
• 直接写入目标单元格的FormulaLocal属性，确保原始公式的行列引用相对性完全保留

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