1、零基礎(chǔ)學(xué)Excel VbaCase:系數(shù)法項(xiàng)目獎(jiǎng)金分配工具,主講：麗水職業(yè)技術(shù)學(xué)院 鈄志斌 電話STEP1:明確自動(dòng)化工作要求,明確自動(dòng)化工作要求,STEP2:熟悉VBA編輯界面,1.進(jìn)入VBA例編程,2.進(jìn)入VBA編輯界面,3.熟悉VAB編輯窗口,4.代碼編寫(xiě)區(qū),STEP3:進(jìn)入VBA編輯界面,第1講：變量、對(duì)象,任務(wù)： 以A列為標(biāo)準(zhǔn)，獲取最后一行的行號(hào) 代碼簡(jiǎn)潔，易讀。,Sub 語(yǔ)句,Sub 過(guò)程名稱(chēng)() End Sub,DIMAS語(yǔ)句,Dim 對(duì)象名 As 對(duì)象類(lèi)型 Dim P2 As Worksheet Set P2 = Worksheets(02分配表) D

2、im P As Range Set P = Range(A2:AI50”),Dim 變量名 As 變量類(lèi)型 Dim R As Integer R = P2.Range(A65536).End(xlUp).Row,不能確定類(lèi)型時(shí),Dim W 如果不能確定何種類(lèi)型或?qū)ο?，直接用Dim,MsgBox 函數(shù),MsgBox N,同步訓(xùn)練,例1：,Sub J01聲明工作表對(duì)象() Worksheets(STEP02 分步教學(xué)演示).Range(A1) = 零基礎(chǔ)學(xué)EXCEL VBA & Chr(10) & 期末獎(jiǎng)金采用貢獻(xiàn)系數(shù)法分配的教學(xué)演示 Worksheets(STEP02 分步教學(xué)演示).Range

3、(H2) = EXCEL VBA的學(xué)習(xí)過(guò)程，是軟件的開(kāi)發(fā)過(guò)程，也是工作精益求精的過(guò)程！“ End Sub,例2：,Sub J01聲明工作表對(duì)象() Dim P2 As Worksheet Set P2 = Worksheets(STEP02 分步教學(xué)演示) P2.Range(A1) = 零基礎(chǔ)學(xué)EXCEL VBA & Chr(10) & 期末獎(jiǎng)金采用貢獻(xiàn)系數(shù)法分配的教學(xué)演示 P2.Range(H2) = EXCEL VBA的學(xué)習(xí)過(guò)程，是軟件的開(kāi)發(fā)過(guò)程，也是工作精益求精的過(guò)程！“ End Sub,例3：,Sub J02聲明變量類(lèi)型() Dim P2 As Worksheet Set P2 = W

4、orksheets(STEP02 分步教學(xué)演示) Dim R As Integer R = P2.Range(A65536).End(xlUp).Row MsgBox R P2.Range(I2) = R End Sub,不同類(lèi)型的變量比較,Dim R2 As Currency R2 = P2.Range(A65536).End(xlUp).Row MsgBox R2 P2.Range(J2) = R2 Dim R3 As Date R3 = P2.Range(A65536).End(xlUp).Row MsgBox R3 P2.Range(K2) = R3,例4：,Sub J03獲取行號(hào)與列

5、號(hào)() Dim R1 As Integer R1 = Range(A65536).End(xlUp).Row MsgBox 以A65536單元格為起點(diǎn)，向上最后一行的行號(hào)為: & R1 End Sub,Dim R2 As Integer R2 = Range(A3).End(xlDown).Row MsgBox 以A3單元格為起點(diǎn)，向下最后一行的行號(hào)為: & R2,Dim C1 As Integer C1 = Range(A2).End(xlToRight).Column MsgBox 以A2單元格為起點(diǎn)，向右最后一列的列號(hào)為: & C1,Dim C2 As Integer C2 = Rang

