基于多个范围值创建字符串时，Excel分隔符更改为系统默认值

Question

这只是一个简单的用例，但它一直困扰着我！

A1: 2046.40
B1: 504.30

当我使用工作表(sheetname(.Range("C1"(=工作表(sheetname(.Lange("A1"(&"amp；工作表(sheetname(.范围("B1"(

我根据系统设置得到结果delimeter＝"结果：2046.40504.30(字符串(

当具有不同语言的用户打开它并运行此函数时，即使A1和B1使用"在表中显示，分隔符在concat_value(C1(中也会改变"作为分隔符。

结果其他语言2046,40504,30(字符串(

我已经尝试了以下代码：

Worksheets(sheetname).Range("A1").NumberFormat = "@"
Application.DecimalSeparator = "."
Application.UseSystemSeparators = False

有人能启发我吗？

问候，

M

Answer 1

小数分隔符问题

• 以下内容对我有效(我用逗号作为小数分隔符(

代码

Option Explicit
Sub DecimalSeparatorIssue()

Const sheetname As String = "Sheet1"
Dim sep As String
sep = Application.DecimalSeparator
Dim wb As Workbook
Set wb = ThisWorkbook ' The workbook containing this code.

Dim ws As Worksheet
Set ws = wb.Worksheets(sheetname)

If sep = "." Then
ws.Range("C1") = CStr(ws.Range("A1")) & "," & CStr(ws.Range("B1"))
Else
ws.Range("C1") = Replace(CStr(ws.Range("A1")), sep, ".") & "," _
& Replace(CStr(ws.Range("B1")), sep, ".")
End If
End Sub

此外，您还可以使用格式化功能进行改进，例如：

Sub DecimalSeparatorIssue()

Const sheetname As String = "Sheet1"
Dim sep As String
sep = Application.DecimalSeparator
Dim wb As Workbook
Set wb = ThisWorkbook ' The workbook containing this code.

Dim ws As Worksheet
Set ws = wb.Worksheets(sheetname)

If sep = "." Then
ws.Range("C1") = Format(ws.Range("A1").Value, "0.00") & "," _
& Format(ws.Range("B1").Value, "0.00")
Else
ws.Range("C1") = Replace(Format(ws.Range("A1").Value, "0.00"), sep, ".") _
& "," _
& Replace(Format(ws.Range("B1").Value, "0.00"), sep, ".")
End If
End Sub