选择范围以应用边框-访问VBA [英] Selecting range to apply borders - Access VBA

问题描述

这是我需要做的.根据行号和列号选择一个单元格范围.由于它是动态的，因此我必须使用行号和列号.我已经在这里对它们进行了硬编码作为示例.找到范围后，需要在其周围应用粗边框.我可以选择范围.任何帮助将不胜感激.我已经包含了部分选择代码.我已经指出了失败的地方.

Here is what I need to do. Select a range of cells based on the row and column numbers. Since it is a dynamic one, I have to use the row and column numbers. I have hardcoded them here as an example. Once I have the range, I need to apply a thick border all around it. I am uable to select the range. Any help would be greatly appreciated. I have included part of the selection code. I have indicated where it fails.

Sub setBorder()

Dim xlApp As Object
Dim wb As Object
Dim ws  As Object
Dim rng      As Object
Dim startRow         As Integer, startCol As Integer
Dim endRow           As Integer, endCol As Integer

Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False

Set wb = xlApp.Workbooks.Open("H:\Documents\Misc-Work\BU\test.xlsx")

startRow = 6
startCol = 5
endRow = 15
endCol = 5

Set ws = wb.worksheets(1)

With ws
   Set rng = .Range(.Cells(startRow, startCol), .Cells(endRow, endCol))
   rng.select -- It fails here
End With

'rng.select

With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
End Sub

推荐答案

无需使用select:

There is no need to use select:

With ws
    Set rng = .Range(.Cells(startRow, startCol), .Cells(endRow, endCol))
End With

With rng.Borders(7)
    .LineStyle = 1
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = 2
End With
With rng.Borders(8)
    .LineStyle = 1
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = 2
End With

顺便说一句，访问不了解常量 xlEdgeLeft 等，直到您在中添加对 Microsoft Excel对象库的引用工具->参考.您也可以将其常量更改为它们的实际值，而无需像上面的代码中那样添加对库的引用，但这会使您的代码不太清楚.

Btw, Access don't know about constants xlEdgeLeft and etc, until you add reference to Microsoft Excel Object Library in Tools->References. You can also change this constanst to their actual values without adding reference to library as in my code above, but this will make your code less clear.

这篇关于选择范围以应用边框-访问VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！