VBA常用技巧(一) 下载本文

工作表中按钮的单击过程,检查所选择的单元格区域是否含有公式。

第2行代码返回所选择单元格区域的HasFormula属性值。如果区域中所有单元格均包含公式,则该值为True;如果所有单元格均不包含公式,则该值为False。

第3、4行代码,如果返回True,说明区域中所有单元格均包含公式。 第5、6行代码,如果返回False,说明区域中所有单元格均不包含公式。

第7、8行代码,如果是混合区域,则显示包含公式的单元格地址,如图 xi-1所示。

图 xi-1 显示包含公式的单元格地址

3. 判断单元格公式是否存在错误

当需要获取的单元格的值由公式返回时,公式返回的结果可能是一个错误文本,包含#NULL!、#DIV/0!、#VALUE!、#REF!、#NAME?、#NUM!、#N/A等。此时,当单元格公式返回结果为错误文本时,如果试图通过Value属性来获得公式的返回结果,将得到类型不匹配的错误信息,如图 xi-2所示。

图 xi-2 公式错误导致的消息

通过Range对象的Value属性的返回结果是否为错误类型,来判断公式是否存在错误,如下面的代码所示。

#001 Sub FormulaIsError()

#002 If VBA.IsError(Range(\#003 MsgBox \单元格错误类型为:\#004 Else

#005 MsgBox \单元格公式结果为\#006 End If #007 End Sub

代码解析“

FormulaIsError过程代码判断单元格A1中公式结果是否为错误,如果为错误则显示该错误类型,否则显示公式的结果,如图 xi-3所示。

第2行代码使用IsError函数返回Boolean值,指出表达式是否为一个错误值,如果表达式表示一个错误,则IsError函数返回True,否则返回False。

图 xi-3 显示公式错误结果

4. 取得单元格中公式的引用单元格

如果需要取得单元格中公式的引用单元格对象,可以使用Range对象的Precedents属性,如下面的代码所示。

#001 Sub RngPrecedent() #002 Dim rng As Range

#003 Set rng = Sheet1.Range(\#004 MsgBox \公式所引用的单元格有:\#005 Set rng = Nothing #006 End Sub

代码解析:

在工作表的C1单元格中写有公式“SUM(“A1:B1”)”,RngPrecedent过程使用Range对象的Precedents属性取得其引用的单元格A1:B1。

Precedents属性返回一个Range对象,该对象代表单元格的所有引用单元格。如果有若干引用单元格,那么该区域可能是多个的选定区域(Range 对象的联合)。

运行RngPrecedent过程结果如图 xi-4所示。

图 xi-4 取得引用单元格

5. 将单元格中的公式转换为数值

工作表中如果存在过多的公式将影响操作速度,将单元格中的函数与公式的结果转换为数值,可以提高工作表运算效率,有下面几种方法可以实现。

使用选择性粘贴的方法可以将函数与公式的结果转换为数值,如下面的代码所示。

#001 Sub SpecialPaste() #002 With Range(\#003 .Copy

#004 .PasteSpecial Paste:=xlPasteValues #005 End With

#006 Application.CutCopyMode = False #007 End Sub

代码解析:

SpecialPaste过程使用选择性粘贴方法将单元格区域的公式转换为数值。 第3行代码将单元格区域复制到剪贴板中。

应用于Range对象的Copy方法将单元格区域复制到指定的区域或剪贴板中,语法如下:

expression.Copy(Destination)

参数expression是必需的,该表达式返回一个Range对象。

参数Destination是可选的,指定区域要复制到的目标区域。如果省略该参数,Microsoft Excel 将把该区域复制到剪贴板中。

第4行代码将剪贴板中的Range对象仅复制值到单元格区域中。

应用于Range对象的PasteSpecial方法将剪贴板中的Range对象粘贴到指定区域中,语法如下:

expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

参数expression是必需的,该表达式返回一个Range对象。

参数Paste是可选的,指定要粘贴的区域部分。在本例中设置为xlPasteValues,仅复制值到单元格区域中。

使用Value属性可以将函数与公式的结果转换为数值,如下面的代码所示。

#001 Sub UseValue()

#002 Range(\#003 End Sub

代码解析:

UseValue过程使用Value属性将函数与公式的结果转换为数值。

使用Formula属性可以将函数与公式的结果转换为数值,如下面的代码所示。

#001 Sub UseFormula()

#002 Range(\#003 End Sub

代码解析:

UseFormula过程Formula属性将函数与公式的结果转换为数值。当Formula属性值为非公式时,返回的结果与Value属性一致。

xii. 单元格中的批注

1. 判断单元格是否存在批注

在VBA中,可以利用Range对象的Comment属性判断单元格是否存在批注,如下面的代码所示。

#001 Sub HasComment()

#002 If Range(\#003 MsgBox \单元格中没有批注!\#004 Else

#005 MsgBox \单元格中批注内容为:\& Chr(13) & Range(\#006 End If #007 End Sub

代码解析:

HasComment过程判断A1单元格是否存在批注,并用消息框显示批注信息。