VBA｜自定义过程、函数、类型、枚举、类（属性及操作和类过程）

VBA中的四类模块：“标准模块”、Microsoft Excel工作簿和工作表对象、窗体、类模块。（VBE→插入菜单）

文档模块：Sheet1，Sheet2，Sheet3，ThisWorkbook；

窗体模块：UserForm1，包含窗体有关的代码；

标准模块：模块1，包含自定义过程和函数有关的代码；

类模块：类1，包含自定义类有关的代码；

1 自定义过程

A Sub is a procedure that performs a specific task but does not return a specific value.

Sub是执行特定任务但不返回特定值的过程。

Sub ProcedureName ([argument_list]) [statements]End Sub

If no access modifier is specified, a procedure is Public by default.

如果未指定访问修饰符，则默认情况下，过程是公共的。

demo code:

'合并单元格并保留全部内容'先选定需要合并的区域；Sub MergerSelectedCells()    Application.DisplayAlerts = False    Dim allstr As String    Dim Str As Object    For Each Str In Selection        If Str.Value <> '' Then            allstr = allstr & ' ' & Str.Value            allstr = Application.WorksheetFunction.Trim(allstr)        End If    Next Str    With Selection        .MergeCells = True        .Value = allstr        .WrapText = True        .HorizontalAlignment = xlGeneral        .VerticalAlignment = xlTop    End With    Application.DisplayAlerts = TrueEnd Sub

2 自定义函数

A Function is a procedure that is given data and returns a value, ideally without global or module-scope sideeffects.

函数是一个给定数据并返回值的过程，理想情况下没有全局或模块范围的副作用。

Function ProcedureName ([argument_list]) [As ReturnType] [statements]End Function

实例代码：

'自定义函数maxg(m, n),求最大公约数Function maxg(m, n)    If m < n Then        t = m        m = n        n = t    End If    r = m Mod n    Do While r > 0        m = n        n = r        r = m Mod n    Loop    maxg = nEnd FunctionFunction mygcd(m As Integer, n As Integer)    If n = 0 Then        mygcd = m    Else        mygcd = mygcd(n, m Mod n)    End IfEnd Function

3 自定义类型和枚举

VBA中，自定义类型相当于C语言中的结构体，枚举类型也与C语言中的枚举类型相似。自定义类型和枚举类型放到模块的子过程的前面即可。

'1自定义类型'1.1 自定义类型的声明Public Type Car Name As String Price As Currency Length As Single ShouDongDang As Boolean ProductionDate As DateEnd Type'1.2 自定义类型的使用Sub Test1() Dim MyCar As Car, YourCar As Car With MyCar .Name = '桑塔纳' .Price = 300000@ .Length = 4.2 .ShouDongDang = False .ProductionDate = #7/8/2015# End With With YourCar .Name = '大众' .Price = 80000@ .Length = 4.5 .ShouDongDang = True .ProductionDate = #2/18/2015# End With MsgBox '两辆车总价值：' & (MyCar.Price + YourCar.Price)End Sub'2 自定义枚举类型的声明和使用'2.1 自定义枚举类型的声明Public Enum JapaneseWeekDay 月曜日 火曜日 水曜日 木曜日 金曜日 土曜日 日曜日End EnumPublic Enum Screen Width = 1366 Height = 768End Enum'2.2自定义枚举类型的使用Sub Test1() Dim a As Long, b As Long a = JapaneseWeekDay.金曜日 b = JapaneseWeekDay.土曜日 MsgBox a + bEnd SubSub Test2() MsgBox Screen.Width * Screen.HeightEnd Sub

4 自定义类

VBA虽是基于对象的语言，但也可以自定义类。

VBA中， 类模块相当于C语言中的类，类模板要单独放到类模块中（自定义类型和子过程放在模块中），类模板的名称就是类的名称，可以做为新的类型进行声明和定义。

Alt+F11→“插入”→“类模块”→在“属性”窗口中修改类的名称为'clsADO'。

