最近更新:'2019-05-14'
- 子過程與模塊化程序設(shè)計(jì)
- 函數(shù)設(shè)計(jì)斑举、自定義公式
1. 子過程與模塊化程序設(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ù)的使用,具體可以參見如下截圖:
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過程調(diào)用
1.4.1過程概念
什么叫過程呢?是一段代碼,有開頭和結(jié)尾并且有自己的名字.
一般會(huì)任務(wù)sub...end sub叫做宏,但是從代碼的角度看，宏就是用VBA編寫的一個(gè)過程赚瘦。
1.4.2使用過程的優(yōu)點(diǎn)
如果程序比較復(fù)雜的話,使用宏會(huì)非常的方便,具體優(yōu)點(diǎn)如下:
1.代碼清晰,易于維護(hù)
2.靈活擴(kuò)展车摄，輕松修改
- 運(yùn)用了子過程調(diào)用寺谤，可以靈活的修改功能。
- 沒有使用子過程吮播，只能笨拙的復(fù)制粘貼变屁，沒有靈活性！
- 代碼重用意狠，提高效率
把可能被多次使用的代碼粟关，寫成過程供其他程序調(diào)用！
注意事項(xiàng):
1.調(diào)用過程的時(shí)候,可以把call忽略,直接寫過程的名稱即可.
2.函數(shù)設(shè)計(jì)环戈、自定義公式
函數(shù)：過程的一種在執(zhí)行結(jié)束后能將運(yùn)行結(jié)果返回給調(diào)用者
2.1 案例1:根據(jù)特征指數(shù),進(jìn)行分類
Function 客戶分類(score)
Dim level
If score < 30 Then
level = "甲型"
ElseIf score < 60 Then
level = "乙型"
ElseIf score < 90 Then
level = "丙型"
Else
level = "丁型"
End If
客戶分類 = level
End Function
Sub 暴力傾向分析()
Dim i, score, level
i = 3
Do While Cells(i, 3)
score = Cells(i, 3)
level = 客戶分類(score)
Cells(i, 4) = level
i = i + 1
Loop
End Sub
2.2案例2:函數(shù)開發(fā)表格公式
通過函數(shù)開發(fā)自己的表格公式,比如,在D3單元格中直接寫"=客戶分類(C3)",就會(huì)根據(jù)C3單元格的分?jǐn)?shù)直接顯示"乙型"
注意事項(xiàng):
1.不同過程中定義的變量闷板，即使同名也互相無(wú)關(guān)
2.子程序（Sub）也可以使用參數(shù)，用法與函數(shù)完全相同院塞。
3.參數(shù)可以有多個(gè)遮晚，每個(gè)之間用逗號(hào)隔開。
