CODEWITHSUNDEEP
excelvbasettingsdelimiter
Miquell
Miquell

Reputation: 1

Excel Delimiter changes to System default when creating a string based on multiple range values

Just a simple use case but it is bugging me big time!

A1: 2046.40
B1: 504.30

When i append multiple values from a table using Worksheet(sheetname).Range("C1") = Worksheet(sheetname).Range("A1") & "," & Worksheet(sheetname).Range("B1")

i get the result delimeter according to the system settings = "." result : 2046.40,504.30 (string)

When a user with a different language opens it up and runs this function the delimiter changes in the concat_value (C1) even though that A1 and B1 are displayed in the table using the "." as delimiter.

result other language 2046,40,504,30 (string)

i already tried the following codes:

Worksheets(sheetname).Range("A1").NumberFormat = "@"

Application.DecimalSeparator = "."
Application.UseSystemSeparators = False

Can anyone please enlighten me?

Regards,

M

Upvotes: 0

Views: 33

Answers (1)

VBasic2008
VBasic2008

Reputation: 54830

Decimal Separator Issue

  • The following is working for me (I have the comma as the decimal separator).

The Code

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

Additionally you could improve with the Format function, e.g.:

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

Upvotes: 1

Related Questions