Results 1 to 4 of 4

Thread: Insert data into an array in date order

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    May 2004
    Location
    Right here
    Posts
    275

    Insert data into an array in date order

    So this is a simplified example of what I'm trying to do (simplified in order to make explaining the situation easier...)

    I have a worksheet with column headers Name, DateOfBirth, Age, Height & Weight. In each row under this are placed, say, a list of employees who work for TheRobster Co. What I want to be able to do, using VBA, is insert new employees in the list in DateOfBirth order (youngest first, also if DoB is the same as an existing entry then place it before the existing entry). I can't use Excel's built-in worksheet sort/filter functions for reasons I won't go in to here.

    I currently have an inefficient and convoluted method in which the current entries in the list are sorted in ascending DateOfBirth order (youngest first, oldest last) and for any new entry it starts with the first entry, compares the DateOfBirth. If new employee is younger the code inserts a whole new row at this point and then inserts the new employee data. If DateOfBirth of new employee is older then it looks at the next entry and repeats the process until it finds the first instance of an older existing employee. If there aren't any then the new employee gets entered at the bottom of the list.

    Does that make sense? In a nutshell all it does is look at the DateOfBirth for existing employees and says "is the new employee younger than this entry? If so create new row and enter their details here. If not then keep looking".

    It's very inefficient though (creating new rows like this). I thought an alternative might be to read the entire existing list of employees into an array, then enter the new employee at the correct location, and then read the new (amended) array back to the worksheet. I've tried to code this though with no luck, especially the "enter new employee in the right DateOfBirth" index. I would appreciate some advice with regards to this if possible.

    Regards
    -Rob
    Last edited by TheRobster; Dec 6th, 2014 at 07:41 AM.
    http://www.sudsolutions.com

  2. #2
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Insert data into an array in date order

    Rob,

    You state that you have four columns:
    1. Name
    2. DOB (Primary Sort Column, Ascending)
    3. Weight
    4. Height
      But based on your insertion criteria, you are enforcing an implied fifth column
    5. Date of Hire (Secondary sort column, Descending)

    If this fifth column existed,but was hidden, you could easily use the built-in sort to enforce your order. But you have some mysterious criteria that disallows this.

    Now you state that your "New Record" routine is inefficient. What is inefficient about it? I see this term thrown around all the time by people. Often in their mind more lines of code equals inefficient whereas calling one line of black-box code equals efficient even if the black-box code takes a 1000 times longer to complete and consumes every resource it can find.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    May 2004
    Location
    Right here
    Posts
    275

    Re: Insert data into an array in date order

    TnTinMN,

    It's inefficient because it uses the Insert Row function so appears to have a lot of overhead when interacting with the worksheet in this way. I thought that importing the data into an array, sorting the array, and the copying back to the worksheet would be quicker.

    There is no fifth column, I just want to sort of DateOfBirth. The reason for not using the in-built sort is that the spreadsheet logic/structure may be taken forward by one of my company's "proper" programmers and turned into a proper app using C++. I've therefore being asked to leave out any Excel-centric functionality such as the Filters.

    Thanks
    http://www.sudsolutions.com

  4. #4
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Insert data into an array in date order

    Quote Originally Posted by TheRobster View Post
    It's inefficient because it uses the Insert Row function so appears to have a lot of overhead when interacting with the worksheet in this way. I thought that importing the data into an array, sorting the array, and the copying back to the worksheet would be quicker.
    To have formulated that opinion, I can only assume that you are doing the VBA coding in the style of the Macro Recorder with a lot of unnecessary Select statements and that you also did not disable screen updating. You can view the WorkSheet or subpart of it as a 2D array of cells. Working with it that way is very quick. Here is simple example that insert 500 rows and it will complete before your even notice that it is running.
    Code:
    Sub test()
       Application.ScreenUpdating = False
       Dim row As Range
       Dim insertedRow As Range
       Dim r As Long
       For r = 1 To 500
          Set row = Range("A2").EntireRow
          row.Insert Excel.xlShiftDown
          Set insertedRow = row.Offset(-1)
          insertedRow.Cells(1, 1).Value = CStr(r)
       Next
       Application.ScreenUpdating = True
    End Sub
    You did not indicate how you are adding data (single record from Form, or in bulk), but it should not matter.

    Quote Originally Posted by TheRobster View Post
    The reason for not using the in-built sort is that the spreadsheet logic/structure may be taken forward by one of my company's "proper" programmers and turned into a proper app using C++. I've therefore being asked to leave out any Excel-centric functionality such as the Filters.
    Not much I can offer in the way of dealing with the stupidity of business types that think they know everything and are trying to save money, but this criteria is complete fallacy.

    Any one coding this simple application in a higher level language will most likely use some simple database engine and as such the logic of adding and pulling records will look nothing like that which you will code into an Excel application. In fact I have spent probably half the time responding to you that it would take to write said application in say VB.Net.

Posting Permissions

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



Click Here to Expand Forum to Full Width