CODEWITHSUNDEEP
vbaexcel
RafaelSTK
RafaelSTK

Reputation: 13

How to stop " point " to be replaced by a " comma" in the output of a VBA code?

Initially it is important to note that I use MS Excel in Brazilian Portuguese.

That's my code:

Dim C1 As Integer
Dim C2 As Integer
Dim Cod As String

C1 = Worksheets("Dictionary").Range("D1").Value
C2 = Worksheets("Dictionary").Range("D2").Value

Cod = C1 & "." & C2

Worksheets("Dictionary").Range("D3").Value = Cod

Cells:

D1 = 1

D2 = 2

I expected that the output would be "1.2", but it's "1,2"!

What am I doing wrong?

And I apologize for my rusty English

Thank you!

Upvotes: 1

Views: 1282

Answers (3)

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

You use your Excel in "Brazilian Portuguese". The change may be due to the International Settings in the Windows Control Panel.

If your International Settings define the "," as the decimal separator, then Excel will convert the dot to the comma when you make the assignment in Visual Basic. This behavior makes the VBA code independent of locale settings and so makes the code portable in the international context (I assume that is the intention of this behavior).

If Brazilian Portuguese needs the dot as separator, then set your computer to use the dot as decimal separator, however, it shouldn't matter because when you send the spreadsheet to Brazil, the Excel there will use the correct decimal separator (I assume numbers are stored as binary floating point so dot or comma is only a matter of presentation).

EDIT: strikethrough of text above that would seem a workaround, as setting the international seting/decimal separator makes no difference for the end result.

Upvotes: 1

David Syriac
David Syriac

Reputation: 65

Sub Replace()

    Sheet1.Range("B1").Value = "=SUBSTITUTE(A1,""."","","")"

End Sub

You can change your range accordingly.

Example - Range("B1:B3").Value = "=SUBSTITUTE(A1:A3,""."","","")"

Upvotes: 0

tyg
tyg

Reputation: 15135

Try this before outputting the value to the range:

Worksheets("Dictionary").Range("D3").NumberFormat = "@"

This changes the format of the cell to Text which prevents Excel to interpret the value you write to it as a number.

Upvotes: 1

Related Questions