How Can I Center Text in an Excel Cell?

ScriptingGuy1

Hey, Scripting Guy! How can I center text in an Excel cell?

— MV

Hey, MV. You know, every now and then we get asked a question and we think to ourselves, “Wow, how many times do you suppose we’ve answered this question?” And, of course, when we go to double-check we find out that we’ve never answered that question; instead, it just seems like we should have answered the question somewhere along the line. We just didn’t remember.

Before you ask, this has nothing to do with the Scripting Guys getting old; in fact, thanks to hard work and clean living none of the Scripting Guys are getting old. And those are not grey hairs; they’ve just been bleached out by the sun.

At any rate, it turns out that we’ve never told people how to center text in an Excel cell. Well, until now, that is:

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

Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1, 1) = “A”
objWorksheet.Cells(1, 2) = “B”
objWorksheet.Cells(1, 3) = “C”
objWorksheet.Cells(1, 2).HorizontalAlignment = -4108

At the risk of spoiling the suspense, we’ll tell you that the only line of code you really need to worry about is the last one; the rest of the script simply creates an Excel spreadsheet and adds some data to it. To do that, the script first creates an instance of the Excel.Application object and then sets the Visible property to True; that gives us a running instance of Excel that’s visible on screen. We use the Add method to add a new workbook to our instance of Excel, then use this line of code to bind to the first worksheet in that workbook:

Set objWorksheet = objWorkbook.Worksheets(1)

Of course, a script that centers text in a cell isn’t all that impressive unless you actually have some text in that cell. Therefore, we use these three lines of code to put the letters A, B, and C into cells A1, B1, and C1, respectively:

objWorksheet.Cells(1, 1) = “A”
objWorksheet.Cells(1, 2) = “B”
objWorksheet.Cells(1, 3) = “C”

In case you’re wondering, that gives us a spreadsheet that looks like this:

Now how do we center the text in one of the cells? Well, this line of code centers the text for cell B1 (that is, cell row 1, column 2):

objWorksheet.Cells(1, 2).HorizontalAlignment = -4108

As you can see, all we have to do is set the value of the HorizontalAlignment property of the cell to -4108; that will center the text in the cell. If we wanted the cell to be right-aligned, we’d set HorizontalValue to -4152; set the value to -4131 to left-align the text.

Run the script, and the finished spreadsheet will look like this, with the text in cell B1 centered:

Another day, another question answered. Speaking of which, how many times have we answered a question about centering text in Excel?

Oh, right: once. The sun appears to be bleaching out our brains as well as our hair ….

ScriptingGuy1

Follow