简单分享建立类的多级属性

microyip

记得前两个月有位兄弟谈到需要建立类的多级属性问题。当时文字简单说了一下处理方法，那位兄弟实现了并解决了。今天因为处理程序时，又使用上这个，于是写了个范例以供大家参详，详见附件。粗糙代码，高手们就不用贱笑了。

microyip

建立父类1、定义准备调用的子类
2、利用Class_Initialize对相关参数进行初始化
3、通过Property Get 子类调用时的名字() As 子类名称建立父类的子类
4、通过Property Let 设定类内使用的值(传递数值)设定类所需的相关值

1. Private clYear As New Info_Year '定义年类变量
   
2. Private clMonth As New Info_Month '定义月类变量
   
3. Private clDay As New Info_Day '定义日类变量
   
4. 
   
5. Private nYear As Integer, nMonth As Integer
   
6. 
   
7. Private Sub Class_Initialize()
   
8.     nYear = Year(Date)
   
9.     nMonth = Month(Date)
   
10.     iDay.vYear = nYear '设置初始的日期类的年的初始值
    
11.     iDay.vMonth = nMonth '设置初始的日期类的月的初始值
    
12. End Sub
    
13. 
    
14. Property Get iYear() As Info_Year '返回年的类的属性
    
15.     Set iYear = clYear
    
16. End Property
    
17. 
    
18. Property Get iMonth() As Info_Month '返回月的类的属性
    
19.     Set iMonth = clMonth
    
20. End Property
    
21. 
    
22. Property Get iDay() As Info_Day '返回月的类的属性
    
23.     Set iDay = clDay
    
24. End Property
    
25. 
    
26. Property Let vYear(ByVal nEnter As Integer) '设定本类的年份值
    
27.     nYear = nEnter
    
28.     iDay.vYear = nYear
    
29. End Property
    
30. 
    
31. Property Let vMonth(ByVal nEnter As Integer) '设定本类的月份值
    
32.     nMonth = nEnter
    
33.     iDay.vMonth = nMonth
    
34. End Property
    
35. 
    
36. Sub Reset() '重置
    
37.     clYear.Reset
    
38.     clMonth.Reset
    
39.     clDay.Reset
    
40. End Sub

复制代码

microyip

附件为建立的三个年、月、日子类
建立子类——年
1、通过Private Sub Class_Initialize()进行类初始化，设定相关参数
2、通过Property Get Values() As Variant引用该子类的Values时，返回该子类所包含的数值的数组
3、通过Property Get Address() As String引用该子类的Address时，返回该子类所包含的数值的所在单元格地址的字符串

年子类代码

1. Private rYear As Range
   
2. Private vYear As Variant
   
3. 
   
4. Private Sub Class_Initialize()
   
5.     Dim sYear As String, nRow As Double
   
6.    
   
7.     With Sheets("基本资料")
   
8.         nRow = .Cells(.Rows.Count, .[A1].Column).End(xlUp).Row
   
9.         If nRow < 33 Then
   
10.             .[A33] = Year(Date)
    
11.             nRow = 33
    
12.         End If
    
13.         Set rYear = .[A33].Resize(nRow - 32)
    
14.     End With
    
15.     vYear = rYear.Value
    
16.     If Not IsArray(vYear) Then
    
17.         sYear = vYear
    
18.         ReDim vYear(1 To 1, 1 To 1)
    
19.         vYear(1, 1) = sYear
    
20.     End If
    
21. End Sub
    
22. 
    
23. Property Get Values() As Variant '返回年份的数组
    
24.     Values = vYear
    
25. End Property
    
26. 
    
27. Property Get Address() As String '年份，单元格地址的字符串
    
28.     Address = "基本资料!" & rYear.Address
    
29. End Property
    
30. 
    
31. Sub Reset() '重置
    
32.     Class_Initialize
    
33. End Sub

复制代码

microyip

建立子类——日
1、通过Private Sub Class_Initialize()进行类初始化，设定相关参数
2、通过Property Get Values() As Variant引用该子类的Values时，返回该子类所包含的数值的数组
3、通过Property Get Address() As String引用该子类的Address时，返回该子类所包含的数值的所在单元格地址的字符串
4、通过Property Get MaxDay() As Integer 引用该子类的MaxDay时，返回月份最后一天的日数
5、因为每月的选值区域随年月在变化，故通过Property Let vYear(ByVal nEnter As Integer) 来设置日期的年份，Property Let vMonth(ByVal nEnter As Integer) 来设置日期的月份
日子类代码

1. Private rDay As Range
   
2. Private vDay As Variant
   
3. Private nYear As Integer, nMonth As Integer
   
4. Private nMaxDay As Integer
   
5. 
   
6. Private Sub Class_Initialize()
   
7.     nYear = Year(Date)
   
8.     nMonth = Month(Date)
   
9.     初始化
   
10. End Sub
    
11. 
    
12. Property Get MaxDay() As Integer '返回月份最后一天的日数
    
13.     MaxDay = Day(DateSerial(nYear, nMonth + 1, 1) - 1)
    
