Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

International Seperator 2

Status
Not open for further replies.
jajinder

jajinder

Technical User
Mar 11, 2004
88
NL
Hi,

In Holland (and other countries ofcourse) we use the "," to seperate the decimals in stead of the "." (1.000,00)

Have me a textbox where users can add a value using the ",". Now they want to use the "." to have the same "value" as the ","

Example: When I type 10.0 then it should be 10,0

In Excel-VBA I had this code to do so in a UserForm, ofcourse this does not work in VB.

Private Sub tbMaand_KeyUp( _
ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)

Dim strCompare As String
On Error Resume Next

If tbMonth = vbNullString Then Exit Sub

strCompare = tbMonth.Value

If KeyCode = 110 Or KeyCode = 190 Then
strCompare = Left(strCompare, Len(strCompare) - 1) & International(xlDecimalSeperator)
tbMaand.Value = strCompare
End If
End Sub

Does someone know how to do this? Searched the net without result. Please be patient.. newbee.

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King
 
Sort by date Sort by votes
You are using XL functionality when calling the function [green]Application.International(xlDecimalSeparator)[/green].

Both, the function [green]Application.International()[/green] and the enum value [green]xlDecimalSeparator[/green] are not defined in VB.

This may help you:
based on code from [URL unfurl="true"]http://www.freevbcode.com/ShowCode.asp?ID=1936[/url]
Code: 
Public Function GetDecimalSeparator() As String
  DecimalSeparator = Strings.Mid$(1 / 2, 2, 1)
End Function

Public Function GetThousandSeparator() As String
  Dim tmpStr As String
  
  tmpStr = Mid$(1000, 2, 1)
  If IsNumeric(tmpStr) Then
    ThousandSeparator = ""
  Else
    ThousandSeparator = Mid$(1000, 2, 1)
  End If
End Function

GetDecimalSeparator() creates a decimal value (0.5), handles it as a string and returns the 2nd character. Possibly you need to add a check for leading zeros.

GetThousandSeparator() has included a check for no thousands separator.
 
