跳转至

04 实用语句<上>

本期我们重点盘点一下那些不好归类，但是又出镜率特别高的方法，方法太多，我们先讲五个

一、Offset语句

首先出场的是offset语句，注意它有两种不同的写法：

  • 单元格.offset(偏移行，偏移列)：偏移的下标从0开始
  • 单元格(偏移行，偏移列)：下标从1开始

1
2
3
4
sub test()
    msgbox range("a1").offset(1,1).value '返回b2单元格的值
    msgbox range("a1")(1,1).value '返回a1单元格的值
end sub
这个方法在： 你要拿的数据 和 你的条件数据 存在固定的对应位置偏移关系的时候非常实用

二、Resize语句

Resize语句将一个单元格可以拓展为一块区域，单元格在拓展区域的左上角，写法是：

单元格.resize（新区域行数，新区域列数）

看下面这个案例：

1
2
3
4
sub test()
    '使用resize方法,将a1单元格扩展为10行10列,并将其值设为1
    range("a1").resize(10,10).value = 1
end sub
这个方法在你需要填充数组或者大量数据的时候特别实用，只需要给定初始位置，然后Resize填充即可

三、交集和并集

如果你需要遍历多块区域，不需要写两个for循环，只需要用Union将所有的区域取并集即可，而如果你想求的是交集，则通过Intersect方法定义，具体用法如下：

  • Union：表示并集，用法为 Union(区域1，区域2，…，区域n)
  • Intersect：表示交集，用法为 Intersect(区域1，区域2，…，区域n)

我们同样看一个例子：

1
2
3
4
5
6
7
8
9
Sub test()
    Dim s As Range
    '遍历A1:A5和C1:C5这两个区域,将其中为空的单元格填充为红色
    For Each s In Union(Range("A1:A5"), Range("C1:C5"))
        If s.Value = "" Then
            s.Interior.Color = vbRed
        End If
    Next s
End Sub

四、SpecialCells

这个方法就像我们通过EXCEL进行定位，设置定位条件时的那个界面，由于只有选择常量或者公式的时候，才需要进一步选择数字，文本，逻辑值和错误这些选项，因此在代码中就有可选参数 [Value]

语法为：Set r = Range("A1:D100").SpecialCells(Type, [Value])

1) SpecialCells 常用类型速查表（Type）

Type 常量 数值 作用（会返回匹配的 Range） Value 是否需要用
xlCellTypeBlanks 4 空白单元格 否
xlCellTypeConstants 2 常量（非公式结果）单元格 是（常用）
xlCellTypeFormulas -4123 公式单元格 是（常用）
xlCellTypeVisible 12 可见单元格（筛选/隐藏后常用） 否
xlCellTypeComments -4144 批注单元格（旧版“注释/批注”） 否
xlCellTypeLastCell 11 工作表“已用区域”的最后一个单元格 否
xlCellTypeAllFormatConditions -4172 含条件格式的单元格 否
xlCellTypeAllValidation -4174 含数据验证的单元格 否
xlCellTypeSameFormatConditions -4173 与活动单元格相同条件格式的单元格 否
xlCellTypeSameValidation -4175 与活动单元格相同验证规则的单元格 否
xlCellTypeRowDifferences 9 与活动单元格相比，“行差异”单元格 否
xlCellTypeColumnDifferences 8 与活动单元格相比，“列差异”单元格 否
xlCellTypeDependents 16 依赖该单元格的单元格（从属） 否
xlCellTypePrecedents 8 该单元格引用到的单元格（先行） 否

2) Value 参数速查（只在 Constants / Formulas 常用）

Value 用来限定“返回的常量/公式属于哪种数据类型”，可组合相加（位掩码）。

Value 常量 数值 含义
xlNumbers 1 数值
xlTextValues 2 文本
xlLogical 4 逻辑 TRUE/FALSE
xlErrors 16 错误值（#N/A 等）

组合示例：

  • 数值 + 文本：xlNumbers + xlTextValues → 1 + 2 = 3
  • 全部类型：xlNumbers + xlTextValues + xlLogical + xlErrors → 23

1
2
3
4
5
6
7
sub test()
    '获取A1:D100中所有常量单元格,这里23代表 xlNumbers + xlTextValues + xlLogical + xlErrors 都勾选
    Set r = Range("A1:D100").SpecialCells(xlcelltypeconstants, 23)
    For Each s In r
        msgbox s.address
    Next s
end sub
但是要注意，SpecialCells 如果一个都找不到，会直接抛错，所以建议放在Error错误处理中：

1
2
3
4
5
6
7
8
Dim r As Range
On Error Resume Next
Set r = Range("A1:D100").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not r Is Nothing Then
    'TODO: 对 r 操作
End If

五、Comment注释

对于单元格注释的处理，主要有以下这些常用的方式：

  • addcomment： 增加注释，后面跟上具体的文本，注意如果单元格本来就有注释，使用此方式就会报错
  • range.comment.text：用于修改注释的文本，但是如果单元格本身没有注释，此方式报错
  • range.comment.shape.textframe.autosize：设置注释的大小根据文本自适应调整
  • range.comment.visible：设置注释是否可见
  • range("a1").clearcomments：清空注释，和range("a1").comment.delete用法类似

1
2
3
4
5
6
7
8
sub test()
    range("a1").addcomment "这是a1单元格"
    range("a1").comment.text "这是a1单元格的注释"
    range("a1").comment.shape.textframe.autosize = True '自动调整注释框大小
    range("a1").comment.visible = true '设置注释一直可见
    range("a1").clearcomments '清除a1单元格的注释
    range("a1").comment.delete '删除a1单元格的注释
end sub
上放的代码在运行到range("a1").comment.visible = true的时候，表格就会一直显示这样一个注释，如下图：

本期我们总结了以上方法，其实VBA中好用的方法还有很多，其他的下回分解