+ Reply to Thread
Results 1 to 20 of 20

Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

  1. #1
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Cool Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    Hi guys,

    Edit: code I am using is at bottom of post.


    I have found that:
    Please Login or Register  to view this content.
    Seems to not always last the same amount of time. Is "Now" from when I hit the commandbutton, or is it at this step starts?
    How can i make "Now" time stamp from when "this step starts" because I want to display the message for a few seconds, however it seems to give me varying amounts of time.

    Thanks,
    Jimmy


    The "Call DirectorysAndFileExistYorN.CheckFiles" function takes a very long time, a few minutes to run and i think that might be the problem with the "Now".


    Please Login or Register  to view this content.
    Last edited by JimmyWilliams; 02-06-2018 at 06:34 PM.
    Thanks,

    JimmyWilliams

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    Quote Originally Posted by JimmyWilliams View Post
    Is "Now" from when I hit the commandbutton, or is it at this step starts?
    "Now" is the time that line of code is executed. I am unclear as to what you are trying to do here. If that function takes a few minutes then why do you want to wait 1 second before starting it? If you just delete the Wait, then the message will display as long as it takes for that function to run.

    In any case, to capture the time the button is pressed do this:
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Cool Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    Please Login or Register  to view this content.
    I preformed the above test
    Start1 = 43139.4122222222
    Start2 = 43139.4123958333
    So you're right, "Now" is time of the line executed, not time of when the macro started (whoops, my bad!). I don't think the Double is doing anything helpful.



    '''''''''''''''''''
    You said something about deleting the waits?
    Please Login or Register  to view this content.
    This runs, but has the problem that the button's text does not visible change.
    '''''''''''''''''''


    My current code is this:
    Please Login or Register  to view this content.
    I have experimented and found that blue is required because otherwise the excel screen does not update with the "Loading, Please Wait." message (even though I have the "application.screen updating"!)
    This currently works and isn't the problem. the code in Blue allows the button's message to appear to users.



    The problem is the part in red. The macro does not display the button's new text for 3 seconds, the button quickly flashes to the "Press to check if files exist." message for a split second, and then goes to the "Press to check if files exist."

    This is what I would like to happen.
    1. Click on the button, and the button changes to "Loading, please wait"
    2. Just before the program ends, the button's message changes to "Program Completed! Files in yellow are misplaced or misnamed."
    3.I After a few seconds the button changes back to its original name, which is "Press to check if files exist."
    (ideally the user should gain control again between steps 2 and 3.)


    Thanks,
    Jimmy
    Last edited by JimmyWilliams; 02-07-2018 at 07:24 PM.

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    Where is the button? On a userform or a worksheet?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    On the worksheet

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    Have you tired using
    Please Login or Register  to view this content.
    to force a screen refresh?

  7. #7
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    Try:
    Please Login or Register  to view this content.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

  8. #8
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    I think the most likely issue here is your line calling another macro:

    Please Login or Register  to view this content.
    We have no idea what this code does. It could be disabling screenupdates and never turning them back on for example. If it does this, doesnt matter how long you wait after youll never see the update.

    I would use DoEvents extremely sparingly. Its meant to allow the system outside of Excel to finish processes before returning control to Excel. Not typically something you want to do unless you are working outside of MS Office via VBA, and even then usually there are better ways to handle it.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    DoEvents is sometimes a necessary workaround, particularly in the more recent versions of Excel, where screenupdating behaviour seems to have been altered to not occur as often in high-frequency update scenarios.

  10. #10
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    Quote Originally Posted by xlnitwit View Post
    DoEvents is sometimes a necessary workaround...
    I agree. Variable times suggests some process is eating cycles; be polite and let it finish before starting your time-out. Perhaps another possibility is to use Application.OnTime instead of .Wait.
    Last edited by leelnich; 02-08-2018 at 06:01 PM.

  11. #11
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    test1.xlsm

    Hi guys,
    Here is the sheet I am working on. let me know if you have any ideas. I've tired some of the ideas above.
    But I have a feeling its because I use "select" cells a lot. I think there must be a better way to do to run the other macros I've made.

    Thanks,
    Jimmy

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    Yes, changing sheet selection is an issue, and unnecessary. You can avoid it
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    I couldn't really see a need for CallByName in there.

  13. #13
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    what do you mean by CallByName? I'm guessing that's the proper term for selecting?

    or do you mean the "Call DirectorysAndFileExistYorN.CheckFiles" (How else could i do it?)

    I've made the changes you've suggested about the selects, but im still getting the same problem of the button changing to the "Complete, Files in yellow are misplaced or misnamed." message for a few seconds.

  14. #14
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    My best solution so far is to use the following:

    Please Login or Register  to view this content.
    but even this sometimes doesnt result in the button's text from being displayed, even with xlintwit's changes.

  15. #15
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    Quote Originally Posted by xlnitwit View Post
    I couldn't really see a need for CallByName in there.
    Quote Originally Posted by JimmyWilliams View Post
    what do you mean by CallByName? I'm guessing that's the proper term for selecting?
    CallByName is a VBA built-in object method that allows you to call a method of an object by using a variable for the method name. I am not sure what this particular reference to it means since it is the first time it is used in this thread.

  16. #16
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    Quote Originally Posted by 6StringJazzer View Post
    CallByName is a VBA built-in object method that allows you to call a method of an object by using a variable for the method name. I am not sure what this particular reference to it means since it is the first time it is used in this thread.
    I think they're discussing this line from post #1, which is actually just a Call statement:
    Please Login or Register  to view this content.
    PS. I still say a DoEvents statement immediately after changing the Button Caption helps. Jeff, do you have an opinion?
    Last edited by leelnich; 02-09-2018 at 08:03 AM.

  17. #17
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    In your BlanksToSkip function you had this loop
    Please Login or Register  to view this content.
    I'm just saying that CallByName was unnecessary. (I do know the difference between Call and CallByName. )

    Regarding your button code, the Application.Screenupdating = True lines need to appear after you update the caption, since they should force a screen refresh. I'd also recommend putting a DoEvents line after each one.

  18. #18
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    Quote Originally Posted by xlnitwit View Post
    ...I do know the difference between Call and CallByName. ...
    Apologies, I should have let you speak for yourself.

  19. #19
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    Some observations that may be affecting run time.

    You have 2,523 styles in the file. You have a named range that for me is #REF. Your used range is significantly past where you have data as well. I would guess your macro may be causing the used range to grow over time (and not cleaning itself up), possibly even duplicating styles too.

    As per this article https://msdn.microsoft.com/en-us/vba...e-obstructions:

    As I think you pointed out, methods like .select, .activate, etc are not good methods for manipulating objects (ranges, sheets, etc). This is in the article about 3/4 down.

    At the bottom of the article ar 2 other performance links worth checking out. I think the below about offsetting from a range is someplace but couldn't locate it.

    Offset in a loop is likely a bad idea too if it can be avoided. When possible, store the offset in its own range and then use that (presuming the same offset will be used multiple times).

    You have some functionality in your macro that could be done using worksheet functions. For example your Text_To_Hyperlink function.

    Please Login or Register  to view this content.
    I see no difference between the 2 possible outcomes in terms of what is actually done. Also, this could very likely be done using the worksheet function HYPERLINK, potentially combined with IF, TRIM, etc.

    I see your checking if a folder exists by trying to set it to an object, why not just use fso's FolderExists() method, returns true/false in 1 line, no need to rely on an error handler to capture if its there or not. Similar thing with using getfile then an error handler, there is also a fileexists method of fso. See this link for examples.

    I want to say Application.goto isnt efficient either for the things your using it for. Your essentially using it to select a range.

    I do not see any call to your sorting function sub, but sorting collections is not super efficient. Dictionaries are faster and arrays are likely even faster for sorting. See a link here on all 3 and sorting.

    Ironically enough, I found some code I used for a similar reason with updating captions, but used it in PowerPoint. May help you. I of course used DoEvents in it but with Timer instead of Application.wait, may need to change it as I cant recall if Excel has the Timer function. You just pass it an interval (1 is what i used) See below:

    Please Login or Register  to view this content.
    So the gist of this routine is you change caption, call this routine passing the delay (start with 1, increase as needed) instead of doing DoEvents and Application.wait in line with your code.

    Hope it helps
    Last edited by Zer0Cool; 02-09-2018 at 03:51 PM.

  20. #20
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Re: Problem with consistent "Application.Wait (Now + TimeValue("0:00:10"))"

    Quote Originally Posted by 6StringJazzer View Post
    CallByName is a VBA built-in object method that allows you to call a method of an object by using a variable for the method name. I am not sure what this particular reference to it means since it is the first time it is used in this thread.
    that makes two of us, im confused.

    Still have the problem of it the text on the button not showing sometimes. I think its because there's loops in other parts of the code or something? which sounds rediculous, a solution would be awesome/ideas.

    thanks,
    Jimmy

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  2. Replies: 2
    Last Post: 07-24-2017, 02:19 AM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  5. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  6. Application.Wait Now + TimeValue("00:00:10"), but it stops my workbook also.
    By esbenhaugaard in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2012, 04:50 PM
  7. Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" not working
    By redders in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2011, 03:52 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1