+ Reply to Thread
Results 1 to 3 of 3

Which is faster: data manipulation in an array or worksheet?

  1. #1
    Registered User
    Join Date
    05-10-2007
    Posts
    7

    Which is faster: data manipulation in an array or worksheet?

    First, a general question, then more specific:
    General:
    If I need to retrieve, manipulate and store data, which method is faster?
    1. 10 x 10 array of string data
    2. worksheet, 10 rows x 10 columns
    Specific:
    Currently, I import web data into a worksheet, with about 10 to 20 rows imported per website import. The complete set of imports results in approximately 25,000 rows x 8 columns: 7 columns are strings (< 75 chars each), one column is a hyperlink to another web site.
    Granted, the web retrievals are the long pole in the tent, what would be faster?
    1. Import data into a 25,000 x 8 array, concatenate some strings, conditional changes to cells, write entire "cleaned up" array to a worksheet
    2. Import all data into a 25,000 x 8 worksheet range, unmerge 3 columns, concatenate strings, conditional changes to cells, delete "extra" rows as processed
    3. For each import, store data in a 4 x 8 array, perform manpulations, write resulting 1 row record to worksheet
    Thank you in advance for your insights.

  2. #2
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    Good afternoon Peaceful4getFul

    ...and welcome to the forum!

    If I need to retrieve, manipulate and store data, which method is faster?
    No contest. It is far faster to write and manipulate strings direct to memory than it is to write to cells. Writing to cells is slow.

    On the specific side, if you have to write to cells, the best way would be method 1, so you are grouping the operations together. Dump to array. Manipulate. Write.

    The only time I would dump into cells is if I was to do some processor intensive task such as sorting. The Excel sort algorithm is far faster than you could write in VBA as it's written in a compiled native programming language, not VBA which is interpreted and far slower by comparison.

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  3. #3
    Registered User
    Join Date
    05-10-2007
    Posts
    7
    Thanks for your help - I suspected that was the case. The worksheet code was simple; now i just need to make a few changes and do it by array.

    Also, while i don't have to sort, that's good insight on the impact of sorting in a worksheet versus an array.

    I haven't done any "real" programming for almost 20 years. I'm having fun now

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1