零基础学Excel Vba 应用实例

零基础学ExcelVbaCase:系数法项目奖金分配工具主讲：丽水职业技术学院钭志斌电话：[phone]STEP1:明确自动化工作要求丽水职业技术学院财贸管理分院13754272368.明确自动化工作要求STEP2:熟悉VBA编辑界面丽水职业技术学院财贸管理分院13754272368.1.进入VBA例编程丽水职业技术学院财贸管理分院13754272368.2.进入VBA编辑界面丽水职业技术学院财贸管理分院13754272368.3.熟悉VAB编辑窗口丽水职业技术学院财贸管理分院13754272368.4.代码编写区STEP3:进入VBA编辑界面第1讲：变量、对象丽水职业技术学院财贸管理分院13754272368.任务：以A列为标准，获取最后一行的行号代码简洁，易读。丽水职业技术学院财贸管理分院13754272368.Sub语句Sub过程名称()……EndSub丽水职业技术学院财贸管理分院13754272368.DIM…AS…语句Dim对象名As对象类型DimP2AsWorksheetSetP2=Worksheets(02分配表)DimPAsRangeSetP=Range(A2:AI50”)丽水职业技术学院财贸管理分院13754272368.Dim变量名As变量类型DimRAsIntegerR=P2.Range(A65536).End(xlUp).Row丽水职业技术学院财贸管理分院13754272368.不能确定类型时DimW•如果不能确定何种类型或对象，直接用Dim丽水职业技术学院财贸管理分院13754272368.MsgBox函数MsgBoxN丽水职业技术学院财贸管理分院13754272368.同步训练丽水职业技术学院财贸管理分院13754272368.例1：SubJ01声明工作表对象()Worksheets(STEP02分步教学演示).Range(A1)=零基础学EXCELVBA&Chr(10)&期末奖金采用贡献系数法分配的教学演示Worksheets(STEP02分步教学演示).Range(H2)=EXCELVBA的学习过程，是软件的开发过程，也是工作精益求精的过程！“EndSub丽水职业技术学院财贸管理分院13754272368.例2：SubJ01声明工作表对象()DimP2AsWorksheetSetP2=Worksheets(STEP02分步教学演示)P2.Range(A1)=零基础学EXCELVBA&Chr(10)&期末奖金采用贡献系数法分配的教学演示P2.Range(H2)=EXCELVBA的学习过程，是软件的开发过程，也是工作精益求精的过程！“EndSub丽水职业技术学院财贸管理分院13754272368.例3：SubJ02声明变量类型()DimP2AsWorksheetSetP2=Worksheets(STEP02分步教学演示)DimRAsIntegerR=P2.Range(A65536).End(xlUp).RowMsgBoxRP2.Range(I2)=REndSub丽水职业技术学院财贸管理分院13754272368.不同类型的变量比较DimR2AsCurrencyR2=P2.Range(A65536).End(xlUp).RowMsgBoxR2P2.Range(J2)=R2DimR3AsDateR3=P2.Range(A65536).End(xlUp).RowMsgBoxR3P2.Range(K2)=R3丽水职业技术学院财贸管理分院13754272368.例4：SubJ03获取行号与列号()DimR1AsIntegerR1=Range(A65536).End(xlUp).RowMsgBox以A65536单元格为起点，向上最后一行的行号为:&R1EndSub丽水职业技术学院财贸管理分院13754272368.DimR2AsIntegerR2=Range(A3).End(xlDown).RowMsgBox以A3单元格为起点，向下最后一行的行号为:&R2丽水职业技术学院财贸管理分院13754272368.DimC1AsIntegerC1=Range(A2).End(xlToRight).ColumnMsgBox以A2单元格为起点，向右最后一列的列号为:&C1丽水职业技术学院财贸管理分院13754272368.DimC2AsIntegerC2=Range(AA2).End(xlToLeft).ColumnMsgBox以AA2单元格为起点，向左最后一列的列号为:&C2第2讲：对象.属性丽水职业技术学院财贸管理分院13754272368.任务：自动设置表格边框范围；自动调整列宽行高20磅边框为虚线丽水职业技术学院财贸管理分院13754272368.自动随着行数增减的技巧：&文本运算符Range(A2:G&R+1)如果R=88那么：Range(A2:G89”)如果R=100那么：Range(A2:G101”)丽水职业技术学院财贸管理分院13754272368.Borders属性Range(A2:G89”).Borders.LineStyle=xlLineStyleNone丽水职业技术学院财贸管理分院13754272368.LineStyle属性XlLineStyle可为以下XlLineStyle常量之一。xlContinuous‘实线xlDash‘虚线xlDashDot‘长短间隔的虚线xlDashDotDotxlDotxlDoublexlSlantDashDotxlLineStyleNone‘取消丽水职业技术学院财贸管理分院13754272368.对象.属性=属性的值Range(“B101)=“合计：“Range(A2:G100”).RowHeight=20丽水职业技术学院财贸管理分院13754272368.设置行高与边框：对象.属性=属性的值P2.Range(A2:G&R+1).Borders.LineStyle=xlDashP2.Range(A2:G&R+1).RowHeight=20P2.Range(A2:G&R+1).Columns.AutoFit丽水职业技术学院财贸管理分院13754272368.同步训练！丽水职业技术学院财贸管理分院13754272368.例5SubJ04设置表格边框行高列宽()P2.Range(A2:G86).Borders.LineStyle=xlLineStyleNoneP2.Range(A2:G86).Borders.LineStyle=xlDashP2.Range(A2:G86).RowHeight=20P2.Range(A2:G86).Columns.AutoFitEndSub丽水职业技术学院财贸管理分院13754272368.例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.AutoFit…EndSub第3讲：WITH语句丽水职业技术学院财贸管理分院13754272368.任务：让代码易读运行速度更快丽水职业技术学院财贸管理分院13754272368.简化前的代码分析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.AutoFit…EndSub丽水职业技术学院财贸管理分院13754272368.WITH语句With对象•.Borders.LineStyle=xlLineStyleNone•.Borders.LineStyle=xlDash•.RowHeight=20•.Columns.AutoFitEndWith丽水职业技术学院财贸管理分院13754272368.例7WithP2.Range(A2:G&R1)•.Borders.LineStyle=xlLineStyleNone•.Borders.LineStyle=xlDash•.RowHeight=20•.Columns.AutoFitEndWith丽水职业技术学院财贸管理分院13754272368.同步训练！第4讲：IF语句丽水职业技术学院财贸管理分院13754272368.任务：根据职员类型自动设置分配系数丽水职业技术学院财贸管理分院13754272368.例8：If...Then...Else语句单条件IfP2.Range(D4)=中层正职ThenP2.Range(E4)=3.5ElseP2.Range(E4)=1EndIf丽水职业技术学院财贸管理分院13754272368.例9：If...Then...ElseIf...Then…Endif多条件IfP2.Cells(R2,4)=中层正职ThenP2.Cells(R2,5)=3.5ElseIfP2.Cells(R2,4)=中层副职ThenP2.Cells(R2,5)=2.5ElseP2.Cells(R2,5)=1EndIf丽水职业技术学院财贸管理分院13754272368.同步训练第5讲：FOR…NEXT循环语句丽水职业技术学院财贸管理分院13754272368.任务：自动计算所有员工的分配金额丽水职业技术学院财贸管理分院13754272368.For...Next语句For（起始值）To（终止值）Step1……Next丽水职业技术学院财贸管理分院13754272368.例10SubJ07循环语句应用()…ForR2=3ToR1Step1IfP2.Range(D&R2)=中层正职ThenP2.Range(E&R2)=3.5ElseP2.Range(E&R2)=1EndIfNextR2…EndSub丽水职业技术学院财贸管理分院13754272368.例11：CELLS的应用SubJ08CELLS单元格的应用()ForR2=3ToR1Step1IfP2.Cells(R2,4)=中层正职ThenP2.Cells(R2,5)=3.5ElseP2.Cells(R2,5)=1EndIfNextR2EndSub丽水职业技术学院财贸管理分院13754272368.同步训练第6讲：综合应用（一）丽水职业技术学院财贸管理分院13754272368.任务：根据表2中的员工性质，自动引用表1中的分配系数。丽水职业技术学院财贸管理分院13754272368.例12：多条件判断语句SubJ09多条件判断语句的应用()…ForR2=3ToR1Step1•IfP2.Cells(R2,4)=中层正职Then•P2.Cells(R2,5)=3.5•ElseIfP2.Cells(R2,4)=中层副职Then•P2.Cells(R2,5)=2.5•Else•P2.Cells(R2,5)=1•EndIfNextR2EndSub丽水职业技术学院财贸管理分院13754272368.例13：表间数据的引用SubJ10表间数据的引用()DimPAsWorksheetSetP=Worksheets(01分配方案)DimP2AsWorksheetSetP2=Worksheets(STEP02分步教学演示)…EndSub丽水职业技术学院财贸管理分院13754272368.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)