14. End Property
    
15. 
    
16. Property Get Values() As Variant '返回日期的数组
    
17.     Values = vDay
    
18. End Property
    
19. 
    
20. Property Get Address() As String '返回日期的单元格地址的字符串
    
21.     Address = "基本资料!$A$2:$A$" & (nMaxDay + 1)
    
22. End Property
    
23. 
    
24. Property Let vYear(ByVal nEnter As Integer) '设置日期的年份
    
25.     nYear = nEnter
    
26.     初始化
    
27. End Property
    
28. 
    
29. Property Let vMonth(ByVal nEnter As Integer) '设置日期的月份
    
30.     nMonth = nEnter
    
31.     初始化
    
32. End Property
    
33. 
    
34. Sub Reset() '重置
    
35.     Class_Initialize
    
36. End Sub
    
37. 
    
38. Private Sub 初始化()
    
39.     nMaxDay = Day(DateSerial(nYear, nMonth + 1, 1) - 1)
    
40.     Set rDay = Sheets("基本资料").[A2].Resize(nMaxDay)
    
41.     vDay = rDay.Value
    
42. End Sub

复制代码

microyip

建立父类
1、定义子类变量
2、通过Private Sub Class_Initialize()进行类初始化，设定相关参数
3、通过Property Get iYear() As Info_Year 引用父类的iYear时，返回年子类所有的属性
4、通过Property Get iMonth() As Info_Month引用父类的iMonth时，返回月子类所有的属性
5、通过Property Get iDay() As Info_Day引用父类的iDay时，返回日子类所有的属性
6、通过Property Let vYear(ByVal nEnter As Integer) 和Property Let vMonth(ByVal nEnter As Integer) 分别设定日子类所需的年份、月份值

父类类代码

1. Private clYear As New Info_Year '定义年类变量
   
2. Private clMonth As New Info_Month '定义月类变量
   
3. Private clDay As New Info_Day '定义日类变量
   
4. 
   
5. Private nYear As Integer, nMonth As Integer
   
6. 
   
7. Private Sub Class_Initialize()
   
8.     nYear = Year(Date)
   
9.     nMonth = Month(Date)
   
10.     iDay.vYear = nYear '设置初始的日期类的年的初始值
    
11.     iDay.vMonth = nMonth '设置初始的日期类的月的初始值
    
12. End Sub
    
13. 
    
14. Property Get iYear() As Info_Year '返回年的类的属性
    
15.     Set iYear = clYear
    
16. End Property
    
17. 
    
18. Property Get iMonth() As Info_Month '返回月的类的属性
    
19.     Set iMonth = clMonth
    
20. End Property
    
21. 
    
22. Property Get iDay() As Info_Day '返回月的类的属性
    
23.     Set iDay = clDay
    
24. End Property
    
25. 
    
26. Property Let vYear(ByVal nEnter As Integer) '设定本类的年份值
    
27.     nYear = nEnter
    
28.     iDay.vYear = nYear
    
29. End Property
    
30. 
    
31. Property Let vMonth(ByVal nEnter As Integer) '设定本类的月份值
    
32.     nMonth = nEnter
    
33.     iDay.vMonth = nMonth
    
34. End Property
    
35. 
    
36. Sub Reset() '重置
    
37.     clYear.Reset
    
38.     clMonth.Reset
    
39.     clDay.Reset
    
40. End Sub

复制代码

microyip

引用类
1、过程使用时，先定义Info为类变量
2、通过Info.iMonth.Address获得对应类别的地址字符串
3、通过Info.vYear = nYear对类内参数进行设置

1. Private Info As New InfoClass
   
2. 
   
3. Sub SelectChange(ByVal Target As Range)
   
4.     Dim sValidation As String, nYear As Integer, nMonth As Integer
   
5.    
   
6.     With Target
   
7.         If .Column = 2 Then
   
8.             If .Row = 1 Then '获得年的数据有效性的地址字符串
   
9.                 sValidation = "=" & Info.iYear.Address
   
10.             ElseIf .Row = 2 Then '获得月的数据有效性的地址字符串
    
11.                 sValidation = "=" & Info.iMonth.Address
    
12.             ElseIf .Row = 3 Then '获得日的数据有效性的地址字符串
    
13.                 nYear = [B1].Value
    
14.                 If nYear = 0 Then nYear = Year(Date)
    
15.                 nMonth = [B2].Value
    
16.                 If nMonth = 0 Then nMonth = Month(Date)
    
17.                 Info.vYear = nYear
    
18.                 Info.vMonth = nMonth
    
19.                 sValidation = "=" & Info.iDay.Address
    
20.             End If
    
21.             With .Validation
    
22.                 .Delete
    
23.                 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=sValidation
    
24.             End With
    
25.         End If
    
26.     End With
    
27. End Sub

复制代码

鸣i

好资料，十分感谢分享。

一指禅62

收藏了，慢慢消化。谢谢！

snowmanjx

先标记，回头过来学习类模块

生活如水

代码量怎么这么大？感觉好难啊！