Как обновить экран в excel

VBA – Turn off Screen Updating

In this Article

As cool as it looks watching your VBA macro manipulate the screen, you can help your Macro run faster if you turn off (disable) ScreenUpdating.

Disable ScreenUpdating

1. To disable ScreenUpdating, At the beginning of your code put this line:

Enable ScreenUpdating

2. To re-enable ScreenUpdating, At the end of your code put this line:

VBA ScreenUpdating Example

Your procedure will then look like this:

ScreenUpdating Refresh

Disabling ScreenUpdating will make your VBA code run MUCH faster, but it will also make your work appear more professional. End-users typically don’t want to see the behind the scenes actions of your procedures (especially when the procedure runs slow). Also, you may not want end-users to see the behind the scenes functionality (ex. Hidden Worksheets). I recommend disabling (and re-enabling) ScreenUpdating in virtually all of your procedures.

However, there are some times when you want the screen to refresh. To refresh the screen, you will need to temporarily turn back on ScreenUpdating (there is no screen “refresh” command):

VBA Settings – Speed Up Code

There are several other settings to play with to improve your code speed.

Disabling Automatic Calculations can make a HUGE difference in speed:

Disabling the Status Bar will also make a small difference:

If your workbook contains events you should usually disable events at the beginning of your procedure:

Last, your VBA code can be slowed down when Excel tries to re-calculate page breaks (Note: not all procedures will be impacted). To disable displaying page breaks use this line of code:

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

Источник

Заставить обновление экрана в Excel VBA

Мой инструмент Excel выполняет длинную задачу, и я стараюсь быть добрым к пользователю, предоставляя отчет о ходе выполнения в строке состояния или в некоторой ячейке на листе, как показано ниже. Но экран не обновляется или не останавливается в какой-либо точке (например, на 33%). Задача в конечном итоге завершается, но индикатор выполнения бесполезен.

Что я могу сделать, чтобы принудительно обновить экран?

Я использую Excel 2003.

ОТВЕТЫ

Ответ 1

Добавьте функцию DoEvents внутри цикла, см. ниже.

Вы также можете убедиться, что строка состояния отображается пользователю и reset, когда ваш код завершен.

Ответ 2

Текстовые поля в листах иногда не обновляются когда их текст или форматирование изменены, и даже команда DoEvent не помогает.

Поскольку в Excel нет команды для обновления рабочего листа в том, как можно обновить форму пользователя, необходимо использовать трюк, чтобы заставить Excel обновить экран.

Следующие команды, похоже, делают трюк:

Ответ 3

Поместите вызов DoEvents в цикл.

Это повлияет на производительность, поэтому вы можете просто вызвать ее только на каждой, скажем, на 10-й итерации.
Однако, если у вас всего 30, это вряд ли проблема.

Ответ 4

Это напрямую не отвечает на ваш вопрос, а просто предоставляет альтернативу. Я нашел во многих длинных вычислениях Excel большую часть времени, ожидая наличия значений обновления Excel на экране. Если это так, вы можете вставить следующий код в начало вашего юнита:

и положим это как конец

Я обнаружил, что это часто ускоряет работу любого кода, с которым я работаю, так что необходимость предупредить пользователя о прогрессе не требуется. Это просто идея для вас попробовать, и ее эффективность в значительной степени зависит от вашего листа и расчетов.

Ответ 5

В частности, если вы имеете дело с UserForm, вы можете попробовать метод Перепаковать. Вы можете столкнуться с проблемой с DoEvents, если вы используете триггеры событий в своей форме. Например, любые клавиши, нажатые во время выполнения функции, будут отправлены DoEvents. Ввод клавиатуры будет обработан до обновления экрана, поэтому, если вы меняете ячейки в электронной таблице, удерживая одну из клавиши со стрелками на клавиатуре, то событие изменения ячейки продолжит стрельбу до завершения основной функции.

UserForm не будет обновляться в некоторых случаях, потому что DoEvents будет запускать события; однако Repaint обновит UserForm, и пользователь увидит изменения на экране, даже если другое событие сразу же следует за предыдущим событием.

В коде UserForm это просто:

Ответ 6

Напишите DoEvents непосредственно перед строкой, в которой вы обновляете пользовательский интерфейс, она должна работать.

Источник

VBA Screen Updating Property

Excel VBA Screen Updating

VBA Screen Updating is a property used to avoid or prevent distraction flashes while running the code and make it fast by turning off screen updating. We can turn off the screen updating by setting this property as “False.”

We often feel the Excel screen going crazy while the Macro is running. We almost get frustrated by that. But, how do we deal with these situations and make the code run faster than the usual slow thing?

Screen Updating is something we can notice while the excel macro Excel Macro A macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more is running. When the task is executing, we notice our screen updating the values until the Macro finishes its assigned task. As our screen flickers or refreshes, it leads to the slowdown of the Excel program and takes a longer time than usual to complete the task.

In VBA, we have a property called “ScreenUpdating,” we set this property to FALSE to eliminate the screen updating process while the code runs.

This article will say goodbye to watching on-screen action drama while the code is running. Instead, today you will make your code run faster and quicker than your usual time.

Table of contents

