零基础学Excel Vba 应用实例.ppt

零基础学ExcelVbaCase:系数法项目奖金分配工具,主讲：丽水职业技术学院钭志斌电话STEP1:明确自动化工作要求,明确自动化工作要求,STEP2:熟悉VBA编辑界面,1.进入VBA例编程,2.进入VBA编辑界面,3.熟悉VAB编辑窗口,4.代码编写区,STEP3:进入VBA编辑界面,第1讲：变量、对象,任务：以A列为标准，获取最后一行的行号代码简洁，易读。,Sub语句,Sub过程名称()EndSub,DIMAS语句,Dim对象名As对象类型DimP2AsWorksheetSetP2=Worksheets(02分配表)DimPAsRangeSetP=Range(A2:AI50”),Dim变量名As变量类型DimRAsIntegerR=P2.Range(A65536).End(xlUp).Row,不能确定类型时,DimW如果不能确定何种类型或对象，直接用Dim,MsgBox函数,MsgBoxN,同步训练,例1：,SubJ01声明工作表对象()Worksheets(STEP02分步教学演示).Range(A1)=零基础学EXCELVBA&Chr(10)&期末奖金采用贡献系数法分配的教学演示Worksheets(STEP02分步教学演示).Range(H2)=EXCELVBA的学习过程，是软件的开发过程，也是工作精益求精的过程！“EndSub,例2：,SubJ01声明工作表对象()DimP2AsWorksheetSetP2=Worksheets(STEP02分步教学演示)P2.Range(A1)=零基础学EXCELVBA&Chr(10)&期末奖金采用贡献系数法分配的教学演示P2.Range(H2)=EXCELVBA的学习过程，是软件的开发过程，也是工作精益求精的过程！“EndSub,例3：,SubJ02声明变量类型()DimP2AsWorksheetSetP2=Worksheets(STEP02分步教学演示)DimRAsIntegerR=P2.Range(A65536).End(xlUp).RowMsgBoxRP2.Range(I2)=REndSub,不同类型的变量比较,DimR2AsCurrencyR2=P2.Range(A65536).End(xlUp).RowMsgBoxR2P2.Range(J2)=R2DimR3AsDateR3=P2.Range(A65536).End(xlUp).RowMsgBoxR3P2.Range(K2)=R3,例4：,SubJ03获取行号与列号()DimR1AsIntegerR1=Range(A65536).End(xlUp).RowMsgBox以A65536单元格为起点，向上最后一行的行号为:&R1EndSub,DimR2AsIntegerR2=Range(A3).End(xlDown).RowMsgBox以A3单元格为起点，向下最后一行的行号为:&R2,DimC1AsIntegerC1=Range(A2).End(xlToRight).ColumnMsgBox以A2单元格为起点，向右最后一列的列号为:&C1,DimC2AsIntegerC2=Range(AA2).End(xlToLeft).ColumnMsgBox以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可为以下XlLineStyle常量之一。xlContinuous实线xlDash虚线xlDashDot长短间隔的虚线xlDashDotDotxlDotxlDoublexlSlantDashDotxlLineStyleNone取消,对象.属性=属性的值,Range(“B101)=“合计：“Range(A2:G100”).RowHeight=20,设置行高与边框：对象.属性=属性的值,P2.Range(A2:G&R+1).Borders.LineStyle=xlDashP2.Range(A2:G&R+1).RowHeight=20P2.Range(A2:G&R+1).Columns.AutoFit,同步训练！,例5,SubJ04设置表格边框行高列宽()P2.Range(A2:G86).Borders.LineStyle=xlLineStyleNoneP2.Range(A2:G86).Borders.LineStyle=xlDashP2.Range(A2:G86).RowHeight=20P2.Range(A2:G86).Columns.AutoFitEndSub,例6：,SubJ04文本连接符的应用()P2.Range(A2:G&R1).Borders.LineStyle=xlLineStyleNoneP2.Range(A2:G&R1).Borders.LineStyle=xlDashP2.Range(A2:G&R1).RowHeight=20P2.Range(A2:G&R1).Columns.AutoFitEndSub,第3讲：WITH语句,任务：让代码易读运行速度更快,简化前的代码分析,SubJ04文本连接符的应用()P2.Range(A2:G&R1).Borders.LineStyle=xlLineStyleNoneP2.Range(A2:G&R1).Borders.LineStyle=xlDashP2.Range(A2:G&R1).RowHeight=20P2.Range(A2:G&R1).Columns.AutoFitEndSub,WITH语句,With对象.Borders.LineStyle=xlLineStyleNone.Borders.LineStyle=xlDash.RowHeight=20.Columns.AutoFitEndWith,例7,WithP2.Range(A2:G&R1).Borders.LineStyle=xlLineStyleNone.Borders.LineStyle=xlDash.RowHeight=20.Columns.AutoFitEndWith,同步训练！,第4讲：IF语句,任务：根据职员类型自动设置分配系数,例8：If.Then.Else语句,单条件IfP2.Range(D4)=中层正职ThenP2.Range(E4)=3.5ElseP2.Range(E4)=1EndIf,例9：If.Then.ElseIf.ThenEndif,多条件IfP2.Cells(R2,4)=中层正职ThenP2.Cells(R2,5)=3.5ElseIfP2.Cells(R2,4)=中层副职ThenP2.Cells(R2,5)=2.5ElseP2.Cells(R2,5)=1EndIf,同步训练,第5讲：FORNEXT循环语句,任务：自动计算所有员工的分配金额,For.Next语句,For（起始值）To（终止值）Step1Next,例10,SubJ07循环语句应用()ForR2=3ToR1Step1IfP2.Range(D&R2)=中层正职ThenP2.Range(E&R2)=3.5ElseP2.Range(E&R2)=1EndIfNextR2EndSub,例11：CELLS的应用,SubJ08CELLS单元格的应用()ForR2=3ToR1Step1IfP2.Cells(R2,4)=中层正职ThenP2.Cells(R2,5)=3.5ElseP2.Cells(R2,5)=1EndIfNextR2EndSub,同步训练,第6讲：综合应用（一）,任务：根据表2中的员工性质，自动引用表1中的分配系数。,例12：多条件判断语句,SubJ09多条件判断语句的应用()ForR2=3ToR1Step1IfP2.Cells(R2,4)=中层正职ThenP2.Cells(R2,5)=3.5ElseIfP2.Cells(R2,4)=中层副职ThenP2.Cells(R2,5)=2.5ElseP2.Cells(R2,5)=1EndIfNextR2EndSub,例13：表间数据的引用,SubJ10表间数据的引用()DimPAsWorksheetSetP=Worksheets(01分配方案)DimP2AsWorksheetSetP2=Worksheets(STEP02分步教学演示)EndSub,ForR2=3ToR1Step1IfP2.Cells(R2,4)=P.Range(a7)ThenP2.Cells(R2,5)=P.Range(B7)ElseIfP2.Cells(R2,4)=P.Range(a8)ThenP2.Cells(R2,5)=P.Range(B8)ElseIfP2.Cells(R2,4)=P.Range(a9)ThenP2.Cells(R2,5)=P.Range(B9)ElseP2.Cells(R2,5)=1EndIfNextR2,同步训练,第7讲：工作表函数的应用,任务：自E3开始，到最后一行求和。显示分配金额的最大值与最小值。,Application.WorksheetFunction,Application.WorksheetFunction.Sum(Range(E3:E89”)Application.WorksheetFunction.Min(Range(E3:E89”)Round(P.Range(B4)/P2.Cells(R+1,5),2),例14,SubJ11工作表函数的应用()系数合计P2.Cells(R1+1,3)=合计P2.Cells(R1+1,5)=Application.WorksheetFunction.SUM(Range(E3:E&R1)EndSub,分配标准DimFF=Round(P.Range(B4)/P2.Cells(R1+1,5),4)计算分配金额ForR2=3ToR1Step1P2.Cells(R2,6)=FP2.Cells(R2,7)=P2.Cells(R2,5)*P2.Cells(R2,6)Next,分配金额合计P2.Cells(R1+1,7)=Application.WorksheetFunction.SUM(Range(G3:G&R1)DimMAX01,MIN02MAX01=Application.WorksheetFunction.MAX(Range(G3:G&R1)MIN01=Application.WorksheetFunction.Min(Range(G3:G&R1)MsgBox最高分配金额为：&MAX01&Chr(10)&最小分配金额为：&MIN01,第8讲：对象.方法,任务：删除最后两行,例15,SubJ12MsgBox信息及其它应用完善()删除最后审核行，以免再次增加审核行内容P2.Rows(R1+2).SelectP2.Rows(R1+2).DeleteP2.Cells(R1+1,3)=合计P2.Cells(R1+2,2)=审批：P2.Cells(R1+2,4)=审核：P2.Cells(R1+2,6)=制表：“EndSub,第9讲：常量的妙用,任务：利用常量，简化每次对常量定义的工作量。,C