That is the problem: if you want to reuse that code, in another project, you need to carefully scrap the worker code. The form is running the show – the “worker” code needs to be in the code-behind, or invoked from it. If we only care to make it work yesterday, a “Smart UI” works: we get a modal dialog, so the user can’t use the workbook while we’re modifying it. It feels like a work-around: we’d like a modal UserForm, but we don’t know how to make that work nicely.It pollutes the worker code with form member calls the worker code decides when to display and when to hide and destroy the form.The user is free to interact with the workbook and change the ActiveSheet at any time, but the progress is reported in an invasive dialog that the user needs to drag around to move out of the way as they navigate the worksheets.I dislike this solution, for several reasons: Modeless Progress IndicatorĪ commonly blogged-about solution is to display a modeless UserForm and update it from the worker code. Rubberduck has an inspection that specifically locates these implicit references though, so you’ll do fine. It’s critical to understand that the user can change the ActiveSheet at any time, so if your long-running macro involves code that implicitly (or explicitly) refers to the active worksheet, you’ll run into problems. 'if ScreenUpdating was off, toggle it back off: 'make sure the update gets displayed (we might be in a tight loop) If Not isUpdating Then Application.ScreenUpdating = True 'we need ScreenUpdating toggled on to do this: Public Sub UpdateStatus(Optional ByVal msg As String = vbNullString) You could use a small procedure to do it: If the macro is written in such a way that the user could very well continue using Excel while the code is running, then why disturb their workflow – simply updating the application’s status bar is definitely the best way to do it. You need a way to report progress to your users. Oh, it’s as efficient as it gets, you’ve put it up for peer review on Code Review Stack Exchange, and the reviewers helped you optimize it. So you’ve written a beautiful piece of code, a macro that does everything it needs to do… the only thing is that, well, it takes a while to complete.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |