Application calculation xlcalculationmanual что значит

Application calculation xlcalculationmanual что значит

We are well aware that in today’s world, a machine’s brain works orders of magnitude faster than a human’s brain. By this, I mean that when there is a requirement to calculate some numbers mathematically, we have quickly defaulted to taking out our calculators, mobile phones, and laptops to guarantee quick and accurate results. This is the great boon of the science and technology.

Microsoft Excel is a host application that makes maintenance of documents easy using formulas. For example, formulas can be put to great use with applications like:

• Loan calculation
• Budgeting
• Business relationship documents
• Grade sheet maintenance
• Database maintenance
• Savings calculation

And those formulas can be reused, over and over again. In Excel, if you select a formula in one cell and drag it down, it can easily be copied to other cells and the result is calculated accordingly in each cell.

How a formula is calculated

There is a category named “Calculations” under the “Formulas” menu. It offers three modes of calculation options:

1. Automatic
2. Automatic except for data tables
3. Manual

These options help us decide when and how the calculation of formulas should be carried out in cells.

Automatic

By default, all calculations are automatic. For example, let us assume that cell A1 has a value of 10 and cell B1 has a value of 5. Now, in cell C1, if I enter a formula to add A1 and B1, and press enter, “15” will immediately –automatically — be visible in the cell C1.

This means that we do not need to manually invoke the calculation. It happens as soon as the cursor leaves the cell.

Automatic except for data tables

This option enables Excel to calculate all cells automatically except the ones involving data tables. Here data tables do not mean the normal tables, or the ones loaded through external connections explicitly. It is specifically the Data What-if Analysis Data Table.

Manual

Let us assume we have the same scenario above where cell A1 as a value of 5 and B1 has a value of 10 . Cell C1 is the sum of A1 and B1 ( =sum( A1, B1) ).

Since the default option is “Automatic,” we can see the value of C1 is 15. Now, change the calculation option to “Manual.” Change the value of A1 to 30 and click on enter. You will see that there is no change in the value of cell C1.

Manually, click on the “calculate now” option in the same menu “Formulas & Calculation.” Now we can see that the value of cell C1 has changed to 40.

Using VBA to turn off or turn on Automation Calculations

The code to set or change the calculation options in VBA is very simple and uses application.calculation.

Syntax

Where the mode can be one of the three below options

• xlCalculationManual
• xlCalculationAutomatic
• xlCalculationSemiAutomatic

Examples

Addition of 2 numbers

Just like above, we’ll add two numbers together in this example. Here we switch the calculation mode to Manual and then again to Automatic to see the difference.

The sum’s value is printed, and a screenshot of the run is also provided for reference.

Validate if a mark is a pass or fail grade

In this example, the first cell is a number and the second cell holds an “if” formula. The value is “Pass” if the value of first cell is greater than 49. If not, the value remains “Fail.”

Concatenate first name and last name to get the full name

This example sets first name, last name, and then uses a formula to concatenate and display the full name. Again, we toggle between the two calculation options to understand its working.

Conclusion

Although the default calculation option of “Automatic” is simple and easy to use, sometimes due to a huge size or volume of data in any Excel workbook, its performance might slow down. In order to handle this situation, we can switch to “Manual” or the “Semi-Automatic” i.e. the “Automatic except for data tables” options to perform calculations only as required.

We may explicitly use the “calculate now” and “calculate sheet” options in our code depending on the area of recalculation. Hence these options are available to users in order to improve performance.

Источник

—>Электронный учебник по VBA MS Excel 2010 —>

Чем больше познаём мы макросы, тем интересней выгледят наши программы. А бывает, что их выполнение происходит очень долго, при сложных и долгих математических расчётах или составлении каких-то отчётов и табилц. В ходе выполнения макроса на мониторе происходит мелькание различных окон, открытие и закрытие книг, и прочая светомузыка. Для того чтобы этого не происходило, и чтобы время выполнение нашего макроса сократить раз в 100, можно воспользоваться командами описанные ниже.

Application.ScreenUpdating

