Excel.Style object: Border problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating and using Excel.Style objects and I'm running into problems
with accessing the Borders of an Excel.Style object.

1. The enumerator to access the Top Border "Excel.xlBorderTop" returns the
Left Border object. It seems that the enumerators when access the Borders
Collection in the style does not match correctly. WHY? and What can I do to
rectify the situation?
2. I was finally able to gain access to the TOP border, however the LINE
CODE and the WEIGHT of the border was incorrect. WHY?

Is there any reason that the Excel.Style object should contain the
formatting information different from the Excel.Range object???? Both contain
the various formatting options like FONT, ALIGNMENT, PATTERN etc. But for
some reason accessing the BORDER information from the Excel.STYLE object
produces erroneous behavior...

Thanks for the help
 
The borders collection is slightly different with Style & CF formats as,
unlike the range object which may have to contend with 'outside' & 'inside'
borders, these are effectively a single cell. Have a go with this -

Sub test2()
Dim sty As Style
Dim bdrs As Border

On Error Resume Next
Set sty = ActiveWorkbook.Styles("TestStyle")
If sty Is Nothing Then
Set sty = ActiveWorkbook.Styles.Add("TestStyle")
End If
On Error GoTo 0

With sty.Borders
'.Item(xlLeft).Weight = xlContinuous
.Item(xlLeft).ColorIndex = 3
.Item(xlTop).LineStyle = xlContinuous
.Item(xlTop).ColorIndex = 4
.Item(xlRight).LineStyle = xlContinuous
.Item(xlRight).ColorIndex = 5
.Item(xlBottom).LineStyle = xlContinuous
.Item(xlBottom).ColorIndex = 6
End With

With Range("C3")
.Style = "TestStyle"
With .Borders
Debug.Print .Item(xlEdgeLeft).ColorIndex ' 3
Debug.Print .Item(xlEdgeTop).ColorIndex '4
Debug.Print .Item(xlEdgeRight).ColorIndex ' 5
Debug.Print .Item(xlEdgeBottom).ColorIndex ' 6
End With
End With

' sty.Delete ' <<
End Sub

Regards,
Peter T
 
I would have assumed that the STYLE object would be directly accessible.

The STYLE object IS directly accessible.
However
my particular project requires me to AVOID touching or altering a cell
Click to expand...
range

If all cells in your Workbook are formatted with your Style, and cells DON'T
include any other formats, you can simply change formats in the 'accessible
Style object as demonstrated in the example I posted.

But if User has added other formats after the Style format was applied,
changing 'your' Style will not necessarily change all the cell formats.
Unless of course you re-apply your Style to cells in which case you might
just as well format the cells instead of bothering with a Style.
Our project is trying to be more efficient in displaying formatted data to
an Excel grid. And our tests have shown that touching an Excel cell costs a
lot.
Click to expand...

I'd be surprised if there's any significant difference in 'cost' between
formatting a grid of cells (even the entire sheet) and changing formats in a
Style Object.

Either I'm not following your objective or your are expecting too much from
the Style object, or a combination of both.

Regards,
Peter T
 
I don't think I'm expecting too much to have the enumerators point to the
correct border.
Click to expand...

You are not using the right constants for the Style borders, see below
And why would the borders collection from RANGE be different
from STYLE?
Click to expand...

I tried to explain last time !
Is there another set of enumerators to use against the STYLE.BORDERS vs. the
RANGE.BORDERS???
Click to expand...

Yes

There are 12 borders in the borders collection which can be set with
constants 1 to 12. Although you can apply the first 4 (L,R,T,B) to a range,
only those in the first cell in the the range object will be applied.
However it is these first 4 that you should apply to a style or CF. You are
trying to set 8 (xlEdgeTop) !

Change your xlEdgeTop value 8, to 3 or xlTop or -4160

Regards,
Peter T


snoriidr said:
Let me simplify... I'm using C# and I'm creating an add in for Excel. Here's
the code that creates a style in Excel.

Excel.Style myStyle = workbook.Styles.Add("myStyle", missing);
Excel.Borders borders = myStyle.Borders;
borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStlye.xlDash;
borders[Excel.XlBordersIndex.xlEdgeTop].Color = 0x00ff00;

I run this code and "myStyle" is added to the list of styles for the workbook.
I choose a cell in the Excel grid. When I apply "myStyle" to the cell the
formatting shows up on the RIGHT border and is a CONTINUOUS line.

So what is wrong with the code above? I'm using the Excel enumerators to
access the Borders collection and to define the line style. Why is the style
that is applied to the cell incorrect??? When I go to the Style dialog box to
see what was defined, the borders show RIGHT and CONTINUOUS. But my code
clearly defines a TOP border with a DASH line. The color is correct, but the
border position and line type is NOT! What's up with that???

I tried the following code which uses the 2nd parameter "basedOn":
Excel.Range cell = workbook.ActiveSheet.Cells(1,1);
cell.ClearFormats();
cell.Borders[Excel.Excel.XlBordersIndex.xlEdgeTop].LineStyle =
Excel.XlLineStyle.xlDash;
cell.Borders[Excel.XlBordersIndex.xlEdgeTop].Color = 0x00ff00;
Excel.Style myStyle2 = workbook.Styles.Add("myStyle2", cell);

