Consulting

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

Thread: Solved: VBA Replace using regular expressions

  1. #21
    [vba] With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    .Cursor = xlWait
    .StatusBar = StatusBarMsg
    .EnableCancelKey = xlErrorHandler
    End With [/vba] How does .Cursor = xlWait help speed up the code? I'm familiar with the rest.
    BTW, I didn't know there was a .Cursor property...neat!
    Toby
    Portland, Oregon

  2. #22
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    The cursor is just a show thing. I like people to know that something is running. You will notice that I put a message on the status bar as well.

  3. #23
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Quote Originally Posted by Kenneth Hobs
    ....I ran these speed tests on 34,000 cells.

    Averages:
    Your last routine vs my last routine less speedup in seconds.
    6.3 vs. 6.5

    Your last routine vs my last routine in seconds with speedup routine in both.
    5.7 vs. 5.4...
    @Kenneth Hobs:

    Howdy

    I am afraid that curiosity has gotten the best of me. Did you include my suggestion as well? I ask, as when I do, I get results of about 3.5 seconds for your and snb's and .734 seconds for mine (all at work on a nice PC; and admittedly by memory).

    At home, 427 seconds (snb), 935 seconds (yours), and 37 seconds (mine) on my "Is this thing really plugged in?" laptop (circa Office 2000). I tested snb's twice (because of the great difference) just to make sure I had run SetData() - but it staill ran at 458 seconds.

    (And yes, I was soooo regretting not having shortened the test data)

    Hope all is blessed with you and yours,

    Mark

  4. #24
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Been getting a lot of good ideas from this thread.

    I assume that there's no way to use RegEx without the 'one cell at a time' VBA looping?

    range.replace will do the whole range at once (or at least without VBA looping) using built in Excel code. I'd think that using the inherent .Replace would be faster that looping, so if RegEx worked the same ....?

    I'd guess that if it were possible, it would have been mentioned


    [vba]
    Sub AllAtOnce()
    Call ActiveSheet.Cells(1, 1).CurrentRegion.Replace("AAA", "zzz", xlPart)
    End Sub
    [/vba]


    Paul

  5. #25
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    I am not surprised Mark. Of course one does need to reset the strings before testing each routine as you said with SetData(). Once the routine runs, a 2nd run is very fast because nothing is changed.

    For my work computer using my speed routine, snb/mine/yours: 3.6/3.8/0.6. Yours ran the same with my speed routines and without. That is because you are only writing once.

    Using Paul's method, it was 0.7 without speedup and 0.6 with speedup.
    [VBA]Sub AllAtOnce()
    Dim lStart As Long
    Dim lEnd As Long

    lStart = GetTickCount
    On Error GoTo EndNow
    SpeedOn

    ActiveSheet.Cells(1, 1).CurrentRegion.Replace "?s", "'s", xlPart

    lEnd = GetTickCount
    MsgBox Format$((lEnd - lStart) / 1000&, "0.000 ""Seconds"""), vbInformation, "Time Elapsed"

    EndNow:
    SpeedOff
    End Sub[/VBA]
    Last edited by Kenneth Hobs; 08-30-2012 at 11:28 AM.

  6. #26
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    A tilde is needed for Paul's method. It was 0.6 seconds with and without speedup but 0.047 faster with speedup.

    [vba]Private Declare Function GetTickCount Lib "kernel32.dll" () As Long

    Sub AllAtOnce()
    Dim lStart As Long
    Dim lEnd As Long

    lStart = GetTickCount
    On Error GoTo EndNow
    SpeedOn

    ActiveSheet.Cells(1, 1).CurrentRegion.Replace "~?s", "'s", xlPart

    lEnd = GetTickCount
    MsgBox Format$((lEnd - lStart) / 1000&, "0.000 ""Seconds"""), vbInformation, "Time Elapsed"

    EndNow:
    SpeedOff
    End Sub[/vba]

Posting Permissions

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