Excel VBA入门(5) - Worksheet对象常用属性

1. Cells

表示一个Range对象, 默认无参数时代表整个工作表的所有单元格

    Dim allCells As range
    Dim ws As Worksheet
    Set ws = Application.ActiveWorkbook.ActiveSheet
    Set allCells = ws.Cells
    With allCells
        With .Font
            .Bold = True
            .Italic = True
            .Size = 18
        End With
    End With
    Set ws = Nothing

上述代码将当前激活的工作表的所有单元格设置格式: 粗体,斜体,字号18

Cells也可以表示一个单元格, 需要加上参数, 行号和列号(均从1开始)

    Dim myCells As range
    Dim ws As Worksheet
    Set ws = Application.ActiveWorkbook.ActiveSheet
    Set myCells = ws.Cells(1, 1)
    myCells.Font.Color = RGB(255, 0, 0)

将A1单元格字体颜色置为红色

2.Columns

亦表示一个Range对象, 默认无参数表示工作表的所有列

    Dim allColumns As range
    Dim ws As Worksheet
    Set ws = Application.ActiveWorkbook.ActiveSheet
    Set allColumns = ws.Columns
    allColumns.Interior.Color = RGB(255, 0, 0)

上述代码将工作表的所有列的背景色置为红色

Columns也可以表示一个列, 需要加上参数, 列号(从1开始)

    Dim myColumn As range
    Dim ws As Worksheet
    Set ws = Application.ActiveWorkbook.ActiveSheet
    Set myColumn = ws.Columns(1)
    myColumn.Interior.Color = RGB(100, 100, 200)

上述代码设置第1列的背景色

顺便说一下, 列号和列名称的对应关系

    Dim myColumn As range
    Dim ws As Worksheet
    Dim index As Integer
    Set ws = Application.ActiveWorkbook.ActiveSheet
    index = ws.Columns("A").Column
    Set myColumn = ws.Columns(index)
    myColumn.Interior.Color = RGB(0, 0, 200)

其中ws.Columns("A").Column返回第A列的列号

而上述Set myColumn = ws.Columns(1) 也可以写成 Set myColumn = ws.Columns("A")

3.Next

Next的本意是下一个(类似Tab键的功能), 至于下一个是什么对象, 要看当前对象是什么 

    Dim ws As Worksheet
    Dim nextWs As Worksheet
    Set ws = Application.ActiveSheet
    Set nextWs = ws.Next
    Debug.Print ws.name
    If nextWs <> Nothing Then
        Debug.Print nextWs.name
    End If

上述代码表示当前工作表的下一个工作表, 默认的如: Sheet1 的Next为Sheet2, 作为最后一个工作表要判读下一个是否为空(Nothing)

    Dim ws As Worksheet
    Dim myCell As range
    Dim nextCell As range
    Set ws = Application.ActiveSheet
    Set myCell = ws.Cells(1, 1)
    Set nextCell = myCell.Next
    Debug.Print myCell.Value
    Debug.Print nextCell.Value

上述代码A1单元格的下一个单元格, 输出A1和B1单元格的内容

4. Previous

和Next的意思相反, 表示上一个

    Dim ws As Worksheet
    Dim prevWs As Worksheet
    Set ws = Application.ActiveSheet
    Set prevWs = ws.Previous
    Debug.Print ws.name
    Debug.Print prevWs.name

上一个工作表

    Dim ws As Worksheet
    Dim myCell As range
    Dim prevCell As range
    Set ws = Application.ActiveSheet
    Set myCell = ws.Cells(1, 2)
    Set prevCell = myCell.Previous
    Debug.Print myCell.Value
    Debug.Print prevCell.Value

上一个单元格

5. Range

表示(多个)单元格区域

    Dim myRange As range
    Dim ws As Worksheet
    Set ws = Application.ActiveSheet
    'Set myRange = ws.range(Cells(1, 1), Cells(2, 2))
    Set myRange = ws.range("A1,B2,C3:D4")
    myRange.Select

选中了多个不连续的单元格

6.Rows

和Columns的含义类似, 默认无参表示工作表中的所有行, 是一个Range对象

    Dim allRows As range
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Set allRows = ws.Rows
    allRows.Font.Color = 255

上述代码将工作表中的所有行的字体颜色设置为红色

    Dim row As range
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Set row = ws.Rows(1)
    row.Font.Color = RGB(0, 255, 0)

带参数的Rows(1)表示工作表的第一行, 将工作表的第一行字体颜色设置为了绿色