Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    xcel cells (Border Count) (Excel97/SR2)

    I'm developing a set of "pickers", whereby the developer can harvest cell-settings.

    I've come across something I don't understand in cell Borders, and wonder if anyone is wise in this matter.

    The attached BAS module can be used to run through the borders formatting of a cell.(Drag the test macro "TESTbdGetBorders" out of the last procedure, decomment and step through it).

    Initial inspection of Borders.Count returned 6, so I DIMensioned to 5 (0, 1, 2, 3, 4, 5) to receive details of the 6 borders. Then used Debug.print to observe the values given by the Help screens for the Border types and found that THEY range from 5 through 12. xlInsideHorizontal(12), xlInsideVertical(11), xlDiagonalDown(5), xlDiagonalUp(6), xlEdgeBottom(9), xlEdgeLeft(7), xlEdgeRight(10) or xlEdgeTop(8). That looks like eight borders to me, and would correspond to the eight small line types at the left hand side and bottom of the border style box.

    It's not a big deal right now; I'm just running through these aspects for completeness; It's unlikely that the end-user will be affected by this.

    I'm curious. Ayone else out there pondered this?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: xcel cells (Border Count) (Excel97/SR2)

    No. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Since you have put the issue kind of rhetorically, is it new information if I explain that xlInsideHorizontal and xlInsideVertical are only used in multicell bordering; if you use them and then examine only one of the cells, you'll see that the individual cells show as one of xlEdgeLeft, xlEdgeTop, xlEdgeBottom, and xlEdgeRight. Or is this useless information?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xcel cells (Border Count) (Excel97/SR2)

    > is this useless information

    No information is useless, although since information comes from processed data, there may well be something useless about the process (grin!).

    Thanks John. As an end-user I knew about the "Inside"s being in effect only in multi-cell animals.

    I'm still puzzled as to why the .Borders.Count registers 6.

    As I began typing I thought "Maybe he's right, maybe I had only one cell selected when I began coding". One cell would give me L, R, T, B and 2 diagonals yielding 6.

    So I selected a rectangular block of cells and ran it again. Border.Count still = 6.

    I expect to see "8".

    On the other hand, it is the end of the week, so maybe the truth will dawn on me while I sleep.

    <pre>Sub test()
    With Selection
    MsgBox .Borders.Count
    End With
    End Sub
    </pre>


    The above test returns "6" no matter what I select. (Excel97/SR2)
    Even when I select 4 sheets!

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: xcel cells (Border Count) (Excel97/SR2)

    The Borders collection that you are counting does not include the diagonals. Bizzare, but useful:
    Selection.Borders.LineStyle = xlDouble
    the above does not create any diagonals. You must create diagonals individually:
    Selection.Borders(xlDiagonalDown).LineStyle = xlDouble

    Don't you wonder how they every coded that? --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: xcel cells (Border Count) (Excel97/SR2)

    Gentlemen
    Perhaps I'm missing something, but I believe the count of six borders are the four outer borders and two diagonals. I recommend running the following code with a break set on the highlighted line. When the procedure stops, view the Locals window. By changing the line weight and configuration of the border segments, then running the code and checking the Locals window; all should become clear.
    If I have missed the boat; I welcome your advice.

    <hr>Sub Macro1()
    '
    '
    Dim myBorders As Borders

    Set myBorders = Range("A1", "C9").Borders

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold"> Set myBorders = Nothing</span hi>

    End Sub<hr>
    Regards
    Don

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: xcel cells (Border Count) (Excel97/SR2)

    Well, you missed the boat just slightly: you forgot about the inside borders that John mentioned. There are eight so-called "borders": xlEdgeLeft, xlEdgeRight, xlEdgeBottom, xlEdgeTop, xlInsideHorizontal, xlInsideVertical, xlDiagonalDown, and xlDiagonalUp. But the Borders collection object does not include the diagonal borders. Thus, if you have the following code:
    <pre>Sub Macro1()
    With Selection
    .Borders(xlDiagonalDown).LineStyle = xlDouble
    .Borders(xlDiagonalUp).LineStyle = xlDouble
    .Borders(xlEdgeLeft).LineStyle = xlDouble
    .Borders(xlEdgeTop).LineStyle = xlDouble
    .Borders(xlEdgeBottom).LineStyle = xlDouble
    .Borders(xlEdgeRight).LineStyle = xlDouble
    .Borders(xlInsideVertical).LineStyle = xlDouble
    .Borders(xlInsideHorizontal).LineStyle = xlDouble
    End With
    End Sub

    Sub Macro2()
    With Selection
    .Borders.LineStyle = xlDouble
    End With
    End Sub

    Sub Macro3()
    With Selection
    .Borders.LineStyle = xlNone
    End With
    End Sub</pre>

    then, Macro1 draws all 8 borders, Macro2 draws the 6 vertical & horizontal borders, and if you run Macro3 after running Macro1, then only the diagonal borders are left. So, the Borders(value) function can be referenced with 8 different values, but the Borders collection object only contains 6 of those borders. Very bizzare!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: xcel cells (Border Count) (Excel97/SR2)

    Oh no, you are absolutely correct! But I am correct about the Borders object not affecting the diagonals. As you said, by looking at the Local window
    Borders.Item1 is xlEdgeLeft
    Borders.Item2 is xlEdgeRight
    Borders.Item3 is xlEdgeTop
    Borders.Item4 is xlEdgeBottom
    Borders.Item5 is xlDiagonalDown
    Borders.Item6 is xlDiagonalUp

    This is too bizzare for me!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: xcel cells (Border Count) (Excel97/SR2)

    Might it be that, as any individual cell can only have six borders, that xlInsideVertical and xlInsideHorizontal are simply a mechanism to ensure that adjacent cells share a single border style. Even in a multi cell range selection, any given cell can only have 6 borders.

    Andrew

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xcel cells (Border Count) (Excel97/SR2)

    > Don't you wonder how they every coded that?

    I'm still wondering how they coded Word!

    I'm going to resurrect my code after a too-long hiatus and explore this more fully, after following throuigh the suggestions in the later posts.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •