零基础学Excel Vba 应用实例

1、零基础学Excel VbaCase:系数法项目奖金分配工具,主讲：丽水职业技术学院 钭志斌 电话STEP1:明确自动化工作要求,明确自动化工作要求,STEP2:熟悉VBA编辑界面,1.进入VBA例编程,2.进入VBA编辑界面,3.熟悉VAB编辑窗口,4.代码编写区,STEP3:进入VBA编辑界面,第1讲：变量、对象,任务： 以A列为标准，获取最后一行的行号 代码简洁，易读。,Sub 语句,Sub 过程名称() End Sub,DIMAS语句,Dim 对象名 As 对象类型 Dim P2 As Worksheet Set P2 = Worksheets(02分配表) D

2、im P As Range Set P = Range(A2:AI50”),Dim 变量名 As 变量类型 Dim R As Integer R = P2.Range(A65536).End(xlUp).Row,不能确定类型时,Dim W 如果不能确定何种类型或对象，直接用Dim,MsgBox 函数,MsgBox N,同步训练,例1：,Sub J01声明工作表对象() Worksheets(STEP02 分步教学演示).Range(A1) = 零基础学EXCEL VBA & Chr(10) & 期末奖金采用贡献系数法分配的教学演示 Worksheets(STEP02 分步教学演示).Range

3、(H2) = EXCEL VBA的学习过程，是软件的开发过程，也是工作精益求精的过程！“ End Sub,例2：,Sub J01声明工作表对象() Dim P2 As Worksheet Set P2 = Worksheets(STEP02 分步教学演示) P2.Range(A1) = 零基础学EXCEL VBA & Chr(10) & 期末奖金采用贡献系数法分配的教学演示 P2.Range(H2) = EXCEL VBA的学习过程，是软件的开发过程，也是工作精益求精的过程！“ End Sub,例3：,Sub J02声明变量类型() Dim P2 As Worksheet Set P2 = W

4、orksheets(STEP02 分步教学演示) Dim R As Integer R = P2.Range(A65536).End(xlUp).Row MsgBox R P2.Range(I2) = R End Sub,不同类型的变量比较,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获取行号与列

5、号() Dim R1 As Integer R1 = Range(A65536).End(xlUp).Row MsgBox 以A65536单元格为起点，向上最后一行的行号为: & R1 End Sub,Dim R2 As Integer R2 = Range(A3).End(xlDown).Row MsgBox 以A3单元格为起点，向下最后一行的行号为: & R2,Dim C1 As Integer C1 = Range(A2).End(xlToRight).Column MsgBox 以A2单元格为起点，向右最后一列的列号为: & C1,Dim C2 As Integer C2 = Rang

6、e(AA2).End(xlToLeft).Column MsgBox 以AA2单元格为起点，向左最后一列的列号为: & C2,第2讲：对象.属性,任务： 自动设置表格边框范围； 自动调整列宽 行高20磅 边框为虚线,自动随着行数增减的技巧：&文本运算符,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 实线 xlDash 虚线 xlDashDot 长短间隔的虚线 xlDashDotDot xlDot xlDouble xlSlantDashDot xlLineStyleNone取消,对象.属性=属性的值,Range(“B101) = “合计：“ Range(A2:G100”).RowHeight = 20,设置行高与边框：对象.属性=属性的值,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,同步训练！,例5,Sub J04设置表格边框行高列宽() 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文本连接符的应用() 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语句,任务： 让代码易读 运行速度更快,简化前的代码分析,Sub J04文本连接符的应用() 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语句,With 对象 .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,同步训练！,第4讲：IF语句,任务： 根据职员类型自动设置分配系数,例8：If.Then.Else 语句,单条件 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,同步训练,第5讲：FORNEXT循环语句,任务： 自动计算所有员工的分配金额,For.Next 语句,For （起始值） To （终止值） Step 1 Next,例10,Sub J07循环语句应用() 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的应用,Sub J08CELLS单元格的应用() 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,同步训练,第6讲：综合应用（一）,任务： 根据表2中的员工性质，自动引用表1中的分配系数。,例12：多条件判断语句,Sub J09多条件判断语句的应用() 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：表间数据的引用,Sub J10表间数据的引用() Dim P As Worksheet Set P = Worksheets(01分配方案) Dim P2 As Worksheet Set P2 = Worksheets(STEP02 分步教学演示) 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,同步训练,第7讲：工作表函数的应用,任务： 自E3开始，到最后一行求和。 显示分配金额的最大值与最小值。,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工作表函数的应用() 系数合计 P2.Cells(R1 + 1, 3) = 合计 P2.Cells(R1 + 1, 5) = Application.WorksheetFunction.SUM(Range(E3:E & R1) End Sub,分配标准 Dim F F = Round(P.Range(B4) / P2.Cells(R1 + 1,

17、 5), 4) 计算分配金额 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,分配金额合计 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讲：对象.方法,任务： 删除最后两行,例15,Sub J12MsgBox信息及其它应用完善() 删除最后审核行，以免再次增加审核行内容 P2.Rows(R1 + 2).Select P2.Rows(R1 + 2).Delete P2.Cells(R1 + 1, 3) = 合计 P2.Cells(R1 + 2, 2) = 审批： P2.Cells(R1 + 2, 4) = 审核： P2.Cells(R1 + 2, 6) = 制表：“ End Sub,第9讲：常量的妙用,任务： 利用常量，简化每次对常量定义的工作量。,Const A