Excel VBA如何阻止小数点自动转换为逗号？

以下是几种可行的解决方案，针对你用Split/Join处理数组时遇到的小数拆分问题：

1. 临时切换系统小数分隔符 #

VBA会跟随系统区域设置的小数分隔符，临时将其改为小数点，处理完后恢复原设置，就能避免Join时把小数拆分成多个元素：

Dim originalDecimalSep As String
originalDecimalSep = Application.DecimalSeparator ' 保存原分隔符
Application.DecimalSeparator = "." ' 临时设为小数点

' 执行你的Split/Join操作
Dim arr1 As Variant, arr2 As Variant
arr1 = Array(0.01, 0.21)
arr2 = Array(1.05, 2.34)
Dim joined1 As String, joined2 As String
joined1 = Join(arr1, ",") ' 此时输出为"0.01,0.21"
joined2 = Join(arr2, ",")
' 后续按索引合并的逻辑...

Application.DecimalSeparator = originalDecimalSep ' 恢复原设置

注意：必须在操作结束后恢复原分隔符，避免影响其他Excel功能。

2. 强制将小数格式化为带小数点的字符串 #

提前把数组中的每个小数元素转换为带小数点的字符串，再执行Join操作，彻底规避系统设置的影响：

Dim arr As Variant, strArr() As String
arr = Array(0.01, 0.21)
ReDim strArr(LBound(arr) To UBound(arr))

Dim i As Long
For i = LBound(arr) To UBound(arr)
    ' 用Format强制输出小数点格式，精度可按需调整
    strArr(i) = Format(arr(i), "0.00")
    ' 或者替换系统默认的逗号分隔符为点
    ' strArr(i) = Replace(CStr(arr(i)), ",", ".")
Next i

Dim joinedStr As String
joinedStr = Join(strArr, ",") ' 结果为"0.01,0.21"

3. 使用非冲突的分隔符 #

换用一个不会和小数分隔符（逗号）重叠的分隔符（比如分号;、竖线|）进行Join/Split，后续再处理小数的逗号转点：

Dim arr As Variant, joinedStr As String, splitArr As Variant
arr = Array(0.01, 0.21)
joinedStr = Join(arr, ";") ' 输出为"0,01;0,21"
splitArr = Split(joinedStr, ";") ' 拆分后得到数组元素"0,01"、"0,21"

' 将逗号转回小数点并转为数值
For i = LBound(splitArr) To UBound(splitArr)
    splitArr(i) = CDbl(Replace(splitArr(i), ",", "."))
Next i

4. 直接按索引合并数组（跳过Split/Join） #

既然需求是按索引合并多个数组，完全可以直接遍历索引完成合并，从根源上避免分隔符冲突：

Dim arr1 As Variant, arr2 As Variant, combinedArr() As Variant
arr1 = Array(0.01, 0.21)
arr2 = Array(1.05, 2.34)

ReDim combinedArr(LBound(arr1) To UBound(arr1))
For i = LBound(arr1) To UBound(arr1)
    ' 示例：将两个数组同索引元素合并为字符串
    combinedArr(i) = arr1(i) & "," & arr2(i)
    ' 若需保留数值，可存为嵌套数组
    ' combinedArr(i) = Array(arr1(i), arr2(i))
Next i

内容的提问来源于stack exchange，提问作者Mauro Meneses Ramirez