类-字典-数组-集合-接口-窗体

hyefeifei

jsxjd 发表于 2013-11-9 13:20
楼主可以试一下：
自定义一个在单元格中使用的函数，该函数修改某单元格的值。

如果我的话让你误解了，我重说一下，我的意思是说
所有sub，都可以被function替换，尽量使用function。
如果你还怀疑，你就构造出一个不能替换的例子，你说的是另一回事。
还是前面的例子
Function test ()
[a1].interior.colorindex=3
end
完全 可以改变a1的背景色，你非要用sub test，那也没办法。
所以，“如果需要直接操作单元格属性……那么Sub是必须的”，这是不对的。
function用做工作表函数时，当然功能受限制，但这不妨碍它在一切sub能运行的地方，
完全可以替换sub这一点。

lipton

很好的技术贴。
我个人的理解，函数(Function) 有返回值，过程(Sub)没有返回值,基于子过程职责清晰，单一职责的原则，还是分别使用Function,Sun的好。

hyefeifei

接上回：上回留了两个任务，一、引入枚举变量，二、引入方法。
这一回把枚举变量放一放，先引进方法，再利用数组引进一个集合类。
这样修改代码：首先要在类rngFormat 模块中建立声明一个变量，随意起个名字（当然变量起名最好有个自己一而贯之的规范），我们在类模块顶端声明变量：
PrivatemDataeype as string
然后把
Public Property Get DataType() As String
End Property
之间的代码剪除，换上DataType=mDatatype
现在代码为：
Public Property Get DataType() As String
   DataType = mDatatype
End Property
然后，我们用一个方法（sub或function）分析mDatatype的类型，代码如下:
Public Function WhatType()
    If IsEmpty(m_oCell) Then
        mDatatype = "空值"
    ElseIf m_oCell.HasFormula Then
        mDatatype = "公式"
    ElseIf IsNumeric(m_oCell) Then
        mDatatype = "数字"
    ElseIf IsDate(m_oCell) Then
        mDatatype = "日期"
    Else
       mDatatype = "文本"
