{"id":65483,"date":"2007-02-20T00:57:00","date_gmt":"2007-02-20T00:57:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/02\/20\/how-can-i-specify-the-number-of-decimal-places-to-display-in-an-excel-spreadsheet\/"},"modified":"2007-02-20T00:57:00","modified_gmt":"2007-02-20T00:57:00","slug":"how-can-i-specify-the-number-of-decimal-places-to-display-in-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-specify-the-number-of-decimal-places-to-display-in-an-excel-spreadsheet\/","title":{"rendered":"How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, <\/H2>\n

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

— JS<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, JS. Yes, it is<\/I> 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.<\/P>\n

Why<\/I> are the Scripting Guys open and ready for business on a national holiday? You know, we never thought about that. Hmmm \u2026.<\/P>\n

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 22nd<\/SUP>, which is Washington\u2019s 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? <\/P>\n

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\u2019re not sure if that really merits a national holiday. <\/P>\n

Oh, wait: and General Zachary Taylor, old \u201cRough and Ready\u201d himself, once loaned us a script that lets you specify the number of decimal places to be displayed in an Excel spreadsheet:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
For i = 1 to 10\n    objExcel.Cells(i, 1).Value = i\/6\nNext<\/p>\n
Set objRange = objWorksheet.UsedRange\nobjRange.NumberFormat = “#.0000”\n<\/PRE>\n
How does this script work? Good question. Although Zachary Taylor died in 1850, Scripting Guy Peter Costantini held a s\u00e9ance for us and managed to contact the late President. Here\u2019s what President Taylor had to say:<\/P>\n
Well, sir, I start off by creating an instance of the Excel.Application<\/B> object (crackin\u2019 good object, Excel.Application) and then set the Visible<\/B> 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:<\/P>
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
If that don\u2019t beat all, huh? Of course, a spreadsheet without data isn\u2019t 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:<\/P>
For i = 1 to 10\n    objExcel.Cells(i, 1).Value = i\/6\nNext\n<\/PRE>\n
That\u2019s gonna give us a spreadsheet that looks like this:<\/P>\"Microsoft \n

Now, I know what you\u2019re thinking. You\u2019re thinking, \u201cTarnation, Zachary, that ain\u2019t no good; that spreadsheet ain\u2019t fit for man nor beast. Can\u2019t we format those cells so they all display 4 decimal places?\u201d<\/P>\n
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:<\/P>
Set objRange = objWorksheet.UsedRange\nobjRange.NumberFormat = “#.0000”\n<\/PRE>\n
As you can see, in the first line we create an object reference (objRange) to the worksheet\u2019s \u201cused range.\u201d What\u2019s a used range? Well, sir, it\u2019s 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.<\/P>\n
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<\/B> property. In turn, that\u2019s gonna give us a spreadsheet that looks like this:<\/P>\"Microsoft \n

Now that\u2019s<\/I> the cat\u2019s pajamas.<\/P>\n
What\u2019s that? You say you\u2019d prefer to display five <\/I>decimal places? Boy, there\u2019s just no pleasing some folks, is there? But that\u2019s OK; just put five zeroes after the decimal point, like so:<\/P>
objRange.NumberFormat = “#.00000”\n<\/PRE>\n
Want to put a leading zero before the decimal point? Then just change the # sign to a 0, like this:<\/P>
objRange.NumberFormat = “0.00000”\n<\/PRE>\n
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\u2019re having johnnycakes and cornpone. Good day to you all.<\/P>\n
So there you have it, JS: Zachary Taylor\u2019s take on specifying the number of decimal places to display in an Excel spreadsheet. You know, the interesting thing about having a s\u00e9ance with President Taylor was the fact that \u2013 what\u2019s that? No, we really did, we \u2026 that is, Peter, he \u2013 oh, OK, we admit it: we didn\u2019t really have a s\u00e9ance 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.<\/P>\n
We\u2019re curious, though: how did you know<\/I> we were making that up? Ah, good point: implying that a US President would make it to heaven was<\/I> kind of a dead giveaway, wasn\u2019t it? Such a silly mistake.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note to US Presidents<\/B>. Come on, guys; we\u2019re just kidding around. Listen, would it help if we gave you a Dr. Scripto bobblehead doll<\/B><\/A>? If so, well, there\u2019s still plenty of time to enter the 2007 Winter Scripting Games<\/B><\/A>. Remember, all you have to do is enter a single event and you\u2019ll have a chance to win one of 250 Dr. Scripto bobbleheads or a copy of the book Windows PowerShell in Action<\/B><\/A>. Those are pretty good prizes, even for a US President.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n\n<\/THEAD>\n\n\n
\n
Editor\u2019s Note<\/B>. After a week of late nights scoring events for the 2007 Scripting Games<\/B><\/A>, the lack of sleep has apparently made the Scripting Guy who writes this column even more disagreeable than usual. We\u2019ll throw a few donuts into his office to try to keep him under control for the rest of the week, but we apologize in advance to everyone he manages to insult this week.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,48,49,3,5],"class_list":["post-65483","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65483","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=65483"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65483\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=65483"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=65483"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=65483"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}