VBA 代码段汇集[不断的更新中]

民扬

第二集 VBA中的语句、对象、方法和属性

1、 VBA语句

1.1宏程序语句

‘运行后可以完成一个功能

Sub test()    ‘开始语句

Range(“ai”)=100

End sub    ‘结束语句

1.2函数程序语句

Function shcount()

Shcount=sheets.count

End function

1.3在程序中应用的语句

Sub test2()

Call test

End sub

Sub test3()

For x = 1 to 100 ‘fer next 循环语句

Cells(x,1)= x

Next x

End sub

2、 VBA对象

VBA中的对象其实就是我们操作的具有方法、属性的Excel中支持的对象

Excel中的几个常用对象表示方法

2.1工作薄

Workbooks 代表工作薄集合，所有的工作薄，Workbooks(N)，表示已打开的第N个工作薄

Workbooks (“工作薄名称”)

Activeworkbook 正在操作的工作薄

2.2工作表

Sheets(“工作表名称”)

Sheet1 表示第一个插入的工作表，Sheet2表示第二个插入的工作表……

Sheets(N) 表示按排列顺序，第N个工作表

Activesheet 表示活动工作表，光标所在工作表

Worksheet 也表示工作，但不包括图表工作表、宏工作表等。

2.3单元格

Cells 所有单元格

Renge（“单元格地址”）

Cells(行数，列数)

Activecell 正在选中或编辑的单元格

Selection 正被选中或选取的单元格或单元格区域

3、 VBA属性

VBA属性就是VBA对象所具有的特点

表示某个对象的属性的方法是

对象。属性=属性值

Sub ttt()

Range(“a1”).value = 100

End sub

Sub ttt1()

Sheets(1).Name = "工作表改名了"

End Sub

Sub ttt2()

Sheets(“sheet2”).range(“a1”).value = “abcd”

End sub

Sub ttt3()

Range("a2").Interior.ColorIndex =3

End Sub

4、 VBA方法

VBA方法是作用于VBA对象上的动作

表示用某个方法作用于VBA的对象上，可以用下面的格式：

对象.方法 参数名称：=参数值

Sub ttt4()

Range("a1").CopyDestination:=Range("a2")

End Sub

Sub ttt5()

Sheet1.Movebefore:=Sheets("sheet3")

End Sub

民扬

第三集 判断语句

1、if判断语句

Sub 判断1() '单条件判断

   If Range("a1").Value > 0 Then

      Range("b1") = "正数"

   Else

       Range("b1") = "负数或0"

   End If

End Sub

Sub 判断2() '多条件判断

   If Range("a1").Value > 0 Then

      Range("b1") = "正数"

    ElseIf Range("a1") = 0 Then

       Range("b1") = "等于0"

   Else

       Range("b1") = "负数"

   End If

End Sub

Sub 多条件判断2()

   If Range("a1") <> "" And Range("a2")<> "" Then

       Range("a3") = Range("a1") * Range("a2")

       End If

End Sub

2、select判断语句

Sub 判断1() '单条件判断

   Select Case Range("a1").Value

   Case Is > 0

       Range("b1") = "正数"

   Case Else

       Range("b1") = "负数或0"

   End Select

End Sub

Sub 判断2() '多条件判断

   Select Case Range("a1").Value

   Case Is > 0

       Range("b1") = "正数"

   Case Is = 0

       Range("b1") = "0"

   Case Else

       Range("b1") = "负数"

   End Select

End Sub

Sub 判断3()

   If Range("a3") < "G" Then

   MsgBox "A-G"

   End If

End Sub

3、Iif函数判断

Sub 判断4()

   Range("a3") = IIf(Range("a1") <= 0, "负数或零", "负数")

End Sub

4、判断范围

Sub if区间判断()

   If Range("a1") <= 1000 Then

       Range("b2") = 0.01

   ElseIf Range("a1") <= 3000 Then

       Range("b2") = 0.03

   ElseIf Range("a1") > 3000 Then

       Range("b2") = 0.05

   End If

End Sub

Sub select区间判断()

   Select Case Range("a1").Value

   Case 0 To 1000

       Range("b2") = 0.01

   Case 1001 To 3000

       Range("b2") = 0.03

   Case Is > 3000

       Range("b2") = 0.05

  End Select

End Sub

xgczwp

感谢分享。

xgczwp

民扬 发表于 2018-12-17 23:13
259个Excl常用宏

先收藏学习，多谢分享

民扬

第四集 循环语句

Sub 示例1()
Range("d2") = Range("b2") * Range("c2")
Range("d3") = Range("b3") * Range("c3")
Range("d4") = Range("b4") * Range("c4")
Range("d5") = Range("b5") * Range("c5")
Range("d6") = Range("b6") * Range("c6")
Range("d7") = Range("b7") * Range("c7")
Range("d8") = Range("b8") * Range("c8")
End Sub


Sub 示例2()
Dim x As Integer
    For x = 2 To 8
    Range("d" & x) = Range("b" & x) * Range("c" & x)
    Next x
End Sub
Sub 示例2A1()
Dim x As Integer
    For x = 10000 To 2 Step -3
    Range("d" & x) = Range("b" & x) * Range("c" & x)
    Next x
