VBA Excel实践如何系列（三）vba如何设置单元格边框、字体

vba设置单元格字体，以下是设置给定单元格内字体的加粗，单元格填充灰色等，参数是Range对象

Sub font_sty(Rng As Range)
    Rng.Select
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
        .PatternTintAndShade = 0
    End With
End Sub

vba设置单元格边框，以下是设置给定单元格边框，参数是Range对象

Sub sty_sig(Rng As Range)
    Rng.Select
    
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    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
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

对这两个方法（片段）进行调用

我们使用《如何系列（二）》中的例子，最后增加两个call 分别call设置单元格字体格式，和设置单元格边框

Sub sqltest()
    Dim Spath As String
    Spath = ThisWorkbook.Path & "\pbxtest.xlsx"
    Set adConn = New ADODB.Connection
    '
    adConn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties=excel 12.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';data source=" & Spath & ""
    Set rs = adConn.Execute("Select 编号,测试环境,测试项目 From [sheet1$a1:j35] ")
    '
    sht_name = "sheet3"
    Set sht = ThisWorkbook.Worksheets(sht_name)
    '
    For i = 1 To rs.Fields.Count Step 1
        sht.Range("A1").Offset(0, i - 1) = rs.Fields(i - 1).Name '
    Next i
    '
    sht.Range("A2").CopyFromRecordset rs
    sht.Cells.EntireColumn.AutoFit
    Call sty_sig(Range(Cells(1, 1), Cells(13, 3)))
    Call font_sty(Range(Cells(1, 1), Cells(1, 3)))
End Sub

结果：

之前的：