vba loop through cell edges for border

Question

Basically, I want to loop through the edges in the Borders collection. is there any "for each" way to do this?

Private Function getCellBorder(ByVal vArg As Range) As String

  For Each Edge in Borders
    Debug.Print vArg.Borders(Edge).LineStyle
  Next Edge

End Function

Answer 1

Not really. I suppose you could define an Edge array/type/enum, but since we're only talking about a handful of borders, it's not really worth it (oh, and your psuedo code would also include diagonal borders as well). Here's some of my production code:

With Objws.Application.Selection
    .NumberFormat = "#,##0"
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    .HorizontalAlignment = xlHAlignCenter
    .VerticalAlignment = xlVAlignCenter
    .WrapText = True

Keep in mind there is such a thing as "over-optimizing" your code.

Answer 2

Function getCellBorder(ByVal vArg As Range) As String

Dim a

  For Each a In vArg.Borders 
     Debug.Print a.LineStyle
  Next a

End Function  

Edit

The following is a much more sofisticated code that uses some kind of reflection and iterates on Enums.

The Enum tyes are catchy in Excel since you can't loop on those ... except with this trick.

Option Explicit
Option Compare Text

Sub a()
getCellBorder (Worksheets("Sheet1").Range("A1"))
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Module:   TLIReporter modified by Belisarius for SO answers
' Author:   Chip Pearson at Pearson Software Consulting, LLC.
' Date:     10-Nov-2000
' Usage:    Freely distributable, with attribution.
' Desription:   Lists all of the objects in the Excel object model, with
'               properties and methods, and their data types.
' Requirements: Requires TLBINF32.DLL (provided with Visual Studio 6) & available at
'               http://www.nodevice.com/dll/TLBINF32_DLL/item16735.html (as of 20090729)
'  TLBINF32.DLL (TypeLib Information) must be referenced from this project.
'  Help file from MS at
'  http://support.microsoft.com/support/kb/articles/Q224/3/31.ASP
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Original file available at Chip Pearson's website:
'http://www.cpearson.com/Zips/XLConsts2.ZIP
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function getCellBorder(ByVal vArg As Range) As String
Dim TLApp As TLI.TLIApplication         ' The TLI application
Dim TLInfo_XL As TLI.TypeLibInfo        ' The TYPELIB for Excel
Dim TLInfo_MSO As TLI.TypeLibInfo       ' The TYPELIB for Office
Dim ConstInfo As TLI.ConstantInfo
Dim MemInfo As TLI.MemberInfo
Dim a As Range
    Set TLApp = New TLI.TLIApplication
    ' Get the XL and MSO typelibs
    Set TLInfo_XL = TLApp.TypeLibInfoFromFile(ThisWorkbook.VBProject.References("EXCEL").FullPath)
    Set TLInfo_MSO = TLApp.TypeLibInfoFromFile(ThisWorkbook.VBProject.References("OFFICE").FullPath)

        For Each ConstInfo In TLInfo_XL.Constants
            'Debug.Print ConstInfo.Name
            If ConstInfo.Name = "XlBordersIndex" Then
                For Each MemInfo In ConstInfo.Members
                   Debug.Print MemInfo.Value, MemInfo.Name, vArg.Borders.Item(MemInfo.Value).LineStyle
                 Next MemInfo
                 Exit Function
            End If
        Next ConstInfo
End Function

Sample Output:

 Border        Border Enum                 Line Style
 Enum Type     Name                        Enum Number

 12            xlInsideHorizontal          -4142 
 11            xlInsideVertical            -4142 
  5            xlDiagonalDown              -4119 
  6            xlDiagonalUp                -4142 
  9            xlEdgeBottom                    1 
  7            xlEdgeLeft                  -4118 
 10            xlEdgeRight                     4 
  8            xlEdgeTop                   -4115 

HTH!