Someone I could not refuse asked me to “take a look” at some Excel macros written in Visual Basic For Applications (VBA), which is a limited version of VB6 running inside Excel.<\/p>\n
Well, of course I found a fair share of cut-and-paste and inefficient code looking for things it has already found again and again. This is not the point. Of course there is verbose syntax, pesky I hate VB6 the most for the lack of proper data structures and algorithms.<\/b><\/font> In VBA you have a whopping of choice of two data structures: arrays and collections. Most people stick with arrays, which is sometimes painful, but collections seem to bring some relief. Collection is a hybrid of dynamic array and map (a.k.a dictionary). It may contain objects, with or without key, that can be looked up by key, by index or via At the first glance, collections provide huge advantage over arrays: they are truly dynamic, and they can be used as maps to quickly retrieve data by key. However, the collection interface lacks some important features:<\/p>\n This makes the collection quite a strange data structure. It is neither an array (no reorder), nor dictionary (no access to keys). Additionally, inability to fetch the keys makes it impossible to create an exact copy of an existing collection. It also makes it impossible to sort the collection: no reorder means one must create a new sorted collection, no keys means you lose key information in the process.<\/p>\n On top of all that, there is no built-in sorting method, neither for arrays nor for collections. This seems a little odd for a language used in an office application environment such as Excel.<\/p>\n The bottom line is, VBA with its limited choice of data structures and lack of sorting algorithms sometimes makes you feel like a fish out of water, desperately gasping for air. Please, oh please, no more Excel macros…<\/p>\n","protected":false},"excerpt":{"rendered":" Someone I could not refuse asked me to “take a look” at some Excel macros written in Visual Basic For Applications (VBA), which is a limited version of VB6 running […]<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"Layout":"","footnotes":""},"categories":[2],"tags":[],"class_list":["entry","author-ikriv","has-more-link","post-300","post","type-post","status-publish","format-standard","category-misc"],"_links":{"self":[{"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/300","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=300"}],"version-history":[{"count":0,"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/300\/revisions"}],"wp:attachment":[{"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=300"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=300"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=300"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Set<\/code> keyword, lack of across-the-board code verification, et cetera. Some of these are painful, but this is not the point.<\/p>\n
\n<\/p>\nFor Each<\/code> loop:<\/p>\nDim c As New Collection\nc.Add obj1, \"green\"\nc.Add obj2, \"blue\"\nSet x = c(1)\nSet y = c(\"green\")\n\nFor Each obj in c\n...\nNext<\/pre>\n
\n