EXCEL VBA初步.doc

应用VBA操作EXCEL Excel对象模型中的常用对象 Application,workbooks,workbook,worksheet,range, cell 1.application对象 application.Activesheet.cells(1,2)=time msgbox activecell.value with activecell.font .bold=true .italic=true End with 2. workbook对象 Application.activeworkbook.save 3.worksheet对象 Msgbox worksheets(“sheet1”).range(“a1”) Worksheets(“sheet1”).range(“a7”).value=20 Worksheets(“sheet1”).range(“b7”).formula= “=sum(a1:a5)” 4.range对象的columns,rows属性 Set rng=activesheet.range(“c1:h26”) Msgbox rng.columns.count Msgbox rng.rows.count 6.woeksheet.range对象的cells属性 Worksheets(1).cells(1,1).value=24 Sub setuptable() Dim I as long Worksheets(“sheet1”).activate For i=1 to 5 Cells(1.I).value=i Next i End sub Worksheet(1).range(“c5:c10”).cells(1,1).formula=”=rand()” 应用实例 使用VBA合并列 Sub mergetest() Dim I as long For I = 3 to 30 Cells(I,3)=cells(I,1) cells(I,2) Next End sub 自动隐藏或者显示表格中无数据的行 Sub hidecell() Dim I as long For I = 1 to 300 If cells(I,1).value=”” then Rows(i).hidden=true End if Next i En d sub Sub shoecell() Dim I as long For i=1 to 300 I f cells(I,1).value” ” then Rows(i).hidden=false End if Next i End sub 使用VBA操作工作表单元格 Public sub writescell() [a1]=100 [a2:a4]=10 Range(“b1”)=200 Range(“c1:c3”)=300 Cells(1,4)=400 Range(cells(1,5),cells(5,5))=50 En sub Public sub readcell() Msgbox [a1] Msgbox range(“b1”) Msgbox cells(1,4) End sub Sheets(4).[a1] Sheets(4).range(“b1”) Sub roundtozero() Dim I as long Dim rcell as range For i= 1 to 20 Set rcell=worksheets(“sheet2”).cells(i,4) If isnumeric(rcell.value) then If abs(rcell.value)0.1 then Rcell.value=0 End if End if Next i End sub 查找工作表的第一个空行 Sub findempty() Dim x as long X=1 Do until (isempty(cells(x,2).value) X=x+1 Loop Msgbox “空行为” x Cells(x,2)=time En d sub 改变EXCEL界面的标题 Sub changecaption() Application.Caption = 我的EXCEL End Sub Sub mytime() Application.Caption = Now() Application.OnTime Now + TimeValue(00:00:01), mytime End Sub Application.ontime(开始运行时间，运行的过程名，运行的最晚时间，是否安排一个新的ontime过程) Applicati