Upvote 0 Downvote
Does not work...:(

Ok.... Let me explain what I am making..(very simple interest-convertor)

textbox1 >> Command1 >> textbox2

When I press Command1 the following code is "executed"

Private sub command1_click()
textbox2 = (((textbox1 / 100 + 1) ^ 100) - 1) * 100
End Sub


If I would type : 1,5 the value in textbox2 would be 19,5... etc (which is correct)
But when I type 1.5 the value becomes 435,02... etc

I did put the first code directly in the form.

Public Function GetDecimalSeparator() As String
DecimalSeparator = Strings.Mid$(1 / 2, 2, 1)
End Function


Am I doing something wrong?

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King
 
Upvote 0 Downvote
Believe this is one of the few moments I realy thought logical... ahum... :p

Now this code really worx!!!!!! to get the international seperator you just have to give the "."-Ascii the same "value" as the ","-Ascii (It is so simple)

Private Sub TextBox1_KeyPress(KeyAscii as Integer)

If KeyAscii = 46 then '46 = . (dot)
KeyAscii = 44 '44 = , (comma)
End If

'to add only numbers
Select Case KeyAscii
Case 8, 44, 46 to 57
Case Else
KeyAscii = 0
End Select
End Sub

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King
 
Upvote 0 Downvote
Let's look at what's happening behind your code:
Code: 
Private sub command1_click()
  textbox2 = (((textbox1 / 100 + 1) ^ 100) - 1) * 100
End Sub
This is a fairly complex formula, so let's turn it into a series of easier steps:
Code: 
Private sub command1_click()
   Dim src As String
   Dim srcVal As Double
   Dim destVal As Double
   Dim dest As String
   
   src = textbox1.Text  ' Store in String variable
   srcVal = CDbl(src)   ' Convert to Double

   ' Do some math
   destVal = (((srcVal / 100 + 1) ^ 100) - 1) * 100

   dest = CStr(srcVal)  ' Convert back to a String
   textbox2.Text = dest ' Store in Textbox2
End Sub
You may need to use Val() instead of CDbl() in order to get your commas to work as a decimal point.

Also, it looks like you're trying to make sure you only have two numbers to the right of the decimal separator. This can be done more easily like this:
Code: 
destVal = CDbl(CLng(srcVal * 100)) / 100.0
Multiplying the value by 100 causes it to shift to the left 2 decimal places. You then convert it to a long with CLng to truncate anything to the right of the decimal separator, convert it back to a Double, and divide by 100.0 to turn it back into a number that looks like 0.00

Note that the next step, storing it into Textbox2, any trailing zeros will be removed. This is because the conversion back to a String always takes up the minimum amount of string space. If you want to display both digits to the right of the decimal separator, you'll need to use the Format$() function.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first
 
Upvote 0 Downvote
jajinder,

If windows is setup to use the dot(.) as the decimal seperator then your code won't work.

I'd suggest using the following to get the current windows set setting for the decimal then you can set both to that.

Please note the first line of code is how I call the following support code. It doesn't actualy belong above the rest.

This just replaces the International(xlDecimalSeperator) from your Excel VBA Code making your code international again.


Code: 
sDecSep = LoadLocaleValue(LOCALE_SDECIMAL)



Private Const LOCALE_SDECIMAL = &HE         '  decimal separator
Private Const LOCALE_STHOUSAND = &HF         '  thousand separator

Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal lCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long

Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long

'Function:
' LoadLocaleValue
'Description:
' This function is to load a Locale Value for the local user
'Parameters:
' lType    Long Integer
'   Specifies one of the LCTYPE constants to indicate the type of
'       information to be retrieved.
'
'   All LCTYPE values are mutually exclusive, with the exception of
'       LOCALE_NOUSEROVERRIDE. An application may use the binary-OR
'       operator to combine LOCALE_NOUSEROVERRIDE with any other LCTYPE
'       value. If passed such an LCType value, the function bypasses user
'       overrides, and returns the system default value for the requested LCID.
'Returns:
' String containg return
Private Function LoadLocaleValue(ByVal lType As Long) As String
    Dim lRet As Long
    
    Dim lLocale As Long
    Dim sCData As String
    Dim lchData As Long
    
    Dim sFinal As String
    
    Dim i As Long
    
    ' Get local user locale
    lLocale = GetUserDefaultLCID
    ' Initilize Return String
    sCData = String(100, " ")
    lchData = Len(sCData)
        
    ' Get Value
    lRet = GetLocaleInfo(lLocale, lType, sCData, lchData)

    ' Clean up Returned String
    If InStr(sCData, Chr(0)) Then
        sFinal = Trim(Left(sCData, InStr(sCData, Chr(0)) - 1))
    Else
        sFinal = Trim(sCData)
    End If
    
    ' Return Value
    LoadLocaleValue = sFinal
    
End Function

-Sean
 
Upvote 0 Downvote
Chip. Tanx for the info.. The code already worked.. But 'cause of the "."(dot) the formula did something else. Seems I have to learn much. Ordered some new books.. :p

Sean... WOW!!!! (8O)

I created a new form with text1 and text2, put your code directly in the form...
"sDecSep = LoadLocaleValue(LOCALE_SDECIMAL)" "Invalid outside procedure"

Fault seems logic to me, because I can't find any declaration for the "sDecSep"


---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King
 
Upvote 0 Downvote
jajinder,

The "sDecSep = LoadLocaleValue(LOCALE_SDECIMAL)" line of code was how I implemented the rest of the code. It does need to go in an appropriate function or sub and sDecSep does need to be defined by you as a string.

-Sean
 
Upvote 0 Downvote
Hi Sean..

Do you have a small (source-code)app to show me how it does work? I'm really curious.. :p (rkl@oordijk.nl) Pleaseeee.
I tried to do so -of course- without the proper result.

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King
 
Upvote 0 Downvote
jajinder,

The following code will replace the "." character and/or "," character with what ever is setup as the decimal seperator on that computer for values TYPED into Text1.

Most of this code matches what I posted previously.

Code: 
Option Explicit


Private Const LOCALE_SDECIMAL = &HE         '  decimal separator
Private Const LOCALE_STHOUSAND = &HF         '  thousand separator

Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal lCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long

Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long


Private Sub Text1_KeyPress(KeyAscii As Integer)
   Dim strIn As String
   Dim sDecSep  As String
    
   sDecSep = LoadLocaleValue(LOCALE_SDECIMAL)
   strIn = Chr(KeyAscii)
    
   If strIn = "." Or strIn = "," Then
      KeyAscii = Asc(sDecSep)
   End If

End Sub

'Function:
' LoadLocaleValue
'Description:
' This function is to load a Locale Value for the local user
'Parameters:
' lType    Long Integer
'   Specifies one of the LCTYPE constants to indicate the type of
'       information to be retrieved.
'
'   All LCTYPE values are mutually exclusive, with the exception of
'       LOCALE_NOUSEROVERRIDE. An application may use the binary-OR
'       operator to combine LOCALE_NOUSEROVERRIDE with any other LCTYPE
'       value. If passed such an LCType value, the function bypasses user
'       overrides, and returns the system default value for the requested LCID.
'Returns:
' String containg return
Private Function LoadLocaleValue(ByVal lType As Long) As String
    Dim lRet As Long
    
    Dim lLocale As Long
    Dim sCData As String
    Dim lchData As Long
    
    Dim sFinal As String
    
    Dim i As Long
    
    ' Get local user locale
    lLocale = GetUserDefaultLCID
    ' Initilize Return String
    sCData = String(100, " ")
    lchData = Len(sCData)
        
    ' Get Value
    lRet = GetLocaleInfo(lLocale, lType, sCData, lchData)

    ' Clean up Returned String
    If InStr(sCData, Chr(0)) Then
        sFinal = Trim(Left(sCData, InStr(sCData, Chr(0)) - 1))
    Else
        sFinal = Trim(sCData)
    End If
    
    ' Return Value
    LoadLocaleValue = sFinal
    
End Function

-Sean
 
Upvote 0 Downvote
WOOOOOOOHOOOOOOO

I would never get it this far
Have a shiny.... ;)

