Using the BorderAround Method in Excel

I was messing around with the borders function in Excel and I noticed that there is also a BorderAround function.

1. Here is the Borders Function:

$xl = new-object -comobject Excel.Application
# make Excel visible
$xl.visible = $true
$xl.DisplayAlerts = $False
# open a workbook
$wb = $xl.workbooks.add()
# Get sheet1
$ws1 = $wb.worksheets | where {$_.name -eq "sheet1"}

$xlAutomatic=-4105
$xlBottom = -4107
$xlCenter = -4108
$xlContext = -5002
$xlContinuous=1
$xlDiagonalDown=5
$xlDiagonalUp=6
$xlEdgeBottom=9
$xlEdgeLeft=7
$xlEdgeRight=10
$xlEdgeTop=8
$xlInsideHorizontal=12
$xlInsideVertical=11
$xlNone=-4142
$xlThin=2
$xlMedium = -4138
$xlThick = 4

$selection = $ws1.usedRange
[void]$selection.select()
$selection.Borders.Item($xlEdgeLeft).LineStyle = $xlContinuous
$selection.Borders.Item($xlEdgeLeft).ColorIndex = $xlAutomatic
$selection.Borders.Item($xlEdgeLeft).Color = 1
$selection.Borders.Item($xlEdgeLeft).Weight = $xlMedium
$selection.Borders.Item($xlEdgeTop).LineStyle = $xlContinuous
$selection.Borders.Item($xlEdgeBottom).LineStyle = $xlContinuous
$selection.Borders.Item($xlEdgeRight).LineStyle = $xlContinuous
$selection.Borders.Item($xlInsideVertical).LineStyle = $xlContinuous
$selection.Borders.Item($xlInsideHorizontal).LineStyle = $xlContinuous

This puts borders around all the cells in the used range.

I tried using the BorderAround Function like this:

$selection = $ws1.usedRange
[void]$selection.select()

$selection.BorderAround

And I got this:

MemberType          : Method
OverloadDefinitions : {Variant BorderAround (Variant, XlBorderWeight, XlColorIndex, Variant)}
TypeNameOfValue     : System.Management.Automation.PSMethod
Value               : Variant BorderAround (Variant, XlBorderWeight, XlColorIndex, Variant)
Name                : BorderAround
IsInstance          : True

So I plugged in some numbers here and there and wound up with this:

$selection.BorderAround(1,4,3); # 1 = Continuous 4 = thick and 3 = Red

Which puts a thick Red border around the whole selection.

So, my question was, What does this mean:
Specifies the line style for the border.

Name Value Description
xlContinuous 1

Continuous line.

xlDash -4115

Dashed line.

xlDashDot 4

Alternating dashes and dots.

xlDashDotDot 5

Dash followed by two dots.

xlDot -4118

Dotted line.

xlDouble -4119

Double line.

xlLineStyleNone -4142

No line.

xlSlantDashDot 13

Slanted dashes.

OverloadDefinitions : {Variant BorderAround (Variant, XlBorderWeight, XlColorIndex,Variant)}

And I was directed to MSDN (http://msdn.microsoft.com/en-us/library/bb209714(office.12).aspx)

Name Required/Optional Data Type Description
LineStyle Optional Variant One of the constants of XlLineStyle
specifying the line style for the border.
Weight Optional XlBorderWeight The border weight.
ColorIndex Optional XlColorIndex The border color, as an index into the current color
palette or as a XlColorIndex constant.
Color Optional Variant The border color, as an RGB value.

BTW, as far as I know Powershell does not do RGB, so the last variant is not used.

Advertisements