dcsimg
Results 1 to 6 of 6

Thread: [RESOLVED] EXCEL VBA: How To: Force Long Macro to Background Task ???

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Resolved [RESOLVED] EXCEL VBA: How To: Force Long Macro to Background Task ???

    Esteemed Forum Participants and Lurkers:
    ===============================
    Excel 2003 VBA

    I have a lengthy Excel macro (VBA program) that takes 10 to 15 minutes to run to completion. While it is running, the entire machine is hung up waiting for it to complete. The user can't open NotePad or Windows Explorer ... it seems that the only thing one can do is to drag around the hourglass cursor and wait.

    Is there any way to run a macro as a background task, or enable other processing to take precedence from time to time? I successfully played around a lot with multi-threading on VB.Net a few years ago, but I only have Excel VBA available for the current project.

    Thank you for any and all comments, suggestions, and assistance.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  2. #2
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: EXCEL VBA: How To: Force Long Macro to Background Task ???

    And with Excel VBA, just as you are with VB6, you are stuck with single threading.

    They will have to wait.

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,413

    Re: EXCEL VBA: How To: Force Long Macro to Background Task ???

    You can't do anything about Excel being locked up, but you can probably let other programs run by making use of DoEvents in your code (but that will of course slow the code down).

    Something else to think about is whether or not the code could be made faster somehow.. if you want to do that, show us your current code.

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: EXCEL VBA: How To: Force Long Macro to Background Task ???

    Thanks All ...

    Most of the time is spent deleting and copying files on a networked drive. I have 150 files ... about 1.7 megs each ... to manipulate. That takes around 10 minutes no matter how I do it.

    About the only decision I have to make is whether to use legacy VBA file operations or FSO from Microsoft Scripting Runtime. I use the legacy "Dir" function because it supports WildCard searches, and FSO doesn't.

    I'll look up DoEvents ... thanks for the tip.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: EXCEL VBA: How To: Force Long Macro to Background Task ???

    Legacy File operations vs. File System Objects (Microsoft Scripting Runtime)
    ========================================================

    Scan 4 directories ... 150 files in each directory.
    Fetch each file specification.
    Extract a 3 digit numeric string that immediately precedes the extension code.
    Add a dictionary entry: key = numeric string, value = filespec
    Fetch the file mod date and save the newest time/date stamp

    ========================================================

    Legacy "Dir" with wildcard and "FileDateTime" in the loop takes about 1 minute.

    FSO "For Each aFile in aFolder.Files" with "aFile.Path" and "aFile.DateLastModified" takes a little over FIVE MINUTES !!!

    No contest here ... Legacy runs circles around FSO!
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,413

    Re: [RESOLVED] EXCEL VBA: How To: Force Long Macro to Background Task ???

    You will probably find that the FindFirstFile API is even faster than Dir, and it returns the modified date/time too - but unfortunately you need to use FileTimeToLocalFileTime and FileTimeToSystemTime to actually read the value.

    Note that at least one of those examples uses ReDim (even worse, with Preserve) which will slow it down quite a bit - so for speed it is best to re-write the code which stores the values (assuming you store them).

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width