End Sub

Sub 示例3()
Dim rg As Range
    For Each rg In Range("d2:d18")
    rg = rg.Offset(0, -1) * rg.Offset(0, -2)
    Next rg
End Sub

Sub 示例3A1()
Dim rg As Range
    For Each rg In Range("A1:B7,D5:E9")
    If rg = "" Then
    rg = 0
    End If
    Next rg
End Sub


Sub 示例4()
Dim x As Integer
x = 1
Do
    x = x + 1
    Cells(x, 4) = Cells(x, 2) * Cells(x, 3)
    Loop Until x = 18   '循环终止
End Sub

Sub 示例4A1()
Dim x As Integer
Do
    x = x + 1
    If Cells(x + 1, 1) <> Cells(x, 1) + 1 Then
        Cells(x, 2) = "断点"
        Exit Do
    Loop Until x = 14
End Sub

Sub 示例5()
    x = 1
    Do While x < 18
        x = x + 1
     Cells(x, 4) = Cells(x, 2) * Cells(x, 3)
Loop
End Sub

民扬

第五集 变量的声明和使用

一、什么是变量

所谓变量，就是可变的量。

Sub 例1()

Dim x As Integer 'x就是一个变量

   For x = 1 To 10

       Cells(x, 1) = x

       Next x

End Sub

二、变量可以以什么形式？

1、 如数字（参见例1）

2、 放文本

Sub 例2()

   Dim st As String

   Dim x As Integer

   For x = 1 To 10

       st = st & "Excel精英培训"

       Next x

End Sub

3、 放对象

Sub 例3()

   Dim rg As String

   Set rg = Range("a1")

   rg = 100

End Sub

4、 放数组

Sub 例4()

   Dim arr(1 To 10) As Integer, x As Integer

   For x = 1 To 10

       arr(x) = x

       Next x

End Sub

三、变量的类型和声明

1、 变量的类型

详见帮助文件

2、 为什么要声明变量

3、 声明变量

Dim public

四、变量的存活周期

1、 过程级变量：过程结束，变量值释放

如例1

2、 模块级变量：变量的值只在本模块中保持，工作薄关闭时随时释放

Sub 例5()

msgbox m

m = 7

End Sub

Sub 例6()

m = 1

End Sub

3、 全局级变量：在所有的模块中都可以调用，值会保存到Excel关闭时才会释放。

Public 变量

在任意模块中声明Public 变量

如：public qq as integer

Sub dd()

       Qq=12

End sub

Sub 例7()

MsgBox qq

End Sub

五、变量的释放

一般情况下，过程级变量在过程运行结束后就会自动从内存中释放。

民扬

第六集 函数与公式

一、在单元格中输入公式

1、 用VBA在单元格中输入普通公式

Sub 例1()

   Range("d2") = "=b2*c2"

End Sub

Sub 例2()

   Dim x As Integer

   For x = 2 To 6

       Cells(x, 4) = "=b" & x & "*c" & x

       Next x

End Sub

2、 用VBA在单元格输入带引号的公式

Sub 例3()

   Range("c9") ="=sumif(a2:a6,""b"",b2:b6)" '遇到带引号的就把引号加倍

End Sub

3、 用VBA在单元格中输入数组公式

Sub 例4()

   Range("c9").formularray = "=sum(b2:b6*c2:c6)"

End Sub

二、利用单元格公式返回值

Sub 例5()

   Range("c13") = Evaluate("=sumif(a2:a6,""b"",b2:b6)")

   Range("c14") = Evaluate("=sum(a2:a6*b2:b6)")

End Sub

三、借用工作表函数

Sub 例6()

   Range("c13") =Application.worksheefunction.Sum(Range("d2:d6"))

End Sub

四、利用VBA函数

Sub 例7()

   Range("c20") = VBA.InStr(Range("a20"),"e")

End Sub

五、编写自定义函数

  Function wn()

   wn= Application.Caller.Parent.Name

  End Function

民扬

第七集 VBA编辑器

一、VBA的窗口

1、 工种窗口

A、 显示工作薄工作表对象

B、 窗体

C、 模块

D、类模块

2、对应工种窗口的对象和模板，显示其所具体的一些特征。

       3、代码窗口

       A、注释文字的设置

       B、代码缩进的设置

       C、代码强制转行的设置

       D、代码运行和调试

              a逐句运行

              b设置断点

E、 对象列表框和过程列表框

4、立即窗口

立即窗口可以把运行过程中的值立即显示出来，主要用于程序的调试

Sub d()

    Dim x As Integer

    For x = 1 To 10

        st = st & Cells(x, 1)

        Debug.Print "第" & x & "次运行结果："& st

        Next x

End Sub

5、本地窗口

在本地窗口中可以显示运行中断对象信息、变量值、数组信息等。

Sub d1()

    Dim x As Integer, k As Integer

    For x = 1 To 10

        k = k + Cells(x, 1)

        If k > 26 Then

        Stop

        End If

    Next x

End Sub

民扬

第八集 VBA分支与END语句

一、End语句

作用：强制退出所有正在运行的程序

