適用范圍：
Microsoft Office Excel 2003
摘要：尋找能夠?yàn)?Excel 工作表增加額外功能的宏。只需稍加練習(xí)，您就可以擴(kuò)展這些程序，使其適合您自己的應(yīng)用程序。
本頁內(nèi)容
引言
導(dǎo)出帶有逗號和引號分隔符的文本文件
計(jì)算包含公式、文本或數(shù)字的單元格數(shù)量
使用 Saved 屬性確定工作簿是否已發(fā)生更
合并數(shù)據(jù)列
數(shù)組中的總行數(shù)和總列數(shù)
結(jié)論
引言
本文介紹幾個 Microsoft Visual Basic for Applications (VBA) 宏，您可以使用這些宏為 Microsoft Office Excel 2003 工作簿和工作表增加額外的功能。這些宏將為您的應(yīng)用程序提供新的功能或增強(qiáng)現(xiàn)有的功能。閱讀示例的同時，您應(yīng)該尋找擴(kuò)展這些宏的方法，以適合您自己的情況。
導(dǎo)出帶有逗號和引號分隔符的文本文件
Excel 沒有自動將數(shù)據(jù)導(dǎo)出為文本文件的菜單命令，因此導(dǎo)出的文本文件同時帶有逗號和引號分隔符。例如，沒有命令能自動創(chuàng)建包含以下內(nèi)容的文本文件：
"Text1","Text2","Text3"
但是，您可以使用 VBA 宏在 Excel 中創(chuàng)建該功能。這種文件格式是在諸如 Microsoft Office Access 2003 和 Microsoft Office Word 2003 之類的應(yīng)用程序中導(dǎo)入文本數(shù)據(jù)時常見的格式。
您可以在如下所示的 VBA 宏中使用 Print 語句，導(dǎo)出同時帶有逗號和引號分隔符的文本文件。要使該程序正常運(yùn)行，必須在運(yùn)行該程序之前選擇包含數(shù)據(jù)的單元格。
使用以下示例之前，請執(zhí)行以下步驟：
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 ‘ 提示用戶指定目標(biāo)文件名。 DestFile = InputBox("Enter the destination filename" & _ Chr(10) & "(with complete path and extension):", _ "Quote-Comma Exporter") ‘ 獲取下一個可用的文件句柄編號。 FileNum = FreeFile() ‘ 關(guān)閉錯誤檢查功能。 On Error Resume Next ‘ 嘗試打開目標(biāo)文件以供輸出。 Open DestFile For Output As #FileNum ‘ 如果出現(xiàn)錯誤，則報(bào)告錯誤并結(jié)束程序。 If Err <> 0 Then MsgBox "Cannot open filename " & DestFile End End If ‘ 打開錯誤檢查功能。 On Error GoTo 0 ‘ 循環(huán)選擇的每一行。 For RowCount = 1 To Selection.Rows.Count ‘ 循環(huán)選擇的每一列。 For ColumnCount = 1 To Selection.Columns.Count ‘ 將當(dāng)前單元格中的文本寫入到文件中，文本用引號括起來。 Print #FileNum, """" & Selection.Cells(RowCount, _ ColumnCount).Text & """"; ‘ 檢查單元格是否位于最后一列。 If ColumnCount = Selection.Columns.Count Then ‘ 如果是，則寫入一個空行。 Print #FileNum, Else ‘ 否則，則寫入一個逗號。 Print #FileNum, ","; End If ‘ 開始 ColumnCount 循環(huán)的下一個迭代。 Next ColumnCount ‘ 開始 RowCount 循環(huán)的下一個迭代。 Next RowCount ‘ 關(guān)閉目標(biāo)文件。 Close #FileNum End Sub
4.
運(yùn)行該宏之前，請選擇要導(dǎo)出的數(shù)據(jù)，然后在“工具”菜單中指向“宏”并單擊“宏”。
5.
選擇 QuoteCommaExport 宏，然后單擊“運(yùn)行”。
計(jì)算包含公式、文本或數(shù)字的單元格數(shù)量
在 Excel 中，您可以對包含公式、文本或數(shù)字的工作表中的單元格數(shù)量進(jìn)行計(jì)算，方法是使用“定位條件”對話框選擇單元格，然后運(yùn)行計(jì)算所選單元格數(shù)量的宏。例如，當(dāng)您需要設(shè)置表格以確定合計(jì)列的每一行是否都包含公式而不用手動檢查每一行時，此方法可能很有用。
選擇單元格
要選擇公式、文本或數(shù)字，請執(zhí)行以下步驟：
1.
在“編輯”菜單中，單擊“定位”，然后單擊“定位條件”。
2.
在“定位條件”對話框中，要選擇所有公式，請單擊“公式”并確保選中“數(shù)字”、“文本”、“邏輯值”以及“錯誤”復(fù)選框。要選擇文本，請選擇“常量”選項(xiàng)，然后僅單擊并選中“文本”復(fù)選框。要選擇數(shù)字，請選擇“常量”選項(xiàng)，然后僅單擊并選中“數(shù)字”復(fù)選框。
計(jì)算所選單元格數(shù)量的 VBA 代碼
要計(jì)算所選的單元格數(shù)量并在消息框中顯示計(jì)算結(jié)果，請使用以下程序：
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
您可以將此程序指定給一個命令按鈕，這樣，當(dāng)您單擊該按鈕時，將顯示所選項(xiàng)的數(shù)量。
使用 Saved 屬性確定工作簿是否已發(fā)生更改
可以通過檢查工作簿的 Saved 屬性來確定工作簿是否已發(fā)生更改。根據(jù)工作簿是否發(fā)生了更改，Saved 屬性將返回 True 或 False 值。
注意：用戶除了可以通過“事件”設(shè)置 Saved 屬性外，還可以通過代碼將其設(shè)置為 True 或 False。本節(jié)包含的示例宏說明了如何在這兩種情況下使用 Saved 屬性。
工作表中的各種情況（例如存在可變函數(shù)）都可能會影響 Saved 屬性?？勺兒瘮?shù)是指工作表中每次發(fā)生更改時都會重新計(jì)算的函數(shù)，而不管發(fā)生的更改是否影響到這些函數(shù)。某些常見的可變函數(shù)包括 RAND()、NOW()、TODAY() 和 OFFSET()。
如果活動工作簿包含未保存的更改，第一個宏將顯示如下消息：
Sub TestForUnsavedChanges() If ActiveWorkbook.Saved = False Then MsgBox "This workbook contains unsaved changes." End If End Sub
下一個宏將關(guān)閉包含示例代碼的工作簿并放棄對工作簿所做的所有更改：
Sub CloseWithoutChanges() ThisWorkbook.Saved = True ThisWorkbook.Close End Sub
下面的示例宏也將關(guān)閉工作簿并放棄更改：
Sub CloseWithoutChanges() ThisWorkbook.Close SaveChanges:=False End Sub
合并數(shù)據(jù)列
在 Excel 中，可以使用宏合并兩個相鄰列中的數(shù)據(jù)并在包含數(shù)據(jù)的右側(cè)列中顯示結(jié)果，完全不需要手動設(shè)置公式。本節(jié)包含的示例宏就可以實(shí)現(xiàn)此功能。
Sub ConcatColumns() Do While ActiveCell <> "" ‘ 一直循環(huán)，直到活動單元格為空。 ActiveCell.Offset(0, 1).FormulaR1C1 = _ ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0) ActiveCell.Offset(1, 0).Select Loop End Sub
要使用宏，請執(zhí)行以下步驟：
1.
打開包含數(shù)據(jù)的工作簿。
2.
按 ALT+F11 組合鍵激活“Visual Basic 編輯器”。
3.
在“插入”菜單中，單擊“模塊”以插入一個模塊。在模塊的代碼窗口中鍵入上面的宏。
4.
單擊“文件”菜單中的“關(guān)閉并返回到 Microsoft Excel”。
5.
選擇包含要合并的數(shù)據(jù)的工作表。
6.
單擊要合并的右側(cè)數(shù)據(jù)列的第一個單元格。例如，如果單元格 A1:A100 和 B1:B100 包含數(shù)據(jù)，則單擊單元格 B1。
7.
在“工具”菜單中，指向“宏”并單擊“宏”。選擇 ConcatColumns 宏并單擊“運(yùn)行”。
注意：可以用語句 ActiveCell.Offset(0, 1).Formula 替換語句 ActiveCell.Offset(0, 1).FormulaR1C1。如果僅使用文本和數(shù)字（不包含公式），那么兩個語句的效果相同。第一個語句末尾使用的 R1C1 表示第一行的第一列，這是 Excel 幫助主題中大多數(shù)示例使用的形式。
數(shù)組中的總行數(shù)和總列數(shù)
在 Excel 中，可以使用數(shù)組來計(jì)算和操作工作表中的數(shù)據(jù)，還可以使用宏將某個范圍內(nèi)的單元格中的值存儲到一個數(shù)組中。本節(jié)中的示例宏代碼將在一個矩形單元格區(qū)域中添加一行和一列，以包含該區(qū)域中每一行和每一列中的單元格總數(shù)。
具體的步驟是，代碼從活動工作表上活動單元格周圍的當(dāng)前單元格區(qū)域中讀取數(shù)據(jù)。宏將這些數(shù)據(jù)存儲在一個數(shù)組中，計(jì)算每一行和每一列中的單元格總數(shù)，然后將輸出顯示在工作表中。數(shù)組的大小由當(dāng)前區(qū)域中的單元格數(shù)量決定。
注意：此宏不會在工作表中添加任何公式，因此如果該范圍內(nèi)的單元格總數(shù)有變化，則必須重新運(yùn)行宏。
使用以下示例之前，請執(zhí)行以下步驟：
1.
打開一個新工作簿。
2.
在“工具”菜單中，指向“宏”，然后單擊“Visual Basic 編輯器”（或者簡單地按下 ALT+F11 組合鍵）。在“Visual Basic 編輯器”中的“插入”菜單中，單擊“模塊”。
將以下示例代碼鍵入或粘貼到模塊中：
Sub TotalRowsAndColumns() ‘ 此宏假定您已從 ‘ 要計(jì)算單元格總數(shù)的矩形區(qū)域內(nèi) ‘ 選擇了一個單元格或一組單元格。行和列的單元格總數(shù)將出現(xiàn)在 ‘ 當(dāng)前區(qū)域下面的行和右側(cè)的列中。 Dim r As Integer Dim c As Integer Dim i As Integer Dim j As Integer Dim myArray As Variant ‘ 將 myArray 聲明為變量將使數(shù)組可以接收 ‘ 一組單元格。此時，數(shù)組將自動轉(zhuǎn)換為 ‘ 以下標(biāo) myArray(1,1) 開始的數(shù)組。 ‘ 指當(dāng)前所選單元格周圍的區(qū)域。 With Selection.CurrentRegion r = .Rows.Count c = .Columns.Count ‘ 重新計(jì)算總行數(shù)和總列數(shù)并將結(jié)果存儲到數(shù)組中。 myArray = .Resize(r + 1, c + 1) ‘ 在下面的嵌套循環(huán)中，變量 i 跟蹤 ‘ 行號，變量 j 跟蹤 ‘ 列號。j 在可用列中每循環(huán)一次， ‘ i 就遞增一，而 j ‘ 則重新從一到 c 循環(huán)一次。 For i = 1 To r For j = 1 To c ‘ 行 i 的總數(shù) myArray(i, c + 1) = myArray(i, c + 1) + myArray(i, j) ‘ 列 j 的總數(shù) myArray(r + 1, j) = myArray(r + 1, j) + myArray(i, j) ‘ 總計(jì) myArray(r + 1, c + 1) = myArray(r + 1, c + 1) + myArray(i, j) Next j Next i ‘ 將數(shù)組返回工作表，數(shù)組中現(xiàn)在包含一個 ‘ 新行和一個新列，用于存儲總數(shù)。 .Resize(r + 1, c + 1) = myArray End With End Sub
3.
突出顯示要求和區(qū)域中的一個單元格，在“工具”菜單中，指向“宏”并單擊“宏”。
4.
選擇 TotalRowsAndColumns 宏，然后單擊“運(yùn)行”。
注意：要執(zhí)行與本示例中的運(yùn)算類似的運(yùn)算，可以修改宏代碼。例如，要對選定范圍內(nèi)的單元格中包含的值進(jìn)行減法、乘法或除法運(yùn)算，可以更改數(shù)學(xué)運(yùn)算符。
結(jié)論
本文介紹了各種 VBA 宏，使用這些宏可以減少使用工作表所需的工作量。此外，您還可以修改本文介紹的大多數(shù)宏以擴(kuò)展它們的功能。始終記住可以在工具或提示庫中添加更多宏，您將會受益匪淺。
