{"id":663,"date":"2011-03-04T21:58:16","date_gmt":"2011-03-04T21:58:16","guid":{"rendered":"http:\/\/www.slyman.org\/blog\/?p=663"},"modified":"2013-06-07T09:47:13","modified_gmt":"2013-06-07T09:47:13","slug":"vba-mergesort-collection","status":"publish","type":"post","link":"https:\/\/slyman.org\/blog\/2011\/03\/vba-mergesort-collection\/","title":{"rendered":"MergeSort VBA Collection"},"content":{"rendered":"<p>VBA&#39;s <strong>Collection<\/strong> object provides a convenient way to handle sets of data, objects etc.&nbsp; However, <a title=\"Wikipedia: Visual Basic for Applications\" href=\"http:\/\/en.wikipedia.org\/wiki\/Visual_Basic_for_Applications\" target=\"_blank\">VBA<\/a> does not natively implement any methods for sorting them.&nbsp; One solution is the <a title=\"Microsoft support: How to use the Dictionary Object with Visual Basic\" href=\"http:\/\/support.microsoft.com\/kb\/187234\" target=\"_blank\">Dictionary<\/a> object (reference the <a title=\"Microsoft: MS Scripting Runtime library\" href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/aa164509%28v=office.10%29.aspx\" target=\"_blank\">Microsoft Scripting Runtime<\/a> library); however, Dictionary objects are not always convenient to use, especially when working with VBA&#39;s built-in Collections from various MS Office object models.<\/p>\n<p>Fast sorting algorithms (e.g. <a title=\"Wikipedia: Quicksort\" href=\"http:\/\/en.wikipedia.org\/wiki\/Quicksort\" target=\"_blank\">QuickSort<\/a>, <a title=\"Wikipedia: Heapsort\" href=\"http:\/\/en.wikipedia.org\/wiki\/Heapsort\" target=\"_blank\">HeapSort<\/a>) already exist for VBA Collection objects and free &amp; open-source implementations are easy to find on the internet.&nbsp; However, most implementations only sort simple collections of variables (numbers, words etc.); and are unsuitable for sorting collections of objects based on a named property within the Object&#39;s Properties collection.<\/p>\n<p>This article presents a VBA based implementation of the <a title=\"Wikipedia: MergeSort\" href=\"http:\/\/en.wikipedia.org\/wiki\/Merge_sort\" target=\"_blank\">MergeSort<\/a> <a title=\"Wikipedia: Sorting algorithm\" href=\"http:\/\/en.wikipedia.org\/wiki\/Sorting_algorithm\">sorting algorithm<\/a> incorporating a number of advanced features for working with Collections of Objects and for customising the output of the algorithm (to sort in ASCending or DESCending order, or obtain DISTINCT results, for example).&nbsp; The VBA module inherits from <strong>MergeSort the following properties:<\/strong><\/p>\n<ul>\n<li>Preserves original order as far as possible (<em><a title=\"Wikipedia: Sorting stability\" href=\"http:\/\/en.wikipedia.org\/wiki\/Sorting_algorithm#Stability\">stable sorting<\/a><\/em>)<\/li>\n<li><a title=\"Wikipedia: Best, worst and average case performance\" href=\"http:\/\/en.wikipedia.org\/wiki\/Best,_worst_and_average_case\" target=\"_blank\">Worst case performance<\/a>:&nbsp; <strong>O(<i>n<\/i> \u00d7 log(<i>n<\/i>))<\/strong>\n\t<\/li>\n<li><a title=\"Wikipedia: Best, worst and average case performance\" href=\"http:\/\/en.wikipedia.org\/wiki\/Best,_worst_and_average_case\" target=\"_blank\">Worst case space complexity<\/a>:&nbsp; <strong>O(<i>n<\/i>)<\/strong>&nbsp; auxiliary (does not sort in place)<\/li>\n<li>Predictable execution time and memory space requirements, with <strong>no<\/strong> need to consider the details of a particular application.&nbsp; Simple to understand, therefore simple to safely modify and cheap to maintain<\/li>\n<li>Ideal for sorting as a one-off job, but structures are unsuitable for ongoing maintenance where records are continually being inserted.&nbsp; For that purpose you should use a Dictionary or similar structure.<\/li>\n<\/ul>\n<hr \/>\n<blockquote>\n<p><a title=\"Select this link to download a zipped copy of the VBA module\" href=\"http:\/\/www.slyman.org\/m_projects\/vba-mergesort\/Collections.zip\">Download zipped VBA module implementing MergeSort algorithm<\/a><\/p>\n<\/blockquote>\n<p>To incorporate this module into your VBA project, unzip the file, then open your VBA Project&#39;s VBA editor, and select &ldquo;<em>Import File<\/em>&rdquo; from the &ldquo;<em>File<\/em>&rdquo; menu.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>VBA&#39;s <strong>Collection<\/strong> object provides convenient ways to handle sets of data, objects etc., but does not natively implement sorting methods.&nbsp; Here is a VBA <a title=\"Wikipedia: MergeSort\" href=\"http:\/\/en.wikipedia.org\/wiki\/Merge_sort\" target=\"_blank\">MergeSort<\/a> algorithm for VBA Collections. <a href=\"https:\/\/slyman.org\/blog\/2011\/03\/vba-mergesort-collection\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[72],"tags":[105,110,104,106,109,107,108,113,112,111,29],"class_list":["post-663","post","type-post","status-publish","format-standard","hentry","category-msoffice-vba","tag-collection","tag-collection-of-objects","tag-mergesort","tag-object","tag-objects","tag-properties","tag-sort","tag-stable","tag-vb6","tag-vb7","tag-vba"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/slyman.org\/blog\/wp-json\/wp\/v2\/posts\/663","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/slyman.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/slyman.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/slyman.org\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/slyman.org\/blog\/wp-json\/wp\/v2\/comments?post=663"}],"version-history":[{"count":3,"href":"https:\/\/slyman.org\/blog\/wp-json\/wp\/v2\/posts\/663\/revisions"}],"predecessor-version":[{"id":2930,"href":"https:\/\/slyman.org\/blog\/wp-json\/wp\/v2\/posts\/663\/revisions\/2930"}],"wp:attachment":[{"href":"https:\/\/slyman.org\/blog\/wp-json\/wp\/v2\/media?parent=663"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/slyman.org\/blog\/wp-json\/wp\/v2\/categories?post=663"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/slyman.org\/blog\/wp-json\/wp\/v2\/tags?post=663"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}