I then apply the style created to a different cell and I get the correct
borders in this case.

I don't think I'm expecting too much to have the enumerators point to the
correct border. And why would the borders collection from RANGE be different
from STYLE? I do understand and have seen that RANGE can be of multiple
cells and would also include the XlInsideHorizontal and XlInsideVertical
borders, whereas the borders in STYLE do not have those borders defined. Is
there another set of enumerators to use against the STYLE.BORDERS vs. the
RANGE.BORDERS???
Click to expand...
 
After working on a different issue for a bit, I finally stumbled across some
borders information that was a bit more clear in what was being explained.
This may have been overlooked because I was developing in C#.

I have to say that there is no documentation in msdn that declares that the
Borders collection is different in Range vs. Style. In the documentation it
simply states that you would access the borders via the Excel.XlBorderIndex.
However as was found in a different article:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=347692&SiteID=1. There
are a different set of values that should be accessing the Borders collection
for a more consistent outcome. This would never have occurred to me. But in
reviewing the code snippets in this thread, there are 2 different sets of
constants. However it was not clear since in C# Excel.XlBorderIndex is the
expected parameter to gain access to the borders collection.

The document explicitly uses another set of constants: Excel.Constants
Aside from this article is there any additional documentation as to when to
use these vs. the Excel.XlBordeIndex??? Geez.. I'm just lucky I happened upon
the little article.

Well in a nutshell, they cast the Top, Bottom, Left and Right enumerations
that come from Excel.Constants to be Excel.XlBorderIndex enumerations.

So here's some code that might help future inquries in this area:

Excel.XlBorderIndex topIndex = (Excel.XlBorderIndex)Excel.Constants.xlTop;
Excel.XlBorderIndex bottomIndex =
(Excel.XlBorderIndex)Excel.Constants.xlBottom;
Excel.XlBorderIndex leftIndex = (Excel.XlBorderIndex)Excel.Constants.xlLeft;
Excel.XlBorderIndex rightIndes = (Excel.XlBorderIndex)Excel.Constants.xlRight;

Hope this helps others who might come across this problem.

This is a WORKAROUND, but why is there such a lack in documentation
regarding that difference?? Go figure. I'm just glad I found a solution.
 
have you tried simply (from you earlier code) -
Excel.Borders borders = myStyle.Borders;
borders[Excel.XlBordersIndex.3].Color = 0x00ff00;
or
borders[Excel.XlBordersIndex.xlTop].Color = 0x00ff00;

not sure if your object name 'borders' is causing any confusion

Regards,
Peter T
 
Thanks for clarifying this. I posted on another group this exact problem.
The weird thing for me is that the Range.Border values, xlEdgeLeft ... ,
worked for me with Styles until Excel 2007.
--
EB


Peter T said:
have you tried simply (from you earlier code) -
Excel.Borders borders = myStyle.Borders;
borders[Excel.XlBordersIndex.3].Color = 0x00ff00;
or
borders[Excel.XlBordersIndex.xlTop].Color = 0x00ff00;

not sure if your object name 'borders' is causing any confusion

Regards,
Peter T

snoriidr said:
After working on a different issue for a bit, I finally stumbled across some
borders information that was a bit more clear in what was being explained.
This may have been overlooked because I was developing in C#.

I have to say that there is no documentation in msdn that declares that the
Borders collection is different in Range vs. Style. In the documentation it
simply states that you would access the borders via the Excel.XlBorderIndex.
However as was found in a different article:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=347692&SiteID=1. There
are a different set of values that should be accessing the Borders collection
for a more consistent outcome. This would never have occurred to me. But in
reviewing the code snippets in this thread, there are 2 different sets of
constants. However it was not clear since in C# Excel.XlBorderIndex is the
expected parameter to gain access to the borders collection.

The document explicitly uses another set of constants: Excel.Constants
Aside from this article is there any additional documentation as to when to
use these vs. the Excel.XlBordeIndex??? Geez.. I'm just lucky I happened upon
the little article.

Well in a nutshell, they cast the Top, Bottom, Left and Right enumerations
that come from Excel.Constants to be Excel.XlBorderIndex enumerations.

So here's some code that might help future inquries in this area:

Excel.XlBorderIndex topIndex = (Excel.XlBorderIndex)Excel.Constants.xlTop;
Excel.XlBorderIndex bottomIndex =
(Excel.XlBorderIndex)Excel.Constants.xlBottom;
Excel.XlBorderIndex leftIndex = (Excel.XlBorderIndex)Excel.Constants.xlLeft;
Excel.XlBorderIndex rightIndes = (Excel.XlBorderIndex)Excel.Constants.xlRight;

Hope this helps others who might come across this problem.

This is a WORKAROUND, but why is there such a lack in documentation
regarding that difference?? Go figure. I'm just glad I found a solution.
Click to expand...
Click to expand...
 
Back
Top