最近更新:'2019-05-14'
- 子過(guò)程與模塊化程序設(shè)計(jì)
- 函數(shù)設(shè)計(jì)、自定義公式
1. 子過(guò)程與模塊化程序設(shè)計(jì)
1. 1案例1:計(jì)算每張成績(jī)表的C2單元格數(shù)字
將工作表張三,李四,王五,趙六各科的成績(jī)求和分別匯總到對(duì)應(yīng)工作表的C2單元格
相關(guān)的代碼如下:
Sub sumAll()
Dim i, j, s
Dim w1 As Worksheet
For i = 1 To Worksheets.Count
Set w1 = Worksheets(i)
s = 0
For j = 2 To 10
s = s + w1.Cells(j, 2)
Next j
w1.Cells(2, 3) = s
Next i
End Sub
代碼最終的結(jié)果如下圖所示:
1. 2案例2:導(dǎo)入總分榜
將張三,李四,王五,趙六的工作表的C2,B2單元格分別匯總到總分榜工作表,具體如下截圖
相關(guān)的代碼如下:
Sub allscore()
Dim wPerson As Worksheet, wAll As Worksheet, i, k
Set wAll = Worksheets("總分榜")
k = 2
For i = 1 To Worksheets.Count
Set wPerson = Worksheets(i)
If wPerson.name <> "總分榜" Then
wAll.Cells(k, 1) = wPerson.Cells(1, 2)
wAll.Cells(k, 2) = wPerson.Cells(2, 3)
k = k + 1
End If
Next i
End Sub
1. 3案例1和案例2的合并:計(jì)算每張成績(jī)表的C2單元格數(shù)字,導(dǎo)入總分榜
以下是使用不同的方法,但是結(jié)果是一樣的
1. 3.1方案1
Sub allscore()
Dim i, k, j, s
Dim wPerson As Worksheet, wAll As Worksheet
Set wAll = Worksheets("總分榜")
k = 2
For i = 1 To Worksheets.Count
Set wPerson = Worksheets(i)
s = 0
For j = 2 To 10
s = s + wPerson.Cells(j, 2)
Next j
wPerson.Cells(2, 3) = s
If wPerson.name <> "總分榜" Then
wAll.Cells(k, 1) = wPerson.Cells(1, 2)
wAll.Cells(k, 2) = wPerson.Cells(2, 3)
k = k + 1
End If
Next i
End Sub
1. 3.2方案2
使用call調(diào)用函數(shù)
Sub sumAll()
Dim i, j, s
Dim w1 As Worksheet
For i = 1 To Worksheets.Count
Set w1 = Worksheets(i)
s = 0
For j = 2 To 10
s = s + w1.Cells(j, 2)
Next j
w1.Cells(2, 3) = s
Next i
End Sub
Sub allscore()
Dim wPerson As Worksheet, wAll As Worksheet, i, k
Call sumAll
Set wAll = Worksheets("總分榜")
k = 2
For i = 1 To Worksheets.Count
Set wPerson = Worksheets(i)
If wPerson.name <> "總分榜" Then
wAll.Cells(k, 1) = wPerson.Cells(1, 2)
wAll.Cells(k, 2) = wPerson.Cells(2, 3)
k = k + 1
End If
Next i
End Sub
call調(diào)用函數(shù)的使用,具體可以參見(jiàn)如下截圖:
1. 3.3方案3
Option Explicit
Sub 匯總及登記()
Call sumAll
Call allscore
End Sub
Sub sumAll()
Dim i, j, s
Dim w1 As Worksheet
For i = 1 To Worksheets.Count
Set w1 = Worksheets(i)
s = 0
For j = 2 To 10
s = s + w1.Cells(j, 2)
Next j
w1.Cells(2, 3) = s
Next i
End Sub
Sub allscore()
Dim wPerson As Worksheet, wAll As Worksheet, i, k
Set wAll = Worksheets("總分榜")
k = 2
For i = 1 To Worksheets.Count
Set wPerson = Worksheets(i)
If wPerson.name <> "總分榜" Then
wAll.Cells(k, 1) = wPerson.Cells(1, 2)
wAll.Cells(k, 2) = wPerson.Cells(2, 3)
k = k + 1
End If
Next i
End Sub
1.4過(guò)程調(diào)用
1.4.1過(guò)程概念
什么叫過(guò)程呢?是一段代碼,有開(kāi)頭和結(jié)尾并且有自己的名字.
一般會(huì)任務(wù)sub...end sub叫做宏,但是從代碼的角度看，宏就是用VBA編寫(xiě)的一個(gè)過(guò)程。
1.4.2使用過(guò)程的優(yōu)點(diǎn)
如果程序比較復(fù)雜的話(huà),使用宏會(huì)非常的方便,具體優(yōu)點(diǎn)如下:
1.代碼清晰,易于維護(hù)
2.靈活擴(kuò)展，輕松修改
- 運(yùn)用了子過(guò)程調(diào)用，可以靈活的修改功能。
- 沒(méi)有使用子過(guò)程，只能笨拙的復(fù)制粘貼，沒(méi)有靈活性！
- 代碼重用，提高效率
把可能被多次使用的代碼，寫(xiě)成過(guò)程供其他程序調(diào)用！
注意事項(xiàng):
1.調(diào)用過(guò)程的時(shí)候,可以把call忽略,直接寫(xiě)過(guò)程的名稱(chēng)即可.
2.函數(shù)設(shè)計(jì)、自定義公式
函數(shù)：過(guò)程的一種在執(zhí)行結(jié)束后能將運(yùn)行結(jié)果返回給調(diào)用者
2.1 案例1:根據(jù)特征指數(shù),進(jìn)行分類(lèi)
Function 客戶(hù)分類(lèi)(score)
Dim level
If score < 30 Then
level = "甲型"
ElseIf score < 60 Then
level = "乙型"
ElseIf score < 90 Then
level = "丙型"
Else
level = "丁型"
End If
客戶(hù)分類(lèi) = level
End Function
Sub 暴力傾向分析()
Dim i, score, level
i = 3
Do While Cells(i, 3)
score = Cells(i, 3)
level = 客戶(hù)分類(lèi)(score)
Cells(i, 4) = level
i = i + 1
Loop
End Sub
2.2案例2:函數(shù)開(kāi)發(fā)表格公式
通過(guò)函數(shù)開(kāi)發(fā)自己的表格公式,比如,在D3單元格中直接寫(xiě)"=客戶(hù)分類(lèi)(C3)",就會(huì)根據(jù)C3單元格的分?jǐn)?shù)直接顯示"乙型"
注意事項(xiàng):
1.不同過(guò)程中定義的變量，即使同名也互相無(wú)關(guān)
2.子程序（Sub）也可以使用參數(shù)，用法與函數(shù)完全相同。
3.參數(shù)可以有多個(gè)，每個(gè)之間用逗號(hào)隔開(kāi)。
