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)表示工作表的第一行, 将工作表的第一行字体颜色设置为了绿色