{"id":125,"date":"2010-10-28T23:00:56","date_gmt":"2010-10-29T03:00:56","guid":{"rendered":"http:\/\/wukenny.host-ed.net\/?p=125"},"modified":"2010-10-28T23:00:56","modified_gmt":"2010-10-29T03:00:56","slug":"class-collection-in-vba","status":"publish","type":"post","link":"http:\/\/kennywu.info\/?p=125","title":{"rendered":"class collection in VBA"},"content":{"rendered":"<p>It takes more effect to set up you collection in a class module but it will make your code more portable and easier to maintain.<br \/>\n<strong>Employee class<\/strong><\/p>\n<pre lang=\"vb\" escaped=\"true\">\nOption Explicit\n\nPrivate FName As String\nPrivate FHoursPerWeek As Double\nPrivate FRate As Double\n\nPublic Function GetGrossWeeklyPay() As Double\n    GetGrossWeeklyPay = FHoursPerWeek * FRate\nEnd Function\n\nPublic Property Get Name() As String\n    Name = FName\nEnd Property\n\nPublic Property Get HoursPerWeek() As Double\n    HoursPerWeek = FHoursPerWeek\nEnd Property\n\nPublic Property Get Rate() As Double\n    Rate = FRate\nEnd Property\n\nPublic Property Let Name(v As String)\n    FName = v\nEnd Property\n\nPublic Property Let HoursPerWeek(v As Double)\n     FHoursPerWeek = v\nEnd Property\n\nPublic Property Let Rate(v As Double)\n    FRate = v\nEnd Property\n<\/pre>\n<p><strong>Emoployees class<\/strong><\/p>\n<pre lang=\"vb\" escaped=\"true\">\nOption Explicit\n\nPrivate FEmployees As New Collection\nPublic Function Add(ByVal value As Employee)\n    Call FEmployees.Add(value, value.Name)\nEnd Function\n\nPublic Property Get Count() As Long\n    Count = FEmployees.Count\nEnd Property\n\nPublic Property Get Items() As Collection\n    Set Items = FEmployees\nEnd Property\n\nPublic Property Get Item(ByVal v As Variant) As Employee\n    Set Item = FEmployees(v)\nEnd Property\n\nPublic Sub remove(ByVal v As Variant)\n    Call FEmployees.remove(v)\nEnd Sub\n<\/pre>\n<p><strong>test module<\/strong><\/p>\n<pre lang=\"vb\" escaped=\"true\">\nOption Explicit\nDim theEmployees As New Employees\n\nSub testEmployeesCollection()\n    Dim anEmployee As Employee\n    Dim I As Long\n    \n    For I = theEmployees.Count To 1 Step -1\n        Call theEmployees.remove(I)\n    Next I\n    \n    Set anEmployee = New Employee\n    anEmployee.Name = \"Paul Kimmel\"\n    anEmployee.Rate = 15\n    anEmployee.HoursPerWeek = 45\n    Call theEmployees.Add(anEmployee)\n    \n    Set anEmployee = New Employee\n    anEmployee.Name = \"Bill Gates\"\n    anEmployee.Rate = 13\n    anEmployee.HoursPerWeek = 56\n    Call theEmployees.Add(anEmployee)\n        \n    Debug.Print \"number of employees = \" & theEmployees.Count\n    Debug.Print \"employees(2) = \" & theEmployees.Item(2).Name\n    Debug.Print \"employees(paul kimmel rate) = \" & theEmployees.Item(\"Paul Kimmel\").Rate\n    For Each anEmployee In theEmployees.Items\n        Debug.Print anEmployee.Name & \"earns $\" & anEmployee.GetGrossWeeklyPay()\n    Next anEmployee\nEnd Sub\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>It takes more effect to set up you collection in a class module but it will make your code more portable and easier to maintain. Employee class Option Explicit Private FName As String Private FHoursPerWeek As Double Private FRate As &hellip; <a href=\"http:\/\/kennywu.info\/?p=125\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[18,20],"class_list":["post-125","post","type-post","status-publish","format-standard","hentry","category-msoffice","tag-class","tag-collection"],"_links":{"self":[{"href":"http:\/\/kennywu.info\/index.php?rest_route=\/wp\/v2\/posts\/125","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/kennywu.info\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/kennywu.info\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/kennywu.info\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/kennywu.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=125"}],"version-history":[{"count":0,"href":"http:\/\/kennywu.info\/index.php?rest_route=\/wp\/v2\/posts\/125\/revisions"}],"wp:attachment":[{"href":"http:\/\/kennywu.info\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=125"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/kennywu.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=125"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/kennywu.info\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=125"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}