Handling seperators(commas, periods) in long numbers in VBA

Posted by Dominic on May 31, 2001 1:32 AM

I have two macros that do Price Calculation and also Weigh/Volume conversions based on Densities of Chemicals etc.

They work fine thanks to help from all on this board except, that some users in my office input numbers as follows 1.200.987,00 or Vice versa 1,200,987.00
How do I handle that or how do I get my MAcro to recoginse these and pop up a message saying please use a different format.

Posted by Dax on May 31, 2001 4:53 AM

Hi,

Doesn't it depend on how the user has numbers set up in Regional Settings in Control Panel. I'd imagine that everyone has the same settings in your workplace so you could use code like this to check that the number is valid: -

Sub CheckNumberFormat()
Dim InpValue As String, ValidValue As Boolean

'This macro assumes that the number should be
'in the format 123,456,789.00 not 123.456.789,00

While Not ValidValue
InpValue = InputBox("Please enter number...", "Number")
If IsNumeric(InpValue) Then ValidValue = True
Wend
'Rest of your code.
End Sub

I may have misunderstood your question but the IsNumeric function will only return True if the string value can be converted into a number e.g. if your decimal symbol is . then IsNumeric(123.456.789) would return False.

Make sense?

Regards,
Dax.

Posted by Dominic on May 31, 2001 5:17 AM

Thanx