Application.ScreenUpdating — отвечает за обновление экрана и может принимать два значения — False (обновление экрана отключено) и True (обновление экрана включено). В коде это обычно прописывается в том месете, где происходит мелькание различных окон или видно как производится расчёт и происходит заполнение таблицы. Ниже показан пример заполнения ячеек, и данную команду вставили в начало и конец макроса, т.е. сначала отключаем обновление экрана, а потом включаем обновление экрана. При такой записи мы не увидим процесс заполнения ячеек. А вот если убрать эти команды, то мы сможем наблюдать за процессом заполнения этих ячеек.

For a = 1 To 100

For b = 1 To 100

Cells(a, b) = «Пример»

Application.Calculation

Application.Calculation — отвечает за автоматический расчёт в книге Excel и может принимать два значения — xlCalculationManual(ручной расчёт) и xlCalculationAutomatic (автоматический расчёт — по умолчанию установлен в Excel). Но тут есть одна осторожность, если вы перевели Excel в ручной расчёт, и в макросе произошла ошибка и он так и не выполнился до конца — т.е. не включился автоматический расчёт формул, то все ваши вычисления в дальнейшем будут в пустую. Так как формулы не будут автоматически пересчитываться, Excel превратиться в обычную таблицу. Но данная команда играет одну из основных ролей в быстроте выполнеия макроса. Вообщем лучше сделать код, который исключает ошибки, чтобы макрос полюбому выполнился и Excel перевёлся в автоматический расчёт. Если у Вас имеется большая таблица с многочисленными формулами, и часть вычислений вы производите при помощи макросов, то для быстроты выполнения расчётов разумно в начало и конец кода поместить команду Application.Calculation.

Application.EnableEvents

Application.EnableEvents — команда отвечающая за выполнение сторонних событий. Эту команду мы уже затрагивали в этом уроке. И она также может принимать два значения — это False (отключить собтие) и True (включить выполнение промежуточных событий). Но теперь ещё известно, что она играет значительную роль в скорости выполнения некоторых кодов макроса. Пример можно взять из Урока №23.

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.DisplayPageBreaks

ActiveSheet.DisplayPageBreaks — отображение границ листа. Может принимать два значения — False (отключить отображение границ) и True (включить отображение границ). Не знаю как это помогает на скорости выполнения макроса, лично я этого не ущутил, но некоторые говорят, что помогает. Я вообще не люблю когда отображаются границы листа, мне кажется, что это нужно только при распечатке. Помещать этот код можно в начало и конец макроса.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.DisplayStatusBar

Application.DisplayStatusBar — строка состояния. Может принимать два значения — False (отключить строку состояния) и True (включить строку состояния). При выполнении макросов в строке состояния отображаются все происходяще события. Для того чтобы не тратить время на просчёт событий и прорисовку их в статусбаре, отключаем её на время выполнения макроса, и включаем её когда макрос закончил выполняться.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.DisplayAlerts

Application.DisplayAlerts — команда, отвечающая за события в Excel. Может принимать два значения — False (отключаем запросы Excel) и True (включаем события Excel). Это чень интересная и полезная команда при помощи, которой можно отключить запросы Excel, например, чтобы он не спрашивал нужно ли сохранить изменения в книге, или отключить запрос на совместимость версий Excel. Ниже приведён пример, в котором книга закрывает сама себя, при этом независимо от того внесли вы изменения или нет в книгу, при закрытии книги вам не поступит запрос «Сохранить изменения», а книга просто закроется без сохранения и уведомления пользователя.

Глобальное ускорение

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

Источник

Как ускорить и оптимизировать код VBA

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

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

если печать производится внутри кода, то эту строку желательно вставить сразу после строки, выводящей лист на печать(при условии, что печать не происходит в цикле. В этом случае — по завершению цикла печати).
Я советую всегда отключать разбиение на страницы, т.к. это может тормозить весьма значительно, т.к. заставляет при любом изменении на листах обращаться к принтеру и переопределять кол-во и размер печатных страниц. А это порой очень не быстро.
На всякий случай можно отключить отображение информации в строке статуса Excel (в каких случаях там вообще отображается информация и зачем можно узнать в статье: Отобразить процесс выполнения). Хоть это и не сильно поедает ресурсы — иногда может все же ускорить работу кода:

Главное, что следует помнить — все эти свойства необходимо включить обратно после работы кода . Иначе могут быть проблемы с работой внутри 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

