title: Excel VBA: Variants in Array Variables
body: <p>A question on variants.  Im aware that variants in Excel vba are both the default data type and also inefficient (from the viewpoint of overuse in large apps).  However, I regularly use them for storing data in arrays that have multiple data types.  A current project I am working on is essentially a task that requires massive optimistaion of very poor code (c.7000 lines)- and it got me thinking; is there a way around this?</p>
  
  <p>To explain; the code frequently stores data in array variables.  So consider a dataset of 10 columns by 10000.  The columns are multiple different data types (string, double, integers, dates,etc).  Assuming I want to store these in an array, I would usually;</p>
  
  <pre><code>dim myDataSet(10,10000) as variant
  </code></pre>
  
  <p>But, my knowledge says that this will be really inefficient with the code evaluating each item to determine what data type it is (when in practise I know what Im expecting).  Plus, I lose the control that dimensioning individual data types gives me.  So, (assuming the first 6 are strings, the next 4 doubles for ease of explaining the point), I could;</p>
  
  <pre><code>dim myDSstrings(6,10000) as string
  dim myDSdoubles(4,10000) as double
  </code></pre>
  
  <p>This gives me back the control and efficiency- but is also a bit clunky (in practise the types are mixed and different- and I end up having an odd number of elements in each one, and end up having to assign them individually in the code- rather than on mass).  So, its a case of;</p>
  
  <pre><code>myDSstrings(1,r) = cells(r,1)
  myDSdoubles(2,r) = cells(r,2)
  myDSstrings(2,r) = cells(r,3)
  myDSstrings(3,r) = cells(r,4)
  myDSdoubles(3,r) = cells(r,5)
  ..etc...
  </code></pre>
  
  <p>Which is a lot more ugly than;</p>
  
  <pre><code>myDataSet(c,r) = cells(r,c)
  </code></pre>
  
  <p>So- it got me thinking- I must be missing something here.  What is the optimal way for storing an array of different data types?  Or, assuming there is no way of doing it- what would be best coding-practise for storing an array of mixed data-types?</p>