You are free to use this image on your website, templates, etc., Please provide us with an attribution link How to Provide Attribution? Article Link to be Hyperlinked
For eg:
Source: VBA Screen Updating Property (wallstreetmojo.com)

When to use Screen Updating Feature?

Suppose you have any doubt about when to use this technique. Then, look into the below points.

How to use the Screen Updating Feature in VBA Code?

Example #1 – Turn Off Screen Updating

Look at the below code.

Code:

The above has a nested VBA loop VBA Loop A VBA loop in excel is an instruction to run a code or repeat an action multiple times. read more to insert serial numbers from the first column to the 50th column and again to insert serial numbers starting from 51 from the second row to the 50th column.

Like this, it will insert until it reaches the 50th row.

While this code is running, you can notice your screen flickering. You cannot do anything apart from watching this crazy moment.

To avoid all of these, we can add Screen Updating to FALSE.

To access the Screen Updating feature, first, we need to access the Application object.

As we can see with the Application object, we have many properties and methods. So, select “Screen Updating” from the IntelliSense list.

Note: You must apply the Screen Updating feature immediately after declaring the variables.

After selecting the Screen Updating property, put an equal sign (=).

As we can see, there are two Boolean values: FALSE and TRUE.

To stop screen updating, set the status to FALSE.

As the Macro starts running first, it will update the screen, updating the status to FALSE, and proceed to the next line.

Since Macro executed Screen Updating to FALSE, it will not allow the screen to update while the code is executing its task.

Example #2 –

Always Set Screen Updating to TRUE at the End

We have seen many people set the Screen Updating to FALSE but forgot to set it back to TRUE at the end of the Macro.

Always set the Screen Updating back to TRUE at the end of the Macro.

Источник

Force a screen update in Excel VBA

My Excel tool performs a long task, and I’m trying to be kind to the user by providing a progress report in the status bar, or in some cell in the sheet, as shown below. But the screen doesn’t refresh, or stops refreshing at some point (e.g. 33%). The task eventually completes but the progress bar is useless.

What can I do to force a screen update?

I’m using Excel 2003.

12 Answers 12

Add a DoEvents function inside the loop, see below.

You may also want to ensure that the Status bar is visible to the user and reset it when your code completes.

Text boxes in worksheets are sometimes not updated when their text or formatting is changed, and even the DoEvent command does not help.

As there is no command in Excel to refresh a worksheet in the way a user form can be refreshed, it is necessary to use a trick to force Excel to update the screen.

The following commands seem to do the trick:

Put a call to DoEvents in the loop.

This will affect performance, so you might want to only call it on each, say, 10th iteration.
However, if you only have 30, that’s hardly an issue.

@Hubisans comment worked best for me.

Specifically, if you are dealing with a UserForm, then you might try the Repaint method. You might encounter an issue with DoEvents if you are using event triggers in your form. For instance, any keys pressed while a function is running will be sent by DoEvents The keyboard input will be processed before the screen is updated, so if you are changing cells on a spreadsheet by holding down one of the arrow keys on the keyboard, then the cell change event will keep firing before the main function finishes.

A UserForm will not be refreshed in some cases, because DoEvents will fire the events; however, Repaint will update the UserForm and the user will see the changes on the screen even when another event immediately follows the previous event.

In the UserForm code it is as simple as:

This worked for me:

I couldn’t gain yet the survey of an inherited extensive code. And exact this problem bugged me for months. Many approches with DoEnvents were not helpful. Above answer helped. Placeing this Sub in meaningful positions in the code worked even in combination with progress bar

This is not directly answering your question at all, but simply providing an alternative. I’ve found in the many long Excel calculations most of the time waiting is having Excel update values on the screen. If this is the case, you could insert the following code at the front of your sub:

and put this as the end

I’ve found that this often speeds up whatever code I’m working with so much that having to alert the user to the progress is unnecessary. It’s just an idea for you to try, and its effectiveness is pretty dependent on your sheet and calculations.

On a UserForm two things worked for me:

1. I wanted a scrollbar in my form on the left. To do that, I first had to add an Arabic language to «Change administrative language» in the Language settings of Windows 10 (Settings->Time & Language->Change Administrative Language). The setting is actually for «Change the language of Non-Unicode Programs,» which I changed to Arabic (Algerian). Then in the properties of the form I set the «Right to Left» property to True. From there the form still drew a partial ghost right scrollbar at first, so I also had to add an unusual timed message box:

1. I tried everything to get the screen to redraw again to show the first text box in it’s proper alignment in the form, instead of partially underneath or at least immediately adjacent to the scrollbar instead of 4 pixels to the right where I wanted it. Finally I got this off another Stackoverflow post (which I now can’t find or I would credit it) that worked like a charm:

In my case the problem was in trying to make one shape visible and another one invisible on a worksheet.

This is my approach to «inactivating» a button [shape] once the user has clicked it. The two shapes are the same size and in the same place, but the «inactive» version has dimmer colors, which was a good approach, but it didn’t work, because I could never get the screen to update after changing .visible = FALSE to = TRUE and vice versa.

None of the relevant tricks in this thread worked. But today I found a solution that worked for me, at this link on Reddit

Essentially you just call DoEvents twice in immediate succession after the code that makes the changes. Now why? I can’t say, but it did work.

Источник