Excel VBA常用代码总结.docVIP

Excel VBA常用代码总结1 改变背景色 Range(A1).Interior.ColorIndex = xlNone ?ColorIndex一览 改变文字颜色 Range(A1).Font.ColorIndex = 1 获取单元格 Cells(1, 2) Range(H7) 获取范围 Range(Cells(2, 3), Cells(4, 5)) Range(a1:c3) 用快捷记号引用单元格 Worksheets(Sheet1).[A1:B5] 选中某sheet Set NewSheet = Sheets(sheet1) NewSheet.Select 选中或激活某单元格 “Range”对象的的Select方法可以选择一个或多个单元格，而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　 路徑＋名稱 或将ActiveWorkbook换成thisworkbook 隐藏文档 Application.Visible = False 禁止屏幕更新 Application.ScreenUpdating = False 禁止显示提示和警告消息 Application.DisplayAlerts = False 文件夹做成 strPath = C:\temp\ MkDir strPath 状态栏文字表示 Application.StatusBar = 计算中 双击单元格内容变换 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 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 If End Sub 文件夹选择框方法1 Set objShell = CreateObject(Shell.Application) Set objFolder = objShell.BrowseForFolder(0, 文件, 0, 0) If Not objFolder Is Nothing Then path= objFolder.self.Path \ end if Set objFolder = Nothing Set objShell = Nothing 文件夹选择框方法2（推荐） Public Function ChooseFolder() As String Dim dlgOpen As FileDialog Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker) With dlgOpen .InitialFileName = ThisWorkbook.path \ If .Show = -1 Then ChooseFolder = .SelectedItems(1) End If End With Set dlgOpen = Nothing End Function 使用方法例： Dim path As String path = ChooseFolder() If path Then MsgBox open folder End If 文件选择框方法 Public Function ChooseOneFile(Optional TitleStr As String = Please choose a file, Optional TypesDec As String = *.*