薛子轩:使用 Excel 2003 工作表的 VBA

使用 Excel 2003 工作表的 VBA 示例
适用范围：
Microsoft Office Excel 2003
摘要：寻找能够为 Excel 工作表增加额外功能的宏。只需稍加练习，您就可以扩展这些程序，使其适合您自己的应用程序。
本页内容
引言
导出带有逗号和引号分隔符的文本文件
计算包含公式、文本或数字的单元格数量
使用 Saved 属性确定工作簿是否已发生更
合并数据列
数组中的总行数和总列数
结论
引言
本文介绍几个 Microsoft Visual Basic for Applications (VBA) 宏，您可以使用这些宏为 Microsoft Office Excel 2003 工作簿和工作表增加额外的功能。这些宏将为您的应用程序提供新的功能或增强现有的功能。阅读示例的同时，您应该寻找扩展这些宏的方法，以适合您自己的情况。
导出带有逗号和引号分隔符的文本文件
Excel 没有自动将数据导出为文本文件的菜单命令，因此导出的文本文件同时带有逗号和引号分隔符。例如，没有命令能自动创建包含以下内容的文本文件：
"Text1","Text2","Text3"
但是，您可以使用 VBA 宏在 Excel 中创建该功能。这种文件格式是在诸如 Microsoft Office Access 2003 和 Microsoft Office Word 2003 之类的应用程序中导入文本数据时常见的格式。
您可以在如下所示的 VBA 宏中使用 Print 语句，导出同时带有逗号和引号分隔符的文本文件。要使该程序正常运行，必须在运行该程序之前选择包含数据的单元格。
使用以下示例之前，请执行以下步骤：
1.
打开一个新工作簿。
2.
在“工具”菜单中，指向“宏”，然后单击“Visual Basic 编辑器”（或者简单地按下 ALT+F11 组合键）。在“Visual Basic 编辑器”中，单击“插入”菜单，然后单击“模块”。
3.
将以下示例代码键入或粘贴到模块中：
Sub QuoteCommaExport() Dim DestFile As String Dim FileNum As Integer Dim ColumnCount As Integer Dim RowCount As Integer ‘ 提示用户指定目标文件名。 DestFile = InputBox("Enter the destination filename" & _ Chr(10) & "(with complete path and extension):", _ "Quote-Comma Exporter") ‘ 获取下一个可用的文件句柄编号。 FileNum = FreeFile() ‘ 关闭错误检查功能。 On Error Resume Next ‘ 尝试打开目标文件以供输出。 Open DestFile For Output As #FileNum ‘ 如果出现错误，则报告错误并结束程序。 If Err <> 0 Then MsgBox "Cannot open filename " & DestFile End End If ‘ 打开错误检查功能。 On Error GoTo 0 ‘ 循环选择的每一行。 For RowCount = 1 To Selection.Rows.Count ‘ 循环选择的每一列。 For ColumnCount = 1 To Selection.Columns.Count ‘ 将当前单元格中的文本写入到文件中，文本用引号括起来。 Print #FileNum, """" & Selection.Cells(RowCount, _ ColumnCount).Text & """"; ‘ 检查单元格是否位于最后一列。 If ColumnCount = Selection.Columns.Count Then ‘ 如果是，则写入一个空行。 Print #FileNum, Else ‘ 否则，则写入一个逗号。 Print #FileNum, ","; End If ‘ 开始 ColumnCount 循环的下一个迭代。 Next ColumnCount ‘ 开始 RowCount 循环的下一个迭代。 Next RowCount ‘ 关闭目标文件。 Close #FileNum End Sub
4.
运行该宏之前，请选择要导出的数据，然后在“工具”菜单中指向“宏”并单击“宏”。
5.
选择 QuoteCommaExport 宏，然后单击“运行”。
计算包含公式、文本或数字的单元格数量
在 Excel 中，您可以对包含公式、文本或数字的工作表中的单元格数量进行计算，方法是使用“定位条件”对话框选择单元格，然后运行计算所选单元格数量的宏。例如，当您需要设置表格以确定合计列的每一行是否都包含公式而不用手动检查每一行时，此方法可能很有用。
选择单元格
要选择公式、文本或数字，请执行以下步骤：
1.
在“编辑”菜单中，单击“定位”，然后单击“定位条件”。
2.
在“定位条件”对话框中，要选择所有公式，请单击“公式”并确保选中“数字”、“文本”、“逻辑值”以及“错误”复选框。要选择文本，请选择“常量”选项，然后仅单击并选中“文本”复选框。要选择数字，请选择“常量”选项，然后仅单击并选中“数字”复选框。
计算所选单元格数量的 VBA 代码
要计算所选的单元格数量并在消息框中显示计算结果，请使用以下程序：
Sub Count_Selection() Dim cell As Object Dim count As Integer count = 0 For Each cell In Selection count = count + 1 Next cell MsgBox count & " item(s) selected" End Sub
您可以将此程序指定给一个命令按钮，这样，当您单击该按钮时，将显示所选项的数量。
使用 Saved 属性确定工作簿是否已发生更改
可以通过检查工作簿的 Saved 属性来确定工作簿是否已发生更改。根据工作簿是否发生了更改，Saved 属性将返回 True 或 False 值。
注意：用户除了可以通过“事件”设置 Saved 属性外，还可以通过代码将其设置为 True 或 False。本节包含的示例宏说明了如何在这两种情况下使用 Saved 属性。
工作表中的各种情况（例如存在可变函数）都可能会影响 Saved 属性。可变函数是指工作表中每次发生更改时都会重新计算的函数，而不管发生的更改是否影响到这些函数。某些常见的可变函数包括 RAND()、NOW()、TODAY() 和 OFFSET()。
如果活动工作簿包含未保存的更改，第一个宏将显示如下消息：
Sub TestForUnsavedChanges() If ActiveWorkbook.Saved = False Then MsgBox "This workbook contains unsaved changes." End If End Sub
下一个宏将关闭包含示例代码的工作簿并放弃对工作簿所做的所有更改：
Sub CloseWithoutChanges() ThisWorkbook.Saved = True ThisWorkbook.Close End Sub
下面的示例宏也将关闭工作簿并放弃更改：
Sub CloseWithoutChanges() ThisWorkbook.Close SaveChanges:=False End Sub
合并数据列
在 Excel 中，可以使用宏合并两个相邻列中的数据并在包含数据的右侧列中显示结果，完全不需要手动设置公式。本节包含的示例宏就可以实现此功能。
Sub ConcatColumns() Do While ActiveCell <> "" ‘ 一直循环，直到活动单元格为空。 ActiveCell.Offset(0, 1).FormulaR1C1 = _ ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0) ActiveCell.Offset(1, 0).Select Loop End Sub
要使用宏，请执行以下步骤：
1.
打开包含数据的工作簿。
2.
按 ALT+F11 组合键激活“Visual Basic 编辑器”。
3.
在“插入”菜单中，单击“模块”以插入一个模块。在模块的代码窗口中键入上面的宏。
4.
单击“文件”菜单中的“关闭并返回到 Microsoft Excel”。
5.
选择包含要合并的数据的工作表。
6.
单击要合并的右侧数据列的第一个单元格。例如，如果单元格 A1:A100 和 B1:B100 包含数据，则单击单元格 B1。
7.
在“工具”菜单中，指向“宏”并单击“宏”。选择 ConcatColumns 宏并单击“运行”。
注意：可以用语句 ActiveCell.Offset(0, 1).Formula 替换语句 ActiveCell.Offset(0, 1).FormulaR1C1。如果仅使用文本和数字（不包含公式），那么两个语句的效果相同。第一个语句末尾使用的 R1C1 表示第一行的第一列，这是 Excel 帮助主题中大多数示例使用的形式。
数组中的总行数和总列数
在 Excel 中，可以使用数组来计算和操作工作表中的数据，还可以使用宏将某个范围内的单元格中的值存储到一个数组中。本节中的示例宏代码将在一个矩形单元格区域中添加一行和一列，以包含该区域中每一行和每一列中的单元格总数。
具体的步骤是，代码从活动工作表上活动单元格周围的当前单元格区域中读取数据。宏将这些数据存储在一个数组中，计算每一行和每一列中的单元格总数，然后将输出显示在工作表中。数组的大小由当前区域中的单元格数量决定。
注意：此宏不会在工作表中添加任何公式，因此如果该范围内的单元格总数有变化，则必须重新运行宏。
使用以下示例之前，请执行以下步骤：
1.
打开一个新工作簿。
2.
在“工具”菜单中，指向“宏”，然后单击“Visual Basic 编辑器”（或者简单地按下 ALT+F11 组合键）。在“Visual Basic 编辑器”中的“插入”菜单中，单击“模块”。
将以下示例代码键入或粘贴到模块中：
Sub TotalRowsAndColumns() ‘ 此宏假定您已从 ‘ 要计算单元格总数的矩形区域内 ‘ 选择了一个单元格或一组单元格。行和列的单元格总数将出现在 ‘ 当前区域下面的行和右侧的列中。 Dim r As Integer Dim c As Integer Dim i As Integer Dim j As Integer Dim myArray As Variant ‘ 将 myArray 声明为变量将使数组可以接收 ‘ 一组单元格。此时，数组将自动转换为 ‘ 以下标 myArray(1,1) 开始的数组。 ‘ 指当前所选单元格周围的区域。 With Selection.CurrentRegion r = .Rows.Count c = .Columns.Count ‘ 重新计算总行数和总列数并将结果存储到数组中。 myArray = .Resize(r + 1, c + 1) ‘ 在下面的嵌套循环中，变量 i 跟踪 ‘ 行号，变量 j 跟踪 ‘ 列号。j 在可用列中每循环一次， ‘ i 就递增一，而 j ‘ 则重新从一到 c 循环一次。 For i = 1 To r For j = 1 To c ‘ 行 i 的总数 myArray(i, c + 1) = myArray(i, c + 1) + myArray(i, j) ‘ 列 j 的总数 myArray(r + 1, j) = myArray(r + 1, j) + myArray(i, j) ‘ 总计 myArray(r + 1, c + 1) = myArray(r + 1, c + 1) + myArray(i, j) Next j Next i ‘ 将数组返回工作表，数组中现在包含一个 ‘ 新行和一个新列，用于存储总数。 .Resize(r + 1, c + 1) = myArray End With End Sub
3.
突出显示要求和区域中的一个单元格，在“工具”菜单中，指向“宏”并单击“宏”。
4.
选择 TotalRowsAndColumns 宏，然后单击“运行”。
注意：要执行与本示例中的运算类似的运算，可以修改宏代码。例如，要对选定范围内的单元格中包含的值进行减法、乘法或除法运算，可以更改数学运算符。
结论
本文介绍了各种 VBA 宏，使用这些宏可以减少使用工作表所需的工作量。此外，您还可以修改本文介绍的大多数宏以扩展它们的功能。始终记住可以在工具或提示库中添加更多宏，您将会受益匪浅。