6、e(AA2).End(xlToLeft).Column MsgBox 以AA2單元格為起點(diǎn)，向左最后一列的列號(hào)為: & C2,第2講：對(duì)象.屬性,任務(wù)： 自動(dòng)設(shè)置表格邊框范圍； 自動(dòng)調(diào)整列寬 行高20磅 邊框?yàn)樘摼€,自動(dòng)隨著行數(shù)增減的技巧：&文本運(yùn)算符,Range(A2:G & R + 1) 如果R=88 那么： Range(A2:G89”) 如果R=100 那么： Range(A2:G101”),Borders屬性,Range(A2:G89”).Borders.LineStyle =xlLineStyleNone,LineStyle 屬性,XlLineStyle 可為以下 XlLineSty

7、le 常量之一。 xlContinuous 實(shí)線 xlDash 虛線 xlDashDot 長(zhǎng)短間隔的虛線 xlDashDotDot xlDot xlDouble xlSlantDashDot xlLineStyleNone取消,對(duì)象.屬性=屬性的值,Range(“B101) = “合計(jì)：“ Range(A2:G100”).RowHeight = 20,設(shè)置行高與邊框：對(duì)象.屬性=屬性的值,P2.Range(A2:G & R + 1).Borders.LineStyle = xlDash P2.Range(A2:G & R + 1).RowHeight = 20 P2.Range(A2:G &

8、R + 1).Columns.AutoFit,同步訓(xùn)練！,例5,Sub J04設(shè)置表格邊框行高列寬() P2.Range(A2:G86).Borders.LineStyle = xlLineStyleNone P2.Range(A2:G86).Borders.LineStyle = xlDash P2.Range(A2:G86).RowHeight = 20 P2.Range(A2:G86).Columns.AutoFit End Sub,例6：,Sub J04文本連接符的應(yīng)用() P2.Range(A2:G & R1).Borders.LineStyle = xlLineStyleNone

9、P2.Range(A2:G & R1).Borders.LineStyle = xlDash P2.Range(A2:G & R1).RowHeight = 20 P2.Range(A2:G & R1).Columns.AutoFit End Sub,第3講：WITH語(yǔ)句,任務(wù)： 讓代碼易讀 運(yùn)行速度更快,簡(jiǎn)化前的代碼分析,Sub J04文本連接符的應(yīng)用() P2.Range(A2:G & R1).Borders.LineStyle = xlLineStyleNone P2.Range(A2:G & R1).Borders.LineStyle = xlDash P2.Range(A2:G &

10、R1).RowHeight = 20 P2.Range(A2:G & R1).Columns.AutoFit End Sub,WITH語(yǔ)句,With 對(duì)象 .Borders.LineStyle = xlLineStyleNone .Borders.LineStyle = xlDash .RowHeight = 20 .Columns.AutoFit End With,例7,With P2.Range(A2:G & R1) .Borders.LineStyle = xlLineStyleNone .Borders.LineStyle = xlDash .RowHeight = 20 .Colum

11、ns.AutoFit End With,同步訓(xùn)練！,第4講：IF語(yǔ)句,任務(wù)： 根據(jù)職員類(lèi)型自動(dòng)設(shè)置分配系數(shù),例8：If.Then.Else 語(yǔ)句,單條件 If P2.Range(D4) = 中層正職 Then P2.Range(E4) = 3.5 Else P2.Range(E4) = 1 End If,例9：If.Then.ElseIf.Then End if,多條件 If P2.Cells(R2, 4) = 中層正職 Then P2.Cells(R2, 5) = 3.5 ElseIf P2.Cells(R2, 4) = 中層副職 Then P2.Cells(R2, 5) = 2.5 Els

12、e P2.Cells(R2, 5) = 1 End If,同步訓(xùn)練,第5講：FORNEXT循環(huán)語(yǔ)句,任務(wù)： 自動(dòng)計(jì)算所有員工的分配金額,For.Next 語(yǔ)句,For （起始值） To （終止值） Step 1 Next,例10,Sub J07循環(huán)語(yǔ)句應(yīng)用() For R2 = 3 To R1 Step 1 If P2.Range(D & R2) = 中層正職 Then P2.Range(E & R2) = 3.5 Else P2.Range(E & R2) = 1 End If Next R2 End Sub,例11：CELLS的應(yīng)用,Sub J08CELLS單元格的應(yīng)用() For R2

