Excel vba отключить пересчет формул

Обновлено: 07.07.2024

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

но почему-то этот макрос отключает также и формулы в др. ячейках.

вобщем смысл такой: пользователь вносит данные без зависания, т.к. автомат.вычисления в определленом диапозоне отключены. После чего переходит на лист "отчет", нажимает кнопку - данные вносятся (и с этим все ок), но другие ячейки (в которых формула типа если А=Б - ок; <> - проверь данные) не пересчитыватся .

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Как отключить пересчет формул
Добрый день. Excel 2010 при закрытии файла, предварительно сохраненного в старой версии, выдает.

Как в Excel отключить пересчет формул?
Для более быстрого расчета, мне нужно в Excel отключить вычесление формул. Через объект.

Выбрать последнее совпавшее знаяение из диапазона с посредством формул
Доброе время суток!! Часто использую для выборки искомых данных из диапазона комбинацию формул.

Application.Calculation - Эта команда уровня приложения (т.е. Excel я) - а не выделенного диапозона.
Поэтому выключаются все вычисления внутри открытого экземляра Excel'я.

Добавлено через 8 минут
Есть метод Range.Calculate.
На сколько я понимаю, после массового отключения вычислений, и ввода данных можно запускать пересчеты для нужных диапазонов. Отталкивайтесь от этого.

Нужно писать типа:
Range("A1:B2").Сalculate или ActiveSheet.Range("A1:B2").Сalculate.

не поддерживает свойство или метод

Добавлено через 6 минут
не так выразился.. не поддерживает потом false или true

Смотрите: у Range.Calculate - нет фалс/тру, это метод - он просто выполняется.
Сначала Application.Calculation = xlCalculationmanual вы выключаете отработку событий.
А потом методом Range("нужный диапозон").Calculate вы калькулируете нужные диапазоны.

интересная идея, но..
если я отключу обработку полностью, то 90% формул на листе перестанут работать. Поэтому каждый рэндж мне придется прописывать в макросе на включение.. а так как таблицы отчета со сводкой расширяются примерно раз в 3 дня - придется каждый раз апдейтить рэндж в макросе. И на сколько я понимаю, это повлечет за собой полность отключение в формул в книге, поэтому диапозонов придется вписывать очень много.
Неужели в экселе нет обратной функции на "включить все, отключить диапозон"?

п.с. перечитал 7 страниц поиска на яндексе по данной теме. ничего путного

Такого нет. Как правило калькуляции выключают для ускорения обработки макроса, а в конце работы макросов их включают для возвращения нормальной работы Excel.

Как вариант можно отлавливать изменения в нужном диапазоне на листе, но сомневаюсь что, это вам чего то даст.

Что если каждый пользователь будет заполнять свою книгу/шаблон. А вы потом будет консолидировать все результаты в одну книгу?
Или нарисовать формочку и данные будут забиваться в неё, а потом перетягивать их в книгу?

Формулы – это хорошо. Они автоматически пересчитываются при любом изменении исходных данных, превращая Excel из "калькулятора-переростка" в мощную автоматизированную систему обработки поступающих данных. Они позволяют выполнять сложные вычисления с хитрой логикой и структурой. Но иногда возникают ситуации, когда лучше бы вместо формул в ячейках остались значения. Например:

Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).
Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании "сползут" все ссылки в формулах.

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

Способ 1. Классический

Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:

Выделите диапазон с формулами, которые нужно заменить на значения.
Скопируйте его правой кнопкой мыши – Копировать(Copy) .
Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values) :

либо наведитесь мышью на команду Специальная вставка (Paste Special) , чтобы увидеть подменю:

Из него можно выбрать варианты вставки значений с сохранением дизайна или числовых форматов исходных ячеек.

В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special - Values) в открывшемся диалоговом окне:

Способ 2. Только клавишами без мыши

При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:

Копируем выделенный диапазон Ctrl + C
Тут же вставляем обратно сочетанием Ctrl + V
Жмём Ctrl , чтобы вызвать меню вариантов вставки
Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter :

Способ 3. Только мышью без клавиш или Ловкость Рук

Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:

Выделяем диапазон с формулами на листе
Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only) .

После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали ;)

Способ 4. Кнопка для вставки значений на Панели быстрого доступа

Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл - Параметры - Панель быстрого доступа (File - Options - Customize Quick Access Toolbar) . В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:

Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:

Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt , то Excel подскажет цифру, которая за это отвечает:

Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу

Если вас не пугает слово "макросы", то это будет, пожалуй, самый быстрый способ.

Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:

Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:

