还剩54页未读，继续阅读
本资源只提供10页预览，全部文档请下载后查看！喜欢就下载吧，查找使用更方便
文本内容:
之学EXCEL VBA习笔记姓名刘磊时间9MsgBox”存在”End IfEnd Sub打开指定目录下的文献2Sub打开指定目录下的文献Dim a$,n!,wbs As Workbooka=DirMc:\*.txtnWorkbooks.Open c:\naDoa=DirIfao H ThenWorkbooks.Open c:VaElseExit SubEnd IfLoopEnd Sub工作簿的表达措施5在workbook对象中，有一种SHEETS集合，其组员是worksheet对象或chart对象worksheets仅指的是工作表，而sheets包括图表，工作表，宏表等等VBA中，常常在工作表之间转换或者对不一样工作表中的单元格区域进行操作.一般有下面几种措施1Sub直接使用工作表名称法MsgBox Worksheets我的工作表.NameMsgBox Sheets我的图表.NameEnd Sub2Sub索引号表达法MsgBox Worksheets
1.NameEnd Sub3Sub工作表代码索引号表达法MsgBox Sheetsl.NameEnd Sub4Sub直接取工作代码法0MsgBox Sheet
1.NameEnd Sub5Sub活动工作表MsgBox ActiveSheet.NameEnd Sub当工作簿包括工作表、宏表、图表等时，注意使用索引号引用工作表如Sheetsl与WorkSheetsl引用的也许不是同一种表Sub worksheetssMsgBoxWorksheets
1.NameMsgBox Sheetsl.NameEnd SubSubsheetssFor i=1To Sheets.CountMsgBox Sheetsi.NameNextEnd Sub工作表集合的应用61Sub遍历sheets卜的所有对象For Each shs In Sheetsk=k+1Cellsk,1=shs.NameNextEnd Sub2Sub遍历worksheets下的所能对象0For Eachshs InWorksheetsk=k+1Cellsk,2=shs.NameNextEnd Sub3Sub工作表存在与否Dim sn$For Eachsht InSheetssn=sht.NameIf sn二”我的工作表”ThenMsgBox”存在“Exit SubEnd IfNextMsgBox不存在”End Sub4Sub工作表存在与否1Dim sn$For i=1To Sheets.Count Sheets.Count指sheet里面的数量a=Sheetsi.NameIf Sheetsi.Name二我的工作表ThenMsgBox”存在”Exit SubEnd IfNextMsgBox”不存在”End Sub工作表的增长与删除7Sheets.Add措施体现式.Add（Before,After,Count,Type）XISheetType常量之一xlWorksheet工作表xlChart图表xlExcel4MacroSheet宏表xlExcel4IntlMacroSheet对话框默认值为xlWorksheetSub新建sheets（）Sheets.Add（默认在活动工作表之前添加一种工作表）Sheets.Add Sheets（Habc）（在工作表名为ABC的工作表之前添加一种工作表）Sheets.Add,Sheets（Habc）（在工作表名为ABC的工作表之后添加一种工作表）Sheets.Add after:=Sheets（HabcH）（与上式等价）Sheets.Add Count:=2（在活动工作表前添加两个工作表）Sheets.Add,,2（与上式等价）Sheets.Add,,,xlChart（添加图表）End SubSub删除工作表0Sheet lO.DeleteEnd Sub工作表的删除与添加8假如想批量新建工作表，可以成果循环来制作Sub新建1到12月份的工作表（）Dimj%For j=12To1Step-1Sheets.Add.Name二j月”NextEnd Sub删除工作表Sub删除sheet（）On Error Resume Next（当出现错误时候忽视错误）Application.DisplayAlerts=False（当屏幕有警告提醒时候忽视启动）Dim i%For i=1To12Sheets”月）.DeleteNextApplication.DisplayAlerts=True（当屏幕有警告提醒时候忽视关闭，否则，下次运行代码时候仍旧是忽视关闭状态）End Sub工作表的移动与复制9工作表的复制1体现式.copyBefore,AfterSub复制0Sheet
1.Copy SheetsSheets.CountEnd Sub工作表的移动2体现式.MoveBefore,AfterSub移动Sheet
1.Move,Sheet3End Sub工作表的选择与激活10措施不支持隐藏选用Worksheet.SelectWorksheet.Activate措施支持隐藏选用迅速选择所有工作表1SubWorksheets.Select只选择工作表Sheets.Select工作表，图表等所有选择End Sub2Sub自定义选择0WorksheetsArray1,3,
5.SelectEnd Sub拆分工作簿实例11:Sub拆分到工作簿Dim wkAs Workbook,ss$,k%申明wk为一种工作簿类型变量Application.Display Alerts=FalseFor Eachsht InWorkbooks”2-
11.工作簿综合运用拆分工作簿”.SheetsSet wk=Workbooks.Add wk为一种对象，对象的措施为添加工作表k=k+1Workbooks
1.Sheetsk.Copy Workbooks
2.Sheets1ss=ThisWorkbook.Path\nsht.Name.xlsxnwk.SaveAs sswk.CloseNextApplication.DisplayAlerts二TrueMsgBox”拆分工作簿完毕！”End Sub第三章单元格区域操作对象1range单元格对象在VBA中一种非常基础，同步也很重要的它的体现方式也是非常的多样化Range对象代表某一单元格、某一行、某一列、某一选定区域该区域可包括一种或若干持续单元格区域，或者某一三维区域Range“文本型装单元格地址”range的常见写法Sub rngRangeaI.Select单元格Rangea:an.Select歹URangel:
3.Select行Rangeal:blOu.Select相邻区域Rangeal:d7,c4:e8H.Select不相个邻区域Rangeal:d7c4:e
8.Select相交的区域End Sub的其他写法2rangeRangeal:blOn.Select一般写法Range“al“，“blO.Select变化写法1RangeRan enaln,RangeCblO.Select变化写法2以便后来可以使用变量替代gRangear,=123给单元格赋值注意
1.假如在range前没有指定工作表，则默认为活动工作表
2.假如对象不是活动工作表如活动图表，则会出现错误Sub单元格对象例子，计数工作表最大的行数Debug.Print Rangea:a.C iintDebug.Print意思是在活动窗口中显示出来Debug.Print Range:1H.Count计算工作表最大的列数Debug.Print Application.CountARangea:a”计算工作表已使用的行数Debug.Print Application.CountARangeH1:1计算工作表已使用的列数End Sub变量与引用3range的变化写法1rangel:range“地址区域”.range地址区域”Sub序号表达法0Rangeb2:d
4.Range“b
2.Select湘对弓|用的写法参照前一种range的左上单元格End Sub2:
2.range地址区域中支持变量Sub range的变量支持Dim a%a=3Rangenaa.SelectRangec3:e
52.SelectEnd Sub3:动态引用实例Sub实例1动态选单元格或区域Dim i%i=Application.CountARangec:c，找到c列中已使用的最终一种单元格位置Ranged”i.Select选择C列最终一格Range“al，ci.Select选择A1到C列的最终一格措施一1^^贝匕1:d58阻选择人1到C列的最终一格措施二小结动态单元格区域的定位，可以应用到单据的保留等实际工作中End Sub引用与索引4Rangerange区域中的每个单元格，我们也可以用索引号表达出来写法单元格区域”行号，列号rangedSub索引号取出range的单元格Rangel:c
44.Select引用次序是从左向右，从上到下选用Range”b2:c
43.Select此前一种单元格区域为照Rangenal:c4n
4.
5.Select当有小数时，则取整注意假如索引号出现小数，则按照“四舍六入五单双”的“银行家舍入法”End SubSub行列号定位Rangeal:c4n3,
2.Select运用行号与列号定位Rangeal:c4n
1.5,
2.
5.Select行列号也可以使用小数单元格的引用5cellscells单元格引使用方法写法cells行号，列号Sub cells基本写法Cells3,
4.Select行列号均为数字Cells2,Mc.Select行为数字，列为列标字母Cells.Select全选End Subcells可以像range同样可以参照前面的单元格位置Sub参照写法Rangeb3:fll.Cells2,
2.SelectRangcb3:fll.Ccns
6.Select从左到右，从上到下Rangeb3:flln
6.Select与上一句相等End Sub注意Icells中的数字同样支持正数，负数，0值，小数四舍六入五单双2ceHs不能像range同样可以引用一种区域，只能引用一种单元格单元格简写6除了前面讲的rangecells单元格区域的表达措施还，还是一种简朴的写法写法:[单元格地址]注意中括号中的单元格地址并不需要双引号””Sub单元格简写单元格引用[a3].Select,单元格区域引用[b2:c6].SelecL多区域引用[a3,b2:c6,b8:dl2].SeIect，整列引用[a:a].SelectH l].Select整行引用End Sub单元格简写的也支持引用子集Sub子集引用0[b2:c6J.Item
3.SelectRangeb2:c6H
3.Select[b2:c6].Cells
4.SelectEnd SubSub动态区域的引用a=Application.CountA[a:a]b=Application.CountA[1:1]RangeRangenaln,RangeChr64+ba.Select，运用chr函数，让字母形式的列号也支持变量End SubSubchr函数字符循环For i=1To65535Cellsi,l=iCellsi,2=Chri NextEnd Sub三种单元格引用小结7功能[单元格地址]Range Cells引用对象单兀格，区域，行，列单兀格单兀格，区域，行，列变量支持支持支持不支持书写难易难难易Rangeal:cni.Select引用单元格是区域且有变量Cellsi,ncn.Select引用的是单个单元格且有变量[al:l9].Select弓用的是区域或单元格且无变量行列的引用8行列引用Sub列引用Columnsl.SelectColumns,bu.Select b列Columnsnc:e.Select c至U e列End SubSub行引用0Rows I.Select Rows
2.Select Rows,,3:4,,.Select3至4行End SubSubrange行列表式法0Rangel:l.Select第一行Range2:
4.Select2到4行Rangea:au.Selecta列Rangeb:d.Select B至ij D歹ijEnd SubSub简写法[a:a].Select[b:d].Select[l:l].Select[2:4].Select End SubSub全选Rows.Select选择所有行Columns.Select选择所有列Cells.Select选择所单元格i=Rows.Count j=Columns.Count k=Cells.Count End SubSub动态引用使用区域0a=Application.CountAColumns1返回第一列当中使用的非空单元格数目b=Application.CountARowsl返回第一行中使用的非空单元格数目Rangenar\Cellsa,b.Select动态引用单元格End Sub与属性9:row column属性Range.Row返回区域中第一种子区域的第一行的行号属性,Range.Column返回指定区域中第一块中的第一列的列号Sub testi=RangeHa3:b
9.Rangena5H.Row返回A3到B9区域的第一行第五列所在单元格位置的真实行号j=Rangea3:b
9.Rowi二Rangenb3:d9n.Rangena5n.Columnj=RangeHb3:d9M.ColumnEnd Sub实例应用Sub rowFor Each rwIn Rows1:13If rw.Row Mod2=0Thenrw.RowHeight=5将偶数行的行高设置为5,其中mod为求余函数End IfNextrwEnd Sub单元格的地址与值10单元格的值表达措施Sub单元格值表达0a=[al[Value实际是什么，就是什么b=[al].Text看到是什么,就是什么c=[al]End Sub，注意一种单元格可以省略value,多单元格区域不能省略Sub多区域赋值Rangeel:e4n=RangeHdl:d4n.ValueEnd Sub单元格地址与引用Sub地址与引用Set rng=[b2:⑵’绝对引[a9]=rng.Addressl,1用[b9]=rng.AddressO,0湘对引用[c9]=rng.Address1,0混合引用[d9]=rng.AddressO,1混合引用End Sub总结代表固定绝对引用，代表不固定，默认是绝对引用10Sub地址引用实例，将表三成绩中为空的单元格标为未考Dim rng As Range,rn$On Error Resume NextFor Each rng InSheet
3.Rangenb2:d10If rng=,,HThenrn=rnrng.Address”JNext符RangeLeftrn,Leng-1二未考left函数返回从左开始取字符串中，Leng-1长度个字End Sub单元格的移动与复制11U.Range.Cut措施将单元格区域剪切到指定的区域
2.Range.Copy措施将单元格区域复制到指定的区域会复制该单元格的值和格式Sub移动复制0Rangeal:d8H.Cut RangenflHRangefl:i8H.Copy RangealnEnd Sub运用单元格赋值的措施也可以完毕复制操作，在此措施中只会复制单元格的值，不会复制格式Sub另类复制措施0Rangeal0:dl7=RangeHal:d
8.ValueEnd Sub注1等号后的区域一定要加value.否则不成功2被赋值的区域格式所有去掉工作表中单元格的删除与插入12工作表中单元格，行与列的插入与删除Sub插入Rows
3.Activate，激活指定单元格End SubSelection光标所选区域Sub光标所选区域Selection=1光标所选区域的每一种单元格的值赋为1End SubSub在selection中的变化活单元格For i=1To Selection.CountSelectioni.Activate激活所选区域单元格NextEnd SubSub运用Dim i As RangeFor Each iIn SelectionIfi=”Ori二“缺勤”Theni=xHEnd IfNext iEnd Sub小结selection的好处在于，可以很自由灵活选择你想要处理的单元格区域已使用区域条件记录14UsedRangeWorksheet.UsedRange属性返回一种Range对象，该对象表达指定工作表上所使用的区域Sub已使用区域Sheet
1.UsedRange.SelectEnd Sub，注意已使用区域的定位措施是已使用的最小单元格最大单元格假如单元格中无内容，但设定了格式，也认为是已使用区域‘假如没有已使用单元格，则默认为A1单元Sub usedrange应用For Each Rng InSheet
1.UsedRangeIf IsNumericRngAnd Rng=90Then k=k+1Next RngMsgBox”不小于等于90分的人数为”k“人End Sub小结U.usedrange自动计算已用区域的所有值2不用当数据增长时的处理问题3比selection以便，但不够灵活属性15currentregionRange.CurrentRegion属性返回一种Range对象，该对象表达目前区域返回以目前单元格说扩展后的单元格区域Sub目前区域[a l].CurrentRegion.Select[f8].CurrentRegion.SelectEnd SubSubcurrentregion应用Rows
8.Cleara=[b2].CurrentRegion.Addressb=[b5].CurrentRegion.Addressc=[b2].CurrentRegion.Count+1Set c=Rangenb8\Cells8,cc.FormulaArray二二ab此为一数组公式，formulaArray为数组公式End Subusedrange与currentregion假如表中只有一种区域，两者最终的成果是同样的只是体现方式不一样样Sub u与cfal].CurrentRegion.SelectEnd Sub单元格的偏移属性16offsetRange.Offset属性返回Range对象，它代表位于指定单元格区域的一定的偏移量位置上的区域体现式Qffset偏移行，偏移列体现式一种代表Range对象的变量彳扁移行列的数字可以是正数，负数，零值Sub test[al].Offsetl,
2.Select彳亍列都偏移[a l].Offset
2.Select1只偏移行[al].Offset,
2.Select，只偏移列假如offset前面的range对象是一种区域，则偏移后也成果尺寸不变[al:dl].Offsetl,
2.Select[al:d l].0ffset
2.Select[al:dl].Offset,
2.SelectEnd SubSub offset应用1Dim i%For i=2To8Step2[al:el].Copy|al:el].OffsetiNext iEnd SubSuboffset应用2Dim i%For i=2To8Step2[al:el].Offseti=Next iEnd Sub单元格的属性（单据数据保17resize,Range.Resize属性调整指定区域的大小返回Range对象，该对象代表调整后的区域语法体现式.Resize（行数,列数）体现式一种返回Range对象的体现式Sub test（）[al].Resize2,
3.SeIect[a l].Resize
2.Select[al].Resize,
3.SelectEnd SubSub保留Dim i%,j%,k%i=[a1].CurrentRegion.Rows.Count-1k=AppIication.CountASheet
2.Columns1[a2].Resizei,j.Copy Sheet
2.[a1J.OffsetkEnd Sub单元格所在的行和列18Range.EntireRow属性返回一种Range对象，该对象表达包括指定区域的整行或多行语法体现式.EntireRow体现式一种代表Range对象的变量Range.EntireColumn属性，返回一种Range对象，该对象表达包括指定区域的整列（或多列）语法体现式.EntireColumn体现式一种代表Range对象的变量Sub test[a1].EntireRow.Select[al].EntireColumn.Select[al:a4].EntireRow.Select[al:dl].EntireColumn.SelectEnd SubSub test1Dim rng As Range,ads AsStringFor Eachrng In[aka10]If rng=n Then ad=adrng.Address Nextads二Leftad,Lenad-1Rangeads.EntireRow.DeleteEnd Sub定位条件19Range.SpecialCells措施返回一种Range对象，该对象代表与指定类型和值即配的所有单元格语法体现式.SpecialCellsType,Value体现式一种代表Range对象的变量Sub批注汇总0MsgBox Application.SumSelection.SpecialCells-4144End SubSub删除空行On ErrorGoTo100Selection.SpecialCellsxlCellTypeBlanks.SelectSelection.EntireRow.DeleteExit Sub100:MsgBox”没有空行”End Sub查找措施20findRange.Find措施’在区域中查找特定信息语法体现式.FindWhat,After,Lookin,LookAt,SearchOrder,SearchDirection,MatchCase,MatchByte,SearchFormat体现式一种代表Range对象的变量Sub查找最终一种单元格Set endrng=Cells.Find*H,,xlFormulas,,,xlPreviousRange[al],endrng.SelectEnd Sub查找系统21findSub查询系统ends=Columnsl.Findn**\,,,,searchdirection:=xlPrevious.Row动态找到A列的最终一种单元格Range”g3:
1999.Clear精除之前所有的筛选成果F^atchByteFor EachRng InRangeHa2:aends TRIIF•辨早llm=m+1If RngLike RangenhrThen，假如条件成立，那么k=k+1RangeHaHm+1”:em+l.Copy RangegHk+2尸将记录复制到另一种区域End IfNextEnd Sub进销存之入库单22Sub开单Set es=Cells.Find*,,xIFormulas,,,xlPreviousa=es.Addresslb2J=HSMFormatNow,nymdhmsRange[a5],es.Offset4=nn[e2]=nEnd SubSub保留On ErrorGoTo100Dim esAs Range,a%IfSheet
2.[f:f].Find[b2]=[b2]ThenMsgBox”已经保留过了!”Else100:Set es=Cells.Find*\,xlFormulas,,,xlPreviousa=Application.CountASheet
2.[a:a]If es.Row=4Then MsgBox”没有填写内容”EndRange[a5],es.Copy Sheet
2.Cellsa+1,1Sheet
2.Cellsa+1,T.Resizees.Row-4=[b2-保留入库单Sheet
2.Cellsa+1,ngn.Resizees.Row-4=[e2「保留供应商Sheet
2.Cellsa+1,nhH.Resizees.Row-4=Now保留H期时间MsgBox”保留成功!”End IfEnd SubSub计算Set es=Columns
3.Find,,*»,xlFormulas,,,xlPreviousFor EachRng InRange|c5],esRng.Offset0,2=Rng.Offset0,1*RngNextEnd Sub单元格属性23endSub宏5Selection.EndxlDown.SelectSelection.EndxlToRight.SelectSelection.EndxlUp.SelectSelection.EndxlToLeft.SelectEnd SubSub分期付款最终月i=CellsRows.Count,
1.EndxlUp.Row，找至!j A列的最终一行的行号Rangen2H,Cellsi,Mn=将最终付款月下的区域清空For j=2To ik=Cells，n.EndxlToLeft.Column找到最终付款月所在的列号Cells，n=Cellsl,k，将对应的月份填入对应的单元格Next jEnd Sub查找最终一种单元格的种措施24N这里讨论怎样找到最终一种单元格！不考虑最终量个单元格是是公式，错误值，隐藏之类的特殊状况’以最终是一种常规的值为准且以A列的最终一种单元格为准Sub最终的单元格0a=CellsRows.Count,
1.EndxlUp.Row end属性b=Columnsl.Findn*»,,,,xlPrevious.Row find措施c=Cells.SpecialCellsxlCellTypeLastCell.Row specialcells措施d=Sheet
1.UsedRange.Rows.Count usedrange属性e=[al].CurrentRegion.Rows.Count currentregion属性f=WorksheetFunction.CountA[a:a]工作表函数countag=Application.CountIf[a:a],工作表函数countifEnd Sub单元格的合并25,Application.Union措施，返回两个或多种区域的合并区域Sub testRangeal:b3,c5:d
8.Select文当地址引用方式Union[al:b3],[c5:d8].Select，单元格区域引用方式End Sub，小结虽然range也可以完毕多区域的引用，但文当地址的引用方式最多不能超过256个字符，而union却没有这个限制我们常常运用变量与union进行单元格的连接Sub连接符单元格连接Dim rngAs RangeFor Each rngs In[b2:b0]adss二mgs.Addressads=adsmgs.Address Nextad=Leftads,Lenads-1End SubSubunion单元格连接Dim rngAs Range,rngs As RangeSet rng=[b2]For EachrngsIn[b2:bl0]adss=mgs.AddressSet rng=Unionrng,rngsads=rng.AddressNextEnd Sub单元格的交集26Application.Intersect措施返回一种Range对象，该对象表达两个或多种区域重叠的矩形区域Sub testIfIntersect[al:dlO],Selection Is Nothing Then注释Is Nothing用于判断对象与否存在,对象可以是工作簿，工作表，单元格区域等MsgBox”没有交集”ElseMsgBox Intersect[al:dlO],Selection.AddressIntersect[al:dlO],Selection.SelectEnd IfEnd SubSub隔行插入0For i=0To Application.CountAColumns1*2Step2Intersect[al:d2].Offseti,[a2:d3].Offseti.EntireRow.InsertNextEnd Sub单元格数字格式的设置代码27Range.NumberFormatLocal属性Sub获取单元格设置数字格式For EachRng In[al:a3]CellsRng.Row,2=Rng.NumberFormatLocal，获取单元格的格式代码Next RngEnd SubSub给单元格设置数字格式0For EachRng In[al:a3]Rng.NumberFormatLocal=
0.00”Next RngEnd SubSub保留1111Set es=Cells.Find*，，xIFormulas,,,xlPrevious a=Application.CountAWorksheets记录保留Range|a5],es.Copy Worksheets记录保留.Cellsa+1,1With Worksheets记录保留.Cellsa+
1.Resizees.Row-4=[b2「保留入库单.Cellsa+l,Hg.Resizees.Row-4=[e2「保留供应商.Cellsa+1,nhu.Resizees.Row-4=Now保留日期时间.Cellsa+1,nh.Resizees.Row-
4.NumberFormatLocal=e-m-d aaaaEndWithMsgBox”保留成功!”End Sub单元格字体格式设置代码实现28,Font对象，包括对象的字体属性字体名称、字号、颜色等等^ange.ClearFormats措施清除对象的格式设置，常见font对象的属性Sub font对象属性With[a2:a6].Font.Name二“微软雅黑字体.Size=8字号.Bold=True咖粗.Color=RGB255,0,255颜色End WithEnd SubSub不小于90分的颜色设置为红色Set i=CellsRows.Count,
3.EndxlUpRangelb2],i.ClearFormatsFor EachRng InRanged b2],iIf Rng.Value=[fl]ThenWith Rng.Font.Name=”华文琥珀”.Size=
20.Bold=True.Color=RGB255,0,0End WithEnd IfNext RngEnd Sub底纹颜色的设置29,Interior对象代表一种对象的内部针对interior对象，我们用得最多的是它的颜色，下面就来讨论一下Sub索引颜色值For i=1To56Cellsi,l.Interior.Color!ndex=iCellsi,2=iNext iEnd SubSub初期颜色值0Fori=0To15Cellsi+1,l.Interior.Color=QBColoriCellsi+l,2=iNext iEnd SubSub三原色颜色值oCells2,
4.Interior.Color=RGB[al],[bl],[cl]End SubSub直接颜色值此颜色有255八3种颜色Cellsl,l.Interior.Color=[bl]End Sub单元格格式设置实例30Sub格式化工资条0Dim i%i=CellsRows.Count,l.EndxlUp.RowFor j=1To iIfjMod2ThenWith Cellsj,l.EntireRow.Rangeal:gl.Font.Bold=True.Size=
8.Colorindex=56End WithElseWithCellsj,
1.EntireRow.RangeHa1:g1n.Interior”以本行第一种单元格为坐标的AlG1”区域.Colorindex=40End WithEnd IfNext jEnd SubSub清除格式化Selection.ClearFormatsEnd Sub运用查找颜色功能拾取颜色求平均31Sub根据查找功能拾取的颜色求平均On ErrorGoTo100Dim erngAs Range,rngAs Range,iAsLong i二Application.FindFormat.Interior.Color运用查找颜色的功能返回拾取的颜色Set erng=CellsRows.Count,e.EndxlUpFor Eachmg InRange|b2],erngIf rng.Interior.Color=i Thenk=k+rng.Value:n=n+1NextMsgBox”最终平均分为”k/n”分”End100:MsgBox”查找功能没有拾取到颜色！”第一章基础知识VBA代码协助1F1代码换行下划线+空格+回车.常用代码操作中的对象23excel、工作簿第个工作簿工作簿名”1Workbooks WorkbooksN NWorkbooks Active活动工作簿代码所在工作簿、工作表Workbook ThisWorkBook2Worksheets第个工作表SheetsN N工作表名”Sheets第个工作表活动工作表与的区别SheetN NActiveSheet worksheetsSheets、单元格3cells单元格地址”Range行号，列号Cells单元格简写[A1]活动单元格Activecell目前被选用的区域Selection4常量与变量常量常量是定义了之后就不做变化了
1.常量定义格式常量名=常量体现式Const.变量:在定义之后还能再次赋值2变量定义格式变量变量类型Dim As数据类型5类型注释简写占用内存Integer整型%2ByteSingle单精度4ByteDouble双精度8ByteLong长整型4ByteString定长或变长变长字符串最多可包括大概字符型中的常见数据类型
1.VBA亿人个字符20231长字符串可包括1到大概64K2八16个字符货币型条件语句Currency6:if@8Byte.单行形式11If…Then条件判断条件成立成果If Then注意在单行形式中，按照判断的成果也可以执行多条语句If...ThenEnd Sub粘贴32Option ExplicitWorksheet.Paste措施将“剪贴板”中的内容粘贴到工作表上体现式.PasteDestination,Link‘体现式一种代表Worksheet对象的变量Sub粘贴RangeB l:B6H.Copy Rangenc9nRangenBl:B
6.Copy复制区域无公式Sheet
1.Paste Range“c9”，粘贴至U c4单元格End SubSub粘贴2Rangec1:c
6.Copy复制单元格有公式Sheet
3.Paste假如不指定Destination参数，则在使用该措施之前必须选择目的区域End SubSub粘贴1Rangec1:C6M.Copy，复制cl单元格Sheet
3.Paste,True Application.CutCopyMode=TrueEnd Sub选择性粘贴33Option ExplicitRange.PasteSpecial措施，将Range从剪贴板粘贴到指定的区域中语法体现式.PasteSpecialPaste,Operation,SkipBlanks,Transpose体现式一种代表Range对象的变量Sub选择怪粘贴0Rangec2:cl
0.CopyRanged2n.PasteSpecial12End SubSub选择怪粘贴运算0Rangeb2:b9n.CopyRanged
2.PasteSpecial,2Rangec2:c9n.CopyRanged
2.PasteSpecial,2End SubSub选择怪粘贴跳过空单元Rangeb2:b9n.CopyRangee
2.PasteSpecial,,TrueEnd SubSub选择性粘贴转置0Rangea2:b
9.CopyRangeal
1.PasteSpecial12,,,TrueEnd Sub名称值说明复制的数据与目标单元格中的值柜瓶xlPasteSpecialOperationAdd2复制的数据赊以目标单元格中的值.xlPasteSpecialOperationDivide5复制的数据素以目标单元格中的值xlPasteSpecialOperationMultiply40粘中不执行任何计算xlPasteSpecialOperationNone-4142复制的数据减去目标单元格中的值xlPasteSpecialOperationSubtract3合并单元格34Option ExplicitRange.Merge措施由指定的Range对象创立合并单元格Sub合并单元格Selection.MergeEnd SubSub合并单元格实例Dim er%,mg%,rg As RangeApplication.Display Alerts=False er=Application.CountA[a:a]For rng=er To2Step-1Set rg二RangenarngIf rg=rg.Offset-l Thenrg.Offset-
1.Resize
2.MergeNextApplication.Display Alerts=TrueEnd Sub合并单元格实例与取消合并单元格!35,Range.MergeArea属性返回一种Range对象，该对象代表包括指定单元格的合并区域,Range.UnMerge措施将合并区域分解为独立的单元格Sub testa=RangeHar.MergeArea.Count[al J.UnMergeEnd SubSub解除合并单元格后保持本来的数据Dim b%,rngAs RangeFor Eachrng InSelectionrng.UnMergerng.Resizeb=rngNextEnd Sub有条件的添加批注36Comment对象代表单元格批注批注添加Sub批注添加0With[al]If.Comment IsNothing Then.AddComment.Text H123n.Comment.Visible=TrueEnd IfEndWithEnd SubSub删除批注For EachRng InSelectionIf NotRng.Comment IsNothing ThenRng.ClearCommentsEnd IfNextEnd SubSub批量添加批注For EachRng InRangea2:a20uRng.ClearCommentsIf Rng=90Then Rng.AddComment.Text“优秀”NextEnd Sub修改批注注意文献地址的书写方式37修改批注Sub修改批注0Rangea
2.AddComment添力口批注[a2].Comment.Shape.Height=50，设置批注高度[a2].Comment.Shape.Width=40设置批注宽度[a2J.Comment.Shape.Fill.UserPicture ThisWorkbook.Path\7pic\阿汤.pag”End SubSub批量将批注增长背景0For EachRng InSelectionpaths=ThisWorkbook.Path\7pic\Rng.Value.pngRng.ClearCommentsRng.AddCommentRng.Comment.Shape.Height=50Rng.Comment.Shape.Width=40设置批注宽度Rng.Comment.Shape.Fill.UserPicture pathsNextEnd Sub图形基础38Shapes对象，指定的工作表上的所有Shape对象的集合阐明每个Shape对象都代表绘图层中的一种对象，如自选图形、任意多边形、图片、图表等Sub abcDimob AsShapen=Sheet
1.Shapes.CountFor Eachob InSheet
1.Shapesk=k+1ob.SelectCellsk+1/T=ob.Name，图形名称Cellsk+1,g”=ob.Type，图形类型Cellsk+1,h=ob.Top，顶端坐标Cellsk+1,i=ob.Left，左端坐标Cellsk+=ob.Width喷度Cellsk+l,kH=ob.Height，高度Next obEnd SubSub图形插入0Sheet
3.Shapes.AddPicture ThisWorkbook.Path\7pic\林志玲.png”,_True,True,100,100,70,70End SubSub图形删除0For Eachshp InSheet
3.Shapesshp.DeleteNext shpEnd Sub图形的插入应用实例39Sub宏1For Eachshap InSheet
1.ShapesIf sh叩.Type8Then shap.DeleteNext shapFor EachRng In Rangeda2],CellsApplication.CountAColumns1,1i=ThisWorkbook.PathM\7pic\RngH,pngHSet mgs=CellsRng.Row,2Sheet
1.Shapes.AddPicture i,True,True,rngs.Left,rngs.Top,rngs.Width,rngs.HeightNext RngEnd Sub多表合并40Option ExplicitSub多表合并0Dim i%,rs%,rss%,st As Worksheet,zst AsWorksheetSet zst=Sheetsl季度将汇总工作表“1季度”定义为变量zstFor i=1To3Set st=Sheetsi”月将1-3每月的工作表定义为变量strs=st.UsedRange.Rows.Count计算1-3月份每个表的最终一行rss=zst.UsedRange.Rows.Count+1计算”1季度的最终一行的下一行”st.Rangena2:brs.Copy zst.Cellsrss,11-3表的数据到总表中zst.Cellsrss,
3.Resizers-1=i”月,-将1-3表的工作表名写入到总表对应的记录中NextEnd Sub多表拆分41Sub多表拆分0Forf=lTo3彳盾环三次只拆分三个月二f月”新建工作表，并以月份命名For EachRng InSheets”总表”.Rangea2:al5”If Rng.Value二f”月The晨假如a列的值等于目前的月份n二aRng.Row”:dRng.Row构造被复制的源表区域y=y+1新表行数合计If y=1ThenSheets总表”.Rangeal:dl”.Copy Sheetsf”月Cells，1End IfSheets总表”.Rangen.Copy Sheetsf“月Cellsy+1,1厂则将目前月份所在行复制到新建月份表中End IfNexty=0分表行数合计归零NextEnd Sub第四章:事件程序:事件程序定义与作用1:1定义七xcel事件程序由于一种操作（动作）而触发了一段程序让程序发生了运行就像机关设置2实例，例1打动工作表1,则运行一段程序（选择工作表1触发程序），例2保护工作表数据实例3作用，excel事件程序的作用此前途序只能通过手工运行或指定宏功能来完毕’事件程序则可以却因不一样的操作而自动触发运行不一样的程序4代码位置fexcel事件程序代码寄存在位置事件类型代码位置工作表事件工作表，工作簿事件工作簿（thisworkbook），程序事件工作簿（thisworkbook）或类模块事件程序基础2代码存储位置事件代码位置应用程序-根据应用程序的动作进行控制类模块或thisworkbook对所有工作簿操作的有关事件工作簿-根据工作簿的动作进行控制thisworkbook对所有工作表操作的有关事件工作表-根据工作表的动作进行控制sheet l.sheet
2....对工作表中所有单元操作的有关事2括号里面是参数（传回值使用方法）匕rget:传递单元格对象（例子严禁选择）七h:传递工作表对象（例子新建工作表时提醒更更名称）3代码保护措施:工具-VBAProject属性-保护应用程序事件注释NewWorkbook当新建一种工作簿时发生此事件SheetActivate当激活任何工作表时发生此事件SheetBeforeDoubleClick在双击任何工作表前发生此事件SheetBeforeRightClick右键单击任何工作表前发生此事件在重新计算工作表时发生此事件SheetCalculate更改任何工作表的单元格时发生此事件SheetChangeSheetDeactivate当工作表失去焦点时发生此事件（离动工作表时）SheetFollowHyperlink SheetPivotTableUpdate在单击工作簿中的任何超链接时发生在更新数据透视表的工作表后发生所选内容SheetSelectionChange WindowActivate在任何工作表上更改时发生在激活任何工作簿窗口时发生工作簿的窗口变为非活动状态时，将产生本领件WindowDeactivateWindowResize变化工作簿窗口大小时发生WorkbookActivate当激活任何工作簿时发生此事件WorkbookAddinlnstall工作簿为加载宏安装时发生此事件WorkbookAddinUninstall当任一工作簿作为卸载宏时发生Workbook AfterXmlEmport在保留或导出工作簿中的XML数据之后发生此事件当刷新既有的XML数据链接或新的XML数据被导入任一打开的Excel工作簿之后日、发WorkbookAfterXmllmport生关闭任何工作簿前发生此事件WorkbookBeforeCloseWorkbookBeforePrint在打印工作簿前发生此事件WorkbookBeforeSave在保留任彳可工作簿前发生引事件WorkbookBeforeXmlExport保留或导出XML数据前发生的事件当刷新既有的XML数据链接或新的XML数据被导入任一打开的Excel工作簿之前不发WorkbookBeforeXmllmport生当打开的工作簿转为非活动状态时发生此事件WorkbookDeactivateWorkbookNewSheet在任何打开的工作簿中新建工作表时发生此事件当打开一种工作簿时发生此事件WorkbookOpen在数据透视表的链接关闭之后发生此事件WorkbookPivotTableCloseCloseConnectionWorkbookPivotTableOpenCloseConnection在数据透视表的链接打开之后发生此事件假如顾客在OLAP数据透视表上深化记录集或调用行集操作，则会发生WorkbookRowsetCompletdWorkbookRowsetComplete事件当作为“文档工作区”一部分的工作簿的当地副本与服务器上的副本进行同步时发4此Workbooksync事件工作簿事件激活工作簿、工作表、图表工作表或嵌入式图表时发生此事件Activate当工作簿作为加载宏安装时，发生此事件Addininstall当工作簿作为加载宏卸载时，发生此事件AddinUninstall在Excel保留或导出指定工作簿中的XML数据之后发生此事件AfterXmlExport在刷新既有的XML数据链接或将新的XML数据导入到指定的Excel工作簿之后，》生此事件AfterXmlimport在关闭工作簿之前，先产生此事件假如该工作簿己经更改正，则本领件在问询顾选与否保留更改之前产生BeforeClose在打印指定工作簿（或者其中的任何内容）之前，发生此事件BeforePrint保留工作簿之前发生此事件BeforeSave在Excel保留或导出指定工作簿中的XML数据之后发生此事件BeforeXmlExport在刷新既有的XML数据链接或将新的XML数据导入到指定的Excel工作簿之后，》生此事件图表、工作表或工作簿被停用时发生此事件BeforeXmlimportNewSheet当在工作簿中新建工作表时发生此事件OD peaec ntivate打动工作簿时，发生此事件PivotTableCloseConnection数据透视表关闭与其数据源的链接后发生此事件PivotTableOpenConnection数据透视表打开与其数据源的链接后发生此事件RowsetComplete假如顾客在OLAP数据透视表上深化记录集或调用行集操作，则会引起此事件SheetActivate当激活任何工作表时发生此事件SheetBeforeDoubleClick当双击任何工作表时发生此事件，此事件先于默认的双击操作发生SheetBeforeRightClick右键单击任一工作表时发生此事件，此事件先于默认的右键单击操作SheetCalculate在重新计算工作表时或在图表上绘制更改的数据之生发生此事件当顾客或外部链接更改了任何工作表中的单元格时发生此事件当SheetChange任何工作表停用时发生此事件SheetDeactivateSheetFollowHyperlink单击Excel中的任何超链接时发生此事件SheetPivotTableUpdate在数据透视表的工作表更新之后发生此事件任一工作表的选定区域发生更改时，将发SheetSelectionChange生此事件Sync当作为“文档工作区”一部分的工作表的当地副本与服务器上的副本进行同步时，为生此事件Window ActivateWindowDeactivate任何工作簿窗口调整大小时发生此事件工作簿窗口被停用时发生此事件WindowResize任何工作簿窗口被停用时发生此事件工作表事件注释Activate激活工作簿，工作表，图表等发生的事件BeforeDoubleClick在工作表中双击前发生的事件BeforeRightClick右键单击工作表前发生的事件Calculate工作表重新计算之后发生的事件Change更改工作表中的单元格发生的事件Deactivate工作表，图表停用（焦点离开）时发生的事件单击工作表上的任意超链接时，发生此事件工作簿中的数据FollowHyperlink透视表更新后发生此事件当工作表上选定区域发生变化时发生此事件PivotTableUpdateSelectionChange工作表事件实例（自选计算与投票记录）31Private Sub Worksheet_SelectionChangeByVal TargetAs RangeDimrsrs=Application.CountAColumns1If Target.Address=RangeCakars.Address ThenFor i=1To rsCellsi,2==Cellsi,1NextEnd IfEnd SubPrivate Sub Worksheet_SelectionChangeByVal TargetAs RangeOn ErrorResumeNextIf Target.Address=$A$3Or Target.Address=H$B$3n_Or Target.Address=$C$3Or Target.Address=$D$3n ThenTarget.Value=Target.Value+1End IfEnd Sub工作表事件实例42当选择的单元格地址显示在状态栏上措施一Private Sub Worksheet_SelectionChangeByVal TargetAs RangeApplication.StatusBar二”目前选择的区域是Target.AddressO,0End SubPrivate SubWorksheet_DeactivateApplication.StatusBar=End Sub当选择的单元格地址显示在状态栏上措施一Private SubWorksheet_SelectionChangeByVal TargetAsRangeApplication.StatusBar=目前选择的区域是Selection.Address0,0End SubPrivate SubWorksheet_DeactivateApplication.StatusBar=”目前选择的区域是”End SubPrivate SubWorksheet_SelectionChangeByVal TargetAs RangeIf Sheet
3.Name”成绩表Then Sheet
3.Name=成绩表”End Sub工作表事件实例自动列出工作表名与单元格区域保护5Private SubWorksheet_ActivateFor Eachsht InSheetsIf sht.Name”整年月份”Thenk=k+1Sheets“整年月份Cellsk,1=sht.NameEnd IfNext所有语句必须在同一行上并且以冒号分开？例子Sub testIf110Thena=a+l:b=l+a:c=l+bEndSub单行形式条件判断条件成立条件不成立例子
2.1If ThenElseSub test2If11Then MsgBoxyes ElseMsgBox noEndSub3•块形式If・・・Their・・End条件判断If Then条件成立成果EndIf例子Sub test3If1110Thena=1+ab=1+ac=1+bEnd IfEndSub.块形式的嵌套4If条件判断If Then成立时的成果条件判断Elself Then成立时的成果Else不成立时的成果EndIf例子:Private SubWorksheet_SelectionChangeByVal TargetAs RangeIfIntersectTarget,[al:cl2]IsNothingThenMsgBox你只能在[al:cl2]区域中工作！”[al].SelectEnd IfEndSub工作簿事件实例（自动选择月份表、右键禁用、打印控制）61事件中的Cancel:，默认为false,在完毕事件内代码效果后，接着继续完毕操作的后续效果，而为true时，在完毕事件内代码效果后，终止目前操作的后续效果相称于给顾客控制事件提供一种开关可以把顾客自定义事件替代默认事件，没有Cancel就是说不给你这个控制权限，一旦一始就要按流程结束Private Sub Workbook_Open mon=FormatNow,m Sheetsmon”月H.Select End SubPrivate SubWorkbook_SheetBeforeRightClickByVal ShAs Object,ByVai TargetAsRange,Cancel AsBooleanCancel=True禁用右键End SubPrivateSubWorkbook_BeforePrintCancel AsBooleanIf MonthNow“月ActiveSheet.Name ThenMsgBox”不能打印”Cancel=TrueElseMsgBox”能打印”Cancel=FalseEnd IfEndSub工作簿事件实例给工作表加密码72Application.EnableEvents属性^pplication.EnableEvents=True/FALSE假如对指定对象启用事件，则该属性值为TrueBoolean类型，可读写作用临时关闭，防止死循环PrivateSubWorkbook_Open^pplication.EnableEvents=True,Sheet
2.Cells.Font.Color二RGB255,255,255,Sheet
3.ActivateEnd SubPrivateSub Worksheel_Activate檄活工作表触发的程序a=InputBox请输入密码”If a=123ThenCells.Font.Color=RGB0,0,0Application.EnableEvents=FalseElseSheet
3.ActivateEnd IfEndSub应用程序事件8应用程序事件是对每个打动工作簿操因操作所发生的事件程序应用程序事件代码位置thisworkbook或者类模块,应用程序事件代码在thisworkbook中的存在的先决条件
1.申明变量Public WithEventsapp AsExcel.Application
2.工作簿打开时运行PrivateSubWorkbook_OpenSet app=Excel.ApplicationEnd Sub
3.将
1、2点的代码写在thisworkbook中，并保留为“加载宏”文献xla,xlam
4.在加载宏菜单中加载第三步保留的加载宏文献1目的任何时候都能依附在excel文献中,例子任何时候都不能增长工作表应用程序事件实例9PrivateSub excelapp_SheetSelectionChangeByVal ShAs Object,ByVai TargetAsRange4-8课内容Application.StatusBar=选择区域是Target.AddressO,0任意工作表显示选择区域地址EndSubPrivateSub excelapp_NewWorkbookByVal WbAs WorkbookApplication.Dialogs
5.Show弓虽制新建就保留EndSubPrivateSubexcelapp_WorkbookBeforePrintByVal WbAs Workbook,Cancel AsBoolean4-8课内a=InputBox（”请输入打印密码）打印机规定输入打印密码Ifa=123ThenCancel=FalseElseCancel=TrueMsgBox”对不起，密码不对的，你不能打印！”End IfEndSub事件总结
101.多种事件的作用范围工作表作用于某个工作表下的所有单元格操作工作簿作用于某个工作簿下的所有工作表应用程序:作用于所有工作簿
2.多种事件过程代码的执行次序工作表事件一工作薄事件一应用程序事件第五章数组VBA数组基础
11.数组概念数组就是一种列表或者一组数据表.
12.数组位置数组存储在内存中.
3.数组特点a.读写速度快从内存读取数据要比从硬盘读取快b.无法永远保留内存只是暂存空间
4.数组分类a.一般分为常量数组，静态数组，动态数组b.如按维度为1维，2维,3维……60维
5.当我们学会了数组，会发现此前的写的诸多代码可以从数组的角度重写Sub test2Dim arrl3Dim arr2l To3Dim arr3l To3,1To2Dim arr43,2EndSub数组维度21数组最多有60维，但在excel中一般最到2维
11.excel中的一行或一列可以转换1维数组
2.excel中的多行多列可以转换成2维数组Sub test1arrl=arr2=Application.Transpose[{1;2;3;4}J【转置】End SubSub test2arr3=[{“张王“2”陈“3“李”,4;林15}]End SubSubtest4arr=Array1,2,3,4arrl=ArrayArraynaH,b,r,Arrayl,2,3EndSub向数组中写数据3Sub循环向数组中写入数据Dim arrl To4For EachRngIn[al:a4]n=n+1arrn=RngEnd SubSub常量数组数据写入一般数组Dim arran*=ArrayV「B，A,9End SubSub单元格区域数据批量写入数组0an*二Application.Transpose[al:a4]，注意当向数组中批量写入多行，成果就是二维数组arrl=Application.TransposeApplication.Transpose[a7:d7]EndSub在数组中取数据4:，怎样在数组中取数Sub取数组中指定位置的元素arr=[a2:a5]MsgBox arr2,1MsgBox arr4,1EndSubSub措施1循环取数0arr=[a2:a9][bl]=arr2,1For i=1To8Cellsi,3=arri,1NextEnd SubSub措施2一次性取数0arr=[a2:a5JRangedl:d4=arrEnd SubSub用transpose函数转置arr=[a2:a5]arrl=Application.Transposearr[a7:d7]=arrl[a8:c8]=arrl[a9:e9]=arrl，注意左右两边尺寸的对应【赋值时候是从左向右】EndSub1组运用I据汇总在数组中求和，平均，最大，最小，极大，极小值等Sub testarr=[b2:c9],MsgBox WorksheetFunction.SumarrMsgBox WorksheetFunction.Averagearr,MsgBox WorksheetFunction.MaxarrMsgBox WorksheetFunction.Minarr,MsgBox WorksheetFunction.Smallarr,2MsgBox WorksheetFunction.Largearr,2EndSubSubtest2Dim arrll To99arr=[b2:c9]ForEacha InarrIf a=80Thenn=n+1arrln=aEnd IfNextMsgBoxWorksheetFunction.Averagean-1EndSub数组写入与读取实例数组的优势体现6比比哪个程序的运行速度更快Sub措施一t=TimerSet Rng=CellsRows.Count,l.EndxlUparr=Rangefal],RngFor Eacha InRange[al],RngIf a=90Then n=n+1:Cellsn,3=aNextMsgBox FormatTimer-1,”
0.0000”EndSubSub措施二t=TimerDim arrllTo5000,1To1Set Rng=CellsRows.Count,l.EndxlUparr=Range[al],Rng数组写入ForEacha InarrIf a=90Thenn=n+1arrln,1=a嘴arr数组中的数据有条件的将数据写入arrl数组End IfNext[dl].Resizen=arrlMsgBox FormatTimer-1,”
0.0000数组应用之计算不反复值7,UBound函数UBoundCarraynamet,dimension]返回一种Long型数据，其值为指定的数组维可用的最大下标Sub testDim arr4To8,1To3,1To9MsgBox UBoundarr可简写为UBoundarrMsgBox UBoundarr,2MsgBox UBoundarr,3MsgBox LBoundarr,LBound用来确定数组某一维的上界EndSubSub运用数组提取不反复值Dim arrllTo10Set lastcell=CeHsRows.Count,bEndxlUp，查找最终B列最终一种非空单元格arr=Rangefb2],lastcell将B列的姓名数据赋值给变量arr形成一种数组For i=1To lastcell.Row-1，循环B列单元格个数的次数For j=1To UBoimdarrl找到arrl数组的最大小标,形成循环x=arri,1:y=arrlj辅助代码Ifarri,1=arrlj ThenGoTo100arr数组元素与arrl元素循环对比，假如相等，则跳出内层循环EndIfNextjk=k+l做个计数器，计算相等反复的元素人数arrlk=arri,1假如循环完后都没有相等的，则将arrl循环的元素赋值给arrl数组100:Next i[e2].Resizek=Application.Transposearr1彳盾环结束后将arrl的成果赋值给单元格区域EndSub数组运用分类求和8Sub运用数组提取不反复值Dim arrllTo10,1TO2Set endr=CeHsRows.Count,c.EndxlUp，查找最终B列最终一种非空单元格arr=Range[b2],endr，将B列的姓名数据赋值给变量arr形成一种数组For i=1To endr.Row■「循环B列单元格个数的次数Forj=1To UBoundarrl1找到arrl数组的最大小标，形成循环x=arri,1:y=arrlj,1辅助代码Ifarri,l=arrlj,lThenarrlj,2=arri,2+arrlj,2GoTlOCTarr数组元素与arrl元素循环对比，假如相等，则跳出内层循环EndIfNextjk=k+r做个计数器，计算相等反复的元素人数arrlk,1=arri,1假如循环完后都没有相等的，则将arrl循环的元素赋值给arrl数组arrlk,2=arri,2100:Nexti[e2].Resizek,2=arr「循环结束后将arrl的成果赋值给单元格区域EndSub动态数组条件筛选9,dim,ReDim语句在过程级别中使用，用于为动态数组变量重新分派存储空间ReDim[Preserve]varname[As type]可以使用ReDim语句反复地变化数组的元素以及维数的数目，有redim之后可以确定数组的上界，而不用估计一种值Subtest3Dim arr,arrlrn=CellsRows.Count,
1.EndxlUp.Address【定位最终一行】arrl=Rangeua2H,rnm=WorksheetFunction.CountlfCRangeCa2,rn,=80,确定重新定义数组的上界【匚作表函数】ReDim arrl To mForEach arIn arrlIf ar=80Thenn=n+1arrn=arEnd IfNextfc2].ResizeUBoundarr=Application.Transposearr10动态数组多表合并EndSub,Preserve可选的关键字，当变化原有数组最末维的大小时，使用此关键字可以保持数组中木来的数据Sub abcDim arri=9arr=[{1,2,3]ReDim PreservearrlTo5ReDim PreservearrlToi EndSubSub数组多表合并0Dim arrForEachshIn SheetsIfsh.Nameo”汇总”Then目的只有“汇总”工作表的数据不合并c=sh.Namen!sh.RangeHa2:bsh.UsedRange.Rows.Count.Address协助代码arrl=sh.Rangea2:bnsh.UsedRange.Rows.Count,将各表的数据循环写入arrl数组中act=act+UBoundarrl，累加各表数据的行数，作为重新申明arrl数组的上界ReDim Preservearr1To2,1To act，重新申明数组arr【变量只能放在最背面，变化最终一种维度】For j=1To UBoundarrl准备将各表的数据循环写入重新申明的arr数组中n=n+「每条件即将要写入arr数组的记录数累加arrl,n=arrlj,1larrl对应写入arr中，此写入的措施需要在单元格中演示一下arr2,n=arrlj,2*arrl对应写入arr中NextEnd IfNextSheets汇总”.[a2].Resizen,2=Application.Transposearr arr中的数据批量写入单元格EndSub函数与数组字符串生成数组11SplitSplit函数作用于1维数组返回一种下标从零开始的一维数组，它包括指定数目的子字符串Split字符串,“分隔符”Sub testDim i$i=a-b-c-d-e-f arr=Spliti,[al].Resizel,UBoundarr=arr EndSubSub数据互换0arr=[al].CurrentRegion【以Al单元格为扩展的单元格】ForEacha Inarrarrl=Splita,n=n+1Cellsn,nc=arrll-narr10NextEndSub函数与数组数组生成字符串12JoinJoin函数作用于1维数组返回一种字符串，该字符串是通过连接某个数组中的多种子字符串而创立的Join数组，连接符”Subtestarr=[{1,2,3,41i=Joinarr,-MEnd SubSub数据合并0i=Sheet
3.CellsRows.Count,l.EndxlUp.Row[寻找最终一行】With Sheet3For j=1To iSetk=Range.Cellsj,1,.Cellsj,Columns.Count.EndxlToLeft【寻找一行最终一种非空单元格，返回一种range对象】arr=Application.TransposeApplication.Transposek【k为一种4列的二维数组，转置两次后变为一种一维数组】Cells，1=醇Joinarr,,H,【前添加逗号，默认的返回一种字符串，否则会以字符串表达，超过12位就会以科学计数法表达】Next jEndWithEnd Sub函数与数组巧用实现多列成果13FilterFilter函数作用于1维数组返回一种下标从零开始的数组，该数组”包括“基于指定筛选条件的一种字符串数组的子集语法Filter要搜索的1维数组，搜索的字符串,[TRUE/FALSE]Sub testarr=[{”abc“Jbb“Jc“Jba“Jdd“Jnba”}]a=Filterarr,nbn,Trueb=Filterarr,bH,0EndSubSub筛选[d2:f999].Cleari=CellsRows.Count,
1.EndxlUp.RowRangec2:cHi.FormulaArray==a2:ani”b2:biarr=Rangenc2:cliRangec2:cli.CIeara二FilterApplication.Transposearr,[gl J,TrueFor Eachb Inan=n+1c=Splitb,Cellsn+l,Hdn=c0Cellsn+l,Hen=cl等级判断SubIfSheetl.Rangenbrf=90ThenSheetl.Rangenb2n=“优”ElseIfSheetl.RangeHbln=80ThenSheetl.RangeHb2u=nfeMElseIfSheetl.RangeHbr,=70ThenSheetl.Rangenb2n=n^MElseSheetl.Rangenb2u=“差”End IfEndSub语句用于判断选择7selectSelect caseCase1Case2Case elseEndselect循环语句8
（1）do loop语句DoLoop
（2）:do whileloop语句Do while（条件成立时候循环）Loop
（3）do untilloop语句Do until（直到条件成立）Loop［注］:与不仅可以放在背面，也可以放在背面实际上有时在循环的最终一while untilDO LOOP行进行判断，更具故意义Do［｛While|Until）体现式］［执行的一条或多条语句］［Exit Do］［执行的一条或多条语句］Loop while当这个条件为True时就循环until直到这个条件为True时就跳出循环或者可以使用下面这种语法:DoEnd Sub工作表函数与数组几句代码制作查询系统14VBA，在工作表支持数组的函数，诸多也支持VBA数组返回成果，当然也是一种数组，如sumif,countif,match,index,vlookup•….，工作表函数INDEX处理VBA数组，如对INDEX函数不理解请移驾自学网观看5-7a.
5.7节，谢谢！index工作表函数可以对VBA二维数组进行1行或1列的截取Sub testarr=[a2:gl3]arrl=WorksheetFunction.Indexarr,0,2arr2=WorksheetFunction.Indexarr,3,0EndSubSub查询系统0[i3:o999].Clear arr=RangeHa2H,CellsRows.Count,HgH.EndxlUpFor i=1To UBoundarrIfarri,1Like fjl]Thenn=n+1Cellsn+2,in.Resizel,7=WorksheetFunction.Indexarr,i,0End IfNextEndSub借助数组迅速格式化单元格15:VBASub VBA数组格式化单元格Cells.ClearFormatsarr二Rangeg2:gCellsRows.Count,ng.EndxlUp.RowFor i=1To UBoundarrIfarri,1=330ThenSet Rng=Cellsi+1,ng.EntireRow.Range,,a1:g1n=n+1If n=1Then Set rngs二Rng ElseSetrngs=Unionrngs,Rng k=rngs.AddressEnd IfNextrngs.Interior.Colorlndex=3EndSub运用数组进行排序16:VBASub排序arr=SelectionFor i=1To UBoundarrFor j=i+1To UBoundarrIfarri,larrj,lThen k=arri,1arri,l=arrj,1arrj,l=kEnd IfNextNext[b1].ResizeUBoundarr=arrEnd Sub数组分类汇总17:VBASub VBA数组分类汇总0Dimarr1arr=[a2:c13]For i=I ToUBoundarrReDim PreservearrllTo2,1To n+1Forj=1To UBoundarrl,2If arrll,j=arri,1Then an-l2,j=arrl2,j+arri,3GoTo100EndIfNextn=n+1arrll,n=arri,1arr12,n=arri,3100:Next[e2].Resizen,2=Application.Transposearrl EndSub第六章与字典技术VBA［执行的一条或多条语句］［Exit Do］［执行的一条或多条语句］Loop［｛While|Until｝体现式］用Do…Loop循环要注意的几点
1.While与Until是放在Do背面还是Loop背面，取决于是先判断再循环，还是先循环再判断前者则在D背面，后者则在Loop背面
2.可以在Do...Loop中的任何位置放置任意个数的Exit Do语句，随时跳出Do...Loop循环
3.Do...Loop+If...Then+Exit Do一般结合使用.
4.假如Exit Do使用在嵌套的Do...Loop语句中，则Exit Do会将控制权转移到Exit Do所在位置的外层循环4for eachnext语句Eg:Sub foreachnext循环1Dim rngAsRange,n!range为单元格对象ForEachrngInSheet
1.Rangea2:a10取a2:al0中的每个单元格If rng=Al Thenrng.Interior.Colorlndex=3NextEnd SubSubforeachnext循环2Dim wshAsWorksheet,n AsByte,m AsString worksheet为工作表变量ForEachwsh InWorksheets取目前工作表集合中的每个组员n=n+1Sheetl.Cellsn,3=wsh.NameNextEnd Sub语句与语句9exit end是退出目前语句1exit
1.Exit Do
2.Exit For
3.Exit Function
4.Exit Sub结束一种过程或块2EndEnd FunctionEndIfEnd SelectEndSub［注］:有时候在某些地方的功能和的作用相似end exitfor跳转语句10GoTo line无条件地转移到过程中指定的行Gosub return跳转到某行，并且可以返回注意太多的GoT语句，会使程序代码不轻易阅读及调试尽量使用构造化控制语句Do...Loop、For...Next If...Then...Else、Select CaseoFor exampleSub gotoreturnDimi!Fori=2To10IfSheetl.Rangeai1/3Then GoSub100Next iExitSub100:作为gosub的跳转标示符号Sheetl.RangebHi=“迟到”Return return语句返回到跳转的地方EndSub对错误语句的处理11措施I OnErrorResumeNext当错误的时候继续执行下去措施2OnErrorgoto当错误时候去哪儿语句12with当对某个对象执行一系列的语句时，不用反复指出对象的名称ForexampleSubwith嵌套1Rangear.Value=Who ami RangeC^l^.Parent.Name=Hello World”RangeHalH.Font.Size=20Rangenal,.Font.Bold=TrueEnd SubSubwith嵌套2With Rangeual.Value=Who ami.Parent.Name=Hello WorldWith.Font.Size=
20.Bold=TrueEnd WithEndWithEnd Sub与公式13:VBAFor exampleSub一般公式Sheetl.Cellsl,3=n=al+blEnd SubSub批量计算0DimiAs IntegerFori=1To10Sheetl.Cells（i,4）=n=ai”+b”iNext iEndSubSub数组公式（）Range（e1:e10M）.FormulaArray=n=a1:a10+b1:b10（FormulaArray为数组公式）EndSubSub公式带引号的计算（）Cells（12,1）=,,=COUNTIF（Al:A10；H,＞9,,,,）n（假如公式当中具有引号,则需要添加双重引号,才可以使公式的输入格式对的）Cells（12,2）=n=sum（INDIRECT（na1:a1EndSub运算符14:运算符是代表某种运算功能的符号VBA1）赋值运算符:二2）数学运算符（字符连接符）、+（加）、-（减）、Mod（取余）、\（整除）、*（乘）、/（除）、-（负号）、八（指数）3）逻辑运算符:Not（非）、And（与）、Or（或）、Xor（异或相似为0,不一样为1）、Eqv（相等，相似为1,不一样为0）、Imp（隐含）4）关系运算符=（相似）、＜＞（不等）、＞（不小于）、＜（不不小于）、＞=（不不不小于）、＜=（不不小于）、Like（判断两个字符串与否相似）代表任何单一字符*代表零个或多种字符[charlist]代表charlist.中的任何单一字符[!charlist]代表不在charlist中的任何单一字符第二章工作簿以及工作表的操作中工作表与工作簿的表达措施1VBA1workbooks“工作表的文献名”Workbooks“工作表的文献名”.parent返回工作簿对象的父对象2工作簿引索号表达法workbooks数字.name返回工作表的名称3窗口表达措施Windows.count返回目前excel工作簿打开的个数WindowsN.parent.Name返回第N个工作簿的名称［注工作簿索引号的表达法与窗口表达法表达的工作簿的次序相反］目前工作簿与活动工作簿2:目前工作簿thisworkbook代码所在工作簿活动工作簿activeworkbook已经激活的工作簿［注］:目前工作簿也许是已经激活的工作簿，也也许不是已经激活的工作簿工作簿的基本操作3workbooks由目前所有在内存中打开的workbook对象构成的集合
1.新建工作簿Sub新建工作簿Dim wkbAsWorkbook申明wkb为工作簿Set wkb=Workbooks.Add新建工作秒簿wkb.SaveAs k:\
123.xls”保留为工作簿EndSub
2.打动工作簿Sub打动工作簿Dim wkbAs WorkbookSetwkb=Workbooks.Openc:\
123.xlsEnd Sub
3.关闭工作簿Sub关闭Workbooksn123H.CloseTrue默认为自动保留，不提醒EndSub
4.文献复制与删除Sub文献复制与删除k:\
123.txt，k:\
321.txt”对所有文献类型都起作用Kill“c:\
321.txt”EndSub工作薄的应用实例4判断文献与否存在1Sub文献与否存在a=DirCc:\l
23.xlsn Dir函数用来取出途径下的目录文献IfaThenMsgBox”不存在”Else。
个人认证
优秀文档
获得点赞 0