End If
WhatType=mDatatype
End Sub
现在这个方法是个函数方法，也可以后回一个值，这个值就是单元格（m_oCell）的类型，但我们除非第一次引用单元格的类型时，用这个方法，在这个方法用过之后，再想得到单元格类型，直接就引用类rngFormat的属性DataType就可以了。
下面我们简单修改一下module模块中的test程序，看看修改后的代码是不是正常运行：
Sub test()
    Dim rng As Range, i As Long
    Dim rF As rngFormat                '声明类模块
    Set rF = New rngFormat           '实例化类，形成对象
    For Each rng In Range("a1:a5")
        rng.Value = Array(#6/1/1966#,"abc", "=3", "100", "")(i)
        Set rF.Cell = rng                    '把rng赋值给类的cell属性
        rF.WhatType                       '此一句新加，运行方法后，才可以得到datatype属性
        rng.Offset(, 1) = rF.DataType     '在右边写出类的datatype属性
        i = i + 1
    Next
End Sub
可以看到，只增加了一句：rF.WhatType。
下面说说集合类，大家都知道，excel 有worksheet 对象，就有worksheets对象，有shape 对象，就有shapes对象，与此对照，我们的类最好也应该有一个与之对应的集合对象rngFormats，下面，我们就要构造rngFormats这个类，这个类的属性大家也能想像到应该包含count属性，item属性等，当然我们可以根据我们的需要，随时增加任何我们想用的属性。
构造集合类，我想应该有三种方法：
第一种是利用collection，
第二种是利用数组，
第三种是利用字典。
利用collection构造集合类，在功能上可以做到最类似shapes，range，等excel原生的对象，但其缺点也是不能令人容忍的，就是慢，个人的意见是处理三千个以内的对象时，可以用，对象数量太多，就要考虑另两种了。
利用数组构造集合类，优点不说大家也猜得到，快，但功能上有所欠缺。
利用字典呢？快，功能上介于数组与collection之间。缺点是需要引用。
个人建议多用字典，但大家也可各随已意。
下面，我们先利用数组，构造集合类
首先插入一个类模块，重新命名为：rngFormats
在其顶端声明一个数组：
Privatearr() as rngFormat
然后在类模块的Initialize事件中，对数组arr初始化，代码如下：
Private SubClass_Initialize()
    ReDim arr(1 To 1)
End Sub
前面已经交代过 Class_Initialize() 事件何时触发，记不得了，请细看前文。
Worksheets,shapes,等等都有一个ADD方法，现在，我们也在我们的集合类rngFormats中添加Add方法:
Public SubAdd(rng As Range)
    Dim rF As rngFormat ‘每次运行add方法时，都要新建一个rngFormat对象，把它加入数组中。
    k = k + 1
    Set rF = New rngFormat
    Set rF.Cell = rng
    rF.WhatType              ‘运行whattype方法,得到datatype属性
    Set arr(k) = rF               
    ReDim Preserve arr(1 To k + 1) ‘当数组增加了一个元素后,把数组下标增加1,为数组储存下个元素做准备.
End Sub
下面为集合类增加Count属性,以得到类中有多少个rngFormat对象。方法用到了一个变量K，要在这个类模块顶部声明变量K。
PublicProperty Get Count() As Long
    Count = UBound(arr) - 1
EndProperty
看Add方法,每增加一个元素后,下标增加1,增加最后一个元素后,下标比最后元素的下标多了1,所以Count等于数组下标减去1
下面再增加一个Item属性，就集合类就完事大吉了
代码如下：
PublicProperty Get Items(ByVal aItem As Long) As rngFormat
    Set Items = arr(aItem)
EndProperty
这段代码就不用解释了。
下面我们稍修改一下标准模块，看看rngFormats的使用，一般来说，或者大多数情况，声明集合类都要声明为Public类型的，因为我们绝大数情况，需要在excel程序关闭前，持续使用它。
另外，我们在a1:a41中先输入一些数据，然后在b1:b41中判断它们的类型。
标准模块代码如下：
Public rngC As rngFormats

'这段程序，在a1:a5里分别输入类型为日期，文本，公式，数值，空 的值
'然后在其右边求出其类型
Sub test()
    Dim rng As Range, i As Long
    Set rngC = New rngFormats           '实例化类，形成对象
    For Each rng In Range("a1:a41")
        rngC.Add rng
    Next
   For i = 1 To rngC.Count
        Cells(i, "b") =rngC.Items(i).DataType
   Next
End Sub
以上代码，需要注意一点，就是我们的rngFormats类，没有默认属性，换句话说，就是不能用rngC(i),取代rngC.item(i)。
而sheets.item(i),却可以用sheets(i)取代。
在用字典和collection构造集合类时，可以模拟默认属性，以后的例子会涉及到。不过，我们先不急着用字典，collection构造集合类，我们下一步，要做的事情是引入事件，并且，要用到前面说的枚举类，大家也可以看到枚举类的方便之处，今天就到此为止。到现在为止的代码，可见附件。
类_2.rar (25.43 KB, 下载次数: 268)

2013-11-9 13:52 上传

点击文件名下载附件

hyefeifei

hyefeifei 发表于 2013-11-9 13:52
接上回：上回留了两个任务，一、引入枚举变量，二、引入方法。
这一回把枚举变量放一放，先引进方法，再利 ...

随写随发，难免错漏，大家也可以见到，用数组构造集合类，其Item属性，参数是一个长整形，也就是说，
它的另一个缺点，是不能用关键字引用 其中包含的对象。而collection,dictionary,是可以支持关键字的。

jsxjd

hyefeifei 发表于 2013-11-9 13:27
如果我的话让你误解了，我重说一下，我的意思是说
所有sub，都可以被function替换，尽量使用function。
...

单元格值，不是其他属性
[A1].value=100

香川群子

lipton 发表于 2013-11-9 13:34
很好的技术贴。
我个人的理解，函数(Function) 有返回值，过程(Sub)没有返回值,基于子过程职责清晰，单一职 ...

实际上，sub过程也可以返回结果……或者是改变byref参数，或者是直接改变公共变量结果。


Dim x
Sub test()
    a = 2: b = 3: c = 0
   
    Call ss1(a, b, c)
    MsgBox c
   
    Call ss2(a, b)
    MsgBox x
End Sub

Sub ss1(a, b, c)
    c = a + b
End Sub

Sub ss2(a, b)
    x = a + b
End Sub

tdyx616

东四没有了！晕！研究的有学问!来这里的学函数的多！

hyefeifei

说点题外的：如果看到现在，不知道是否有人会问，弄了这么多代码，要实现的功能确很简单，有这个必要吗？
第一：诚然，类模块能实现的，不用类模块原则上也能实现，但是，原则上能实现的，你不一定能实现，除非你天赋异禀，对复杂的逻辑有着超强的解析力。但就算如此，你也要费大量脑细胞，何苦呢？类模块可以抽象复杂的过程，化繁为简，变难为易，不用岂不可惜。
第二：开发稍大一点的项目，管理，扩展就变得非常重要，如果不用类模块，你会感到这项工作就算不是一件不可完成的任务，也是一件头昏脑胀的任务。
第三：它可以模拟二元树，队列，堆栈之类的数据结构，封装API，捕获事件，引发事件，种种方便之处，不一而足。（这些以后慢慢都会讲到）
第四：类模块初看似难，其实简单，一旦掌握，就会越用越顺，越顺越爱用，学习成本并不是你想像的那么高，真正是低成本，高收益的知识。
个人认为，学习VBA,两个知识最重要，第一是ADO，次之的就是类。这个贴子只是期望引发大家对类的兴趣，惜乎现在看来，关注这个贴子的人并不多，不过，继续这个贴子，就当是一个自己提高的过程好了。
闲言少续，进入正题
Choose函数下面，我们引进枚举变量，并引入事件。
在这之前，先讲讲choose这个函数
Y=Choose(index,a1,a2,a3，……，an)
Index是一个介于1至n之间的数值,Y就等于a(index)
举个例子choose(3,”yy”,”xx”,”qq”,”dd”)就返回qq。
枚举变量引入
现在我们把文本，公式，空值，数值这几个属性值定义成一个枚举变量。
在类rngFormat 顶端输入：
Public Enum cType
    文本
公式
日期
    数值
    空值
End Enum
记住，这时，文本，公式，日期，数值，空值，就变了数值0，1，2，3，4
修改rngFormat代码如下：
原Private mDatatype As String
改为：Private mDatatype As cType
原Public Function WhatType()
    If IsEmpty(m_oCell) Then
        mDatatype = "空值"
    ElseIf m_oCell.HasFormula Then
        mDatatype = "公式"
    ElseIf IsNumeric(m_oCell) Then
        mDatatype = "数字"
    ElseIf IsDate(m_oCell) Then
        mDatatype = "日期"
    Else
       mDatatype = "文本"
    End If
    WhatType = mDatatype
End Function
这段程序，把其中的空值，公式，数字，日期，文本，用其相应的数值代替变为：
    If IsEmpty(m_oCell) Then
        mDatatype = 4
    ElseIf m_oCell.HasFormula Then
        mDatatype = 1
    ElseIf IsNumeric(m_oCell) Then
        mDatatype = 3
    ElseIf IsDate(m_oCell) Then
        mDatatype = 2
    Else
       mDatatype = 0
    End If
WhatType = mDatatype
现在可以运行一下标准模块中的test程序测试一下，就会看到b1:b41原来的文本，现在已经被01234这几个数替代。
增加一个方法现在为rngFormat增加一个方法，这个方法给其cell属性接受的单元格增加背景色。方法如下：

Public FunctionrColor()

    m_oCell.Interior.ColorIndex =Choose(Me.DataType + 1, 8, 37, 40, 24, 15)

End Function
  在类模块中引用自身，要用Me.这和在窗体中是一样的，不要忘了，datatype属性是01234，而索引值是1-N的值，所以要加1。
8, 37, 40, 24, 15 是5个颜色值，分别对应文本，公式，日期，数值，空值五个类型的背景色，例如，你想把公式的背景色变成红色，就把37改成3好了。
现在，我们在标准模块中，增加一句：rngC.Items(i).rColor，test变为：
Sub test()
    Dim rng As Range, i As Long
    Set rngC = New rngFormats           '实例化类，形成对象
    For Each rng In Range("a1:a41")
        rngC.Add rng
    Next
   For i = 1 To rngC.Count

        Cells(i, "b") =rngC.Items(i).DataType

        rngC.Items(i).rColor
   Next
End Sub
此时，再运行test，发现单元格依各自的类型变色了。
讲到这里，忽然想到了一个问题，就是用数组构造集合类的时候，其Add方法，

有这么一句：redim preserve arr(1 tok+1)

提醒一下，这种声明最好不用，因为数组在运算大量数据时速度快是使用它的一个理由，但是当程序运行到：

redim preserve arr(1to k+1)

这一句时，vba会先建立一个空的数组arr(1 to k+1),然后把原先的arr(1 to k) 的数据复制过来，这一过程，会降低效率，所以，在实际应用中，最好是预先判断好数组最大的下标，声明一个固定数组，然后用一个长整形变量，来记录数组中有效下标。如果非用preserve关键字，也要设一个条件 ，批量增加下标。关于数组，坛子里有详细介绍的贴子，就不多说了。（在主贴的附件里，用数组构造类，用的就是固定数组）
到此为止的附件： 类_3.rar (26.51 KB, 下载次数: 138)

2013-11-10 07:19 上传

点击文件名下载附件

原打算讲事件来着，不过现在想来，还是先介绍一下用字典构造集合类，然后再在字典集合类中引入事件，不要把所有的知识点，都在数组里讲了，且由于数组不支持关键字索引（当然可以通过曲折手段实现，但那样没效率也没意义），好多功能实现起来麻烦。暂停……

lee1892

jsxjd 发表于 2013-11-9 13:20
楼主可以试一下：
自定义一个在单元格中使用的函数，该函数修改某单元格的值。

你别打岔，你说的是表单的自定义函数，完全是另外一回事。

用函数返回代码片断执行成功与否是标准做法。

大家都明白的事，没必要争。

lee1892

hyefeifei 发表于 2013-11-10 07:19
说点题外的：如果看到现在，不知道是否有人会问，弄了这么多代码，要实现的功能确很简单，有这个必要吗？
...

如果要构造集合类，建议你用Collection，作为非VB原生的字典并不是必须的。

实际上，在非必要的情况下，我越来越倾向于不使用VBS的Dictionary。