borderAround optional parameters

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS
I looked at the documentation for Range().BorderAround and it is unclear what it does if you leave out the optional parameters.

The reason for this post is informational.

Excel 2011 (mac) behaves differently from Excel 2010 (Windows). The mac alters the linestyle, weight and color and sets them to the default xlcontinuous, xlthin and black. Excel 2010 leaves them with the current values.

I was writing something to make a simple wall calendar. I set the color and weight on the borders collection for the entire area I was going to use. The I selected smaller ranges and said borderaround. This worked fine on the PC but on the mac all the color and weight reset.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Does this come as a surprise :-) (I mean in general, not necessarily about BorderAround)

Not really. I run 99% of my macros on PC only. I was writing this to stop somebody who was doing it by hand every month and she only has the mac version. I even updated my mac version to the latest and it still did the same thing.
 
Upvote 0
The way Range.BorderAround is supposed to work is here:
Range.BorderAround Method (Excel)


When I tried leaving off the optional parameters the border was not removed in Excel 2011 on the Mac or in Excel 2013 on Windows. I did not test in 2010. To clear the formats, you could reset your parameters using the third example in this code.

Code:
[COLOR=#232323][FONT=Verdana]
Sub Colorit()[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]Worksheets("Sheet1").Range("A1:D4").BorderAround _[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana] ColorIndex:=3, Weight:=xlThick[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]End Sub
[/FONT][/COLOR]

[COLOR=#232323][FONT=Verdana]Sub ReturnToDefault()[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]' Doesn't work in Excel 2011 Mac or Excel 2013 Windows[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]Worksheets("Sheet1").Range("A1:D4").BorderAround[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]End Sub
[/FONT][/COLOR]

[COLOR=#232323][FONT=Verdana]Sub RemoveFormatting()[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]    Selection.Borders(xlDiagonalDown).LineStyle = xlNone[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]    Selection.Borders(xlDiagonalUp).LineStyle = xlNone[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]    Selection.Borders(xlEdgeLeft).LineStyle = xlNone[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]    Selection.Borders(xlEdgeTop).LineStyle = xlNone[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]    Selection.Borders(xlEdgeBottom).LineStyle = xlNone[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]    Selection.Borders(xlEdgeRight).LineStyle = xlNone[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]    Selection.Borders(xlInsideVertical).LineStyle = xlNone[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]    Range("E8").Select[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]End Sub

[/FONT][/COLOR]


 
Last edited:
Upvote 0
The way Range.BorderAround is supposed to work is here:
Range.BorderAround Method (Excel)


Yes I did read the help. Here is some sample code. The behavior between Window Excel 2010 and Mac Excel 2011 is different.

Code:
Sub t()
Dim highlightColor As Long


highlightColor = RGB(58, 185, 70)


With Range(Cells(2, 6), Cells(5, 10))
    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Borders.Color = highlightColor
    .Borders.LineStyle = xlContinuous
    .Borders.Weight = xlThick
    .BorderAround
End With


With Range(Cells(8, 6), Cells(11, 10))
    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .BorderAround
    .Borders.Color = highlightColor
    .Borders.LineStyle = xlContinuous
    .Borders.Weight = xlThick
End With


End Sub


On Excel 2010, this makes 2 identical green boxes.

On Excel 2011, the top box is black with thin lines and the bottom box is green with thick lines. The only difference is when BorderAround is called.
 
Upvote 0
I confirm the behavior for XL 2011 (Mac). The Mac behavior is the correct behavior according to the documentation. When BorderAround is not specified, the default color is used. The default color is AutoMatic, which is black on my Mac.

In XL 2013 both boxes are green. It appears that the code is using BorderAround by default on the PC - even when BorderAround is not specified, which it is not supposed to do.
 
Upvote 0
I assumed since I set the values for the borders.color and borders.weight before calling bordersAround that it would use what was set not change them to some default.
 
Upvote 0
Ah - I see. BorderAround is in both the upper and lower sections of code. I noticed it only in the lower section.

So should it make a difference when BorderAround is applied? Code should be execute top to bottom, so it seems the Mac is paying attention and Windows isn't.
 
Upvote 0
So should it make a difference when BorderAround is applied? Code should be execute top to bottom, so it seems the Mac is paying attention and Windows isn't.

Hence, my original comment that they behave differently.

On the mac, If you set the border thickness and color on a range and THEN call borderAround with no parameter, it resets them to the default thin and black. If you call border around first and then set the color and thickness it does what you would expect. This says to me that the mac is ALTERING the state of the object if the optional parameters are nil. I know the documentation says it will use the default for them if they are not specified. But if they are set before the call, shouldn't those be the default values for that range?

What I expected was the behavior of the PC where it does not matter if you set the color and thickness before or after the call to borderAround. Since I didn't tell borderAround a color or a thickness I expected it to leave them alone.

This gets really messy when you are trying to draw something like a calendar where you want 7 adjacent squares (an example I know there are other ways to do this if you know exactly how many are being drawn)

Before this I was not aware that there were parameters for BorderAround. I had always set the color and thickness and then made the call and it had done what I expected. This was the first time I had given somebody with MAC only one of my macros to run. I have both but do 95% on the Win Excel 2010.
 
Upvote 0
Hence, my original comment that they behave differently.

On the mac, If you set the border thickness and color on a range and THEN call borderAround with no parameter, it resets them to the default thin and black. If you call border around first and then set the color and thickness it does what you would expect. This says to me that the mac is ALTERING the state of the object if the optional parameters are nil. I know the documentation says it will use the default for them if they are not specified. But if they are set before the call, shouldn't those be the default values for that range?

I don’t think so. Since there is a defined expected behavior for BorderAround, that behavior should be what happens at the moment BorderAround is encountered. If you have already specified other parameters, but apply BorderAround with new parameters after other parameters were executed, you would want BorderAround to override the past and do what you say at that moment, right? So BorderAround without parameters should reset anything you’ve already done to default (AutoMatic).

What I expected was the behavior of the PC where it does not matter if you set the color and thickness before or after the call to borderAround. Since I didn't tell borderAround a color or a thickness I expected it to leave them alone.

But that’s not what BorderAround is supposed to do. If you use BorderAround without parameters, it should set those parameters to Excel’s default, AutoMatic.

This gets really messy when you are trying to draw something like a calendar where you want 7 adjacent squares (an example I know there are other ways to do this if you know exactly how many are being drawn)

Before this I was not aware that there were parameters for BorderAround. I had always set the color and thickness and then made the call and it had done what I expected. This was the first time I had given somebody with MAC only one of my macros to run. I have both but do 95% on the Win Excel 2010.

Still, now that you are aware of the differences, simply use conditional formatting to accommodate the slightly different behavior. Whether one side has it “right” and the other side has it “wrong” doesn’t really matter. As a programmer, you just program for the difference and get on with your life. Because of possible complications with existing code, I doubt Microsoft would change either platform’s behavior even if we reported this as a bug on the Windows side.
 
Upvote 0

Forum statistics

Threads
1,226,504
Messages
6,191,425
Members
453,657
Latest member
DukeJester

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top