Ecel VBA常用代码总结1.docx

Excel VBA Excel VBA 常用代码总结 1 改变背景色 Range( Range(A1). = xlNone ColorIndex 一览 Range(A1). = Range(A1). = 1 Cells(1, 2) Range(H7) 获取范围 Range(Cells( Range(Cells(2, 3), Cells(4, 5)) Range(a1:c3) 用快捷记号引用单元格Worksheets(Sheet1).[A1:B5] 选中某 sheet Set Set NewSheet = Sheets(sheet1) “Range”对象的的 “Range”对象的的 Select 方法可以选择一个或多个单元格，而 Activate 方法可以指定某一个单元格为活动单元格。 下面的代码首先选择 A1:E10 区域，同时激活 D4 单元格： Range(a1:e10).Select Range(d4:e5).Activate Range(d4:e5).Activate 而对于下面的代码： Range(a1:e10).Select Range(f11:g15).Activate 由于区域 A1:E10 和 F11:G15 没有公共区域，将最终选择 F11:G15，并激活 F11 单元格。 路径 名称 路径＋名称 或将 ActiveWorkbook 换成 thisworkbook 隐藏文档 = = False 禁止屏幕更新 = = False = FalsestrPath = = False strPath = C:\temp\ MkDir strPath 状态栏文字表示 = = 计算中 Private Sub Worksheet_BeforeDoubleClick( Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If = 5 And = 8) Then If = ● Then = Else = ● End If Cancel = True End If End Sub Set objShell = CreateObject Set objShell = CreateObject() Set objFolder = (0, 文件, 0, 0) If Not objFolder Is Nothing Then path= \ end if Set objFolder = Nothing Set objShell = Nothing Set objFolder = Nothing Set objShell = Nothing Public Function ChooseFolder() As String Dim dlgOpen As FileDialog Set dlgOpen = (msoFileDialogFolderPicker) With dlgOpen .InitialFileName = \ 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( Public Function ChooseOneFile(Optional TitleStr As String = Please choose a file, Optional TypesDec As String = *.*, Optional Exten As String = *.*) As String Dim dlgOpen As FileDialog Set dlgOpen = (msoFileDialogFilePicker) With dlgOpen .Title = TitleStr . . TypesDec, Exten .AllowMultiSelect = False .InitialFileName = If .Show = -1 Then .AllowMultiSelect = True For Each vrtSelectedItem MsgBox Next Path name: In .SelectedItems vrtSelectedItem vrtSelectedItem ChooseOneFi