Excel-VBA常用代码总结1

1、Excel VBA常用代码总结1? 改变背景色Ran ge("A1" ).1 nterior.Colorl ndex = xIN oneColorl ndex 览无色01920白色21224；2526572338394041424344454647霽4950515253545556改变文字颜色Ran ge("A1" ).Fo nt.Colorl ndex =? 获取单元格1Cells( 1, 2)Ran ge("H7")? 获取范围Range(Cells( 2, 3), Cells( 4, 5)Range("a1:c3&qu

2、ot;)'用快捷记号引用单元格Worksheets( "Sheet1" ).A1:B5? 选中某sheetSet NewSheet = Sheets( "sheet1")NewSheet.Select? 选中或激活某单元格'“Rangd'对象的的Select方法可以选择一个或多个单元格，而Activate方法 可以指定某一个单元格为活动单元格。'下面的代码首先选择A1:E10区域，同时激活D4单元格：Range( "a1:e10" ).SelectRange( "d4:e5" .Ac

3、tivate'而对于下面的代码：Range( "a1:e10" .SelectRange( "f11:g15" .Activate'由于区域A1:E10和F11:G15没有公共区域，将最终选择 F11:G15，并激活F11 单元格。? 获得文档的路径和文件名ActiveWorkbook.PathActiveWorkbook.NameActiveWorkbook.FullName'路徑'名稱'路徑+名稱或将 ActiveWorkbook 换成 thisworkbook?隐藏文档Applicati on .Visibl

4、e = False ?禁止屏幕更新Applicati on. Scree nUpdati ng =False? 禁止显示提示和警告消息Applicati on .DisplayAlerts =False? 文件夹做成strPath = "C:temp"MkDir strPath? 状态栏文字表示Application.StatusBar ="计算中"? 双击单元格内容变换Private Sub Worksheet_BeforeDoubleClick( ByVal Target As Range, Cancel AsBoolea n)If (Target.

5、Cells.Row >=5 And Target.Cells.Row <=8) ThenIf Target.Cells.Value =" " ThenTarget.Cells.Value =""ElseTarget.Cells.Value =" "End IfCan cel =TrueEnd IfEnd Sub? 文件夹选择框方法1Set objShell = CreateObject ("Shell.Application")Set objFolder = objShell.BrowseForFold

6、er( 0,"文件",0, 0) If Not objFolder Is NothingThen path= objFolder.self.Path &""end ifSet objFolder = Noth ingSet objShell = Nothi ng? 文件夹选择框方法 2 (推荐)Public Function ChooseFolder() As StringDim dlgOpe nAs FileDialogSet dlgOpe n = Applicati on. FileDialog(msoFileDialogFolderPick

7、er) With dlgOpe nn itialFileName = ThisWorkbook.path &""If .Show = - 1 ThenChooseFolder = .SelectedItems(1)End IfEnd WithSet dlgOpe n = Noth ingEnd Function '使用方法例：Dim path As String path = ChooseFolder()If path <>"" ThenMsgBox"open folder"End If"Plea

8、seExte n As? 文件选择框方法Public Function ChooseOneFile( Optional TitleStr As String choose a file" , Optional TypesDec As String = "*.*" , Optional String = "*.*" ) As StringDim dlgOpe nAs FileDialogSet dlgOpe n = Applicati on. FileDialog(msoFileDialogFilePicker)With dlgOpe n.Tit

9、le = TitleStr.Filters.Clear.Filters.Add TypesDec, Exte n.AllowMultiSelect =False.I nitialFileName = ThisWorkbook.PathIf .Show = - 1 Then'.AllowMultiSelect = True'For Each vrtSelectedItem In .Selectedltems'MsgBox "P ath name: " & vrtSelectedItem'Next vrtSelectedItemChoos

10、e On eFile = .SelectedItems(1)End IfEnd WithSet dlgOpe n = Noth ingEnd Function? 某列到关键字为止循环方法1(假设关键字是end)Set Curre ntCell = Ra nge("A1")Do While CurrentCell.Value <> "end"Set CurrentCell = CurrentCell.Offset(1, 0)Loop? 某列到关键字为止循环方法2(假设关键字是空字符串)i = StartRowDo While Cells(i,

11、1) <> ""i = i +1Loop? "For Each.Next 循环(知道确切边界)For Each c In Worksheets( "Sheet1" ).Range( "A1:D10" ).CellsIf Abs(c.Value) <0.01 Then c.Value =0Next? "For Each.Next 循环(不知道确切边界),在活动单元格周围的区域内循环For Each c In ActiveCell.CurrentRegion.CellsIf Abs(c.Value)

12、<0.01 Then c.Value =0Next? 某列有数据的最末行的行数的取得(中间不能有空行)Ion Row=1Do While Trim (Cells(lonRow,2 ).Value) <> ""Ion Row = Ion Row +1LoopIon Row11 = Ion Row11 -1? A列有数据的最末行的行数的取得另一种方法Range(" A65536").End(xlUp).Row? 将文字复制到剪贴板Dim MyData As DataObjectSet MyData = NewDataObjectMyData

13、.SetText Range( "H7").ValueMyData.Put In Clipboard? 取得路径中的文件名Private Function GetFileName( ByVal s As String )Dim sname() As Stringsname =Split (s, "")GetFileName = sn ame( UBo undsn ame)End Fun cti on? 取得路径中的路径名Private Function GetPathName(ByVal s As String )intFileNameStart =In

14、StrRev (s, "")GetPathName = Mid(s, 1, i ntFileNameStart)End Fun cti on? 由模板sheet拷贝做成一个新的 sheetThisWorkbook.Worksheets( "template" ).CopyAfter:=ThisWorkbook.Worksheets(Sheets.Cou nt)Set doc_s = ThisWorkbook.Worksheets(Sheets.Cou nt)doc_s.Name = "newsheetname" & Forma

15、t(Now, "yyyyMMddhhmmss"? 选中当列的最后一个有内容的单元格(中间不能有空行)'删除B3开始到B列最后一个有内容的单元格为止的所有内容Ran ge("B3" ).SelectRan ge(Select ion, Selecti on.En d(xlDow n).SelectSelecti on .ClearC ontents? 常量定义Private Const StartRowAs Integer = 3? 判断sheet是否存在Private Function IsWorksheet( ByVal strSeetName

16、 As String ) As BooleanOn Error GoToErrHandleDim blnRet As Booleanbln Ret = IsNull(Worksheets(strSeetName)IsWorksheet = TrueExit FunctionErrHa ndle:IsWorksheet = FalseEnd Fun cti on? 向单元格中写入公式Worksheets( "Sheet1" ).Range( "D6").Formula ="=SUM(D2:D5)"? 引用命名单元格区域Range(&qu

17、ot;MyBook.xls!MyRange")Ran ge("Report.xlsSheet1!Sales"? 选定命名的单元格区域Applicati on .Goto Refere nce:="MyBook.xls!MyRa nge"'或者worksheets( "sheetname").range( "rangename").selectSelecti on .ClearC ontents?使用 Dictionary使用 Dictio nary 需要添加参照 Microsoft Scripti

18、 ng Run time前面是Key后面是ValueDim dic As NewDictionary dic.Add "Table" , "Cards" dic.Add "Serial" , "serialno" dic.Add "Number", "surface" msgbox "文件 C:aaa1.txt 不存在" end ifMsgBoxdic.ltem( "Table") dic.Exists( "Table&quo

19、t;)由Key取得Value'判断某Key是否存在? 将EXCEL表格中的两列表格插入到一个Dictionary 中'函数：在ws工作表中，从iStartRow行开始到没有数据为止， iKeyCol右一列插入到一个字典中，并返回字典。Public Function SetDic(ws As Worksheet, iStartRow, iKeyCol As Dictio nary把iKeyCol列和As Integer )Dim dic As NewDictionaryDim i As Integeri = iStartRowDo Un til ws.Cells(i, iRule

20、Col).Value =IlliIf Not dic.Exists(ws.Cells(i, iKeyCol).Value)dic.Add ws.Cells(i, iKeyCol).Value, ws.Cells(i, iKeyCol +1).ValueEnd IfThe ni = i +1LoopSet SetDic = dicEnd Fun cti on? 判断文件夹或文件是否存在'文件夹If Dir ("C:aaa" , vbDirectory) ="" ThenMkDir "C:aaa"End If'文件If D

21、ir ("C:aaa1.txt") = "" Then次注释多行视图-工具栏-编辑调出编辑工具栏，工具栏上有个“设置注释块”和“解除注释快”? 打开文件并将文件赋予到第一个参数wb中'注意，这里的path是文件的完整路径，包括文件名。Public Function OpenWorkBook(wbAs Workbook, path As String ) As BooleanOn Error GoToErrOpen WorkBook = TrueDim isWbOpened As BooleanisWbOpe ned = FalseDim file

22、Name As StringfileName = GetFileName(path)'check file is opened or eitherDim wbTemp As WorkbookFor Each wbTemp In WorkbooksIf wbTemp.Name = fileName Then isWbOpened = TrueNext'ope n fileIf isWbOpened = False ThenWorkbooks.Ope n pathEnd IfSet wb = Workbooks(fileName)Exit FunctionErr:Ope nWork

23、Book = FalseEnd Fun cti on? 打开一个文件，并将文件赋予到wb中，将文件的sheet页赋予到ws中的完整代码。(用到了上面的函数)'If OpenWorkBook(wb, path & "" & "filename") = False Then MsgBox"ope n file error."GoToErrEnd Ifwb.ActivateSet ws = wb.Worksheets( "sheetname")? 打开一个不知道确切名字的文件(文件名中含有sera

24、chname)，并将文件赋予到 wb中,将文件的sheet页赋予到ws中的完整代码。'用到了上上面的函数 OpenWorkBook'If Ope nCompa nyFile(wb, path, "search name") = False The n MsgBox"ope n file error."GoToErrEnd Ifwb.ActivateSet ws = wb.Worksheets( "sheetname")'直接使用的函数OpenCompanyFileFunction OpenCompanyFile

25、(wbComAs Workbook, strPath As String , strFileName As String ) As BooleanDim fs As Varia ntfs = Dir (strPath &"*.xls") 'seach filesOpen Compa ny File =FalseDo While fs <>""If In Str (1, fs, strFileName) >0 The n'file name matchIf OpenWorkBook(wbCom, strPath &

26、amp; "" & fs) = False Then 'ope n fileOpen Compa ny File =FalseExit DoElseOpen Compa ny File =TrueExit DoEnd IfEnd Iffs = DirLoopEnd Fun cti on陌I? 数字转字母(如1转成A, 2转成B)和字母转数字Chr(i +64)比如i=1的时候，Chr(i +64)=AAsc(i -64)比如i=A的时候，Asc(i -64)=1? 复选框总开关实现。假如有 10个子checkbox1checkbox10，还有一个总开关 ch

27、eckbo x11，让checkbox11控制110的选择和非选择。怜IPrivate Sub CheckBox11_Click()Dim chb As VariantIf MeCheckBox11.Value = True ThenFor Each chb In ActiveSheet.OLEObjectsIf chb.Name Like "CheckBox*" And chb.Name <> "CheckBox11" Thenchb.Object.Value =TrueEnd IfNextElseFor Each chb In Activ

28、eSheet.OLEObjectsIf chb.Name Like "CheckBox*" And chb.Name <> "CheckBox11" Thenchb.Object.Value =FalseEnd IfNextEnd IfEnd Sub石? 修改B6单元格所在的pivot的数据源，并刷新 pivotSet pvt = ActiveSheet.Ra nge( "B6").PivotTablepvt.Cha ngePivotCacheActiveWorkbook.PivotCaches.Create(Source

29、Type:=xlDatabase,SourceData:= _"SheetName!R4C2:R" & In gLastRow &"C22",Versi on: =xlPivotTableVersi on 10)pvt.PivotCache.Refresh? 将一个图形(比如一个长方形的框"Recta ngle 2")移动到与某个单元格对齐。ws.ActivateApplicati on. Scree nUpdat ing =Truews.Shapes.Range(Array( "Rectangle 2&qu

30、ot; ).Selectws.Shapes.Range(Array( "Rectangle 2" ).Top = ws.Range( "T5" ).Topws.Shapes.Range(Array( "Rectangle 2" ).Left = ws.Range( "T5" ).Left Applicati on. Scree nUpdati ng =False? 遍历控件。比如遍历所有的checkbox是否被打挑。If MeOLEObjects( "CheckBox" & i).Obj

31、ect.Value = True Then flgChecked =Trueend if? 得到今天的日期dateNow = WorksheetFu nctio n.Text(Now(),"YYYY/MM/DD"? 在某个sheet页中查找某个关键字*'Search keyword from a worksheet (not workbook!)*Public Function SearchKeyWord(ws As Worksheet, keyword As String ) As Boolea nDim varl As Varia ntSet varl = ws

32、.Cells.Fi nd(What:=keyword, After:=ActiveCell,Look In: =xlFormulas, LookAt _:=xlPart, SearchOrder:=xlByRows, SearchDirecti on: =xlNext,MatchCase:= _False , MatchByte:= False , SearchFormat:= False)If varl Is Nothing ThenSearchKeyWord =FalseElseSearchKeyWord =TrueEnd IfEnd Fun cti on? 单元格为空，取不到值的时候，转

33、化为空字符串。Empty to ""*'Empty to ""*Public Function ChangeEmptyToString(var As Variant)As StringOn Error GoToErrCha ngeEmptyToStri ng = CStr(var)Exit FunctionErr:Chan geEmptyToStri ng =""End Fun cti on7? 单元格为空，取不到值的时候，转化为0。Empty to 0*'Empty to 0*Public Function Chan

34、geEmptyToLong(var As Variant)As LongOn Error GoToErrChan geEmptyToL ong = CLng(var)Exit FunctionErr:Chan geEmptyToL ong =0End Fun cti on7? 找到某个sheet页中使用的最末行MeUsedRa nge.Rows.Co unt? 遍历文件夹下的所有文件（自定义文件夹和后缀名），并返回文件列表字典As String )Function SetFilesToDic( ByVai path As String , ByVai extension As Dictio n

35、aryDim MyFile As StringDim s As StringDim count As IntegerDim dic As NewDictionaryIf Right (path, 1) <> "" Thenpath = path &II"End IfMyFile =Dir (path & "*"& extension)count =Do While MyFile <>If MyFile = "" The n Exit DoEnd Ifdic.Add count,

36、 MyFilecount = count +MyFile = DirLoopSet SetFilesToDic = dic'Debug.Pri nt sEnd Fun cti on? 生成logSub txtPrint( ByVal txt$, Optional myPath$ ="")'第 2 参数可以指定保存 txt文件路径If myPath = "" Then myPath = ActiveWorkbook.path & "log.txt"Ope n myPathFor Appe nd As # 1Pri

37、nt # 1, txtClose #1End Sub百? &nbsp; Non-Breaking Space 网页空格在 VBA中的处理替换字符ChrB(160) & ChrB( 0)上述最终解决方法来自于.tw/board/FUM20060608180224R4M/BRD2009031011234606U/2.htmlSdany用户是通过如下思路找到解决方法的(用MidB和AscB):Dim I As IntegerFor I =1 To Len B(Cells( 1, 1)Debug.Pri nt AscB(MidB(Cells(1, 1), I, 1)Next? 延时-J

38、这段代码在Excel VBA和VB里都可以用'*VB'声明延时函数定义*As LongPrivate Declare Function timeGetTime Lib "winmm.dll"() '延时Public Sub Delay( ByVal num As Integer )Dim t As Longt = timeGetTimeDo Until timeGetTime - t >= num *1000DoEve ntsLoopEnd Sub*使用方法：delay 3'3表示秒数? 杀掉某程序执行的所有进程Sub KillWord()Dim ProcessFor Each Process In GetObject ("winmgmts:" ).ExecQuery( "select * from Win 32_Process where name='WINWORD.EXE)"P