日志
值得反思的VBA数据类型的拓展类
热度 1 ||
值得反思的VBA数据类型的拓展类
VBA用了很多年了，它的内建数据类型应该很多人都非常熟悉。
Boolean数据类型 变量存储为 16 位 只能是 True 或是 False
Byte数据类型 变量存储为 8 位 范围在 0 至 255 之间
Currency数据类型 变量存储为 64 位 范围可以从 -922,337,203,685,477.5808 到 922,337,203,685,477.5807
Date数据类型 变量存储为 64 位
日期范围从 100 年 1 月 1 日到 9999 年 12 月 31 日，
时间可以从 0:00:00 到 23:59:59。
Decimal数据类型 变量存储为 96 位
Double数据类型 变量存储为 64 位
Integer数据类型 变量存储为 16 位 范围为 -32,768 到 32,767 之间
Long数据类型 变量存储为 32 位 范围从 -2,147,483,648 到 2,147,483,647
Object数据类型 存储为 32 位（4 个字节）的地址形式
Single数据类型 变量存储为 IEEE 32 位
String数据类型 字符串有两种：变长与定长的字符串。
变长字符串最多可包含大约 20 亿 ( 2^31)个字符。
定长字符串可包含 1 到大约 64K ( 2^16 ) 个字符。
用户定义数据类型 UDT
Variant数据类型 Variant 是一种特殊的数据类型，除了定长 String 数据及用户定义类型外，可以包含任何种类的数据。Variant 也可以包含 Empty、Error、Nothing 及 Null等特殊值。可以用 VarType 函数或 TypeName 函数来决定如何处理 Variant 中的数据
一般的基础数据类型，在一个sub或function中，它的变量作用域只能固定在本函数中，也就是调用这个函数时，变量在栈上自动生成，调用完，它的变量自动在栈上清除。Public和static静态变量除外。这样子的机制，无形中能够节省很多内存。
但在今天，“一切都是对象”的口号下，很多语言的内建数据类型本身也是对象。换句话说，数据类型本身也是类。这会带来了便利性，但同时也意味性能的损耗。因为清除一个对象远远比在栈上清除一个变量要复杂一些。
一般语言的类的构造器和析构器，都是属于类的本身内部。但vba很特殊，它的构造器是在外部，即iClassFactory，析构器是在内部，但你必须祈祷当set object=nothing时，引用计数能变成0，这样才有办法去清除对象。总之这个析构过程也不是那么清晰明朗。
因为这个特殊性，所以我们才有必要对VBA数据类型拓展到底有没有必要，进行斟酌。
但毫无疑问数据类型拓展，会带来很多便利性。
其中VBA数据类型，最需要被拓展的就是数组和字符串，其它的数值，相比较而言，意义不那么大。
数组Array的拓展类：
Public Property Get BoundL(Optional ByVal
Dimension As Long) As Long
'相当于Lbound()函数，返回某维数组的下标，Dimension:表示维数，维数必须>0且<=总维数。如果未指定维数，默认使用第一维
Public Property Get BoundU(Optional ByVal Dimension As Long) As Long
Public Property Get Data() As Long
'返回数组第一个元素数据的指针。注意：元素数据在内存中的指针并不等于数组的指针
Public Property Get Dimensions() As Long
'返回数组的总维数，如二维数组返回2，三维数组返回3
Public Property Get Elements(Optional ByVal
Dimension As Long) As Long
'返回某维数组的元素总数。相当于Ubound()-Lbound()
Public Property Get ElementSize() As Long
'返回数组所有元素总的字节大小
Public Property Get Item(ParamArray Indexes()) As Variant
Public Property Let Item(ParamArray Indexes(), ByRef NewValue As Variant)
Public Property Get Pointer() As Long
'返回当前数组的指针。该指针指向数组的首地址。注：可以用指针值来判断数组是否存在（非0）或不存在（等于0）
Public Property Get Type_() As VbVarType
'返回数组元素的数据类型
Public Property Get Value() As Variant
'返回数组元素的值，默认
Public Property Let Value(ByRef NewValue As Variant)
Public Function Add(ByRef Item As Variant)
As ArrayEx
'在当前数组最后一维追加一个元素，如果数组类的数组不存在，将按该元素的数据类型初始化数组，并确保数组元素是该元素的数据类型
Public Function AddRange(ByRef Range As Variant) As ArrayEx
'在当前数组的最后一维追加一系列元素。
Range:一般是一维数组，注意：该系列的元素数据类型必须和数组类中已存在的数组元素是相同的数据类型，否则数组类的数组无法追加。
例子：
Dim a As New ArrayEx
Dim b(2) As Long
b(0) = 123&: b(1) = 456&: b(2) = 789&
MsgBox a.AddRange(b).Item(2) '显示 789
Set a = Nothing
Public Function Clone() As ArrayEx
例子：
Dim a As New ArrayEx
Dim a2 As New ArrayEx
a.Create(vbByte, 1&, Array(0&, 1&)).Item(0) = 255
Set a2 = a.Clone
a2.Item(0) = 100
MsgBox a.Item(0) & vbCrLf & a2.Item(0) '显示 255 and 100
Set a = Nothing
Set a2 = Nothing
Public Function Create(ByVal ArrayType As
VbVarType, ParamArray Bounds()) As ArrayEx
'创建一个新数组。
ArrayType：数组的数据类型
Bounds()：数组每一维的元素个数，Bounds最大值是32
Dim a As New ArrayEx
a.Create vbLong, 2&, Array(0&, 4&), Array(-1&, 10&) '定义二维数组5行，12列
Set a = Nothing
Public Function Destroy() As ArrayEx
'销毁数组类数组并自动 set 数据类对象=nothing
Public Function Parse(ByRef Value As
Variant) As ArrayEx
'转换数据类型并赋值给数组元素
Public Function Resize(ByVal NewUpperBound
As Long) As ArrayEx
'重置数组类数组最后一维的大小，如果NewUpperBound>OldUpperBound，将追加元素总数大小，如果小于，将裁剪元素总数大小。
String类型拓展：
Public Property Get Asc(Optional ByVal
Index As Long) As Long
'返回字符串指定位置的字符的ASNI码，index是字符在字符串中的位置，必须>0且<Length大小，否则函数返回0
Public Property Get Capacity() As Long
'返回内存分配的字符串缓存区字节大小
Public Property Get Length() As Long
'返回字符串的大小
Public Property Get Pointer() As Long
'返回字符串缓存区的指针
Public Property Get Value() As String
'返回字符串，默认值
Public Property Let Value(ByRef NewValue As
String)
'对字符串赋值
Public Function Clone() As StringEx
'新建一个相同的String类，深复制当前String对象，包括数据结构和数据
例子：
Dim s As New StringExMsgBox s.Parse("Hello World").Clone.Parse("!!!") '显示 !!!
MsgBox s '显示 Hello World
Set s = Nothing
Public Function Compare(ByRef CompareString
As String, Optional ByVal CompareMethod As VbCompareMethod) As IntegerEx
'比较字符串 CompareString:被比较字符串，如果CompareString大于当前类的字符串，返回-1；如果相等，返回0；如果小于，返回1；
Dim s As New StringEx
MsgBox s.Parse("Hello World").Compare("hello world") '显示 -1
Set s = Nothing
Public Function Concat(ByRef ConcatString
As String) As StringEx
'字符串拼接，Concat是基于内存缓存直接拼接，效率要高于传统的&或+的字符串拼接。
例子：Dim s As New StringEx
MsgBox s.Parse("Hello World").Concat("!!!") '显示 Hello World!!!
Set s = Nothing
Public Function ConcatPointer(ByVal
ConcatStringPointer As Long) As StringEx
'以指针的方式进行字符串拼接，ConcatPointer比Concat方法快10倍左右
例子：
Dim i As Long
Dim p As String
Dim s As New StringEx
Dim t As String
t = "Hello World! "
p = StrPtr(t)
For i = 1& To 300000
s.ConcatPointer p
Next i
MsgBox s '显示 300 000 次Hello World!拼接 非常快
Set s = Nothing
Public Function Duplicate(ByVal Count As
Long) As StringEx
' 以指定次数，重复同一字符
例子：
Dim s As New StringEx
MsgBox s.Parse("Hello World ").Duplicate(2&) '显示 Hello World Hello World Hello World
Set s = Nothing
Public Function Find(ByRef SearchString As
String, Optional ByVal Start As Long, Optional ByVal Reverse As Boolean,
Optional ByVal CompareMethod As VbCompareMethod) As LongEx
'查找子字符串在字符串中的位置。Reverse:是指从左向右，还是从右向左
例子：
Dim s As New StringEx
MsgBox s.Parse("Hello World ").Find("World") '显示 7
Set s = Nothing
Public Function Insert(ByVal Index As Long, ByRef InsertString As String) As StringEx
Public Function Left(ByVal Length As Long)
As StringEx
Public Function Lower() As StringEx
Public Function Mid(ByVal Start As Long, Optional ByVal Length As Long) As StringEx
Public Function Numeric() As StringEx
'清除字符串中非数值，并只返回数值部分
例子：
Dim s As New StringEx
MsgBox s.Parse("Hello 123 World").Numeric '显示 123
Set s = Nothing
Public Function PadRight(ByVal Width As Long) As StringEx
Public Function Parse(ByRef Value As
Variant) As StringEx
'对任何可以转换成String的值进行转换并赋值给String类的字符串 。注：一维的Byte数组被视作字符串数组，Parse方法将先检查数组的Bom(UTF-8, UTF-16 BE/LE)和字节大小，然后开始转换。对于常见的任何字符集编码如utf-8等，都可以通Parse方法转化成vb能用的string类型
例子：
Dim bAnsi(4) As Byte
Dim bUnicodeBOM(11) As Byte
Dim bUnicode(9) As Byte
Dim s As New StringEx
bAnsi(0) = 72: bAnsi(1) = 101: bAnsi(2) = 108: bAnsi(3) = 108: bAnsi(4) = 111
bUnicodeBOM(0) = 255: bUnicodeBOM(1) = 254: bUnicodeBOM(2) = 72: bUnicodeBOM(3) = 0:
bUnicodeBOM(4) = 101: bUnicodeBOM(5) = 0: bUnicodeBOM(6) = 108: bUnicodeBOM(7) = 0:
bUnicodeBOM(8) = 108: bUnicodeBOM(9) = 0: bUnicodeBOM(10) = 111: bUnicodeBOM(11) = 0
bUnicode(0) = 72: bUnicode(1) = 0: bUnicode(2) = 101: bUnicode(3) = 0: bUnicode(4) = 108:
bUnicode(5) = 0: bUnicode(6) = 108: bUnicode(7) = 0: bUnicode(8) = 111: bUnicode(9) = 0
MsgBox s.Parse(bAnsi) & vbCrLf & s.Parse(bUnicodeBOM) & vbCrLf & s.Parse(bUnicode) '显示Hello in all cases
Set s = Nothing
Public Function Remove(ByVal Index As Long, ByVal Length As Long) As StringEx
'移除指定开始位置，指定数量的子字符串
Public Function Replace(ByRef SearchString As String, ByRef ReplaceString As String, Optional ByVal Start As Long = 1&, Optional ByVal Count As Long = L_NG, Optional ByVal CompareMethod As VbCompareMethod) As StringEx
Public Function Split(Optional ByRef
Delimeter As String, Optional ByVal Limit As Long = L_NG, Optional ByVal
CompareMethod As VbCompareMethod, Optional ByVal ArrayType As VbVarType =
vbString) As ArrayEx
Public Function Right(ByVal Length As Long) As StringEx
Public Function ToArray(Optional ByVal
AsANSI As Boolean) As ArrayEx
'将字符串转成Byte数组，AsANSI是true，以ansi码转换，否则，以unicode码转换。
Dim s As New StringEx
MsgBox s.Parse("Hello").ToArray(True).Item(0) '显示 72 (剩下的分别是 101, 108, 108, 111)
Set s = Nothing
Public Function ToBoolean() As BooleanEx
Public Function ToByte() As ByteEx
Public Function ToCurrency() As CurrencyEx
Public Function ToDecimal() As DecimalEx
Public Function ToDouble() As DoubleEx
Public Function ToInteger() As IntegerEx
Public Function ToLong() As LongEx
Public Function ToSingle() As SingleEx
Public Function TrimL() As StringEx
Public Function TrimNull() As StringEx
Public Function TrimR() As StringEx
Public Function Upper() As StringEx
DateTime数据类型拓展:
Public Property Get Day() As Integer
Public Property Get Hour() As Integer
Public Property Get IsLeap() As Boolean
Public Property Get Length() As Long
Public Property Get Max() As Date
Public Property Get Millisecond() As Integer
Public Property Get Min() As Date
Public Property Get Minute() As Integer
Public Property Get Month() As Integer
Public Property Get Pointer() As Long
Public Property Get Second() As Integer
Public Property Get Value() As Date
Public Property Let Value(ByVal NewValue As Date)
Public Property Get Weekday() As Integer
Public Property Get Year() As Integer
Public Function AddDays(ByVal Count As Integer) As DateTimeEx
Public Function AddHours(ByVal Count As Integer) As DateTimeEx
Public Function AddMilliseconds(ByVal Count As Integer) As DateTimeEx
Public Function AddMinutes(ByVal Count As Integer) As DateTimeEx
Public Function AddMonths(ByVal Count As Integer) As DateTimeEx
Public Function AddSeconds(ByVal Count As Integer) As DateTimeEx
Public Function AddYears(ByVal Count As Integer) As DateTimeEx
Public Function Clone() As DateTimeEx
Public Function DateSerial(ByVal Year As Integer, ByVal Month As Integer, ByVal Day As Integer) As DateTimeEx
Public Function Now(Optional ByVal UTC As Boolean) As DateTimeEx
Public Function Parse(ByRef Value As Variant) As DateTimeEx
Public Function TimeSerial(ByVal Hour As Integer, ByVal Minute As Integer, ByVal Second As Integer, Optional ByVal Millisecond As Integer) As DateTimeEx
Public Function ToLong() As LongEx
Public Function ToString(Optional ByRef Format As String) As StringEx
DecimalEx拓展类
Decimal平时隐藏在variant下面，所以用的人很少很少。
和double相比，decimal 类型具有更高的精度和更小的范围，该类型适用于必须避免舍入错误的应用程序如财务、货币计算、金融等。
double是64位的，比single-32位精度高
decimal是128位高精度浮点数，常用于金融运算，不会出现浮点数计算的误差，
decimal 类型具有更高的精度和更小的范围，这使它适合于财务和货币计算。
注意：decimal在大多数情况下是安全的，但浮点数在理论上是不安全的。
decimal不是基本数据类型，所以在所有的数值计算中，decimal计算是最慢的。
Public Property Get Length() As Long
'返回当前Decimal值的字节大小
Public Property Get Max() As Variant
‘返回Decimal的最大值
Public Property Get Min() As Variant
‘返回Decimal的最小值
Public Property Get Pointer() As Long
‘返回Decimal值的指针，注意是值的指针不是DecimalEX对象的指针
Public Property Get Value() As Variant
‘返回Decimal值 默认值
Public Property Let Value(ByRef NewValue As
Variant)
Public Function Clone() As DecimalEx
‘新建一个Decimal对象，并深度复制当前Decimal对象。
例子：
Dim d As New DecimalEx
Dim d2 As New DecimalEx
d = 0.000008
Set d2 = d.Clone
d2 = 1.05555
MsgBox d & vbCrLf & d2 '显示0.000008 and 1.05555
Set d = Nothing
Set d2 = Nothing
Public Function Parse(ByRef Value As Variant) As DecimalEx
‘将值转换数据类型，并赋值
Dim d As New DoubleEx
MsgBox d.Parse("hi115.004") & vbCrLf & d.Parse(115&) & vbCrLf & d.Parse(345478.0002@)
'显示115.004 (非数字部分自动忽略), 115 (long转换成decimal) and 345478.0002(Currency转换成decimal)
Public Function Round(ByVal DecimalDigits As Long) As DecimalEx
‘对Decimal值进行十进制数学上的四舍五入。DecimalDigits：保留小数点后的位数
例子：
Dim d As New DoubleEx
MsgBox d.Parse(115.11289).Round(3&) '显示115.113
Set d = Nothing
Public Function ToArray() As ArrayEx
‘新建一个数据类型是内建的decimal类型的一维数组，并将当前decimal值赋值给数组的第一元素
例子：
Dim d As New DecimalEx
MsgBox d.Parse("1.0000000005").ToArray.Item(0) '显示1.0000000005
Set d = Nothing
Public Function ToBoolean() As BooleanEx
例子：
Dim d As New DecimalEx
MsgBox d.Parse("1,0000000005").ToBoolean '显示 True
Set d = Nothing
Public Function ToByte() As ByteEx
例子：
Dim d As New DecimalEx
MsgBox d.Parse("1.0000000005").ToByte '显示 1
Set d = Nothing
Public Function ToCurrency() As CurrencyEx
例子：
Dim d As New DecimalEx
MsgBox d.Parse("1.0002000005").ToCurrency '显示 1.0002
Set d = Nothing
Public Function ToDouble() As DoubleEx
例子：
Dim d As New DecimalEx
MsgBox d.Parse("1.0002000111354005").ToDouble '显示 1.0002000111354
Set d = Nothing
Public Function ToInteger() As IntegerEx
例子：
Dim d As New DecimalEx
MsgBox d.Parse("321.0002000111").ToInteger '显示 321
Set d = Nothing
Public Function ToLong() As LongEx
例子：
Dim d As New DecimalEx
MsgBox d.Parse("32146.0002000111").ToLong '显示 32146
Set d = Nothing
Public Function ToSingle() As SingleEx
例子：
Dim d As New DecimalEx
MsgBox d.Parse("32146.02000111").ToSingle '显示 32146.02
Set d = Nothing
Public Function ToString(Optional ByRef Format As String) As StringEx
‘将当前Decimal值转换成String，Format：用户自定义格式，详见帮助文档Format函数
例子：
Dim d As New DecimalEx
MsgBox d.Parse("1234.0000000005").ToString("##,##0.000000000") '显示 1,234.000000001
Set d = Nothing
待续……
我比较担心的是，如果使用这样的数据类型拓展类，会不会引来不少人使用上的混乱。
因为任何一次使用都必须dim 变量 as new StringEX，使用完之后，必须是 set 变量=nothing。
如果忘了，如果真的忘了……,那老衲有罪呀。