应用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()” 应用实例 1. 使用VBA 合并列 Sub mergetest() Dim I as long For I = 3 to 30 Cells(I,3)=cells(I,1) & cells(I,2) Next End sub 2. 自动隐藏或者显示表格中无数据的行 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 3. 使用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 4. 查找工作表的第一个空行 Sub findempty() Dim x as long X=1 Do until (isempty(cells(x,2).va...
