Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Putting Borders Around Spreadsheet Cells

One of the trends these days is to be an entity without borders: Doctors Without Borders, Teachers Without Borders, Engineers Without Borders, etc. Heck, even one of the local shopping centers in the Seattle area is now without Borders. Because the Borders bookstore there closed. Ha. Get it? Without Borders because the Borders bookstore…. Never mind.

We agree: that is a good example of why we’re the Scripting Guys and not the Standup Comedy Guys.

There is, however, one place where borders are still very much in demand: Excel spreadsheets. People want to put borders around the cells in their spreadsheets, and for good reason: borders make it easier for the eye to keep track of which cells go where, especially when the spreadsheet is printed. Because borders are so useful to spreadsheet makers, we decided that, rather than puzzle you with one-liners (“I don’t get it; was that supposed to be funny?”) maybe we should do what we do best and show you a script that will place a thick red border around all the cells in your spreadsheet. (And don’t worry, you aren’t limited to thick red borders; we’ll talk about border variations in a minute or two.)

Here’s our initial put-borders-around-my-spreadsheet script:

Const xlContinuous = 1
Const xlThick = 4

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
k = 1
For i = 1 to 5
    For j = 1 to 5
        objWorksheet.Cells(i,j) = k
        k = k + 1
    Next
Next

objWorksheet.UsedRange.Borders.LineStyle = xlContinuous 
objWorksheet.UsedRange.Borders.Color = RGB(255, 0, 0)
objWorksheet.UsedRange.Borders.Weight = xlThick

Let’s talk about how this baby works and how we might modify it. We begin by defining a pair of constants – xlContinuous and xlThick – that we’ll use later on to configure the line style and the line weight (thickness). Are there other line styles we could use beside continuous? You bet there are:

Not too surprisingly, there are also other line weights we can use besides thick:

If you want to specify a different line style and/or thickness, then just define (and then later use) the appropriate constants. For example, to use a double line of medium thickness we’d make these the first two lines in the script:

Const xlDouble = -4119
Const xlMedium = -4138

Yes, very straightforward.

Next we create an instance of the Excel.Application object and set the Visible property to True; we set Visible to True just so we can see our instance of Excel on screen. We then use these two lines of code to add a new Workbook and to bind to the first worksheet in that workbook:

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

All that simply gives us a blank worksheet to work on. With that in mind, the next block of code is nothing more than a couple of For Each loops that type some data into the spreadsheet; we do that just so we have some cells with values in them. (And thus something worth putting a border around.)

Now, at long last, we get to the code that actually puts borders around each cell in the UsedRange (in Excel, the UsedRange represents the first cell with a value, the last cell with a value, and all the cells in between):

objWorksheet.UsedRange.Borders.LineStyle = xlContinuous 
objWorksheet.UsedRange.Borders.Color = RGB(255, 0, 0)
objWorksheet.UsedRange.Borders.Weight = xlThick

All we’re doing here is binding to the UsedRange (borders are configured using Range objects) and assigning values to the LineStyle, Color, and Weight properties. For LineStyle and Weight we use the constants defined at the beginning of the script. For the Color property we use an RGB value of (255, 0, 0), which happens to be the color red; you can find out more about RGB values here.

That’s it. When we run the script we get a nifty little spreadsheet that looks like this:

Note. One of the Scripting Guys is going to Paris later this summer. After seeing this spreadsheet, though, he realizes that there’s no point in going to the Louvre; everything there would be a disappointment after this.

Admittedly, most Internet columns devoted to Microsoft Office scripting would probably end right here. But at Scripting Guys, Inc. we like to go the extra mile for you (plus we get paid by the number of words we type). If you want to format your cells as a grid (that is, each cell surrounded by a box) then the script we just showed you might be all you’ll ever need. But what if you wanted to do something different? For example, what if you wanted a spreadsheet that looked like this:

That’s pretty easy to do within Excel itself; after all, you can individually specify the different borders for a cell or range of cell. And guess what: you can do the same thing programmatically. If you don’t specify a border within your script you will get the grid effect by default; that’s what we did with our first script. However, you can alternatively use one of the constants and values shown below to format a specific border:

For example, suppose we want to limit our formatting to the inside vertical border; to that we simply need to define and use the constant xlInsideVertical (with the value 11). When we get to the point in the script where we actually put borders around our cells we specify xlInsideVertical as an inline parameter:

objWorksheet.UsedRange.Borders(xlInsideVertical).LineStyle = xlContinuous 
objWorksheet.UsedRange.Borders(xlInsideVertical).Color = RGB(255, 0, 0)
objWorksheet.UsedRange.Borders(xlInsideVertical).Weight = xlThick

In other words, we stick (xlInsideVertical) between Borders and the trailing period. That’s all you have to do to format a particular border. Want to format 2 or 3 borders? Then just repeat the code 2 or 3 times, once for each border you need to format.

Here’s a complete script that formats just the inside vertical border for our range of cells:

Const xlContinuous = 1
Const xlThick = 4
Const xlInsideVertical = 11

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
k = 1
For i = 1 to 5
    For j = 1 to 5
        objWorksheet.Cells(i,j) = k
        k = k + 1
    Next
Next

objWorksheet.UsedRange.Borders(xlInsideVertical).LineStyle = xlContinuous 
objWorksheet.UsedRange.Borders(xlInsideVertical).Color = RGB(255, 0, 0)
objWorksheet.UsedRange.Borders(xlInsideVertical).Weight = xlThick

Next question, please. Ah, yes, what if you want to format the border for a subset of cells, or even for just a single cell? No problem; remember a range can consist of anything from a single cell to every cell on the spreadsheet. All you have to do is specify the cells that you want formatted; for example, this code will place a border only around cell B3:

objWorksheet.Range("B3").Borders.LineStyle = xlContinuous 
objWorksheet.Range("B3").Borders.Color = RGB(255, 0, 0)
objWorksheet.Range("B3").Borders.Weight = xlThick

Pretty easy, huh? Here’s a complete script that puts a border around only cell B3:

Const xlContinuous = 1
Const xlThick = 4

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
k = 1
For i = 1 to 5
    For j = 1 to 5
        objWorksheet.Cells(i,j) = k
        k = k + 1
    Next
Next

objWorksheet.Range("B3").Borders.LineStyle = xlContinuous 
objWorksheet.Range("B3").Borders.Color = RGB(255, 0, 0)
objWorksheet.Range("B3").Borders.Weight = xlThick

And here’s what you get when you run the script:

That’s something you’ll probably never see in the Louvre. Yet another reason to read the Office Space column rather than visit Paris!