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
2、”对象的的 Select 方法可以选择一个或多个单元格，而 Activate 方法可以指定某一个单元格为活动单元格。下面的代码首先选择 A1:E10 区域，同时激活 D4 单元格：Range(“a1:e10“).SelectRange(“d4:e5“).Activate而对于下面的代码：Range(“a1:e10“).SelectRange(“f11:g15“).Activate由于区域 A1:E10 和 F11:G15 没有公共区域，将最终选择 F11:G15，并激活 F11单元格。 获得文档的路径和文件名ActiveWorkbook.Path 路徑ActiveWorkbook.Name 名
3、稱ActiveWorkbook.FullName 路徑名稱或将 ActiveWorkbook 换成 thisworkbook 隐藏文档Application.Visible = False 禁止屏幕更新Application.ScreenUpdating = False 禁止显示提示和警告消息Application.DisplayAlerts = False 文件夹做成strPath = “C:temp“MkDir strPath 状态栏文字表示Application.StatusBar = “计算中“ 双击单元格内容变换Private Sub Worksheet_BeforeDoubleCl
4、ick(ByVal Target As Range, Cancel As Boolean)If (Target.Cells.Row = 5 And Target.Cells.Row “ ThenMsgBox “open folder“End If 文件选择框方法Public Function ChooseOneFile(Optional TitleStr As String = “Please choose a file“, Optional TypesDec As String = “*.*“, Optional Exten As String = “*.*“) As StringDim d
5、lgOpen As FileDialogSet dlgOpen = Application.FileDialog(msoFileDialogFilePicker)With dlgOpen.Title = TitleStr.Filters.Clear .Filters.Add TypesDec, Exten .AllowMultiSelect = False .InitialFileName = ThisWorkbook.PathIf .Show = -1 Then .AllowMultiSelect = True For Each vrtSelectedItem In .SelectedIte
6、ms MsgBox “Path name: “ Non-Breaking Space网页空格在 VBA 中的处理替换字符ChrB(160) & ChrB(0)上述最终解决方法来自于http:/.tw/board/FUM20060608180224R4M/BRD2009031011234606U/2.htmlSdany 用户是通过如下思路找到解决方法的（用 MidB 和 AscB）：Dim I As Integer For I = 1 To LenB(Cells(1, 1) Debug.Print AscB(MidB(Cells(1, 1), I, 1) Next 延时这段代码在 Excel V
7、BA 和 VB 里都可以用*VB 延时函数定义*声明Private Declare Function timeGetTime Lib “winmm.dll“ () As Long延时Public Sub Delay(ByVal num As Integer)Dim t As Longt = timeGetTimeDo Until timeGetTime - t = num * 1000DoEventsLoopEnd Sub*使用方法:delay 33 表示秒数 杀掉某程序执行的所有进程Sub KillWord()Dim ProcessFor Each Process In GetObject(
8、“winmgmts:“).ExecQuery(“select * from Win32_Process where name=WINWORD.EXE“)Process.Terminate (0)NextEnd Sub 监视某单元格的变化这里最需要注意的问题就是，如果在这个事件里对单元格进行改变，会继续出发此事件变成死循环。所以要在对单元格进行变化之前加上 Application.EnableEvents = False，变完之后再改为True。Private Sub Worksheet_Change(ByVal Target As Range)On Error GoTo ErrApplicat
9、ion.EnableEvents = FalseDim cSet dicKtoW = SetDic(ThisWorkbook.Sheets(“reference“), 3, 1, 2)Set dicKtoX = SetDic(ThisWorkbook.Sheets(“reference“), 3, 1, 3)For Each c In TargetIf c.Column = 11 ThenMsgBox c.ValueMe.Range(“W“ & c.Row).Value = GetDic(dicKtoW, c.Value)Me.Range(“X“ & c.Row).Value = GetDic
10、(dicKtoX, c.Value)End IfNextSet dicKtoW = NothingSet dicKtoX = NothingApplication.EnableEvents = TrueExit SubErr:MsgBox (“Error!Please contact macro developer.“)Application.EnableEvents = TrueEnd Sub On Error 的用法1.一般用法On Error GoTo Label各种代码exit subLabel:msgbox Err.Description其他错误处理2.对于某段代码单独处理On Error Resume Next需要监视的代码If Err.Number 0 ThenMsgBox Err.DescriptionGoto LabelEnd IfOn Error GoTo 0exit subLabel:其他错误处理 EXCEL 的分组功能和展开收缩功能将 A 列到 C 列进行分组Range(“A:C“).Columns.Group默认情况下，分组后的 A 到 C 列会是展开状态，如果想让 A 到 C 列收缩Range(“A:C“).EntireColumn.Hidden=True
