Excel VBA生成公式的跨语言兼容问题求解

问题描述 #

我开发了一份电子表格，运行完全正常。其中部分VBA代码会根据用户的数据和选择生成公式，示例公式如下：

=(TEXT(EDATE([@[First Payment Date]],12*[@[Mortgage Length]]),"dd/mm/yyyy")- 
TEXT("01/01/"&YEAR(EDATE([@[First Payment Date]],
12*[@[Mortgage Length]])),"dd/mm/yyyy"))/(DATE(YEAR([@[First Payment Date]]),12,31)-
DATE(YEAR([@[First Payment Date]]),1,1)+1)*[@[Customer Equity Amount Per Year]]

该公式使用了DATE、YEAR等函数。我运行代码后将表格交给德国同事时，公式会自动转为德语版本并正常运行；但同事用新数据运行代码时，生成的是英文公式，因德语版Excel无法识别而报错。请问是否必须修改代码判断用户语言，输出对应语言的公式？还是有其他解决方案？

对应的VBA代码片段：

Set DealTable = shDbDealInfo.ListObjects("Deal_Information_Table")

If NewDealLine = 1 Then
    shDbDealInfo.Cells(8, "A") = "a"
End If

With DealTable
    
    .ListColumns("Customer Equity Amount Per Year").DataBodyRange(NewDealLine) = "=[@[Property Value]]*[@[Customer Equity %]]"
    .ListColumns("Customer Purchase Cost Per Year").DataBodyRange(NewDealLine) = shCalculators.Range("Calc_Customer_Purcase_Cost")
    .ListColumns("Bank Guarantee Cost Year").DataBodyRange(NewDealLine) = "=IF(Forecast_Start_Date=[@[First Payment Date]],1,ROUNDUP(YEARFRAC(Forecast_Start_Date,[@[First Payment Date]]),0))"
    .ListColumns("First Year Partial Equity").DataBodyRange(NewDealLine) = "=((TEXT(""31/12/""&YEAR([@[First Payment Date]]),""dd/mm/yyyy"")-TEXT([@[First Payment Date]],""dd/mm/yyyy""))+1)/(DATE(YEAR([@[First Payment Date]]),12,31)-DATE(YEAR([@[First Payment Date]]),1,1)+1)*[@[Customer Equity Amount Per Year]]"
    .ListColumns("First Year Partial Purchase Cost").DataBodyRange(NewDealLine) = "=((TEXT(""31/12/""&YEAR([@[First Payment Date]]),""dd/mm/yyyy"")-TEXT([@[First Payment Date]],""dd/mm/yyyy""))+1)/(DATE(YEAR([@[First Payment Date]]),12,31)-DATE(YEAR([@[First Payment Date]]),1,1)+1)*[@[Customer Purchase Cost Per Year]]"
    .ListColumns("First Month Partial Equity").DataBodyRange(NewDealLine) = "=(((TEXT(DAY(EOMONTH([@[First Payment Date]],0))&""/""&MONTH([@[First Payment Date]])&""/""&YEAR([@[First Payment Date]]),""DD/MM/YYYY"")-[@[First Payment Date]])+1)/((TEXT(""31/12/""&YEAR([@[First Payment Date]]),""dd/mm/yyyy"")-TEXT([@[First Payment Date]],""dd/mm/yyyy""))+1))*[@[First Year Partial Equity]]"
    .ListColumns("First Month Partial Purchase Cost").DataBodyRange(NewDealLine) = "=(((TEXT(DAY(EOMONTH([@[First Payment Date]],0))&""/""&MONTH([@[First Payment Date]])&""/""&YEAR([@[First Payment Date]]),""DD/MM/YYYY"")-[@[First Payment Date]])+1)/((TEXT(""31/12/""&YEAR([@[First Payment Date]]),""dd/mm/yyyy"")-TEXT([@[First Payment Date]],""dd/mm/yyyy""))+1))*[@[First Year Partial Purchase Cost]]"
    .ListColumns("Final Year Partial Equity").DataBodyRange(NewDealLine) = "=(TEXT(EDATE([@[First Payment Date]],12*[@[Mortgage Length]]),""dd/mm/yyyy"")- TEXT(""01/01/""&YEAR(EDATE([@[First Payment Date]],12*[@[Mortgage Length]])),""dd/mm/yyyy""))/(DATE(YEAR([@[First Payment Date]]),12,31)-DATE(YEAR([@[First Payment Date]]),1,1)+1)*[@[Customer Equity Amount Per Year]]"
    .ListColumns("Final Year Partial Purchase Cost").DataBodyRange(NewDealLine) = "=(TEXT(EDATE([@[First Payment Date]],12*[@[Mortgage Length]]),""dd/mm/yyyy"")- TEXT(""01/01/""&YEAR(EDATE([@[First Payment Date]],12*[@[Mortgage Length]])),""dd/mm/yyyy""))/(DATE(YEAR([@[First Payment Date]]),12,31)-DATE(YEAR([@[First Payment Date]]),1,1)+1)*[@[Customer Purchase Cost Per Year]]"
    .ListColumns("Final Month Partial Equity").DataBodyRange(NewDealLine) = "=(((EDATE([@[First Payment Date]],12*[@[Mortgage Length]]))-TEXT(""01/""&MONTH(EDATE([@[First Payment Date]],12*[@[Mortgage Length]]))&""/""&YEAR(EDATE([@[First Payment Date]],12*[@[Mortgage Length]])),""DD/MM/YYYY""))/((TEXT(""31/12/""&YEAR(EDATE([@[First Payment Date]],12*[@[Mortgage Length]])),""dd/mm/yyyy"")-TEXT(EDATE([@[First Payment Date]],12*[@[Mortgage Length]]),""dd/mm/yyyy""))+1))*[@[First Year Partial Equity]]"
    .ListColumns("Final Month Partial Purchase Cost").DataBodyRange(NewDealLine) = "=(((EDATE([@[First Payment Date]],12*[@[Mortgage Length]]))-TEXT(""01/""&MONTH(EDATE([@[First Payment Date]],12*[@[Mortgage Length]]))&""/""&YEAR(EDATE([@[First Payment Date]],12*[@[Mortgage Length]])),""DD/MM/YYYY""))/((TEXT(""31/12/""&YEAR(EDATE([@[First Payment Date]],12*[@[Mortgage Length]])),""dd/mm/yyyy"")-TEXT(EDATE([@[First Payment Date]],12*[@[Mortgage Length]]),""dd/mm/yyyy""))+1))*[@[First Year Partial Purchase Cost]]"

