VBA程序集
(第5輯)
[摘要] VBA程序集匯集了一些小型代碼程序。其中的代碼可直接運(yùn)用到您的應(yīng)用程序中，也可以根據(jù)您的需要稍作調(diào)整或修改后運(yùn)用到您的應(yīng)用程序中。一小段代碼也能附加額外的功能或增強(qiáng)現(xiàn)有的功能，或許能大大改善您的工作效率。
本程序集中匯集了5個(gè)小型實(shí)用功能程序代碼：
? >>將Excel數(shù)據(jù)表輸出為一個(gè)帶有逗號(hào)和引號(hào)分隔符的文本文件
? >>統(tǒng)計(jì)所選區(qū)域中包含公式、文本或數(shù)字的單元格數(shù)
? >>使用Saved屬性判斷工作簿是否有改變
? >>連接相鄰兩列單元格中的數(shù)據(jù)
? >>匯總單元格區(qū)域的行單元格值和列單元格值
如何創(chuàng)建和使用這些程序
使用程序前，您必須選創(chuàng)建它。您可以在VBE編輯器中輸入或粘貼下面的代碼以創(chuàng)建宏程序，然后執(zhí)行工作表菜單“工具”中的宏程序，或者在工作表中為自定義的菜單或命令按鈕附加宏，這樣就可以方便使用它們。
1. 打開您想創(chuàng)建宏程序的工作簿或新工作簿。
2. 在工作表中選擇菜單“工具——宏——Visual Basic編輯器”(或按Alt+F11組合鍵)，打開VBE編輯器。
3. 在VBE編輯器中選擇菜單“插入——模塊”，插入一個(gè)模塊并打開代碼窗口。
4. 在代碼窗口中輸入或粘貼程序代碼。
5. 關(guān)閉VBE窗口。
6. 若程序要求運(yùn)行前需要選擇單元格區(qū)域或特定單元格，則先按要求選擇。
7. 選擇工作表菜單“工具——宏——宏”命令，打開“宏”對(duì)話框。在“宏”對(duì)話框中選擇所創(chuàng)建的宏，單擊“執(zhí)行”按鈕運(yùn)行宏程序。
提示 (1) 當(dāng)然，上面的創(chuàng)建和使用程序的過(guò)程不是唯一的，您可以根據(jù)習(xí)慣來(lái)進(jìn)行，如可以直接在VBE編輯器中運(yùn)行宏，或者將宏程序附加到自定義菜單或按鈕中，點(diǎn)擊它們即運(yùn)行。
(2) 在閱讀或理解這些程序的時(shí)候，您應(yīng)該思考如何擴(kuò)展這些應(yīng)用程序來(lái)滿足您的需要。
程序分析和程序代碼
■ 將Excel數(shù)據(jù)表輸出為一個(gè)帶有逗號(hào)和引號(hào)分隔符的文本文件
Excel沒(méi)有一個(gè)菜單命令能夠自動(dòng)輸出數(shù)據(jù)到一個(gè)文本文件，例如輸出為一個(gè)帶有逗號(hào)和引號(hào)標(biāo)記作為分隔符的文本文件。例如，內(nèi)容為“Text1”，”Text2”，”Text3”的文本文件。
但是，你能在Excel中使用VBA宏程序?qū)崿F(xiàn)這個(gè)功能。在VBA宏程序中使用Print語(yǔ)句，輸出一個(gè)帶有逗號(hào)和引號(hào)作為分隔符的文本文件。為了實(shí)現(xiàn)程序的功能，你必須在運(yùn)行程序前選擇你所有輸出為文本文件的數(shù)據(jù)的單元格區(qū)域。
程序代碼如下：
****************************
程序18(數(shù)據(jù)輸出)
Option Explicit
Sub 數(shù)據(jù)輸出()
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
' 提示用戶輸入目標(biāo)文件名
DestFile = InputBox("輸入您所要保存數(shù)據(jù)的目標(biāo)文件名" & _
Chr(10) & "(最好帶有完整的保存路徑和擴(kuò)展名):", _
"引號(hào)-逗號(hào)分隔輸出")
' 獲取下一個(gè)文件號(hào)
FileNum = FreeFile()
' 忽略出現(xiàn)的錯(cuò)誤
On Error Resume Next
'打開想要輸入數(shù)據(jù)的目標(biāo)文件
Open DestFile For Output As #FileNum
'若發(fā)生錯(cuò)誤則結(jié)束運(yùn)行
If Err <> 0 Then
MsgBox "不能打開文件!請(qǐng)確保您輸入了正確的文件名和路徑." & DestFile
End
End If
' 打開錯(cuò)誤檢查
On Error GoTo 0
' 查找所選區(qū)域中的每一行
For RowCount = 1 To Selection.Rows.Count
' 查找所選區(qū)域的每一列
For ColumnCount = 1 To Selection.Columns.Count
' 寫當(dāng)前單元格中的文本并帶有引號(hào)標(biāo)記到目標(biāo)文件中
Print #FileNum, """" & Selection.Cells(RowCount, _
ColumnCount).Text & """";
'檢查單元格是否是最后一列中的單元格
If ColumnCount = Selection.Columns.Count Then
'如果是,則寫入一個(gè)空白行
Print #FileNum,
Else
'否則,寫入一個(gè)逗號(hào)
Print #FileNum, ",";
End If
' 開始下一列循環(huán)判斷
Next ColumnCount
' 開始下一行循環(huán)判斷
Next RowCount
' 關(guān)閉寫入的目標(biāo)文件
Close #FileNum
End Sub
****************************
文檔示例見(jiàn)[過(guò)程]輸出所選數(shù)據(jù)到文本文件.xls。UploadFiles/2006-7/71278799.rar
■ 統(tǒng)計(jì)所選區(qū)域中包含公式、文本或數(shù)字的單元格數(shù)
在Excel中，您能使用定位對(duì)話框來(lái)選取工作表中某單元格區(qū)域所包含的公式、文本或數(shù)字的單元格，即
1. 在編輯菜單中，單擊“定位”，打開“定位”對(duì)話框。
2. 單擊“定位條件”按鈕，打開“定位條件”對(duì)話框。為了選擇所有的公式，單擊公式選項(xiàng)按鈕，確保選中數(shù)字、文本、邏輯值和錯(cuò)誤復(fù)選框。若要選擇文本，則選取常量選項(xiàng)按鈕并只選取公式按鈕下的“文本”復(fù)選框；若要選擇數(shù)字，則選取常量選項(xiàng)按鈕并只選取公式按鈕下的“數(shù)字”復(fù)選框。
要統(tǒng)計(jì)所選區(qū)域單元格的數(shù)量，并在消息框中顯示結(jié)果，使用下面的程序：
****************************
程序19(循環(huán))
Option Explicit
Sub 選區(qū)單元格數(shù)()
Dim cell As Object
Dim count As Integer
count = 0
For Each cell In Selection
count = count + 1
Next cell
MsgBox "您選擇了" & count & "個(gè)單元格."
End Sub
****************************
提示 您能在工作表中將該程序指給一個(gè)自定義按鈕，當(dāng)您單擊此按鈕時(shí)，將顯示您工作表上所選區(qū)域單元格數(shù)。
示例文檔見(jiàn)[過(guò)程]統(tǒng)計(jì)所選區(qū)域的單元格數(shù).xls。UploadFiles/2006-7/71979122.rar
■ 使用Saved屬性判斷工作簿是否有改變
您能通過(guò)檢查工作簿的Saved屬性判斷該工作簿是否發(fā)生了改變。根據(jù)工作簿是否改變，Saved屬性返回True值或False值。
若您的工作表中存在可變函數(shù)，可能影響Saved屬性判斷?？勺兒瘮?shù)是在工作表中每次發(fā)生變化時(shí)都重新進(jìn)行計(jì)算的函數(shù)，不管該變化是否影響到這個(gè)函數(shù)。最常用的可變函數(shù)有RAND(),NOW(),TODAY()和OFFSET()。
下面是不同條件下該屬性的運(yùn)用代碼：
程序20(對(duì)工作簿的操作)
****************************
‘當(dāng)活動(dòng)工作簿發(fā)生改變而沒(méi)有保存時(shí)顯示一個(gè)消息
Sub 測(cè)試1()
If ActiveWorkbook.Saved = False Then
MsgBox "這個(gè)工作簿已經(jīng)改變，但您沒(méi)有保存該變化. "
End If
End Sub
****************************
‘不保存并關(guān)閉工作簿，而不管工作簿是否發(fā)生改變
Sub 測(cè)試2()
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub
****************************
‘功能與上面的程序相同，即不管工作簿是否變化，不保存而直接關(guān)閉工作簿
Sub 測(cè)試3()
ThisWorkbook.Close SaveChanges:=False
End Sub
****************************
■ 連接相鄰兩列單元格中的數(shù)據(jù)
在Excel中，你能使用一個(gè)宏程序來(lái)連接兩個(gè)相鄰列中的數(shù)據(jù)，并在右邊相鄰的空列顯示結(jié)果，下面的宏程序?qū)崿F(xiàn)該功能：
****************************
程序21(單元格)
Sub 連接相鄰兩列數(shù)據(jù)()
'循環(huán)直到活動(dòng)單元格為空
Do While ActiveCell <> ""
ActiveCell.Offset(0, 1).FormulaR1C1 = _
ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
****************************
提示 在運(yùn)行程序前，應(yīng)該將選第二列的第一個(gè)單元格作為當(dāng)前單元格。即若想合并A1：A10和B1：B10中的數(shù)據(jù)，運(yùn)行程序前，單元格B1應(yīng)為活動(dòng)單元格。
示例文檔見(jiàn)[過(guò)程]連接相鄰兩單元格中的數(shù)據(jù).xls。UploadFiles/2006-7/71978092.rar
■ 匯總單元格區(qū)域的行單元格值和列單元格值
在Excel中，您能使用數(shù)組去計(jì)算和操作工作表中的數(shù)據(jù)。你也能使用宏程序在一個(gè)數(shù)組中存儲(chǔ)一個(gè)單元格區(qū)域中的值。這里介紹的宏程序代碼將添加一個(gè)附加列和行到一個(gè)矩形單元格區(qū)域中，并匯總這個(gè)區(qū)域中每行和每列的列數(shù)和行數(shù)。
代碼首先在活動(dòng)工作表中從活動(dòng)單元格所在的單元格區(qū)域中讀取數(shù)據(jù)，并存諸數(shù)據(jù)在數(shù)組中，然后對(duì)單元格區(qū)域中的每行和每列進(jìn)行統(tǒng)計(jì)，最后輸出到工作表中。數(shù)組的大小取決于當(dāng)前區(qū)域中單元格的數(shù)量。
注 該宏程序沒(méi)有添加任何公式到工作表中，因此，如果所統(tǒng)計(jì)區(qū)域發(fā)生改變，匯總結(jié)果不會(huì)相應(yīng)變化，故您必須重新運(yùn)行宏。
****************************
程序22(數(shù)組應(yīng)用)
Option Explicit
Sub 匯總行列值()
' 該程序假設(shè)您已在一個(gè)矩形單元格區(qū)域中選取了單元格
' 匯總數(shù)據(jù)將顯示在矩形區(qū)域右側(cè)和下面
Dim r As Integer
Dim c As Integer
Dim i As Integer
Dim j As Integer
Dim myArray As Variant
'選取包含所選單格的區(qū)域
With Selection.CurrentRegion
r = .Rows.Count
c = .Columns.Count
'用區(qū)域中的行列數(shù)定義數(shù)組大小
myArray = .Resize(r + 1, c + 1)
' 變量i代表行數(shù),變量j代表列數(shù).
'當(dāng)一行循環(huán)完后進(jìn)入下一行
For i = 1 To r
For j = 1 To c
myArray(i, c + 1) = myArray(i, c + 1) + myArray(i, 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
****************************
提示 (1)運(yùn)行該程序前，應(yīng)保證活動(dòng)單元格處于數(shù)據(jù)區(qū)域中。
(2)您能修改該程序執(zhí)行相似的操作，例如，對(duì)單元格區(qū)域進(jìn)行相減、相乘或相除等數(shù)學(xué)運(yùn)算。
示例文檔見(jiàn)[過(guò)程]匯單元格區(qū)域中的行列值.xls。UploadFiles/2006-7/71957932.rar
一點(diǎn)心得
在學(xué)習(xí)VBA的過(guò)程中，注意積累一些實(shí)有的小型程序，不僅對(duì)提高您的VBA編程水平有所幫助，而且可以在運(yùn)用時(shí)對(duì)程序作些小的調(diào)整以方便地滿足您所需的功能。這些，都絕對(duì)是值得做的。
聯(lián)系客服