'1 创建私有变量Option Explicit'使用本类模块，需添ADO的引用'Microsoft ActiveX Data Objects 2.8 Library'Private cnn As Connection '连接对象Private rs As Recordset '记录集对象Private m_DBFullName As String 'Access数据库名称（包含路径信息)Private m_CommandText As String 'SQL语句'2 创建Property Get过程来获取对象的属性值Property Get DBFullName() As String '返回数据库名称    DBFullName = m_DBFullNameEnd PropertyProperty Get CommandText() As String '返回SQL语句    CommandText = m_CommandTextEnd Property'3 创建Property Let过程改变对象属性Property Let DBFullName(strDBFullName As String) '设置数据名称    If LCase(Right(strDBFullName, 4)) <> '.mdb' And _        LCase(Right(strDBFullName, 6)) <> '.accdb' Then        Exit Property    End If    m_DBFullName = strDBFullNameEnd PropertyProperty Let CommandText(strSQL As String) '设置SQL语句    m_CommandText = strSQLEnd Property'3 定义类的方法Private Sub ConnDB() '连接数据库    If cnn.State = 0 And Len(m_DBFullName) > 0 Then        cnn.Provider = 'Microsoft.Jet.OLEDB.4.0'        cnn.Open m_DBFullName    End IfEnd SubPublic Sub CloseDB() '关闭连接    cnn.CloseEnd SubPublic Function RunSQL() As Recordset '执行SQL语句    If Len(m_CommandText) > 0 Then        Call ConnDB '创建数据库连接        If rs.State <> 0 Then rs.Close            rs.Open Source:=m_CommandText, _            ActiveConnection:=cnn, _            CursorType:=adOpenStatic, _            LockType:=adLockReadOnly            Set RunSQL = rs '返回记录集    End IfEnd Function'2.4 定义类模块的事件Private Sub Class_Initialize() '初始化类    Set cnn = New Connection    Set rs = New RecordsetEnd SubPrivate Sub Class_Terminate() '终止类    If cnn.State = 1 Then        cnn.Close    End If    Set cnn = Nothing    Set rs = NothingEnd Sub'3 使用类模块'3.1 用新定义的类声明对象Dim tstADO As New clsADO'3.2 使用对象编写过程Sub test()    Dim rs As Recordset, fld As Field    On Error Resume Next    tstADO.DBFullName = ThisWorkbook.Path & '\Northwind.mdb' '设置属性    tstADO.CommandText = 'select * from 客户' '设置属性    Set rs = tstADO.RunSQL '调用自定义对象的方法    With Worksheets(1)        i = 1: j = 1        For Each fld In rs.Fields            .Cells(i, j) = fld.Name            j = j + 1        Next        j = 1        i = i + 1        Do While Not rs.EOF            For Each fld In rs.Fields                .Cells(i, j) = fld.Value                j = j + 1            Next            j = 1            rs.MoveNext            i = i + 1        Loop    End WithEnd Sub

A Property is a procedure that encapsulates module data. A property can have up to 3 accessors: Get to return a value or object reference, Let to assign a value, and/or Set to assign an object reference.

属性是封装模块数据的过程。一个属性最多可以有3个访问器：Get以返回值或对象引用，Let以指定值，和/或Set以指定对象引用。

Property Get|Let|Set PropertyName([argument_list]) [As ReturnType] [statements]End Property

Properties are usually used in class modules (although they are allowed in standard modules as well), exposing accessor to data that is otherwise inaccessible to the calling code. A property that only exposes a Get accessor is 'read-only'; a property that would only expose a Let and/or Set accessor is 'write-only'. Write-only properties are not considered a good programming practice - if the client code can write a value, it should be able to read it back.

属性通常在类模块中使用（尽管在标准模块中也允许使用），对访问者暴露调用代码无法访问的数据。仅公开Get访问器的属性为“只读”；仅公开Let和/或Set访问器的属性为“只写”。只写属性被认为不是一种好的编程实践——如果客户机代码可以写一个值，它应该能够读回它。

A Function or Property Get procedure can (and should!) return a value to its caller. This is done by assigning the identifier of the procedure:

函数或Property Get过程可以（而且应该！）将值返回给其调用者。这是通过给过程的标识符赋值来完成的：

Property Get Foo() As Integer    Foo = 42End Property

Any public Sub, Function, or Property inside a class module can be called by preceding the call with an object reference:

通过在调用之前使用对象引用，可以调用类模块中的任何公共子、函数或属性：

Object.Procedure

-End-