二、Exit语句

1、 Exit sub

Sub 例1()

   Dim x As Integer

       For x = 1 To 100

           Cells(1, 1) = x

           If x = 5 Then

                Exit Sub

           End If

       Next x

   Range("b1") = 100

End Sub

2、 Exit function

Function 例2()

   Dim x As Integer

       For x = 1 To 100

            If x = 5 Then

                Exit Function

           End If

       Next x

   ff=100

End Function

3、 Exit for

Sub 例3()

   Dim x As Integer

       For x = 1 To 100

           Cells(1, 1) = x

           If x = 5 Then

                Exit For

           End If

       Next x

   Range("b1") = 100

End Sub

4、 Exit do

Sub 例4()

    Dim x As Integer

   Do

       x = x + 1

           Cells(1, 1) = x

           If x = 5 Then

                Exit Do

           End If

       Loop Until x = 100

   Range("b1") = 100

End Sub

三、分支语句

1、 Goto语句，跳转到指定的地方

Sub 例1()

   Dim x As Integer

   Dim sr

100:

   sr = Application.InputBox("请输入数字","输入提示")

   If Len(sr) = 0 Or Len(sr) = 5 Then GoTo 100

End Sub

2、 gosub..return,跳过去，再跳回来

Sub 例2()

   Dim x As Integer

   Dim x As Integer

   For x = 1 To 10

       If Cells(x, 1) Mod 2 = 0 Then GoSub 100

   Next x

Exit Sub

100:

       Cells(x, 1) = "偶数"

       Return

End Sub

3、 on error resume next 遇到错误，跳过继续执行下一句

Sub 例3()

'On Error Resume Next

   Dim x As Integer

   For x = 1 To 10

        Cells(x, 3) = Cells(x, 2) * Cells(x, 1)

   Next x

EndSub

4、 on error goto 出错时跑到指定的行数

Sub 例4()

On Error GoTo 100

   Dim x As Integer

   For x = 1 To 10

        Cells(x, 3) = Cells(x, 2) * Cells(x, 1)

   Next x

       Exit Sub

100:

       MsgBox "在第" & x & "出错了"

End Sub

5、 on error goto 0 取消错误跳转

Sub 例5()

On Error Resume Next

   Dim x As Integer

   For x = 1 To 10

       If x > 5 Then On Error GoTo 0

        Cells(x, 3) = Cells(x, 2) * Cells(x, 1)

   Next x

       Exit Sub

End Sub

民扬

第九集 Excel文件操作

一、Excel文件和工作薄

Excel文件就是excel工作薄，excel文件找开需要excel程序的支持

Workbooks 工作薄集合，泛指excel文件或工作薄

Workbooks(“A,xls”),名称为A的excel工作薄

Sub 例1()

   Workbooks("A,xls").Sheets(1).Range("a1") = 100

End Sub

Wworkbooks(2),按打开顺序，第二个打开的工作薄

Sub 例2()

   Workbooks(2).Sheets(2).Range("a1") = 200

End Sub

ActiveWorkbook,当打开多个excel工作薄时，你正在操作的那个就是ActiveWorkbook(活动工作薄)

Thisworkbook,VBA程序所在的工作薄，无论你打开多少个工作薄，无论当前是哪个工作薄是活动的，thisworkbook就是批它所在的工作薄

二、工作薄窗口

Windows(“A,xls”)，A工作薄的窗口，使用windows可以设置工作薄窗口的状态，如是否隐藏等。

Sub 例3()

   Windows("A,xls").Visible = False

End Sub

Sub 例4()

   Windows(2).Visible = True

End Sub

三、excel文件的操作与方法

1、 判断A.xls文件是否存在

Sub 例1()

   If Len(Dir("d:/A.xls")) = 0 Then

       MsgBox "A文件不存在"

   Else

       MsgBox "A文件存在"

   End If

End Sub

2、 判断A.xls文件是否打开

Sub 例2()

Dim x As Integer

   For x = 1 To Windows.Count

       If windosw(x).Caption = "A.xls" Then

           MsgBox "A文件打开了"

           Exit Sub

       End If

   Next

End Sub

3、 excel文件新建和保存

Sub 例3()

Dim wb As Workbook

   Set wb = Workbooks.Add

       wb.Sheets("sheet1").Range("a1") = "abcd"

   wb.SaveAs "d:/b.xls" ‘SaveAs另存为的意思

End Sub

4、 excel文件打开和关闭

Sub 例4()

Dim wb As Workbook

   Set wb = Workbooks.Open("D:/B.xls")

       MsgBox wb.Sheets("sheet1").Range("a1").Value

        wb.Close False 'True就是保存，False就是不保存

End Sub

5、 excel文件保存和备份

Sub 例5()

Dim wb As Workbook

   Set wb = Workbooks

       wb.Save  '保存文件

       wb.SaveCopyAs "D:/ABC.xls" '备份文件

End Sub

6、 excel文件复制和删除

Sub 例6()

   FileCopy "D:/ABC.xls", "E:/ABCd.XLS" '复制文件，从D盘复制到E盘

   Kill "D:/ABC.xls"  '删除文件

End Sub