Convert a string from a TextBox to a number with point as decimal separator

Question

I need to pass a number from a TexBox with this format 1,2 to a sql string in this format 1.2

My locale is It-it and uses decimal point with a comma (1,2).

How can I do this change in VBA? I wrote this code...

Private Sub insert_article_Click()
'set decimal separator as point
With Application
	.DecimalSeparator = "."
	.ThousandsSeparator = ","
	.UseSystemSeparators = False
End With
Dim number As Double
Dim word As String
word = "test"<br/>
number = CDbl(Textnumber.Value) 'if I enter 1,2 ,number will be 1,2
'I try also this, but number will be 1,2
'number = Replace(Textnumber.Value, ".", ",")
strSQL = "INSERT INTO table (col_word, col_number) VALUES ('" & word & "'," & number & ")"
...
End Sub

Thanks, Paolo

Answer 1

There isn't any easy way to do this. Application.DecimalSeparator, etc. affect the Excel UI, not VBA. As long as you dont want, or need, thousands separators, you can use Replace as your commented code shows, but you have the dot and the comma the wrong way round - try ....

 

    number = Replace(Textnumber.Value, ",", ".")

 

---

Enjoy,
Tony
www.WordArticles.com

Answer 2

Following Tony's post, ensure the 'number' variable is declared a string. If it's variant, the replace may not work properly.

---

Tiago Cardoso VB / VBA Analyst

Answer 3

Uuuhhh! I'm a chicken! :)

I wrote

number = Replace(Textnumber.Value, ".", ",")

but you're right... the correct string is

number = Replace(Textnumber.Value, ",", ".")

Thanks a lot!!