Many, many, many thanks from Sexbierum (The Netherlands)

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King
 
Upvote 0 Downvote
Thanx again Sean.

Nice coding, very nice :)P)

Problem is when I type 1,50 , the value 1,00 is shown in a TextBox on an other form. How is that possible? Is it 'cause of the Type "Long"? How should I solve this?

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King
 
Upvote 0 Downvote
jajinder,

I don't understand how you've implemented things. If you could answer a few questions I'll see if I can help.

What is the realationship of the text box where 1,50 was entered and the 1,00 was displayed?

What type long are you wondering if might be affecting it?

Are you doing anything other than typeing in the 1,50 to have the 1,00 displayed?

-Sean
 
Upvote 0 Downvote
Good morning Sean.. (It is 9.14 AM over here)

I have 2 forms.

1st Form has serveral TextBoxes where I can add values, no matter what. Numeric only. For instance: 115,32 in Text1 & 118,45 in Text2.

The second Form also has several TextBoxes. Form2 gets his values from Form1. In Form2 a lot of things are happening with the values. The (very) basic:
Code: 
Form2.Text1 = Val(Form1.Text1)
Form2.Text2 = Val(Form1.Text2)

Form2.Text1 = FormatNumber(Form2.Text1, 2)
Form2.Text2 = FormatNumber(Form2.Text2, 2)
Form2.Text3 = FormatNumber(Form2.Text3, 2)
Code: 
Form2:  Text3 = Val(Text1) + Val(Text2)
When I type in Form1.Text1 115,32 the value appears in Form2.Text1 as 115,00 in stead of 115,32. (Without the code it does what it should do)

I tried it without the format, removed codes, etc. Nothing seems to solve the problem. But I'm still newbee and the is a lot I don't no and to learn.

I thought that it was, because of the Type "Long". This Type declares the values in whole numbers. Am I right? Probably not.. :)P)

Could you please explain what is happening when:
Code: 
"Private Function LoadLocaleValue(ByVal lType As Long) As String ..... etc"?
I hope I don't ask to much.

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King
 
