Displayalerts excel что это
Обновлено: 02.07.2024
Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.
Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.
Visual Basic
Опции
Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:
Так же рекомендуется прописать:
В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что:
— VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант;
— иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).
Ещё одним важным оператором является ON ERROR. Привожу варианты:
Возможности языка
Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT'ах (аналог switch):
Ускорение работы макросов
Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.
Важно понимать, что 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, который записывается при изменении высоты ячейки по содержимому (двойной клик на границе слева), на самом деле не работает.
Предлагаю знатокам поделиться своим опытом, дать советы в комментариях. Спасибо за внимание, удачных разработок вам.
PrintOut предназначен для вывода документов на печать. Например, следующий код (листинг 9.14.) выводит на печать все документы Microsoft Word 2007 (то есть - с расширениями docx и docm ), расположенные в корневом каталоге диска C .
Листинг 9.14. Печать всех документов из корневого каталога диска CМетод PrintOut существует для различных объектов и может принимать множество параметров, управляющих всеми тонкостями печати. Например, с помощью такого кода (листинг 9.15.) мы можем распечатать первые пять страниц текущего документа:
Листинг 9.15. Вывод на печать первых 5 страниц текущего документа9.4.9. Quit - выход из приложения
Quit используется для выхода из приложения (листинг 9.16.):
9.4.10. Run - запуск макросов
Run позволяет запускать макросы и, при необходимости, передавать им параметры (до 30). При вызове методу Run передается имя, состоящее из имени проекта, имени модуля, и, собственно, имени макроса. Например, запуск макроса MyMacros , расположенного в модуле NewModule проекта Project1 выглядит так (листинг 9.17.):
Параметры, передаваемые макросу, перечисляются после его имени через запятую.
9.4.11. ScreenRefresh - принудительное обновление экрана
ScreenRefresh позволяет принудительно обновить экран. Используется обычно в комбинации с запретом автоматического обновления экрана (свойство Application.ScreenUpdating , его мы рассмотрим ниже). Как правило, обновление экрана делают через некоторые промежутки времени - тогда у пользователя не возникает ощущения, что программа зависла.
Теперь рассмотрим наиболее полезные свойства объекта Application .
9.5. Свойства объекта Application
9.5.1. ActiveDocument и другие - активный документ
ActiveDocument возвращает объект активного документа - того, который открыт в данный момент в Microsoft Word для редактирования. У объекта ActiveDocument есть множество полезных свойств и методов. Мы обсудим их при разговоре об объекте Document .
Надо отметить, что нельзя модифицировать свойство ActiveDocument - то есть не можем с его помощью сделать неактивный документ активным. Чтобы сделать документ активным, используют специальный метод объекта Document .
Существует немало других свойств Application , имена которых начинаются с Active .
ActivePrinter возвращает объект активного принтера - устройства, используемого для печати документов по умолчанию.
ActiveWindow возвращает активное окно - объект типа Window.
9.5.2. CapsLock - текущее состояние Caps Lock
CapsLock показывает текущее состояние режима Caps Lock . Если этот режим активен, по умолчанию при вводе с клавиатуры вводятся прописные буквы. Если режим Caps Lock включен - свойство возвращает True , если выключен - False .
9.5.3. Caption - заголовок окна MS Word
Caption позволяет узнать, и, при желании, изменить заголовок окна Microsoft Word.
Например код в листинге 9.19. меняет словосочетание Microsoft Word в заголовке окна на слова "Моя программа".
9.5.4. CustomizationContext - область сохранения настроек
CustomizationContext позволяет узнавать и задавать документ и шаблон, в котором сохраняются такие настройки, как изменения в настройке меню, панелей инструментов, горячих клавиш. Чтобы узнать текущую область настройки, достаточно выполнить код из листинга 9.20.
Чтобы установить в качестве области настройки шаблон Normal.dotm (то есть - сделать настройки доступными для всех документов), достаточно воспользоваться кодом листинга 9.21.:
Листинг 9.21. Будем сохранять изменения настроек в Normal.dotmОбратите внимание на то, что в предыдущих версиях MS Word этот шаблон назывался Normal.dot .
Для сохранения изменений в шаблоне, присоединенном к активному документу, можно воспользоваться командой из листинга 9.22.:
Листинг 9.22. Сохраняем изменения в присоединенном шаблонеДля сохранения изменений лишь в текущем документе, можно использовать команду из листинга 9.23.
Листинг 9.23. Сохраняем изменения в присоединенном шаблоне9.5.5. Dialogs - диалоговые окна MS Word
Dialogs возвращает коллекцию Dialogs (Диалоговые окна), которая дает доступ ко всем диалоговым окнам Microsoft Word. Если выполнить код листинга 9.24., можно узнать количество объектов в коллекции Dialogs.
Листинг 9.24. Количество диалоговых окон в коллекции DialogsЧтобы отобразить диалоговое окно поиска строк в документе, можно использовать код, представленный в листинге 9.25.
Листинг 9.25. Запуск диалогового окна поиска строк в документеЗдесь мы объявляем объектную переменную типа Dialog , создаем ссылку на окно поиска строк в документе - это окно представлено константой wdDialogEditFind , после чего присваиваем свойству Find окна поиска значение "Строка" - именно это значение будет отображаться в строке поиска, и показываем окно поиска, используя метод Show . Похожим образом работают и с другими диалоговыми окнами. Их имена (около 230) можно найти в перечислении WdWordDialog .
9.5.7. EnableCancelKey - запрещаем остановку программы
EnableCancelKey - позволяет разрешать и запрещать пользователю остановку программы по нажатию сочетания клавиш Ctrl + Break . Это может быть полезно при выполнении участков кода, которые нельзя прерывать.
Для того чтобы запретить прерывание работы программы нужно присвоить этому свойству значение wdCancelDisabled , для разрешения - wdCancelInterrupt .
Пример использования этого оператора вы можете найти в листинге 9.27.
Листинг 9.27. Запрет прерывания выполнения программы9.5.8. IsObjectValid - проверка объектных переменных
IsObjectValid - позволяет проверить объектную переменную . Если объект, на которую она ссылается, существует - проверка возвратит True , если нет - False . Это свойство полезно использовать для проверки объектов, которые могут быть удалены пользователем. Если объект существует, можно произвести с ним какие-либо действия. Если нет - сообщить причину, по которой действия невозможны.
9.5.9. KeyBindings - назначаем клавиатурные сокращения
KeyBindings - возвращает коллекцию KeyBindings , которая содержит информацию о клавиатурных привязках. Это очень полезная коллекция - с ее помощью можно, например, назначить клавиатурную комбинацию для запуска какого-нибудь макроса. листинг 9.28. позволяет назначить комбинацию клавиш Alt + Shift + T макросу TextEdit , хранящемуся в модуле MyMacros шаблона Normal.Dotm
Листинг 9.28. Программное назначение комбинации клавиш макросуСначала с помощью свойства CustomizationContext мы устанавливаем место, где будет сохранена привязка. Это - шаблон Normal.Dotm . Далее мы используем метод Add коллекции KeyBindings . Мы передаем этому методу три параметра. Первый ( wdKeyCategoryMacro ) указывает методу на то, что мы назначаем клавиатурную комбинацию макросу. Второй -" Normal.MyMacros.TextEdit " - указывает путь к макросу, запуск которого мы назначаем клавиатурному сокращению. Третий параметр содержит вызов метода BuildKeyCode - напомню, что он генерирует код сочетания клавиш на основе переданных ему параметров. В нашем случае он сгенерирует код для сочетания клавиш Alt + Shift + T - мы передали методу параметры wdKeyAlt , wdKeyShift и wdKeyT .
Если все сделано верно - в частности, макрос, запуск которого мы автоматизируем, существует - после выполнения такого кода нажатие выбранного сочетания клавиш в любом активном документе приведет к запуску этого макроса.
9.5.10. NumLock - состояние цифровой клавиатуры
NumLock возвращает состояние клавиши NumLock - True если цифровая клавиатура находится в режиме ввода цифр, иначе - False.
9.5.11. RecentFiles - недавно открытые файлы
RecentFiles - позволяет работать с файлами, которые вы недавно открывали. Список этих файлов можно увидеть в диалоговом окне открытия файлов. Последний файл, с которым вы работали, хранится в списке первым. Чтобы открыть его, можно воспользоваться кодом из листинга 9.29.
Листинг 9.29. Открываем последний из недавно открытых файлов9.5.12. ScreenUpdating - запрет обновления экрана
Свойство ScreenUpdating используют для отключения обновления экрана во время вывода в документ большого количества информации или других действий с документом. Это позволяет ускорить работу, так как системные ресурсы не тратятся на постоянное обновление экрана. В листинге 9.30. мы запрещаем обновление экрана, выводим в документ 10000 строк, принудительно обновляя экран после каждой 1000 строк, после чего разрешаем автоматическое обновление.
Очевидно, что присвоив False свойству ScreenUpdating мы запрещаем обновление экрана, а присвоив True - разрешаем.
Полное отключение кэша в браузрах
Всем привет. Я вот немного занимаюсь сайтостроительством, и достаточно давно столкнулся с.
Полное отключение Recycle Bin в Windows
Здравствуйте. (Я вернулся. Хд.) Облазил гугл и раздел здешнего форума, но не нашёл того, что.
Постоянное отключение WiFi и полное зависание компьютера
Добрый день. Проблема появилась около месяца назад. Постоянно (а точнее несколько раз в день).
Нужно полное отключение двойного щелчка мыши
Здравствуйте! Нужно отключить двойной клик (двойной щелчок) мыши как таковой во всей ОС. NB! речь.
У нашей компании есть специальный софт, который экспортирует в эксель Chart (с формой сигнала физического процесса) на отдельную страницу и данные для chartа на отдельную страницу. Далее на одной из страниц книги у нас подготовлен отчёт, в который надо добавить этот chart подогнав его по размерам к ячейки в которой ожидается наличие этого chartа. Как раз из-за подгонки размера происходит вышеуказанное предупреждение. Причём, почему то не каждый раз. Далее у нас таких файлов примерно 2000 шт.
Я сделали отдельный сценарий который перебирает все эти файлы, в которых уже экспортированы chartы, и мне надо было чтобы этот alert не выводился, иначе мой сценарий практически бесполезен.
Вообщем, проблема решилась добавлением такой строки:
за которой следуют операции по подгонки размера
есть одно но, несколько раз этот алерт всё равно вылез, но возможно этому послужила ещё какая то другая причина. В любом случае этот код сработал.
Отключение дисплея в играх и полное зависание компьютера.
Проблема: Во время игры всё зависло и отключился дисплей. Потом это стало повторятся чаще.
Полное закрытие Excel, а не текущего листа
Если я последней строкой макроса делаю ActiveWindow.Close ' или ActiveWorkbook.Closeто остается.
Сопоставить в Excel полное ФИО вместо сокращенного
День добрый. Есть файл экселя, в нём в колонке "А" сокращенное ФИО, а в колонке "C" полное ФИО.
Отключение Excel
Добрый день. Имеется следующая проблема. Есть файл, в котором работает макрос: Private Sub.
отключение меню сохранить в EXCEL
Я создаю Excel документ с выводом в него каких-то данных, но в целях безопасности мне надо сделать.
Я инженер-проектировщик ОВиК, не программист. И не хочу, да и некогда, вникать в серьезное программирование. Чаще всего появляется ситуация, что нужно как-то автоматизировать рутину здесь и сейчас. На помощь приходит простой язык VBA.
Далее я покажу, как можно без особых забот сделать самому то, за что серьезные ребята берут не плохие денежки. А именно перенос данных из Excel в AutoCAD и обратно. Заинтересованных прошу под кат.
Программировать будем на стороне Excel — мне так проще. Для подключения нужно войти в режим разработчика: Alt+F8 Либо можно открыть вкладку «разработчик» из настроек ленты.
В окне разработчика VBA входим в верхнее меню: Tools/References. В этом окне нужно поставить галочку на вашей версии AutoCAD
В моем случае это AutoCAD 2014 Type Library. Далее нужно в левом окне создать в вашей книге модуль, как на скриншоте (Module)
И в модуль вставляем нижеприведенный код:
Аналогичным способом можно создавать блоки с атрибутами, в которые можно вставлять текст из ячеек.
Нужно внести в верхний код изменения вроде:
Код обновления текста по хэндлу — написан ниже: 'получаем хэндл из ячейки, в которую мы записали кодом выше.
entHandle = ActiveCell.Offset(0, 3).Value 'получили наш блок по хэндлу
Set blockObj = acadDoc.HandleToObject(entHandle)
А дальше делаем всё то же самое, что и выше.
Для того, чтобы немного разъяснить как это работает вживую — записал видео:
Как видите, кода минимум, однако на больших объектах мне экономит по несколько часов работы. И снижается риск ошибки. Т.к. обычно это выглядит следующим образом у проектировщиков — открываются два окна на разных экранах, и или вручную, или через буфер обмена начинается заполнение выносок или блоков на чертеже.
Опять же чем хорош VBA — что он всегда под рукой :) Excel-то основной инструмент у инженера.
Читайте также: