Add a New Number Format

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)	Else		' 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, _				Format:=sFormat)		ElseIf IsMissing(FirstDayOfWeek) Then			Format = VBA.Format(Expression:=Expression, _				FirstWeekOfYear:=FirstWeekOfYear)		ElseIf IsMissing(FirstWeekOfYear) Then			Format = VBA.Format(Expression:=Expression, _				FirstDayOfWeek:=FirstDayOfWeek)		End If	End IfEnd 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.

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as