Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Which is faster? (XL2003)

    Hi, quick question for you (VBA related, too).

    I've got a large set of IDs comprising a 5 character alpha-numeric plus a single alphanumeric extension (0-9, A-Z) in no particular order. I want to preserve the order that's there when I add new ones at the bottom. Adding new ones consists of creating the 5-character part, and finding the next unused extension for that base. Some extensions will be missing, having previously been created and then deleted and I don't want to reuse these. I will do this in VBA.

    I could do it by copying the range to a temporary workbook, use the LEFT and RIGHT functions to separate out the component bits, sort the whole thing on BASE as key1 and EXTENSION as key2 and then use MATCH to get the end of the list and add one to the extension. That's the Excel way, if you like, only in code.

    In "pure" VBA, the only way I know is to run through the entire array (about 5000 lines) and check each one for matching BASE and keep a record of the maximum EXTENSION until I get to the end.

    The question is, which is faster? Or is there a better way? I can't help feeling there ought to be, but my brain seems to have seized and I can't think of one. All ideas greatly appreciated!

    Thanks.

    Stuart

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Which is faster? (XL2003)

    Any chance of using Access?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Which is faster? (XL2003)

    Only after a seriously long driving lesson.

    The problem is that the data that I am trying to maintain is on a very large spreadsheet which carries all sorts of data, and the whole thing is driven by a suite of Excel VBA macros that I would have to re-write from the ground up.

    Regrettably, I can't "do" Access, and haven't yet found the time to go and learn!

    Cheers,

    Stuart

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Which is faster? (XL2003)

    See attached workbook. It uses some formulas together with code. Click the command button to add a new item.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Which is faster? (XL2003)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    i like that approach, not only do you use a formula to make excel do the "heavy-lifting" (so you don't have to do any of that coding), but it should be faster since it is a formula and not VB.

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Which is faster? (XL2003)

    That's neat. I hadn't thought of using an array formula to generate the ASCII values like that. Stunning!

    Thank you very much!

    Stuart

Posting Permissions

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