代码之家 › 专栏 › 技术社区 › jason m

vba循环通过单元格边界

excel-vba vba excel

jason m · 技术社区 · 14 年前

基本上,我想循环遍历borders集合中的边。有没有什么“为每一个”的方法来做到这一点?

Private Function getCellBorder(ByVal vArg As Range) As String

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

End Function

2 回复 | 直到 14 年前

Dr. belisarius 14 年前

Function getCellBorder(ByVal vArg As Range) As String

Dim a

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

End Function

编辑

下面是一个更为详细的代码,它使用某种反射并对枚举进行迭代。

在Excel中,枚举类型很吸引人,因为您不能在这些类型上循环…除了这个把戏。

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

样品输出:

 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

嗯!

PowerUser 14 年前

~~不完全是~~ . 我想您可以定义一个边缘数组/类型/枚举,但是由于我们只讨论少数边界,因此它并不真正值得(哦,您的psuedo代码也将包括对角边界)。以下是我的一些生产代码:

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

请记住,您的代码存在“过度优化”的情况。