EXCEL VBA常用代码集

1.显示活动工作簿名称

MsgBox " 当前活动工作簿是 " & ActiveWorkbook.Name

2. 保存活动工作簿

Activeworkbook.Save

3. 保存所有打开的工作簿关闭 EXCEL

For Each W in Application.Workbooks

W.Save

Next W

Application.Quit

4. 将网格线设置为蓝色

ActiveWindow.GridlineColorIndex = 5

5. 将工作表 sheet1 隐藏

Sheet1.Visible = xlSheetVeryHidden

6. 将工作表 Shtte1 显示

Sheet1.Visible = xlSheetVisible

7. 单击某单元格 , 该单元格所在的行以蓝色背景填充 , 字体颜色为白色

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

  If Target.Row >= 2 Then ’ 第二行以下的区域

     On Error Resume Next

     [ChangColor_With1].FormatConditions.Delete

     Target.EntireRow.Name = "ChangColor_With1"

     With [ChangColor_With1].FormatConditions

      .Delete

      .Add xlExpression, , "TRUE"

      .Item(1).Interior.ColorIndex = 5

      .Item(1).Font.ColorIndex = 2

     End With

  End If

End Sub

8. 使窗体在启动的时候自动最大化

Private Sub UserForm_Initialize()

   Application.WindowState = xlMaximized

   With Application

       Me.Top = .Top

       Me.Left = .Left

       Me.Height = .Height

       Me.Width = .Width

   End With

End Sub

9. 不保存工作簿退出 EXCEL

Application.DisplayAlerts = False

Application.Quit

10. 使窗体的关闭按纽不好用

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = vbformcontrdmenu Then

MsgBox " 请用关闭按钮关闭窗口 !!", 64, " 提示 "

Cancel = True

End If

End Sub

11. 使窗体在 3 秒后自动关闭

Private Sub UserForm_Activate()

Application.Wait Now + TimeValue("00:00:03")

UserForm1.Hide

End Sub

12. 启动窗体的时候自动使 Label1 显示 Sheet1 工作表 3 列 ,8 行的内容

Private Sub UserForm_Activate()

Label1.Caption = Sheets("sheet1").Cells(3, 8)

End Sub

13. 让按纽 CommandButton1 在窗体上以不可用状态显示

CommandButton1.Enabled = False

14. 让按纽 Commandbutton1 在窗体上以隐藏方式存在

CommandButton10.Visible = False

15. 点击 Commandbutton1 按纽进入 ” 工资 ” 工作表

Sheets(" 工资 ").Select

16. 在 Textbox1 中输入数据 , 窗体可显示出 ” 工资 ” 工作表中与输入内容关联的项

Private Sub TextBox1_Change()

 For X = 1 To Application.CountA(Sheets(" 工资 ").Range("a:a"))

If Sheets(" 工资 ").Cells(X, 1) = TextBox1.Text Then ’ 在工资表第一列查找与 Textbox1 输入相符的项

    Label2.Caption = Sheets(" 工资 ").Cells(X, 2) ’ 在 Label2 中显示 Textbox1 数据所在的第二列的数据

    Label7.Caption = Sheets(" 工资 ").Cells(X, 3) ’ 在 Label2 中显示 Textbox1 数据所在的第三列的数据 End If

   Next

End Sub

17. 使 EXCEL 启动的时候自动最小化 / 最大化

Private Sub Workbook_Open()

Application.WindowState = xlMinimized ’ 最小化

Application.WindowState = xlMaximized ’ 最大化

End Sub

18. 在 Label25 以数字的形式显示 TextBox12 × Label14 的结果

Label25.Caption = Val(TextBox12.Text) * Val(Label14.Caption)

19. 单选按纽名与 Sheet6 工作表名相同

OptionButton6.Caption = Sheet6.Name

20.” 登陆 ” 窗体的显示 , 隐藏

登陆 .Show ’ 显示

登陆 .Hide ’ 隐藏

21. 使窗体的标题栏不显示

(1) 插入类模块 ” CFormChanger” 代码如下 :

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long

Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Private Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Private Const GWL_STYLE As Long = (-16)

Private Const WS_CAPTION As Long = &HC00000

Dim hWndForm As Long

……………………………………………………………………………………………………………..

Public Property Set Form(oForm As Object) '29

    If Val(Application.Version) < 9 Then

        hWndForm = FindWindow("ThunderXFrame", oForm.Caption)

    Else

        hWndForm = FindWindow("ThunderDFrame", oForm.Caption)

    End If

    SetFormStyle

End Property

……………………………………………………………………………………………………………….

Private Sub SetFormStyle()

Dim iStyle As Long, hMenu As Long, hID As Long, iItems As Integer

    iStyle = GetWindowLong(hWndForm, GWL_STYLE)

    iStyle = iStyle And Not WS_CAPTION

    iStyle = iStyle Or WS_THICKFRAME

    SetWindowLong hWndForm, GWL_STYLE, iStyle

    DrawMenuBar hWndForm

End Sub

(2) 在所在窗体代码里声明

Dim oFormChanger As New CFormChanger

(3). 在窗体的 Activate 事件中插入代码

Set oFormChanger.Form = Me

Me.SpecialEffect = fmspecia1EffectRaised

以上三步每一步都不可缺少 , 否则不能完成 .

22. 单击某单元格 , 该单元格所在的行与列都以蓝色背景填充

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

  If Target.Row >= 2 Then ’ 第二行以下的所有列

     On Error Resume Next

     [ChangColor_With2].FormatConditions.Delete

     [ChangColor_With3].FormatConditions.Delete

     Target.EntireRow.Name = "ChangColor_With2"

     Target.EntireColumn.Name = "ChangColor_With3"

     With [ChangColor_With2].FormatConditions

      .Delete

      .Add xlExpression, , "TRUE"

      .Item(1).Interior.ColorIndex = 5

     End With

     With [ChangColor_With3].FormatConditions

      .Delete

      .Add xlExpression, , "TRUE"

      .Item(1).Interior.ColorIndex = 5

     End With

  End If

End Sub

23. 显示动态时间

（ 1 ）插入窗体 Userform1 及 Label1 并在窗体声明中插入

Option Explicit

Public nextRun As Date

（ 2 ）在窗体 Activate 事件中插入

Showtime

（ 3 ）在窗体 QueryClose 事件中插入

Application.OnTime nextRun, "showtime", schedule:=False

（ 4 ）插入模块 Module1 并输入

    Option Explicit

Sub showtime()

UserForm1.Label1 = Now

UserForm1.Repaint

DoEvents

UserForm1.nextRun = Now + 1 / 86400

Application.OnTime UserForm1.nextRun, "showtime"

End Sub

24. 加载 Combobox1 选项

ComboBox1.AddItem " 收入型 "

ComboBox1.Additem “ 支出型 ”

25. 使 Textbox1 自动程输入状态显示 ( 有光标闪动 )

TextBox1.SetFocus

26. 打开 C 盘目录

Shell "explorer.exe  C:\", 1