Разрывы печатных страниц можно не возвращать — они тормозят работу в любом случае.
Следует избегать циклов, вроде Do While для поиска последней ячейки . Часто такую ошибку совершают начинающие. Куда эффективнее и быстрее вычислять последнюю ячейку на всем листе или в конкретном столбце без этого тормозного цикла Do While. Я обычно использую

другие варианты определения последней ячейки я детально описывал в статье: Как определить последнюю ячейку на листе через VBA?

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

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

Sub TestOptimize_Array() ‘Непосредственно код заполнения ячеек Dim arr, lr As Long ‘запоминаем в массив одним махом все значения 10000 строк первого столбца arr = Cells(1, 1).Resize(10000).Value ‘если нужно заполнение для двух и более столбцов ‘arr = Cells(1, 1).Resize(10000, 2).Value ‘или ‘arr = Range(Cells(1, 1),Cells(10000, 2)).Value ‘или автоматически вычисляем последнюю ячейку и заносим в массив данные, начиная с ячейки А3 ‘llastr = Cells(Rows.Count, 1).End(xlUp).Row ‘последняя ячейка столбца А ‘arr = Range(Cells(3, 1),Cells(llastr, 2)).Value For lr = 1 To 10000 arr(lr,1) = lr ‘заполняем массив порядковыми номерами Next ‘Выгружаем обработанный массив обратно на лист в те же ячейки Cells(1, 1).Resize(10000).Value = arr End Sub

Но здесь следует учитывать и тот момент, что большие массивы могут просто вызвать переполнение памяти. Наиболее актуально это для 32-битных систем, где на VBA и Excel выделяется памяти меньше, чем в 64-битных системах

Если используете быстрый ЕСЛИ — IIF , то замените его на IF . Then . Else

Так же лучше вместо Switch() и Choose() применить тот же IF . Then . Else

В большинстве случаев проверять строку на «не пусто» лучше через Len() , чем прямое сравнение с пустотой: Len(s)=0 вместо s = «» . Связано с тем, что работа со строками значительно медленнее, чем с числовыми данными и Len по сути не подсчитывает длину переменной, а берет это число непосредственно уже готовое из памяти. При сравнении же текста с пустой строкой(«»), VBA сначала создает в памяти переменную нулевой длинны, а уже потом сравнивает с ней наш текст. Поэтому в некоторых случаях так же ускоряет сравнение и в таком виде: s = vbNullString

Не применять объединение строк без необходимости. Например, s = «АВ» , будет быстрее, чем: s =»А» & «В»

Не применять сравнение текстовых величин напрямую. Лучше применить встроенную функцию StrComp:
If s <> s1 Then будет медленнее, чем
If StrComp(s, s1, vbBinaryCompare) <> 0
и тем более, если при сравнении необходимо не учитывать регистр:
If LCase(s) <> LCase(s1) Then будет медленнее, чем
If StrComp(s, s1, vbTextCompare) <> 0

Циклы For … Next в большинстве случаев работает быстрее, чем цикл Do . Lоор

Избегать присвоения переменным типа Variant . Хоть соблазн и велик — этот тип забирает много памяти и в дальнейшем замедляет работу кода. Так же для объектных переменных следует избегать по возможности безликого глобального типа Object и применять конкретный тип:

Dim rRange as Object, wsSh as Object

будет медленнее работать, чем:

Dim rRange as Range, wsSh as Worksheet

Причина в том, что при объявлении As Object мы не даем VBA практически никакой информации о типе данных, кроме того, что это какой-то объект. И VBA приходится «на лету» внутри кода при каждом обращении к такой переменной определять её конкретный тип(Range, Worksheet, Workbook, Chart и т.д.). Что опять же занимает время.
Если работаете с массивами, то можно при объявлении указать это явно:

Такая инициализация происходит быстрее.
А еще лучше будет при этом еще и тип данных сразу присвоить:

Dim arr() as string, arr2() as long

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

Конечно, это не все приемы и решения для оптимизации. Но на первых парах должно хватить. Плюс, всегда следует исходить из здравого смысла . Например, если код выполняется за 2 секунды, то вероятно нет смысла его дальше оптимизировать. Конечно, если этот код не из тех, которые просто изменяют значение одной-двух ячеек.

Статья помогла? Поделись ссылкой с друзьями!

Источник