Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Visual Basic
Expertise: Intermediate
Dec 23, 1998



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Add a New Number Format

A client needed the numbers to show up in certain data files in the "x100" format to accommodate interchanging data with a legacy system. That is, if the number is "23.56," it shows up as "2356," and "23" becomes "2300." Because I didn't want to create a special case throughout my code to manage this, and the VB Format function doesn't support such a format, I subclassed the Format function and added the new format myself:
Public Function Format(Expression As Variant, Optional _
	sFormat As Variant, Optional FirstDayOfWeek As _
	Variant, Optional FirstWeekOfYear As Variant) As String
	If IsMissing(sFormat) Then
		Format = VBA.Format(Expression)
	ElseIf sFormat = "x100" Then
		' handle the special x100 case
		Expression = Expression * 100
		Format = VBA.Format(Expression, "0.")
		Format = Left$(Format, InStr(1, Format, ".") - 1)
		' wasn't my special format, so pass through to the 
		' real format function
		If IsMissing(FirstWeekOfYear) And _
			IsMissing(FirstDayOfWeek) Then
			Format = VBA.Format(Expression:=Expression, _
		ElseIf IsMissing(FirstDayOfWeek) Then
			Format = VBA.Format(Expression:=Expression, _
		ElseIf IsMissing(FirstWeekOfYear) Then
			Format = VBA.Format(Expression:=Expression, _
		End If
	End If
End Function
This allows me to simply call the Format function as I normally would everywhere in my code, have my "x100" format, and still support all the normal Format parameters and options. Note the use of VBA.Format in the routine to reference the built-in format function.
Jon Pulsipher
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date