《ExcelVBA常用代码总结1》由会员分享，可在线阅读，更多相关《ExcelVBA常用代码总结1（15页珍藏版）》请在金锄头文库上搜索。
1、Excel VBA常用代码总结1 改变背景色Range(A1).Interior.ColorIndex = xlNoneColorIndex一览 改变文字颜色Range(A1).Font.ColorIndex = 1 获取单元格Cells(1, 2)Range(H7) 获取范围Range(Cells(2, 3), Cells(4, 5)Range(a1:c3)用快捷记号引用单元格Worksheets(Sheet1).A1:B5 选中某sheetSet NewSheet = Sheets(sheet1)NewSheet.Select 选中或激活某单元格“Range”对象的的Select方法可以选
2、择一个或多个单元格，而Activate方法可以指定某一个单元格为活动单元格。下面的代码首先选择A1:E10区域，同时激活D4单元格： Range(a1:e10).Select Range(d4:e5).Activate而对于下面的代码： Range(a1:e10).Select Range(f11:g15).Activate由于区域A1:E10和F11:G15没有公共区域，将最终选择F11:G15，并激活F11单元格。 获得文档的路径和文件名ActiveWorkbook.Path路徑ActiveWorkbook.Name名稱ActiveWorkbook.FullName 路徑名稱或将Activ
3、eWorkbook换成thisworkbook 隐藏文档Application.Visible = False 禁止屏幕更新Application.ScreenUpdating = False 禁止显示提示和警告消息Application.DisplayAlerts = False 文件夹做成strPath = C:tempMkDir strPath 状态栏文字表示Application.StatusBar = 计算中 双击单元格内容变换Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolea
4、n) If (Target.Cells.Row = 5 And Target.Cells.Row = 8) Then If Target.Cells.Value = Then Target.Cells.Value = Else Target.Cells.Value = End If Cancel = True End IfEnd Sub 文件夹选择框方法1Set objShell = CreateObject(Shell.Application)Set objFolder = objShell.BrowseForFolder(0, 文件, 0, 0)If Not objFolder Is No
5、thing Then path= objFolder.self.Path & end ifSet objFolder = NothingSet objShell = Nothing 文件夹选择框方法2（推荐） Public Function ChooseFolder() As String Dim dlgOpen As FileDialog Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker) With dlgOpen .InitialFileName = ThisWorkbook.path & If .Show = -
6、1 Then ChooseFolder = .SelectedItems(1) End If End With Set dlgOpen = Nothing End Function使用方法例：Dim path As Stringpath = ChooseFolder()If path Then MsgBox open folderEnd If 文件选择框方法 Public Function ChooseOneFile(Optional TitleStr As String = Please choose a file, Optional TypesDec As String = *.*, Op
7、tional Exten As String = *.*) As String Dim dlgOpen As FileDialog Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker) With dlgOpen .Title = TitleStr .Filters.Clear .Filters.Add TypesDec, Exten .AllowMultiSelect = False .InitialFileName = ThisWorkbook.Path If .Show = -1 Then .AllowMultiSele
8、ct = True For Each vrtSelectedItem In .SelectedItems MsgBox Path name: & vrtSelectedItem Next vrtSelectedItem ChooseOneFile = .SelectedItems(1) End If End With Set dlgOpen = Nothing End Function 某列到关键字为止循环方法1(假设关键字是end)Set CurrentCell = Range(A1)Do While CurrentCell.Value endSet CurrentCell = Curren
9、tCell.Offset(1, 0)Loop 某列到关键字为止循环方法2(假设关键字是空字符串)i = StartRowDo While Cells(i, 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.Cells If
10、 Abs(c.Value) 0.01 Then c.Value = 0Next 某列有数据的最末行的行数的取得(中间不能有空行)lonRow=1Do While Trim(Cells(lonRow, ).Value) lonRow = lonRow + 1LooplonRow11 = lonRow11 - 1 A列有数据的最末行的行数的取得 另一种方法Range(65536).End(xlUp).Row 将文字复制到剪贴板Dim MyData As DataObjectSet MyData = New DataObjectMyData.SetText Range(H7).ValueMyData
11、.PutInClipboard 取得路径中的文件名Private Function GetFileName(ByVal s As String) Dim sname() As String sname = Split(s, ) GetFileName = sname(UBound(sname)End Function 取得路径中的路径名Private Function GetPathName(ByVal s As String) intFileNameStart = InStrRev(s, ) GetPathName = Mid(s, 1, intFileNameStart)End Funct
12、ion 由模板sheet拷贝做成一个新的sheetThisWorkbook.Worksheets(template).Copy After:=ThisWorkbook.Worksheets(Sheets.Count)Set doc_s = ThisWorkbook.Worksheets(Sheets.Count)doc_s.Name = newsheetname & Format(Now, yyyyMMddhhmmss) 选中当列的最后一个有内容的单元格(中间不能有空行)删除B3开始到B列最后一个有内容的单元格为止的所有内容Range(B3).SelectRange(Selection, Se
13、lection.End(xlDown).SelectSelection.ClearContents 常量定义Private Const StartRow As Integer = 3 判断sheet是否存在Private Function IsWorksheet(ByVal strSeetName As String) As Boolean On Error GoTo ErrHandle Dim blnRet As Boolean blnRet = IsNull(Worksheets(strSeetName) IsWorksheet = True Exit FunctionErrHandle: IsWorksheet = FalseEnd Function 向单元格中写入公式Worksheets(Sheet1).Range(D6).Formula = =SUM(D2:D5) 引用命名单元格区域Range(MyBook.xls!MyRange)Range(Report.xlsSheet1!Sales
