The sNumToUS Function

This function converts a number, date, or Boolean variable to a U.S.-formatted string. There is an additional parameter that can be used to return a string using Excel's DATE function, which would typically be used when constructing .Formula strings:

Function sNumToUS(vValue As Variant, Optional bUseDATEFunction) As String

Dim sTmp As String

'Don't accept strings or arrays as input If TypeName(vValue) = "String" Then Exit Function

If Right(TypeName(vValue), 2) = "()" Then Exit Function

If IsMissing(bUseDATEFunction) Then bUseDATEFunction = False

'Do we want it returned as Excel's DATE function '(which we can't do with strings)? If bUseDATEFunction Then

'We do, so build the Excel DATE() function string sTmp = "DATE(" & Year(vValue) & "," & Month(vValue) & "," & _ Day(vValue) & ")"


'Is it a date type? If TypeName(vValue) = "Date" Then sTmp = Format(vValue, "mm""/""dd""/""yyyy") Else

'Convert number to string in US format and remove leading space sTmp = Trim(Str(vValue))

'If we have fractions, we don't get a leading zero, so add one. If Left(sTmp, 1) = "." Then sTmp = "0" & sTmp If Left(sTmp, 2) = "-." Then sTmp = "-0" & Mid(sTmp, 2) End If End If

'Return the US formatted string sNumToUS = sTmp End Function vValue is a variant containing the number to convert, which can be:

□ A number to be converted to a string with U.S. formats

□ A date to be converted to a string in mm/dd/yyyy format

□ A Boolean converted to the strings "True" or "False"

bUseDATEFunction is an optional Boolean for handling dates. When it is set to False, sNumToUS returns a date string in mm/dd/yyyy format. When it is set to True, sNumToUS returns a date as DATE(yyyy,mm,dd).

0 0

Post a comment