Upvote 0 Downvote
Good Afternoon jajinder, (I'm in Michigan in the United States so it's about 12:30 here [I'm about 4 hours earlier you I believe])


As for you first Question:


I believe you're loosing the decimal portion of the number becaues you're using the Val function. I would suggest changing it to CSng or CDbl depening on how large your values may get.


As for an explanation of my function defination.


The ByVal lType as Long is intended to take a selection from the constants (possibly some combined with an OR) defined at the start. This value will be passed to the GetLocaleInfo API function. There is a long list Constants that can be used with this function defined in the Win32Api text file that is easily readable witht API Viewer distrubted with VB6. I have only defined two of them as constants at the top of my example code.

This long tells the API Function what type of string to returen which I route back to the you through my function.


If you want a rough explanation of how the function works read on.


The GetUserDefaultLCID funciton (API) will return the user-default locale identifier.

I use the User-Default Locale because that is the one that is changed if go make changes in RegionalSettings in the Control Panel.

I then set sCData to a string of 100 spaces. This is to alocate space for the response string from the API call. I do this allocation because API Calls don't always alocate memory the same way VB would.

I then get the number of character that I Alocated Memory for with the return string.

I then call the GetLocaleInfo API function. This will return the requested value in the alloted string. I get the return in lRet if I was doing better error trapping I would examine lRet for an Error return.

The Windows API are written in one of the C family of lanuages. The C family of lanuages uses null terminated strings. So the next thing I do is crop the return string at a null if it exitst. The Trim function that you see there will remove spaces from both ends of the string. I use this here becase I want to make sure my initial string of 100 spaces isn't being used as part of the return string.

Then I set the Value as the return of the function.

I hope I'm not too long winded for you.

-Sean

 
Upvote 0 Downvote
Many thanks again Sean. Although it is still hard for me to understand what you are saying, I learned much.

I used cdbl and it works fine now.

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King
 
Upvote 0 Downvote

Sean,

I have had this same problem. Now because I'm a newbie in VB I started to wonder where I should place magic code :)

I have two textboxes where the user can input values and there the code is needed.

So the question is where I should place your code so that it applies to these to textboxes?

Many thanks inadvance!
 
Upvote 0 Downvote
episode80,

I would suggest that you place all the code in my last code box except the KeyPress event (Text1_KeyPress) to a module. Then change this main function to be able to be seen outside of the module (This will allow it to be used on multiple forms).

Change:

Private Function LoadLocaleValue(ByVal lType As Long) As String

To:

Public Function LoadLocaleValue(ByVal lType As Long) As String

Then for every text box you want this done to implement a KeyPress event simular to Text1_KeyPress.

Hope this helps

-Sean
 
Upvote 0 Downvote
Sean,

I have still some problems.

Now I have this code in the module:

Code: 
Option Explicit


Private Const LOCALE_SDECIMAL = &HE         '  decimal separator
Private Const LOCALE_STHOUSAND = &HF         '  thousand separator

Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal lCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long

Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long

'Function:
' LoadLocaleValue
'Description:
' This function is to load a Locale Value for the local user
'Parameters:
' lType    Long Integer
'   Specifies one of the LCTYPE constants to indicate the type of
'       information to be retrieved.
'
'   All LCTYPE values are mutually exclusive, with the exception of
'       LOCALE_NOUSEROVERRIDE. An application may use the binary-OR
'       operator to combine LOCALE_NOUSEROVERRIDE with any other LCTYPE
'       value. If passed such an LCType value, the function bypasses user
'       overrides, and returns the system default value for the requested LCID.
'Returns:
' String containg return
Public Function LoadLocaleValue(ByVal lType As Long) As String
    Dim lRet As Long
    
    Dim lLocale As Long
    Dim sCData As String
    Dim lchData As Long
    
    Dim sFinal As String
    
    Dim i As Long
    
    ' Get local user locale
    lLocale = GetUserDefaultLCID
    ' Initilize Return String
    sCData = String(100, " ")
    lchData = Len(sCData)
        
    ' Get Value
    lRet = GetLocaleInfo(lLocale, lType, sCData, lchData)

    ' Clean up Returned String
    If InStr(sCData, Chr(0)) Then
        sFinal = Trim(Left(sCData, InStr(sCData, Chr(0)) - 1))
    Else
        sFinal = Trim(sCData)
    End If
    
    ' Return Value
    LoadLocaleValue = sFinal
    
End Function

and this code inside the form behind the textbox 'tag'

Code: 
Private Sub TextBox10_Change()

textbox10_KeyPress(KeyAscii As Integer)
   

   
   Dim strIn As String
   Dim sDecSep  As String
    
   sDecSep = LoadLocaleValue(LOCALE_SDECIMAL)
   strIn = Chr(KeyAscii)
    
   If strIn = "." Or strIn = "," Then
      KeyAscii = Asc(sDecSep)
   End If

'the rest of the code

ens sub

It seems that VB does not understand the code which is inside the form (above) the keypress function is marked red
and VB says that it does not recognize it.

what am I doing wrong?

Thanks for your help!

-epi80
 
Upvote 0 Downvote
episode80,

Sorry about that. I frogot to make the constants public when the code was moved to a module. This is so code outside of the module can use these constants.

Change

Code: 
Private Const LOCALE_SDECIMAL = &HE         '  decimal separator
Private Const LOCALE_STHOUSAND = &HF         '  thousand separator

to

Code: 
Public Const LOCALE_SDECIMAL = &HE         '  decimal separator
Public Const LOCALE_STHOUSAND = &HF         '  thousand separator

-Sean
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gmmastros
  • Locked
  • Question
Internationalization problems
Replies
12
Views
349
strongm
strongm
vcharles2013
  • Locked
  • Question
Help with formating number in Datagrid View for international distribution
Replies
0
Views
81
vcharles2013
vcharles2013
AndyGroom
  • Locked
  • Question
Calculating international time
2
Replies
27
Views
479
strongm
strongm
DeGeus
  • Locked
  • Question
Decimal seperator and Excel 2000
Replies
1
Views
49
SBerthold
SBerthold
happyabc
  • Locked
  • Question
International applications
Replies
5
Views
72
LostInCode
LostInCode

Part and Inventory Search

Sponsor

Back
Top