How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! How can I specify the number of decimal places to be displayed in an Excel spreadsheet?

— JS

Hey, JS. Yes, it is Presidents Day here in the US, a day on which schools, banks, and government offices are all closed. Fortunately, though, the Scripting Guys are open and ready for business.

Why are the Scripting Guys open and ready for business on a national holiday? You know, we never thought about that. Hmmm ….

Interestingly enough, JS, Presidents Day is really supposed to mark the birthday of George Washington, the first US President. Because of that, the holiday used to be celebrated on February 22^nd, which is Washington’s actual birthday. Many years ago, however, the US Congress voted to move the holiday to the third Monday in February, and to rename it Presidents Day. Is that because the US has had lots and lots of outstanding Presidents, each one as deserving of a national holiday as George Washington?

Uh, maybe we should just leave that question unanswered for now. William Howard Taft, who weighed over 300 pounds, once got stuck in the White House bathtub. But we’re not sure if that really merits a national holiday.

Oh, wait: and General Zachary Taylor, old “Rough and Ready” himself, once loaned us a script that lets you specify the number of decimal places to be displayed in an Excel spreadsheet:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

For i = 1 to 10
    objExcel.Cells(i, 1).Value = i/6
Next
Set objRange = objWorksheet.UsedRange
objRange.NumberFormat = “#.0000”

How does this script work? Good question. Although Zachary Taylor died in 1850, Scripting Guy Peter Costantini held a séance for us and managed to contact the late President. Here’s what President Taylor had to say:

Well, sir, I start off by creating an instance of the Excel.Application object (crackin’ good object, Excel.Application) and then set the Visible property to True. That gives us a running instance of Excel that we can view on the screen. I then use these two lines of code to add a new workbook to our instance of Excel, and to bind us to the first worksheet in that workbook:

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

If that don’t beat all, huh? Of course, a spreadsheet without data isn’t much fun now, is it? We need some numbers to work with, so I stuck in this little For Next loop that simply takes the number 1 through 10, divides the number by 6, then puts each quotient in a separate cell in column A:

For i = 1 to 10
    objExcel.Cells(i, 1).Value = i/6
Next

That’s gonna give us a spreadsheet that looks like this:

Now, I know what you’re thinking. You’re thinking, “Tarnation, Zachary, that ain’t no good; that spreadsheet ain’t fit for man nor beast. Can’t we format those cells so they all display 4 decimal places?”

Easy does it now; just hold on to your hats. Yes, we can format those cells so that they all display 4 decimal places; why else would I put in these two lines of code:

Set objRange = objWorksheet.UsedRange
objRange.NumberFormat = “#.0000”

As you can see, in the first line we create an object reference (objRange) to the worksheet’s “used range.” What’s a used range? Well, sir, it’s just what the name says it is: the used range represents the range of cells that have data in them (that is, the cells that have been used). In our sample spreadsheet we have data in cells A1 through A10, so guess what: our used range is going to encompass those very same cells, cells A1 through A10.

And then in the second line of code we specify the number of decimal places for the cells in that range. We want to display four decimal places, so we put 4 zeroes after the decimal point when assigning a value to the NumberFormat property. In turn, that’s gonna give us a spreadsheet that looks like this:

Now that’s the cat’s pajamas.

What’s that? You say you’d prefer to display five decimal places? Boy, there’s just no pleasing some folks, is there? But that’s OK; just put five zeroes after the decimal point, like so:

objRange.NumberFormat = “#.00000”

Want to put a leading zero before the decimal point? Then just change the # sign to a 0, like this:

objRange.NumberFormat = “0.00000”

I tell you, I could sit here and do this all day. Or at least I could, except for the fact that they serve dinner early up here in heaven, and tonight we’re having johnnycakes and cornpone. Good day to you all.

So there you have it, JS: Zachary Taylor’s take on specifying the number of decimal places to display in an Excel spreadsheet. You know, the interesting thing about having a séance with President Taylor was the fact that – what’s that? No, we really did, we … that is, Peter, he – oh, OK, we admit it: we didn’t really have a séance with Zachary Taylor; we made the whole thing up. And no US President gave us this script; we wrote it ourselves. The Scripting Guys apologize for any misconceptions we may have created here. Sorry.

We’re curious, though: how did you know we were making that up? Ah, good point: implying that a US President would make it to heaven was kind of a dead giveaway, wasn’t it? Such a silly mistake.

ScriptingGuy1

Follow