VB6 Excel Changing Regional Settings Comma To Period

[Qpido]

Dear Members,

I have finished several projects to calculate certain prices, however I'm running into problems with other computers who use european standard settings.

My calculations are based on the "." system as a decimal point.

However, almost all computers are using a comma. This messes up my calculations that are being written to Excel and get a bunch of wrong numbers.

The code I found somewhere on the internet is this:

 'Written: December 29, 2010
 'Author: Leith Ross
 'Summary: Changes the decimal separator from a period to comma. Another macro
 '         reverses the operation to restore the period as the decimal separator.
 
 Private dwLCID As Long
 Private Const LOCALE_SDECIMAL = &HE

 Private Declare Function SetLocaleInfo _
   Lib "kernel32.dll" _
     Alias "SetLocaleInfoA" _
       (ByVal Locale As Long, _
        ByVal LCType As Long, _
        ByVal lpLCData As String) As Boolean
        
 Private Declare Function GetSystemDefaultLCID _
   Lib "kernel32.dll" () As Long


Sub ChangeDecimalSeparator()

  Dim RetVal As Long
  
    dwLCID = GetSystemDefaultLCID
    
    RetVal = SetLocaleInfo(dwLCID, LOCALE_SDECIMAL, ".")
    
       If RetVal = 0 Then
          MsgBox "Unable to Change the Decimal Separator.", vbOKOnly + vbCritical
          Exit Sub
       End If
    
End Sub

I realize this is an Excel code, can anyone help me translate this into VB6 code?

I keep getting "Only text can come after end function end sub etc etc." on the 3rd paragraph code.

Thanks as always,

Jerome

 

[Jaafar Tribak]

How about using the Str VB conversion function at the appropriate locations in your code which is a simple way plus it doesn't mess up with the user's regional settings.

Here is what I mean :

1- Change the Decimal separator from "." to a Comma "," via Control Pannel.

2- Now run this this code :

Sub Test()

    Dim x As Single
    Dim y As Single
    
    x = 1.5
    y = 1.5
    
    MsgBox x         [COLOR=Green][B]'Returns 1[COLOR=Red],[/COLOR]5[/B][/COLOR]
    MsgBox Str(y)   [B][COLOR=Green] 'Returns 1[COLOR=Red].[/COLOR]5[/COLOR][/B]
    
    
End Sub

 

[Qpido]

I already thought of this solution, but it won't work seeing as the code I am using is reading out of Excel.

It will read from Excel in comma's so it won't work.

If frmVerzend.lstPostcode = "10-12" Or frmVerzend.lstPostcode = "15-26" Or frmVerzend.lstPostcode = "28-29" Or frmVerzend.lstPostcode = "36-37" Or frmVerzend.lstPostcode = "39" Or frmVerzend.lstPostcode = "13-14" Or frmVerzend.lstPostcode = "30-35" Or frmVerzend.lstPostcode = "38" Or frmVerzend.lstPostcode = "90-99" Then
If frmVerzend.lstGewicht = "t/m 50kg" Then
ObjT.txtPrijs.Text = Val(Str(FOTRICWB("f:\Transporting Costs\Data\BosmanPrijzen.xls", "Belgium", "h4")))
End If

Function FOTRICWB(ClosedWorkbookFullName As String, _
    SheetName As String, RangeAddress As String) As Variant
    
    Dim conn As Object, rs As Object, SQL As String
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ClosedWorkbookFullName & _
    ";Extended Properties=""Excel 8.0;HDR=NO;"""
    
    SQL = "Select * From [" & SheetName & "$" & RangeAddress & ":" & RangeAddress & "]"
    rs.Open SQL, conn, 1, 3
    FOTRICWB = rs.Fields(0).Value
    rs.Close: conn.Close
    
    If IsNull(FOTRICWB) Then FOTRICWB = ""
    
End Function

 

[RoryA]

That code should work fine in VB6 as far as I can see, though I would note that it is generally considered bad practice to alter a user's settings just so your program works. (what if it crashes and leaves them with regional settings they don't want, for example?)

 

[Qpido]

I figured it out, never mind.

Turns out you have to place those statements at the beginning of the form before anything else.

Don't worry I have a code that checks what they had before and puts it back on exit.

Jerome