How to use arrays in Excel for large data

Excel is such a wonderful tool, but if this is not used correctly it could be a pain. If we want to copy/perform some operation on large amount of data and assign values to each cell this could get slow like hell.

 

if we have below code

  Dim Data
  Data = Sheets("bid").Range(dtarcollet & 8).Resize(numrowstomap, 1)
  
   Sheets("worksheet")
  .Range(dcurcollet & (dcurrownum + 1)).Resize(numrowstomap, 1) = Data 

 

 

Following will happen inside excel

If you say ‘Sheets(“bid”)’
    the sheets object must search for the sheet “bid” every time.
  If you say ‘Range(dtarcollet & (ctr + 7))’
    the code must calculate the value for “(ctr + 7)” => 8
    convert the result to a string => “8”
    combine the result with the other string “dtarcollet & (ctr + 7)” => “D8”
    the range object must parse the string to get the column and row => D is column 4
    prove if column and row is valid and return the value

All this take some time.

Reference : http://answers.microsoft.com/en-us/office/forum/office_2010-customize/excel-2010-macro-super-slow/6d5ae73c-ca75-e011-8dfc-68b599b31bf5

 

Now lets have a look at the magic code :

Sub ArrayTest()
  Dim R As Range
  Dim myData
  Dim i As Long, j As Long
  
  
  myData = Range("A1:C46494")
  'OR
  myData = Range("A1").Resize(46494, 3)
  'OR
  Set R = Range("A1:C46494")
  myData = R
  
'  'array lower limit of each dimension is always 1
'   For i = 1 To UBound(myData)
'    For j = 1 To UBound(myData, 2)
'      'first dimension is the row, second is the column
'      Debug.Print myData(i, j)
'    Next
'  Next
  Row
  Range("K1").Resize(UBound(myData), UBound(myData, 2)) = myData
End Sub

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s