И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:

Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt + F11 чтобы попасть в Visual Basic, далее Insert - Module). Запускать их потом можно через вкладку Разработчик - Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 . Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить - применяйте их с осторожностью.

Способ 6. Для ленивых

Если ломает делать все вышеперечисленное, то можно поступить еще проще - установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:

Если в коде есть много всяких Activate и Select , тем более в циклах - следует немедленно от них избавиться. Как это сделать я писал в статье: Select и Activate - зачем нужны и нужны ли?
Обязательно на время выполнения кода отключить:
автоматический пересчет формул . Чтобы формулы не пересчитывались при каждой манипуляции на листе во время выполнения кода - это может дико тормозить код, если формул много:

Если во время кода все же нужно пересчитывать какие-то диапазоны, то можно пересчитывать только их:

Главное, что следует помнить - все эти свойства необходимо включить обратно после работы кода . Иначе могут быть проблемы с работой внутри Excel. Например, если забыть включить автопересчет формул - большинство формул будут пересчитывать исключительно принудительным методом - после нажатия сочетания клавиш Shift + F9 . А если забыть отключить обновление экрана - то есть шанс заблокировать себе возможность работы на листах и книгах. Хотя по умолчанию свойство ScreenUpdating и должно возвращаться в True, если было отключено внутри процедуры - лучше не надеяться на это и привыкать возвращать все свойства на свои места принудительно. По сути все это сведется к нескольким строкам:

'Возвращаем обновление экрана Application.ScreenUpdating = True 'Возвращаем автопересчет формул Application.Calculation = xlCalculationAutomatic 'Включаем отслеживание событий Application.EnableEvents = True

Как такой код выглядит на практике. Предположим, надо записать в цикле в 10 000 строк значения:

Sub TestOptimize() 'отключаем обновление экрана Application.ScreenUpdating = False 'Отключаем автопересчет формул Application.Calculation = xlCalculationManual 'Отключаем отслеживание событий Application.EnableEvents = False 'Отключаем разбиение на печатные страницы ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False 'Непосредственно код заполнения ячеек Dim lr As Long For lr = 1 To 10000 Cells(lr, 1).Value = lr 'для примера просто пронумеруем строки Next 'Возвращаем обновление экрана Application.ScreenUpdating = True 'Возвращаем автопересчет формул Application.Calculation = xlCalculationAutomatic 'Включаем отслеживание событий Application.EnableEvents = True End Sub

Для более опытных пользователей VBA я приведу несколько решений по оптимизации кодов в различных ситуациях:

В таблице Excel, изображенной ниже, я хотел бы, чтобы перерасчет прекратился для верхней части из строк 1-5. Под перерасчетом я имею в виду, что, например, в ячейках M2, H4, S4 есть формула today (), и каждый раз, когда я открываю ее, она пересчитывается. Я бы хотел, чтобы он рассчитывался один раз и один раз сохраняю, чтобы не пересчитывать еще раз, снова открывал. Какие-либо предложения? я пытался

3 ответа

Что ж, установка Application.Calculation = xlCalculationManual во время Workbook_Open не всегда может гарантировать вам желаемый результат, поскольку существует вероятность того, что Excel может быть в xlCalculationAutomatic при открытии файла.

Итак, обходной путь: откройте Excel и установите вычисление вручную либо в VBE, либо через меню, а затем откройте свой файл.
В VBE введите Application.Calculation = xlCalculationManual в окне, затем нажмите Enter и откройте файл.
В Excel для версии 2007 нажмите Formula Menu , перейдите к Calculation Group , в Calculation Options выберите Automatic/Manual . Для более старых версий Tools > Options > Calculation .

Я бы посоветовал оценить функцию в VBA на нужных вам событиях.

Если вы хотите, чтобы функция СЕГОДНЯ выполнялась по вашему запросу, я бы создал кнопку с макросом, который переоценивает СЕГОДНЯ.

Как я бы это реализовал, зависит от того, хотите ли вы / нужно ли иметь ячейку в виде формулы. Если рассчитанного значения достаточно, вы можете рассчитать его в VBA и установить значение в нужные ячейки. Если вы хотите иметь формулу и сохранить последнее вычисленное значение СЕГОДНЯ, вы можете установить формулу в ячейку. Range("M2").Formula = Date() & "other formula part"

Самое простое решение - установить сегодняшнее значение в какой-либо ячейке в VBA.

У меня нет репутации, чтобы оставлять комментарий, чтобы задать уточняющий вопрос, поэтому я опубликую ответ вместо него (сделав предположение о том, каким будет ваш ответ).

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

Читайте также: