Office 365中VBA如何读取单元格的区域无关一致数值？

核心问题分析 #

你需要在不同区域设置（比如荷兰区域用逗号做小数点、句号做千位分隔符）的环境下，读取单元格的实际数值并转换为英国格式（小数点为点，千位分隔符为逗号），且同时支持常量单元格和公式单元格，避免字符串替换的繁琐操作。

之前尝试修改Application.ThousandsSeparator等属性无效，是因为没有先设置Application.UseSystemSeparators = False；而Range.Formula仅对常量单元格有效，是因为公式单元格的Formula属性返回的是公式文本，而非计算后的数值。

可行解决方案 #

方法1：使用Format函数强制指定区域（推荐） #

通过Range.Value2获取单元格的实际存储数值（不受区域和显示格式影响），再用Format函数结合vbEnglishUS参数，强制生成英国格式的字符串：

Function GetUKFormattedValue(rng As Range) As String
    Dim cellValue As Variant
    cellValue = rng.Value2
    
    If IsNumeric(cellValue) Then
        ' 格式字符串可按需调整：#,##0.00 带千位分隔符，0.00 仅保留两位小数
        GetUKFormattedValue = Format(cellValue, "#,##0.00", vbEnglishUS)
    Else
        ' 非数值单元格返回原显示文本
        GetUKFormattedValue = rng.Text
    End If
End Function

使用示例

在VBA中调用该函数，直接获取目标单元格的英国格式数值：

Dim ukValue As String
ukValue = GetUKFormattedValue(Range("A1"))
' 输出示例：对于显示为"123.456,78"的单元格，返回"123,456.78"

方法2：临时修改Excel全局分隔符设置（批量处理适用） #

如果需要批量处理大量单元格，可以临时修改Excel的区域分隔符设置，读取完成后恢复原设置，确保全局环境不受影响：

Sub BatchProcessUKValues()
    ' 保存原始区域设置
    Dim origDecimal As String
    Dim origThousands As String
    Dim origUseSystem As Boolean
    
    origDecimal = Application.DecimalSeparator
    origThousands = Application.ThousandsSeparator
    origUseSystem = Application.UseSystemSeparators
    
    ' 切换为英国格式设置
    Application.UseSystemSeparators = False
    Application.DecimalSeparator = "."
    Application.ThousandsSeparator = ","
    
    ' 示例：将A1:A10的数值转为英国格式后写入B列
    Dim i As Integer
    For i = 1 To 10
        With Range("A" & i)
            If IsNumeric(.Value2) Then
                Range("B" & i).Value = CStr(.Value2)
            Else
                Range("B" & i).Value = .Text
            End If
        End With
    Next i
    
    ' 恢复原始设置，避免影响用户操作
    Application.UseSystemSeparators = origUseSystem
    Application.DecimalSeparator = origDecimal
    Application.ThousandsSeparator = origThousands
End Sub

关键注意事项 #

• 优先使用Range.Value2：它直接读取单元格存储的原始数值（二进制浮点数），完全不受显示格式和系统区域的干扰，是获取数值的最可靠方式。
• 避免依赖Range.Formula：公式单元格的Formula属性返回的是公式文本，而非计算结果；常量单元格的Formula虽能返回数值字符串，但并非设计用于读取数值。
• 格式字符串可自定义：根据数据库需求调整Format函数的格式参数，比如不需要千位分隔符时用"0.00"，需要整数时用"#"。

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