值得反思的VBA數(shù)據(jù)類型的拓展類

VBA用了很多年了，它的內(nèi)建數(shù)據(jù)類型應(yīng)該很多人都非常熟悉。

Boolean數(shù)據(jù)類型 　　 變量存儲為 16 位       只能是 True 或是 False

Byte數(shù)據(jù)類型 　　　　變量存儲為 8 位         范圍在 0 至 255 之間

Currency數(shù)據(jù)類型 　　變量存儲為 64 位      范圍可以從 -922,337,203,685,477.5808 到 922,337,203,685,477.5807

Date數(shù)據(jù)類型 　　　　變量存儲為  64 位    

                                       日期范圍從 100 年 1 月 1 日到 9999 年 12 月 31 日，

                                       時(shí)間可以從 0:00:00 到 23:59:59。

Decimal數(shù)據(jù)類型　　  變量存儲為 96 位

Double數(shù)據(jù)類型 　　  變量存儲為 64 位

Integer數(shù)據(jù)類型 　　  變量存儲為 16 位　　范圍為 -32,768 到 32,767 之間

Long數(shù)據(jù)類型 　　　變量存儲為 32 位　　范圍從 -2,147,483,648 到 2,147,483,647

Object數(shù)據(jù)類型 　　存儲為 32 位（4 個(gè)字節(jié)）的地址形式

Single數(shù)據(jù)類型             變量存儲為 IEEE 32 位

String數(shù)據(jù)類型            字符串有兩種：變長與定長的字符串。

                                      變長字符串最多可包含大約 20 億 ( 2^31)個(gè)字符。

                                      定長字符串可包含 1 到大約 64K ( 2^16 ) 個(gè)字符。

用戶定義數(shù)據(jù)類型      UDT

Variant數(shù)據(jù)類型       Variant 是一種特殊的數(shù)據(jù)類型，除了定長 String 數(shù)據(jù)及用戶定義類型外，可以包含任何種類的數(shù)據(jù)。Variant 也可以包含 Empty、Error、Nothing 及 Null等特殊值�？梢杂� VarType 函數(shù)或 TypeName 函數(shù)來決定如何處理 Variant 中的數(shù)據(jù)

 

 

一般的基礎(chǔ)數(shù)據(jù)類型，在一個(gè)sub或function中，它的變量作用域只能固定在本函數(shù)中，也就是調(diào)用這個(gè)函數(shù)時(shí)，變量在棧上自動(dòng)生成，調(diào)用完，它的變量自動(dòng)在棧上清除。Public和static靜態(tài)變量除外。這樣子的機(jī)制，無形中能夠節(jié)省很多內(nèi)存。

 

但在今天，“一切都是對象”的口號下，很多語言的內(nèi)建數(shù)據(jù)類型本身也是對象。換句話說，數(shù)據(jù)類型本身也是類。這會帶來了便利性，但同時(shí)也意味性能的損耗。因?yàn)榍宄�一個(gè)對象遠(yuǎn)遠(yuǎn)比在棧上清除一個(gè)變量要復(fù)雜一些。

一般語言的類的構(gòu)造器和析構(gòu)器，都是屬于類的本身內(nèi)部。但vba很特殊，它的構(gòu)造器是在外部，即iClassFactory，析構(gòu)器是在內(nèi)部，但你必須祈禱當(dāng)set object=nothing時(shí)，引用計(jì)數(shù)能變成0，這樣才有辦法去清除對象�？傊�這個(gè)析構(gòu)過程也不是那么清晰明朗。

因?yàn)檫@個(gè)特殊性，所以我們才有必要對VBA數(shù)據(jù)類型拓展到底有沒有必要，進(jìn)行斟酌。

但毫無疑問數(shù)據(jù)類型拓展，會帶來很多便利性。

其中VBA數(shù)據(jù)類型，最需要被拓展的就是數(shù)組和字符串，其它的數(shù)值，相比較而言，意義不那么大。

 數(shù)組Array的拓展類：

Public Property Get BoundL(Optional ByVal Dimension As Long) As Long 

'相當(dāng)于Lbound()函數(shù)，返回某維數(shù)組的下標(biāo)，Dimension:表示維數(shù)，維數(shù)必須>0且<=總維數(shù)。如果未指定維數(shù)，默認(rèn)使用第一維