13、= 3 To R1 Step 1 If P2.Cells(R2, 4) = 中層正職 Then P2.Cells(R2, 5) = 3.5 Else P2.Cells(R2, 5) = 1 End If Next R2 End Sub,同步訓(xùn)練,第6講：綜合應(yīng)用（一）,任務(wù)： 根據(jù)表2中的員工性質(zhì)，自動(dòng)引用表1中的分配系數(shù)。,例12：多條件判斷語(yǔ)句,Sub J09多條件判斷語(yǔ)句的應(yīng)用() For R2 = 3 To R1 Step 1 If P2.Cells(R2, 4) = 中層正職 Then P2.Cells(R2, 5) = 3.5 ElseIf P2.Cells(R2, 4) = 中層

14、副職 Then P2.Cells(R2, 5) = 2.5 Else P2.Cells(R2, 5) = 1 End If Next R2 End Sub,例13：表間數(shù)據(jù)的引用,Sub J10表間數(shù)據(jù)的引用() Dim P As Worksheet Set P = Worksheets(01分配方案) Dim P2 As Worksheet Set P2 = Worksheets(STEP02 分步教學(xué)演示) End Sub,For R2 = 3 To R1 Step 1 If P2.Cells(R2, 4) = P.Range(a7) Then P2.Cells(R2, 5) = P.Ra

15、nge(B7) ElseIf P2.Cells(R2, 4) = P.Range(a8) Then P2.Cells(R2, 5) = P.Range(B8) ElseIf P2.Cells(R2, 4) = P.Range(a9) Then P2.Cells(R2, 5) = P.Range(B9) Else P2.Cells(R2, 5) = 1 End If Next R2,同步訓(xùn)練,第7講：工作表函數(shù)的應(yīng)用,任務(wù)： 自E3開(kāi)始，到最后一行求和。 顯示分配金額的最大值與最小值。,Application.WorksheetFunction,Application.WorksheetFunc

16、tion.Sum(Range(E3:E89”) Application.WorksheetFunction.Min(Range(E3:E89”) Round(P.Range(B4) / P2.Cells(R + 1, 5), 2),例14,Sub J11工作表函數(shù)的應(yīng)用() 系數(shù)合計(jì) P2.Cells(R1 + 1, 3) = 合計(jì) P2.Cells(R1 + 1, 5) = Application.WorksheetFunction.SUM(Range(E3:E & R1) End Sub,分配標(biāo)準(zhǔn) Dim F F = Round(P.Range(B4) / P2.Cells(R1 + 1,

17、 5), 4) 計(jì)算分配金額 For R2 = 3 To R1 Step 1 P2.Cells(R2, 6) = F P2.Cells(R2, 7) = P2.Cells(R2, 5) * P2.Cells(R2, 6) Next,分配金額合計(jì) P2.Cells(R1 + 1, 7) = Application.WorksheetFunction.SUM(Range(G3:G & R1) Dim MAX01, MIN02 MAX01 = Application.WorksheetFunction.MAX(Range(G3:G & R1) MIN01 = Application.Workshee

18、tFunction.Min(Range(G3:G & R1) MsgBox 最高分配金額為： & MAX01 & Chr(10) & 最小分配金額為： & MIN01,第8講：對(duì)象.方法,任務(wù)： 刪除最后兩行,例15,Sub J12MsgBox信息及其它應(yīng)用完善() 刪除最后審核行，以免再次增加審核行內(nèi)容 P2.Rows(R1 + 2).Select P2.Rows(R1 + 2).Delete P2.Cells(R1 + 1, 3) = 合計(jì) P2.Cells(R1 + 2, 2) = 審批： P2.Cells(R1 + 2, 4) = 審核： P2.Cells(R1 + 2, 6) = 制表：“ End Sub,第9講：常量的妙用,任務(wù)： 利用常量，簡(jiǎn)化每次對(duì)常量定義的工作量。,Const A