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

"snoriidr" wrote in message
...
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

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
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.

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


"snoriidr" wrote in message
...
Thanks for your response.

I had already determined that by applying the style to a range, the range
would have the correct values and access to the borders collection.
However
my particular project requires me to AVOID touching or altering a cell
range.


I would have assumed that the STYLE object would be directly accessible.
But it seems that the enumerations which access the borders collection or
incorrect when referring to the STYLE.BORDERS collection vs. the
RANGE.BORDERS collection. That seems rather odd to me. Is this an Excel
API
limitation???

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. If we have to touch a cell range just to get the correct formatting
that is defined in a STYLE object, what's the point of having the
Excel.Style
object at all???

Thanks for your help... I really do appreciate it. I just think it's so
darn frustrating to use Microsoft API calls that basically don't work.



"Peter T" wrote:

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

"snoriidr" wrote in message
...
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

I don't think I'm expecting too much to have the enumerators point to the
correct border.

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?

I tried to explain last time !

Is there another set of enumerators to use against the STYLE.BORDERS vs.
the
RANGE.BORDERS???

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" wrote in message
...
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???

Although you can apply the first 4 (L,R,T,B) to a range,
only those in the first cell in the range object will be applied.

Ignore that bit

Peter T

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/Sho...47692&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

"snoriidr" wrote in message
...
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/Sho...47692&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.

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" wrote:

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" wrote in message
...
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/Sho...47692&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.