Public Property Get BoundU(Optional ByVal Dimension As Long) As Long 

Public Property Get Data() As Long 

'返回?cái)?shù)組第一個(gè)元素?cái)?shù)據(jù)的指針。注意：元素?cái)?shù)據(jù)在內(nèi)存中的指針并不等于數(shù)組的指針

Public Property Get Dimensions() As Long

'返回?cái)?shù)組的總維數(shù)，如二維數(shù)組返回2，三維數(shù)組返回3

Public Property Get Elements(Optional ByVal Dimension As Long) As Long  

'返回某維數(shù)組的元素總數(shù)。相當(dāng)于Ubound()-Lbound()

Public Property Get ElementSize() As Long  

'返回?cái)?shù)組所有元素總的字節(jié)大小

Public Property Get Item(ParamArray Indexes()) As Variant 

Public Property Let Item(ParamArray Indexes(), ByRef NewValue As Variant)  

Public Property Get Pointer() As Long   

'返回當(dāng)前數(shù)組的指針。該指針指向數(shù)組的首地址。注：可以用指針值來判斷數(shù)組是否存在（非0）或不存在（等于0）

Public Property Get Type_() As VbVarType   

'返回?cái)?shù)組元素的數(shù)據(jù)類型

Public Property Get Value() As Variant

'返回?cái)?shù)組元素的值，默認(rèn)

Public Property Let Value(ByRef NewValue As Variant)   

Public Function Add(ByRef Item As Variant) As ArrayEx  

'在當(dāng)前數(shù)組最后一維追加一個(gè)元素，如果數(shù)組類的數(shù)組不存在，將按該元素的數(shù)據(jù)類型初始化數(shù)組，并確保數(shù)組元素是該元素的數(shù)據(jù)類型

Public Function AddRange(ByRef Range As Variant) As ArrayEx

'在當(dāng)前數(shù)組的最后一維追加一系列元素。
Range:一般是一維數(shù)組，注意：該系列的元素?cái)?shù)據(jù)類型必須和數(shù)組類中已存在的數(shù)組元素是相同的數(shù)據(jù)類型，否則數(shù)組類的數(shù)組無法追加。

例子：

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  

'創(chuàng)建一個(gè)新數(shù)組。
ArrayType：數(shù)組的數(shù)據(jù)類型

Bounds()：數(shù)組每一維的元素個(gè)數(shù)，Bounds最大值是32

Dim a As New ArrayEx

a.Create vbLong, 2&, Array(0&, 4&), Array(-1&, 10&) '定義二維數(shù)組5行，12列
Set a = Nothing

Public Function Destroy() As ArrayEx

'銷毀數(shù)組類數(shù)組并自動(dòng) set 數(shù)據(jù)類對象=nothing

Public Function Parse(ByRef Value As Variant) As ArrayEx  

'轉(zhuǎn)換數(shù)據(jù)類型并賦值給數(shù)組元素

Public Function Resize(ByVal NewUpperBound As Long) As ArrayEx  

'重置數(shù)組類數(shù)組最后一維的大小，如果NewUpperBound>OldUpperBound，將追加元素總數(shù)大小，如果小于，將裁剪元素總數(shù)大小。

String類型拓展：

 

Public Property Get Asc(Optional ByVal Index As Long) As Long  

'返回字符串指定位置的字符的ASNI碼，index是字符在字符串中的位置，必須>0且<Length大小，否則函數(shù)返回0

Public Property Get Capacity() As Long   

'返回內(nèi)存分配的字符串緩存區(qū)字節(jié)大小

Public Property Get Length() As Long   

'返回字符串的大小

Public Property Get Pointer() As Long   

'返回字符串緩存區(qū)的指針

Public Property Get Value() As String

'返回字符串，默認(rèn)值

Public Property Let Value(ByRef NewValue As String)  

'對字符串賦值

Public Function Clone() As StringEx   

'新建一個(gè)相同的String類，深復(fù)制當(dāng)前String對象，包括數(shù)據(jù)結(jié)構(gòu)和數(shù)據(jù)

例子：

