The number 1000000000 is difficult to read at a glance, isn’t it? How many zeroes was that? Similarly, the number 12.349845423084 sure contains a lot of decimal digits, but are all those numbers really necessary if you’re looking at a product’s cost-per-unit? Take a quick look at these numbers and tell me how you would compare them.
12.54332 955.3345 1.058383 2342.234
At a glance, they might all seem like they’re of the same order of magnitude, but the decimal point appears in different columns for each number. All the numbers are actually different orders of magnitude.
All these examples illustrate some serious readability concerns, which is a big human performance error trap. To fix these readability issues, use VBA NumberFormat, which is a property of Range objects (the same Range object that controls individual cells in your spreadsheet). In Excel, the main formats are easily accessible via the Number portion of the ribbon:
The most common Excel cell formatting options, displayed in a dropdown
You might have even seen the cryptic number formats under Custom, like these:
Custom Formats Dialogue Box
In this tutorial, we will work with the VBA NumberFormat property, which is a way to automate number formatting in Excel without having to slowly navigate through the screens above. At the end of this tutorial, you’ll understand how these codes work and you’ll be able to write our own in your VBA macros.
- Accessing the VBA NumberFormat Property
- The Format Code
- Decimal Point and Comma
- VBA NumberFormat Code Sections and Coloring
- Conditional Formatting
- VBA NumberFormat Symbols
Assigning and Accessing the VBA NumberFormat Property
Most of this tutorial will focus on how to write the codes themselves. But since this is a VBA blog, we’ll note how to programmatically assign the formats first.
The VBA NumberFormat property is a property of a range object, so you can assign the format to your range like this:
Sub assigning_numberformats() Dim cell_to_format As Range Dim vector_to_format As Range Dim matrix_to_format As Range Set cell_to_format = Range("A1") Set vector_to_format = Range("B1:B100") Set matrix_to_format = Range("C1:D50") range_to_format.NumberFormat = "YOUR FORMAT CODE HERE" 'replace range and format code End Sub
Make powerful macros with our free VBA Developer Kit It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit full of pre-built macros so you can master file I/O, arrays, strings and more - grab a copy below.
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit full of pre-built macros so you can master file I/O, arrays, strings and more - grab a copy below.
Of course, you need to replace
That’s all there is to assigning the range format. It’s just as easy as clicking the buttons in Excel, except with VBA you can mass-produce your desired results
If you want to access a cell or range’s existing NumberFormat property, you can read it as a string in your macro like this:
the_format = Range("YOUR RANGE HERE").NumberFormat
That’s all there is to it. It really is quite simple to assign and access a NumberFormat using VBA. Now let’s look at the hard part: writing our own format codes.
The Format Code
VBA NumberFormat codes are similar to Regular Expressions in that you define a pattern for the computer to follow. That makes the format dynamic and capable of accepting many inputs.
An expression like
0.0#" per part" might seem a bit odd at first, but it really denotes a pattern. Let’s break it down into its parts:
The Zero (0): there must be at least one digit displayed to the left and one to the right of the decimal
The Pound/Hashtag/Number symbol (#): if there is a significant digit here, it will display, if not, there will be no digit here. Conversely, if there are more than 3 digits to the right of the decimal place, the number will be rounded to two decimal places.
The Quotes (“”): The text between the quotes will always be appended to the numbers entered.
By applying this format code with this line:
Range("A1:A4").NumberFormat = "0.0#"" per part"""
We will end up with these outputs: 2018-09-19-first-one.png
The raw numbers (General format) are on the right and the formatted ones are on the left
Notice what happens here. If we only enter
See how the words “per part” were appended to every entry? I want you to note the double quotes in our macro If you want text within the format via VBA, make sure to keep track of your quotes, like
"Outer Text ""Inner Text"" Outer Text." If you type just one set of quotes around the inner text, VBA will think the string is finished and get confused. This common mistake will throw frustrating errors at you until you satisfy its strict quotation mark requirements. I’ve discussed the complexities of concatenating quotation marks in VBA before. I encourage you to take a look if you want additional guidance.
Here’s a quick test for you: How would our output change if we changed our NumberFormat macro to
Range("A1:A4").NumberFormat = "#.0#"" per part"""
The only difference is the # before the decimal.
Answer: Everything would remain the same, except A3. Remember that # means “display if there, but do not display if not there”. Thus, A3’s number part would change to
You might want to force all your numbers to include 3 decimals, regardless of whether the original input had 3 decimals. To do this, we can use “.000”. More than 4 decimal places will round, less than three will result in at least one trailing zero.
Thus 5 will become 5.000 and 5.9337 will become 5.934.
On the other hand, we could write “000.”, which would force a number like 52 to display as 052.
Aligning the Decimal Place
The 0 and # allow us to modify the number of insignificant digits by either forcing them to be displayed, rounding them, or ignoring them altogether. But what about that set of numbers we had earlier with different orders of magnitude? How can we make them easier to compare at a glance? We use the question mark. This number format example
Range("A1:A4").NumberFormat = ".????"
will produce the following result:
The decimal points are now aligned
The decimal points are now aligned, with four decimal places. If there are more than 4 places, they’re rounded (just like # and 0).
Of course, we could achieve the same thing with 0s (
".0000"), but our last entry would become “2342.2340”.
Caveat with Alignment
Technically, the question mark (?) is simply a placeholder that adds a space if the digit is missing. It pads the display with spaces when no number is present. Thus, if we center our text above and use the same code, we will get
To remedy this, you will need to use at least as many ?s as your biggest number has on the left side. Here, we would need
The Decimal Point and the Comma
The decimal point obviously denotes where the decimal part of the number begins. If it is omitted, all numbers displayed will be whole numbers. The format
000 will always be at least three digits, but none of them will ever be less than one (neer have a decimal part).
Formatted and Unformatted using
000 as the NumberFormat code
If we have a code like
"00.", then the display will always include a decimal point but never any digits after it, like
The comma is somewhat more interesting. When I say comma, I really mean whatever your thousands separator is. My visitors from outside the States may be used to seeing a period as the thousands separator.
Anyway, in most formats, it will likely just be the thousands separator:
0,000.0# will show us
Two different formats with commas as the thousands separator
This is how the comma affects output. We can see the difference with the forced digits (using
0) and optional zeroes and decimals (with
This is where things get interesting. The comma also acts as a scaler by a factor of 1000, or three orders of magnitude. To use it in this way, simply append it to the end of your format. To scale by one thousand, use a single comma. To scale by one million, use two commas. You get th idea.
Let’s say we are entering market capitalizations of the biggest companies in the world. Reading a number like 957847923 is a little difficult, but we can easily round to the millions place using this format code
"$#,###.00#,, \M". What does this do?
First, it requires at least two decimal places and has an optional third, and it scales the number by one million. It also uses the
\ character to add a single letter, M (for millions), and it adds the dollar sign before the number.
Literals show up exactly as typed. Using a backslash, we can add a character literal, such as M (for millions) or C (for Celsius). Most symbols (other than the reserved ones like 0, #, ?, and the like) also act as literals. So the degree symbol, the euro symbol, or the trademark symbol can all be typed into the NumberFormat string and show up on the spreadsheet.
One use of literals that frequently occurs in accounting is showing negative numbers in parentheses. People do this because a minus sign is easy to miss when scanning many numbers. You can simply use
"-(0.00) in the “negative section” of the format, which we will look at next. This code utilizes the parentheses literal.
VBA NumberFormat Sections and Coloring
The VBA NumberFormat property has four sections corresponding to positive, negative, zero, and text entries. You separate the four sections using the semicolon.
"0.0#; [Red]-0.00#; [Blue]0.; [Magenta]@" will format the cell depending on what’s entered. Positive numbers will be black, with at least one and up to two decimals, negative number will be red with the at least two and up to three decimal digits, zero will be blue and simply a zero with a decimal point, and all text will be magenta.
Four types of entries, all formatted differently
The @ Symbol
What’s with the
On a related not, if your purpose is to format for text (say for coloring or adding information), you can override the number format with a text format, like so:
Sub text_formatter() Range("A1:A4").NumberFormat = "[Magenta] ""[TEXT:]"" @" End Sub
Each entry has [TEXT] appended to it and its color changed
The takeaway here is that our entered text (in Column B) is displayed in place of @, and our forced text
You can even use conditional formatting directly in your VBA NumberFormat format codes by using square brackets, just like for the colors. You would separate the conditions with the semicolon.
Sub conditional_number_formats() Range("A1:A4").NumberFormat = "[>=10][Green]$0.00;[<10][Red]$0.00" End Sub
This NumberFormat example will force anything with a dollar amount greater than or equal to 10 to green and anything less will be red (negative or between 0 and 10).
Decimals are definitely the most common way to displays information, but sometimes the industry standard is to use fractions (in some length measurements, for example). To force fractions, you just need to use the forward slash, like this:
Range("A1:A4").NumberFormat = "[>=10][Green]0 ""dollars and"" 00/100 ""cents"";[<10][Red]0 ""dollars and"" 00/100 ""cents"""
Common way to write check amounts, with a whole dollar amount and a fractional part for cents
Remember to separate the whole number part from the fractional part with at least a space to get mixed fractions. Otherwise you will get a pure fraction. Here’s what I mean. The NumberFormat
The base number can be anything. So if we just wanted to show eighths (as in 5 and 3/8ths inches for a carpentry measurement), we could use this code:
Sub fraction_non_base_ten() Range("A1:A4").NumberFormat = "00 "" and "" 0/8 ""inches""" End Sub
which would give us
Fractions with a denominator of 8 and some textual information included
Adding more 0s will increase the accuracy of the fraction. For 0.25, of course, we will get 2/8 exactly. But something like 1.86, using
00/80 in our code, will yield
VBA NumberFormat Symbols
Before we go, I want to ask you to subscribe using the form below, and then I’m going to leave you with a table you might find helpful when creating your own VBA NumberFormat strings. This table explains each of the common symbols used for formatting numbers and gives a brief example.
|0||Placeholder, forces zeroes if no digits entered, rounds if too many digits entered||
5.339 would display as 05.34
|#||Placeholder, only appears if digits available; otherwise it rounds like 0||
5.339 would display as 5.34
|?||Placeholder, inputs space if no digits entered; otherwise rounds like 0 and #||
5.339 would display as _5.34
(_ represents a space)
|.||Decimal point, acts like expected, except just the decimal point will lead to leading or trailing points||
55 would display as 55.
0.34 would display as .34
Conditional Formatting, can be colors or a test case. Conditions are separated by semicolons
|""||Allows the addition of strings for every cell||
10.32 would display as 10.3 pounds
|,||Thousands separator OR scaler; if added after a format code, it becomes a scaler||
10953 would display as 10.95
156 would display as 0.16
|\||To add literals, like a single character||
10953 would display as 10.95 T
||To denote text, forcing any entries to become text formatted||
1 would display as "1" (as text)
|/||To create fractions; adding more 0s will increase the decimal-to-fraction conversion; can change bases, and remember to separate the whole number part from the fractional part to obtain mixed fractions
0.5 would display as 1/2
11.5 would display as 11 1/2
This has been a really informative tutorial for working with number formats and the VBA NumberFormat property. It certainly isn’t comprehensive, though. Format codes are quite extensive. For your garden-variety macro, these format codes should be sufficient for you. We’ve shown you how to force numbers to look a certain way, including requiring decimal points and leading zeroes. We’ve also demonstrated how to format text, display fractions, and include conditional formatting in your VBA NumberFormat strings..
The next time you open up the “Custom” cell formatting dialog box, scroll through some of the listed items. They might just make a bit more sense to you now!