0

I'm trying to wrap my head around jagged arrays, or array of arrays, and I am having difficulty locating more advanced resources. I've also seen a lot of buzz about collections being far superior than arrays, especially for non-rectangular data sets.

My original thought was to create four static arrays and one 4-dimensional array, but I couldn't get them to even work with a dummy data set.

Real data set: U.S. Air Force hierarchy.

  • Flights are in Squadrons

  • Squadrons are in Groups

  • Groups are in Wings

4 sets (Wings, Groups, Squadrons, Flights)

If there were 3 items in every set (let's say [A, B, C]) how would the end array or collection look and function? Which one would be optimal? How do you know the answer?

(This sounds like a test question, but I'm just trying to learn. Plus, who takes VBA classes nowadays anyway?)

Posted from mobile app.

kennyrogersjr
  • 3
  • 4
  • Really hard to answer without knowing how you're going to use the arrays, this question is very very broad. I understand you're trying to understand conceptually but I don't think Stack Overflow is the best place for these types of questions. – David G Jul 25 '16 at 19:22
  • some examples here http://stackoverflow.com/questions/19633937/can-you-declare-jagged-arrays-in-excel-vba-directly/38568507#38568507 – Slai Jul 25 '16 at 19:24
  • Let's go with string storage at the moment. – kennyrogersjr Jul 25 '16 at 19:24
  • 1
    @Slai This is very informative. Thank you. I'll practice with this for a little bit and update or close this question. – kennyrogersjr Jul 25 '16 at 19:27
  • This is begging for an OOP approach. – Comintern Jul 25 '16 at 19:31
  • Limitations of VB. ;p – kennyrogersjr Jul 25 '16 at 19:34
  • 1
    OOP isn't limited out because it's VBA. There are some good examples over on [Code Review](http://codereview.stackexchange.com/search?q=vba+oop); – Comintern Jul 25 '16 at 19:58
  • I think it will be easier to deal with `Range`'s rather than arrays or collection. If you have any experience with SQL, querying the data can be even easier. If you use Excel 2010 or above, you can look into Power Query and PowerPivot – Slai Jul 25 '16 at 20:49
  • Having read even further into these topics (collections, nested collections/arrays) I have deduced that what I originally thought was possible using arrays and collections may just have been a new problem that needed to be solved differently. Non-rectangular data sets can be used by both collections and arrays; however, when you introduce a tiered, "ownership"-like structure (using the example above: where one wing has many groups, each of the many groups can have many squadrons, and each of the many squadrons can have many flights,) we tend to see a database-like structure. – kennyrogersjr Jul 28 '16 at 00:01
  • Granted, Excel can be used as a database, which has similar attributes, but we don't really want to use it like so and doing so makes it cumbersome to use. My research led me into creating my own class(es) for the purpose of solving my example above. I guess that I spun myself into a rabbit hole that would take [me] many weeks to solve. @Comintern You were right when you said [research] OOP. – kennyrogersjr Jul 28 '16 at 00:11
  • @Slai You were right when you pointed me towards the collections resources. Thank you both! – kennyrogersjr Jul 28 '16 at 00:11

0 Answers0