Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 23 of 23

Thread: Sorting based on letter, and then on number

  1. #21
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Faster alternative to randomize

    Sub M_snb()
      Dim sn(28 * 10 ^ 4, 0)
      Randomize
      
      For j = 1 To UBound(sn)
        sn(j, 0) = Mid(Join(Array(Chr(65 + Int(25 * Rnd)), Chr(65 + (25 * Rnd)), Chr(65 + Int(26 * Rnd)), CLng(10 ^ 5 * Rnd)), ""), 1 + Int(3 * Rnd))
      Next
        
      Sheet1.Cells(1, 4).Resize(UBound(sn) + 1) = sn
    End Sub

  2. #22
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by p45cal View Post
    Paul,
    About point 2 I'm not so sure. The r in the pvtFormatToSort sub was not meant to be a range but a value/member in the aryValues array. The r in that sub is a different r from that in the LetterNumberSort sub (scope and all that). I tried running your code and was immediately met with a ByRef argument type mismatch error, which I'm nigh on certain is because you've Dim-med it as a range.
    Yea, you are 100% correct.

    In my second ('Sub' version) version I was passing a Range to the sub, but in your improved version using arrays, it should have been a Variant.

    That was a change I made at the last minute using the "I don't need to test THIS little change" rule.

    Private Sub pvtFormatToSort(r As Variant)
    
    Private Sub pvtFormatToDisplay(r As Variant)


    Quote Originally Posted by p45cal View Post
    Re. point 3, I'm going to have an explore of making them into functions, but later…
    I was going by this (FWIW) figuring that the default ByRef in a Sub would save time taking the return from a Function and putting it back where it came from since the Sub would be operating on the 'real' parameter via it's address

    https://www.aivosto.com/articles/stringopt.html#whyslow

    Pass strings ByRef

    How should you define procedure parameters for calls from within the same project?
    ByVal is slow for string parameters. ByVal makes a copy of the string on every call. The good side is that a ByVal parameter is safe to modify: the modifications aren't passed back to the callers.
    ByRef is faster because the string doesn't get copied. The drawback is that you have to be careful. If your intention is not to return a value in the ByRef parameter back to the caller, you may not accidentally write to this parameter.
    Use ByRef instead of function return value

    There's also an optimization trick for returning a string value. Returning a string as the function return value is the normal practice. However, returning a string in a ByRef parameter is faster.
    Last edited by Paul_Hossler; 10-20-2021 at 09:36 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #23
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Alternative:

    Sub M_snb()
      sn = Sheet1.ListObjects(1).DataBodyRange
       
      For j = 1 To UBound(sn)
        y = Val(StrReverse(sn(j, 1) & "9"))
        y = Len(sn(j, 1)) + 1 - Len(y)
        sn(j, 1) = Left(sn(j, 1), y) & Format(Mid(sn(j, 1), 1 + y), "00000")
      Next
       
      Cells(1, 10).Resize(UBound(sn)) = sn
      With Columns(10)
        .SpecialCells(2).Sort Cells(1, 10)
        sn = Columns(10).SpecialCells(2)
        .ClearContents
      End With
       
      For j = 1 To UBound(sn)
        y = Val(StrReverse(sn(j, 1) & "9"))
        y = Len(sn(j, 1)) + 1 - Len(y)
        sn(j, 1) = Left(sn(j, 1), y) & Val(Mid(sn(j, 1), 1 + y))
      Next
       
      Sheet1.ListObjects(1).DataBodyRange = sn
    End Sub

Posting Permissions

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