3

I'm coding a small programm to extract data from SAP and analyze it in Excel. I'm doing all of this with VBA.

I'm using a function called

Application.ScreenUpdating = False

But whenever i activate a sheet like this

WorkSheets("data_tmp").Activate

it does show on my screen.

So, does the function Activate actually show even when doing the previous ScreenUpdating = False? Are other functions showing on screen even though I'm setting screenupdating to false?

Despite the title, this is not entirely about using active and select - it is more about what shows and what doesnt on screen when setting screenupdating on false, and how to prevent the user of seeing anything at all.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Samuel
  • 59
  • 9
  • 1
    Just don't use `.Activate` and `.Select` at all. These are only to show a worksheet on top or to visibly select a cell (so you can see it on the screen). If you don't want to see anything on the screen stop using them both: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Apr 25 '19 at 12:54
  • 1
    @Pᴇʜ I don't think this question is actually about the `.Select` function, despite the title. This seems like it has more to do with the differences between `Application.ScreenUpdating` and `Application.Visible`. I suggest a title edit. – SmrtGrunt Apr 25 '19 at 13:05
  • 1
    @Samuel Screen updating is different from visibility. Setting `Application.ScreenUpdating` to `False` will prevent the application from re-drawing the screen each time your function changes something. If you want to hide the application, use `Application.Visible = False`. More info can be found at the [Microsoft documentation](https://learn.microsoft.com/en-us/office/vba/api/excel.application.screenupdating). – SmrtGrunt Apr 25 '19 at 13:07
  • @SmrtGrunt Thanks! I didn't know about this function (i.e Application.visible)! Could you post an answer so i can close this post? I will edit the title – Samuel Apr 25 '19 at 13:10
  • @Samuel I can post it as an answer once the post is unlocked. I suggested an edit to the title, but it will need to be approved. – SmrtGrunt Apr 25 '19 at 13:13
  • @SmrtGrunt i did edit my post indeed, after your answer. Thanks again! – Samuel Apr 25 '19 at 13:15
  • @SmrtGrunt the post isn't locked because of the pending edit, it's locked because it was closed as a duplicate. I disagree with the duplicate flag. This question isn't about not using `.Activate` or `.Select`, but about what they do. Neither of those are necessary in 99% of the VBA code written, and they _should_ be avoided, but I believe this question is different. – FreeMan Apr 25 '19 at 13:16
  • @FreeMan I'm tracking on the reason for the post being locked. See my comment above. I just don't have the standing to do anything about it... – SmrtGrunt Apr 25 '19 at 13:18
  • @Freeman is it any way to un-mark a post as a duplicate? It is not (since i didnt asked about how to use select and activate, but again i understand that it might look like it). I want to mark this question as answered (since it has been since.) – Samuel Apr 25 '19 at 13:20
  • @SmrtGrunt & @freeman I re-opened it. But you still should consider to avoid `.Activate` and `.Select` they slow down a lot, cause many strange issues and are in most cases not needed. – Pᴇʜ Apr 25 '19 at 13:34
  • @Samuel I added my comment as an answer, per your request. – SmrtGrunt Apr 25 '19 at 16:45
  • 1
    @SmrtGrunt & OP - Normal SE/SO operation requires 5 votes to close or reopen a question. PEH did it single-handedly because he has a gold [tag:VBA] badge and is trusted (by the system) with that privilege. Read through the [help] for more details. – FreeMan Apr 25 '19 at 17:45
  • @FreeMan I'm tracking that, but thanks for taking the time to explain. I was just trying to let Samuel know earlier that I couldn't immediately do what he was looking for. – SmrtGrunt Apr 25 '19 at 17:51

1 Answers1

3

Screen updating is different from visibility.

Setting Application.ScreenUpdating to False will prevent the application from re-drawing the screen each time your function changes something (the exact rate of refresh is not specified, and seems to depend on the overall process load at the time of execution).

If you want to hide the application, use Application.Visible = False before working with the document (don't forget to reverse this at the end of your function). More info can be found at the Microsoft documentation.

As mentioned in the comments and links above, using .Activate and .Select are probably not necessary to accomplish your intent, especially when the application is hidden from view. Instead, refer to specific cells and ranges.

SmrtGrunt
  • 869
  • 12
  • 25