Dim s As New StringEx
MsgBox 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大于當(dāng)前類的字符串，返回-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是基于內(nèi)存緩存直接拼接，效率要高于傳統(tǒng)的&或+的字符串拼接。

例子：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   

'以指針的方式進(jìn)行字符串拼接，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   

' 以指定次數(shù)，重復(fù)同一字符

例子：

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   

'清除字符串中非數(shù)值，并只返回?cái)?shù)值部分

例子：

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 

'對任何可以轉(zhuǎn)換成String的值進(jìn)行轉(zhuǎn)換并賦值給String類的字符串 。注：一維的Byte數(shù)組被視作字符串?dāng)?shù)組，Parse方法將先檢查數(shù)組的Bom(UTF-8, UTF-16 BE/LE)和字節(jié)大小，然后開始轉(zhuǎn)換。對于常見的任何字符集編碼如utf-8等，都可以通Parse方法轉(zhuǎn)化成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

'移除指定開始位置，指定數(shù)量的子字符串
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

'將字符串轉(zhuǎn)成Byte數(shù)組，AsANSI是true，以ansi碼轉(zhuǎn)換，否則，以unicode碼轉(zhuǎn)換。

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數(shù)據(jù)類型拓展:

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平時(shí)隱藏在variant下面，所以用的人很少很少。

和double相比，decimal 類型具有更高的精度和更小的范圍，該類型適用于必須避免舍入錯(cuò)誤的應(yīng)用程序如財(cái)務(wù)、貨幣計(jì)算、金融等。

double是64位的，比single-32位精度高  

decimal是128位高精度浮點(diǎn)數(shù)，常用于金融運(yùn)算，不會出現(xiàn)浮點(diǎn)數(shù)計(jì)算的誤差，

decimal 類型具有更高的精度和更小的范圍，這使它適合于財(cái)務(wù)和貨幣計(jì)算。

注意：decimal在大多數(shù)情況下是安全的，但浮點(diǎn)數(shù)在理論上是不安全的。

         decimal不是基本數(shù)據(jù)類型，所以在所有的數(shù)值計(jì)算中，decimal計(jì)算是最慢的。

Public Property Get Length() As Long

'返回當(dāng)前Decimal值的字節(jié)大小

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值     默認(rèn)值

Public Property Let Value(ByRef NewValue As Variant)

Public Function Clone() As DecimalEx

‘新建一個(gè)Decimal對象，并深度復(fù)制當(dāng)前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

‘將值轉(zhuǎn)換數(shù)據(jù)類型，并賦值

Dim d As New DoubleEx

MsgBox d.Parse("hi115.004") & vbCrLf & d.Parse(115&) & vbCrLf & d.Parse(345478.0002@)

'顯示115.004 (非數(shù)字部分自動(dòng)忽略), 115 (long轉(zhuǎn)換成decimal) and 345478.0002(Currency轉(zhuǎn)換成decimal)

Public Function Round(ByVal DecimalDigits As Long) As DecimalEx

‘對Decimal值進(jìn)行十進(jìn)制數(shù)學(xué)上的四舍五入。DecimalDigits：保留小數(shù)點(diǎn)后的位數(shù)

例子：

Dim d As New DoubleEx

MsgBox d.Parse(115.11289).Round(3&) '顯示115.113

Set d = Nothing

Public Function ToArray() As ArrayEx

‘新建一個(gè)數(shù)據(jù)類型是內(nèi)建的decimal類型的一維數(shù)組，并將當(dāng)前decimal值賦值給數(shù)組的第一元素

例子：

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

‘將當(dāng)前Decimal值轉(zhuǎn)換成String，Format：用戶自定義格式，詳見幫助文檔Format函數(shù)

例子：

Dim d As New DecimalEx

MsgBox d.Parse("1234.0000000005").ToString("##,##0.000000000") '顯示 1,234.000000001

Set d = Nothing

待續(xù)……

我比較擔(dān)心的是，如果使用這樣的數(shù)據(jù)類型拓展類，會不會引來不少人使用上的混亂。

因?yàn)槿魏我淮问褂枚急仨?/span>dim 變量 as new StringEX，使用完之后，必須是 set 變量=nothing。

如果忘了，如果真的忘了……,那老衲有罪呀。