End With

解决方案 #

方案1：使用Formula属性（推荐） #

Excel的Formula属性支持直接输入英文函数名称，无论当前语言环境，Excel会自动将其转换为本地语言版本的公式。只需把原代码中直接赋值字符串的方式，改为设置单元格的Formula属性：

示例修改后的代码片段：

With DealTable
    .ListColumns("Customer Equity Amount Per Year").DataBodyRange(NewDealLine).Formula = "=[@[Property Value]]*[@[Customer Equity %]]"
    .ListColumns("Bank Guarantee Cost Year").DataBodyRange(NewDealLine).Formula = "=IF(Forecast_Start_Date=[@[First Payment Date]],1,ROUNDUP(YEARFRAC(Forecast_Start_Date,[@[First Payment Date]]),0))"
    .ListColumns("First Year Partial Equity").DataBodyRange(NewDealLine).Formula = "=((TEXT(""31/12/""&YEAR([@[First Payment Date]]),""dd/mm/yyyy"")-TEXT([@[First Payment Date]],""dd/mm/yyyy""))+1)/(DATE(YEAR([@[First Payment Date]]),12,31)-DATE(YEAR([@[First Payment Date]]),1,1)+1)*[@[Customer Equity Amount Per Year]]"
    ' 其他列的公式同理替换为.Formula赋值
End With

这个方法无需判断用户语言，也不用处理区域分隔符差异，是最通用的解决方案。

方案2：使用FormulaLocal属性 #

如果需要适配本地语言的公式写法（比如德语区用分号作为参数分隔符），可以使用FormulaLocal属性，直接输入符合当前区域设置的公式。但这个方法需要确保公式的语法适配目标语言环境，不如Formula属性通用。

示例修改：

.ListColumns("Bank Guarantee Cost Year").DataBodyRange(NewDealLine).FormulaLocal = "=WENN(Forecast_Start_Date=[@[First Payment Date]];1;AUFRUNDEN(JAHRESBRUCH(Forecast_Start_Date;[@[First Payment Date]]);0))"

错误原因分析 #

原代码是直接将公式字符串赋值给单元格的Value属性，相当于输入纯文本。在英文Excel环境中，可能自动将文本识别为公式并转换，但德语版Excel无法识别英文函数名称的文本，因此报错。而使用Formula或FormulaLocal属性时，Excel会明确将其识别为公式，并自动处理语言和区域适配。

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