The bWinToNum Function

This function checks if a string contains a number formatted according to the Windows Regional Settings and converts it to a Double. The function returns True or False to indicate the success of the validation, and optionally displays an error message to the user. It is best used as a wrapper function when validating numbers entered by a user, as shown in the "Interacting with Users" section.

Note that if the user has used Excel's International Options to override the WRS decimal and thousands separators, the OverrideToWRS function must be used to ensure you send a WRS-formatted string to this function:

Function bWinToNum(ByVal sWinString As String, _ ByRef dResult As Double, _ Optional bShowMsg) As Boolean

Dim dFrac As Double

' Take a copy of the string to play with sWinString = Trim(sWinString) dFrac = 1

If IsMissing(bShowMsg) Then bShowMsg = True If sWinString = "-" Then sWinString = "0" If sWinString = "" Then sWinString = "0"

' Check for percentage, strip it out and remember to divide by 100 If InStr(1, sWinString, "%") > 0 Then dFrac = dFrac / 100

sWinString = Application.Substitute(sWinString, "%", "") End If

' Are we left with a number string in windows format? If IsNumeric(sWinString) Then

' If so, convert it to a number and return success dResult = CDbl(sWinString) * dFrac bWinToNum = True Else

' If not, display a message, return zero and failure

If bShowMsg Then MsgBox "This entry was not recognized as a number," _

& Chr(10) & "according to your Windows Regional Settings.", vbOKOnly dResult = 0 bWinToNum = False End If

End Function sWinString is the string to be converted, and dResult is the converted number, set to 0 if the number is not valid or empty. bShowMsg is optional and should be set to True (or missing) to show an error message, or False to suppress the error message.

0 0

Post a comment