Несколько советов по работе с VBA в Excel
Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.
Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.
Visual Basic
Опции
Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:
Так же рекомендуется прописать:
В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что:
— VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант;
— иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).
Ещё одним важным оператором является ON ERROR. Привожу варианты:
Возможности языка
Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT’ах (аналог switch):
Ускорение работы макросов
Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.
По порядку:
1. Отключить перерисовку объектов на экране, чтобы ничего не мигало.
2. Выключить расчет. Внимание, если макрос прерваляс посреди работы, то расчет так и останется в ручном режиме!
3. Не обрабатывать события.
4. Отображение границ страниц, тоже почему-то помогает.
5. В статусной строке выводятся различные данные, что замедляет работу, отключаем.
6. Это если нужно. Выключает сообщения Экселя. Например, мы делаем Workbook.Close, Эксель хочет спросить сохранить ли изменения. При выключении этого параметра все ответы будут даны автоматически (изменения не сохранятся).
Важно понимать, что VBA выполняет все действия так же, как и пользователь. Поэтому для того, чтобы установить параметры страницы, он каждый раз открывает и закрывает окно параметров. У меня выставлялись параметры для 10 листов, это реально не быстро. Поэтому делаем так:
Далее, часто нужно просмотреть различные диапазоны ячеек и что-то с ними сделать. Тут важно не использовать циклы for с перебором индексов, они медленные. Можно использовать встроенные функции Экселя, но удобнее всего такой вариант:
Данный код просматривает указанный диапазон, выбирает в нем «специальные ячейки», в данном случае все, в которых есть формулы (т.е. начинаются со знака равно). Для каждой ячейки смотрится, если она не закрашена, то её надо защитить (см. далее) и покрасить. Такой код работает очень быстро.
Для любых переменных, которым вы собираетесь присвоить книгу, лист, диапазон (ячейку) нужно предварительно объявить как Variant.
Естественно, что если вам нужны однотипные значения в ячейках, нужно использовать автозаполнение, всё равно как «растягивание» ячеек пользователем.
Второй диапазон должен включать первый, а второй необязательный параметр указывает тип автозаполнения.
Загрузка книги и события
При открытии книги каждый раз срабатывает процедура.
В данном случае настройки печати (поля, ориентация) сбрасываются на дефолтные. Можно и другую инициализацию выполнять. Важно, что если макросы отключены, то и не выполнится ничего. Если в Экселе вылезла вверху панелька с предупреждением о макросах и пользователь нажал «Включить», то именно в этот момент выполнится процедура Workbook_open().
Список доступных событий можно посмотреть вверху редактора VB. Например, я делал на событие Change проверку, где лежит ячейка, в которой было изменения, и если это нужный диапазон, то делалась запись в лог со старым и новым значением.
Защита
Во-первых сразу отмечу, что MS Office не исполняет макросы на компьютерах, где он не нашел антивируса, если книга зашифрована. Сталкивался на компьютерах, где антивирус был, но видимо Windows XP об этом не знала.
Ещё антивирус может странным образом мешать работе, вызывать ошибки, не совсем объяснимые. Показал айтишникам, сказали ок, что-то сделали, не знаю.
Итак, нам надо защитить книгу, чтобы ввод был разрешен только в нужные ячейки (формулы и заголовки поменять нельзя). Во-первых, нужно сделать соответствующие ячейки «не защищенными». Для этого делаем одно из:
— выделяем диапазон, формат ячеек, снять галочку «Блокировать ячейку»;
— выводим кнопку «Блокировать ячейку» в быстрый доступ и нажимаем её, очень удобно смотреть на неё чтобы понять, защищена ячейка или же нет;
— а это пригодится, чтобы проверить третий вариант — написать макрос, который снимает защиту с нужных ячеек сам.
Далее нужно защитить лист. На вкладке Рецензирование есть такая кнопка. Окошко просит ввести пароль и установить исключения (что можно будет делать пользователю). К сожалению, список исключений маловат. Самое обидное, что нельзя разрешить сворачивать/разворачивать группы столбцов/строк. Поэтому действуем так, на загрузку книги прописываем:
Знак подчеркивания продолжает логическую строку на следующей физической строке. Итак, здесь мы:
1. Сняли защиту.
2. Включили группировку.
3. Поставили защиту, при этом:
— защита только от юзера, макросы продолжают иметь полный доступ (!), крайне важно;
— разрешили сортировку, фильтрацию и форматирование строк/столбцов (высота/ширина);
— DrawingObject в данном случае снимает защиту с примечаний к ячейкам, может и ещё с чего.
Тут мы сталкиваемся с парой сюрпризов. Во-первых, не все макросы будут работать даже так. Известный баг, ничего не сделаешь. Нельзя вставить строку, например. Приходится снимать и тут же ставить защиту. Если «злоумышленник» в этот момент нажмет ctrl+break, то защита слетит.
Во-вторых, скажем никаким способом нельзя удалять строки (AllowDeletingRows), в которых есть защищенные ячейки, хоть одна. Подробнее вот тут.
Решением (костылем) является добавление кнопки или сочетания клавиш для удаления. Заодно можно проверить, чтобы пользователь не удалил чего не надо. В Workbook_open добавляем:
Теперь процедура будет вызываться при нажатии shift+delete.
Знаю, код некрасивый, простите. Здесь я пытался проверить, что выделена строка, то есть строк там 1, а ячеек не меньше тысячи. Чтобы удалить не то, придется выделить тысячу ячеек начиная не с первого столбца. Далее проверяется имя листа и номера строк. Вместо 50 был расчет последенй строки (ведь их число меняется, если мы их удаляем и добавляем).
Заключение
VBA — весьма глючная вещь, которая позволяет сворачивать горы в MS Office. Многие предприятяи используют модели на Excel годами, и если они сделаны хорошо, то всё работает.
Для изучения VBA подходит он сам, во-первых там хорошая справка. Например, чтобы узнать все варианты что можно разрешить в методе Protect, нажимаем F1, Protect, ввод. И вуаля.
Во-вторых, можно проделать требуемые действия вручную, записав макрос, а потом просмотрев его код. Код будет ужасен (например, при изменении параметров страницы, макрос запишет значения всех параметров и полей, а не только измененного вами), но ответы найдутся. Хотя, например, .AutoFit, который записывается при изменении высоты ячейки по содержимому (двойной клик на границе слева), на самом деле не работает.
Предлагаю знатокам поделиться своим опытом, дать советы в комментариях. Спасибо за внимание, удачных разработок вам.
Displayalerts excel что это
Смотрите также есть такое свойство спасает. Уровень безопасности property to True так заработало: Думаю неправильная методика
куда — не )) вопрос на совместимость вызванная процедура Create_Mobile_Rep
уже понимаемыми, конечно — помоему потомуreply = MsgBox(«Вы неделю» план на следующуюRange(«G9,G11,G13,G15,G17,G19,G21,G23,G27,G33,G35,G37,G39,G41,G43,G45,G47,G49,G51,G53,G55,G57″).Locked = True True, тогда уKoNooS104 DisplayAlerts для отключения в excel и when the code[email protected] :) знаю, я ненебольшое уточнение Excel, хотя яOption Explicit Sub если в теме что это не указали план наCancel = True неделю?» файл должен
Range(«G59,G61,G63,G65,G67,G69,G73,G75,G77,G79,G81,G83,G85,G87,G89,G90,G92,G94,G96,G98,G100,G102,G104»).Locked = True Вас в голове: Здравствуйте. Вообщем, у предупреждений. Есть ли в internet explorer is finished, unless: Спасибо, друзья! Разобрался
Попробуйте так: MVP ^_^Application.DisplayAlerts ж отключаю его Create_Mobile_Rep() Dim ws1 хоть немного и стандартный алерт. Тут следующую неделю?», vbYesNo,
Где в код вставляется Application.DisplayAlerts? (Макросы/Sub)
ElseIf reply = закрываться с сохранением,
Range(«G108,G110,G112,G114,G116,G118,G120,G122,G124,G126,G128,G130,G132,G134,G136,G138,G140,G142,G145,G147,G149,G151,G153»).Locked = True преобладает только одна
меня на кнопке аналог при работе
минимальный.
you are running
SpongyUrchin
Sub tt() Application.DisplayAlerts
Xapa6apgaделает это на
в первом модуле. As Worksheet, ws2 таких ляпов, которые три варианта ответа.
«Запрос на продолжение»)
vbYes Then Me.Save: а в противном
Dim reply As мысль: «Достала ты удаления страницы графика
с word? WordApp.DisplayAlerts:=falseweeper cross-process code.
: А можете поподробнее
= False ThisWorkbook.SaveAs : , спасибо большое уровне Excel наЕсли поставить в As Worksheet Dim
мешают пониманию, меньшеДа и если’Application.DisplayAlerts = False Exit Sub случае возвращать пользователя Integer меня уже, подруга.» с екселя записана ошибку не выдает
: По моему никак.Но что он
объяснить в чем «путь к существующему
) конкретном компьютере ?
самой процедуре Create_Mobile_Rep,
CurBook As Workbook,
становится.
бы он сработал
‘ отключение вопросовEnd If
на рабочий лист.reply = MsgBox(«ВыKoNooS104 команда:
но и не
Филин считает за «cross-process
проблема? файлу» End Sub[email protected]
Так это ?
тогда ок, но MobRep As WorkbookXapa6apga
— то Вы
If reply =
End SubДобавил файл. Пароль указали план на: Ты пьян?) ахахApplication.DisplayAlerts=false charts.delete Application.DisplayAlerts=trueОбъясните помогает ничего не: Для подавления выводов code»? Разве открытиеУ меня такжеИ вот запустите: Добрый день уважаемыеЛузер™
почему ? ‘| Статус бар
: Здравствуйте, прошу очередной
бы не получили
vbNo Then
bmv98rus
528 следующую неделю?», vbYesNo,
спасибо за такой пожалуйста как она делает сообщений Excel’я ставлю
новой книги входит не получается перевести
с работающей и Гуру!
: Не только наЗаранее благодарен за Application.StatusBar = «|»
раз Вашей помощи.
тот результат, которыйMsgBox «Укажите плановые
:
SLAVICK
«Запрос на продолжение») подробный ответ, разобрался. работает? Как связанаSheeby
в проге Application.DisplayAlerts в это определение? приложение в состояние неработающей строкой Application.DisplayAlertsСобственно проблема -
конкретном компе, но ответ! & «Create_mobile_rep «Есть основной модуль хотели книга закрывалась задания на следующуюlight26
: Может так:Application.DisplayAlerts = False Можно было просто с графиком?: я применял = False. Но Если да, то
.DisplayAlerts = False = False
не работает Application.DisplayAlerts. и в конкретнойЛузер™ & «|» &
с которого запускаются бы с сохранением. неделю», просто добавьте Me.SavePrivate Sub Workbook_BeforeClose(Cancel AsIf reply = написать: Спрашивает разрешение.
Igor_TrDelphi w.Application.DisplayAlerts := трабл в том, есть ли какой-либоЕсли выдать MsgBoxИли вот такТ.е. наоборот - запущенной копии Эксель.: Моя версия: «Запустился в «
Не срабатывает Application.DisplayAlerts = False
другие макросы. bmv98rusCancel = True
а Application.DisplayAlerts = Boolean) vbNo Then
): Я наверное на False; что когда я способ установить .DisplayAlerts сразу после выполнения погонять: работает, а вотВедь всегда можноОбъект Application в & «|» &Option Explicit Sub: https://msdn.microsoft.com/ru-ru. y-excelElseIf reply = False , даApplication.EnableEvents = TrueMsgBox «Укажите плановые
Igor_Tr дежурстве. Команда «запрещает»ПраПрапорщик процедуру работы с = False на строки отключения, значениеSub tt() Application.DisplayAlerts перевести в состояние написать процедуре Create_Mobile_Rep создается Format(Now, «hh:mm:ss») & A_MACRO() Application.ScreenUpdating =light26 vbYes Then Me.Save и exit subRange(«G9,G11,G13,G15,G17,G19,G21,G23,G27,G33,G35,G37,G39,G41,G43,G45,G47,G49,G51,G53,G55,G57»).Locked = True задания на следующую: Да не спрашивает. системе задавать лишние: А что Вы Excel’ем прогоняю в все время выполнение указано False, но = False ThisWorkbook.SaveAs =FALSE не получаетсяSet xlApp = заново с дефолтными «|» ‘| Set False Application.DisplayAlerts =:End If
не нужно мнеRange(«G59,G61,G63,G65,G67,G69,G73,G75,G77,G79,G81,G83,G85,G87,G89,G90,G92,G94,G96,G98,G100,G102,G104»).Locked = True неделю» Это Вы ей
вопросы. Вы удаляете, с вордом такого
первый раз, то макроса?
при открытии книги, «C:\Documents and Settings\юзер\Desktop\Book2.xls»Поиском подобного не New Application
свойствами. CurBook = Application.ThisWorkbook False ‘Отключит глупыеSLAVICK
End Sub кажется, а вотRange(«G108,G110,G112,G114,G116,G118,G120,G122,G124,G126,G128,G130,G132,G134,G136,G138,G140,G142,G145,G147,G149,G151,G153»).Locked = True
ElseIf reply = разрешаете, или нет. например, лист как делаете что он
всё нормально, сообщенияекфузя
в которой запрашивается Application.DisplayAlerts = True нашел, если проглядели получим новуюЕсли его передавать Set ws1 =
вопросы ) Application.Calculation, усвоилlight26 Сancel = trueDim reply As
vbYes Then Exit False -запрещаете ,
обычно, и тогда алерты Вам выкидывает?
подавляются, дальше же: Привет обновление ссылок, значение
ThisWorkbook.SaveAs «C:\Documents and — дайте ссылку копию (запущенный процесс) из A_MACRO в CurBook.Sheets(«Сеть») Set MobRep = xlCalculationAutomatic Application.EnableEventsbmv98rus: на пользу пойдет Integer Sub Тrue -разрешаете задавать
система Вас спрашивает:Sheeby Application.DisplayAlerts = False
Не работает переключение .DisplayAlerts
При работе методами сразу становится True Settings\юзер\Desktop\Book2.xls» End Sub
пожалуйста. Эксель.
Create_Mobile_Rep, то и = Application.Workbooks.Add MobRep.SaveAs = False ‘|, да пытался я
SLAVICK :-)reply = MsgBox(«ВыEnd If
вопросы. А чего «А не сохранить, спосибо, коротко и
не срабатывает, как VBA открываю файл и диалоговое окно
Sergei_AВ примере все
Некоторые из свойств свойства передадутся. Filename:=»C:\Сеть\Mobile_rep\» + «m»
Статус бар Application.StatusBar
туда сунуться. Только, Спасибо за кодPrivate Sub Workbook_BeforeClose(Cancel As указали план на
End Sub Вы такой сухарь? ли нам изменения». точно
будто он там в интернете. Перед
не подавляется.: Здесь та же зацепил на кнопки, Application будут сохраненыXapa6apga + CurBook.Name, _
= «|» & не понимаю ничегоДа, работает в Boolean) следующую неделю?», vbYesNo,Не могу добитьсяlight26
А Вы ей:»ДаFaTaL-CS и не ночевал.
открытием программа останавливаетсяSpongyUrchin
история что и лист без пароля. и восстановлены при: , почти так
FileFormat:=xlExcel8, Password:=»», WriteResPassword:=»», «A_MACRO » & по-английски. А перевод обоих вариантах. Спасибо.
Application.EnableEvents = True «Запрос на продолжение») отключения запроса на: Доброго времени суток. нет, спасибо, не, открываю шоблон заполняю :-((( В чём и запрашивает разрешение: В справке MSDN Application.ScreenUpdating. При корректном
.EnableEvents в этом открытии книги даже и было,
ReadOnlyRecommended:=False, CreateBackup:=False Set «|» & «Запустился корявый. Но все Но почему Application.DisplayAlertsRange(«G9,G11,G13,G15,G17,G19,G21,G23,G27,G33,G35,G37,G39,G41,G43,G45,G47,G49,G51,G53,G55,G57″).Locked = TrueApplication.DisplayAlerts = False сохранение книги посредствомВ книге есть утруждайтесь». И книга(лист,
и если возникнет дело ?Помогите разобраться, на открытие файла. также указано: выходе из продцедуры, примере включается и на другом компе,До этого вызывался ws2 = MobRep.Sheets(1) в » & равно спасибо
Как избавиться от окон? Application.DisplayAlerts = False не спасает.
= False неRange(«G59,G61,G63,G65,G67,G69,G73,G75,G77,G79,G81,G83,G85,G87,G89,G90,G92,G94,G96,G98,G100,G102,G104»).Locked = True
If reply = Application.DisplayAlerts = False макрос обьект. ) удаляется (закрывается) ошибка пытаюсь закрыть плиз. Как можно сделать,If you set значение встает в выключается без проблем. например, Calculation. Некоторые еще одна процедура ‘Какие-то манипуляции ‘ «|» & Format(Now,
bmv98rus работал?
не работает Application.DisplayAlerts
Range(«G108,G110,G112,G114,G116,G118,G120,G122,G124,G126,G128,G130,G132,G134,G136,G138,G140,G142,G145,G147,G149,G151,G153»).Locked = True vbNo ThenПо замыслу приPrivate Sub Workbook_BeforeClose(Cancel As без сохранения последних без вопросов иПраПрапорщик что-бы это сообщение this property to True автоматически.Вот где лыжи — нет, при и в ней ‘ MobRep.Save ‘ «hh:mm:ss») & «|»: Да, переводы тамSLAVICKDim reply AsMsgBox «Укажите плановые
Есть ли в MS word аналог Displayalerts:=false как в excel?
нажатии «Да» после Boolean) изменений. Это когда возражений со стороны: при роботе с не появлялось. Application.DisplayAlerts False , MicrosoftSanja не едут? закрытии будут сброшеныApplication.DisplayAlerts = True Задает вопрос на
‘| Call Create_Mobile_Rep не литературные, это
: ессли честно не Integer
Name already in use
VBA-content / VBA / Excel-VBA / articles / application-displayalerts-property-excel.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
Application.DisplayAlerts Property (Excel)
True if Microsoft Excel displays certain alerts and messages while a macro is running. Read/write Boolean .
expression . DisplayAlerts
expression A variable that represents an Application object.
The default value is True . Set this property to False to suppress prompts and alert messages while a macro is running; when a message requires a response, Microsoft Excel chooses the default response.
If you set this property to False , Microsoft Excel sets this property to True when the code is finished, unless you are running cross-process code.
Note When using the SaveAs method for workbooks to overwrite an existing file, the Confirm Save As dialog box has a default of No, while the Yes response is selected by Excel when the DisplayAlerts property is set to False . The Yes response overwrites the existing file.When using the SaveAs method for workbooks to save a workbook that contains a Visual Basic for Applications (VBA) project in the Excel 5.0/95 file format, the Microsoft Excel dialog box has a default of Yes, while the Cancel response is selected by Excel when the DisplayAlerts property is set to False . You cannot save a workbook that contains a VBA project using the Excel 5.0/95 file format.
This example closes the Workbook Book1.xls and does not prompt the user to save changes. Changes to Book1.xls are not saved.
This example suppresses the message that otherwise appears when you initiate a DDE channel to an application that is not running.
Ускоряем работу макроса в Excel
Чем больше познаём мы макросы, тем интересней выгледят наши программы. А бывает, что их выполнение происходит очень долго, при сложных и долгих математических расчётах или составлении каких-то отчётов и табилц. В ходе выполнения макроса на мониторе происходит мелькание различных окон, открытие и закрытие книг, и прочая светомузыка. Для того чтобы этого не происходило, и чтобы время выполнение нашего макроса сократить раз в 100, можно воспользоваться командами описанные ниже.
Application.ScreenUpdating
Application.ScreenUpdating — отвечает за обновление экрана и может принимать два значения — False (обновление экрана отключено) и True (обновление экрана включено). В коде это обычно прописывается в том месете, где происходит мелькание различных окон или видно как производится расчёт и происходит заполнение таблицы. Ниже показан пример заполнения ячеек, и данную команду вставили в начало и конец макроса, т.е. сначала отключаем обновление экрана, а потом включаем обновление экрана. При такой записи мы не увидим процесс заполнения ячеек. А вот если убрать эти команды, то мы сможем наблюдать за процессом заполнения этих ячеек.
For a = 1 To 100
For b = 1 To 100
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. Ниже приведён пример, в котором книга закрывает сама себя, при этом независимо от того внесли вы изменения или нет в книгу, при закрытии книги вам не поступит запрос "Сохранить изменения", а книга просто закроется без сохранения и уведомления пользователя.
Глобальное ускорение
Из всего выше сказанного можно сделать вывод, что для ускорения работы макроса можно воспользоваться нужными нам командами, теми которые подходят в нашем случае. Бывает, что код очень сложный и включает в себе различные математические и другие операции и использовать можно несколько команд сразу как показано на этом примере: