Содержание
Основные преимущества инструкции With :
Для того, чтобы убедиться в этом, нам надо сравнить два VBA кода.
Пример :
Sub TestNoWith()
ActiveWorkbook.Worksheets(1).Range("A1").Value = "S.O.S."
ActiveWorkbook.Worksheets(1).Range("A1").Font.Name = "Arial"
ActiveWorkbook.Worksheets(1).Range("A1").Font.Bold = True
ActiveWorkbook.Worksheets(1).Range("A1").Font.Strikethrough = True
ActiveWorkbook.Worksheets(1).Range("A1").Font.Size = 20
ActiveWorkbook.Worksheets(1).Range("A1").Font.ColorIndex = 3
В этом примере мы использовали свойство Value об’екта Range для ввода в ячейку определённого значения, а также изменили некоторые свойства об’екта Font , для изменения представления введённых в ячейку данных, т.е проще говоря применили форматирование
Для этого нам пришлось обращаться к об’ектам и их свойствам 23 раза, если считать по числу точек в инструкциях. При этом на обработку каждого обращения уходит некоторое количество времени.
Пример, c использованием инструкции With :
Sub TestYesWith()
With ActiveWorkbook.Worksheets(1).Range("A1")
.Value = "S.O.S."
.Font.Name = "Arial"
.Font.Bold = True
.Font.Strikethrough = True
.Font.Size = 20
.Font.ColorIndex = 3
End With
Во время выполнения этого VBA кода нам пришлось всего 13 раз использовать обращение к об’ектам и их свойствам. Так что мы можем быть уверены, что увеличили скорость выполнения этой программы. Последнее утверждение особенно актуально при работе с большим об’емом информации.
На самом деле этот код также можно сократить, всего до 9 обращений.
Пример использования инструкции With (сокращённый вариант) :
Sub TestYesWith2()
With .Font
.Name = "Arial"
.Bold = True
.Strikethrough = True
.Size = 20
.ColorIndex = 3
End With
Выполняет последовательность операторов, которые многократно ссылаются на единственный объект или структуру, чтобы операторы могли использовать упрощенный синтаксис доступ к членам объекта или структуры. Executes a series of statements that repeatedly refer to a single object or structure so that the statements can use a simplified syntax when accessing members of the object or structure. При использовании структуры можно только считывать значения членов или вызвать методы. При попытке присвоения значений членам структуры, используемым в операторе With. End With , возникает ошибка. When using a structure, you can only read the values of members or invoke methods, and you get an error if you try to assign values to members of a structure used in a With. End With statement.
Синтаксис Syntax
Части Parts
| Термин Term | Определение Definition |
|---|---|
| objectExpression | Обязательно. Required. Выражение, результатом которого является объект. An expression that evaluates to an object. Выражение может быть произвольно сложным и вычисляется только один раз. The expression may be arbitrarily complex and is evaluated only once. Результатом выражения могут быть данные любого типа, включая простейшие типы. The expression can evaluate to any data type, including elementary types. |
| statements | Необязательный элемент. Optional. Один или несколько операторов между With и End With , которые могут ссылаться на члены объекта, создаваемого при вычислении выражения objectExpression . One or more statements between With and End With that may refer to members of an object that’s produced by the evaluation of objectExpression . |
| End With | Обязательно. Required. Завершает определение блока With . Terminates the definition of the With block. |
Примечания Remarks
С помощью With. End With можно выполнять последовательность операторов с указанным объектом без необходимости многократного указания имени объекта. By using With. End With , you can perform a series of statements on a specified object without specifying the name of the object multiple times. В блоке операторов With члены объекта можно указывать начиная с точки, как если бы перед ней стоял объект оператора With . Within a With statement block, you can specify a member of the object starting with a period, as if the With statement object preceded it.
Например, чтобы изменить несколько свойств одного объекта, поместите операторы присваивания свойств внутрь блока With. End With и укажите объект лишь один раз, а не по одному разу для каждого свойства. For example, to change multiple properties on a single object, place the property assignment statements inside the With. End With block, referring to the object only once instead of once for each property assignment.
Если код обращается к одному и тому же объекту в нескольких операторах, оператор With обеспечивает следующие преимущества: If your code accesses the same object in multiple statements, you gain the following benefits by using the With statement:
Не требуется многократно вычислять сложное выражение или присваивать результат временной переменной, чтобы несколько раз сослаться на членов объекта. You don’t need to evaluate a complex expression multiple times or assign the result to a temporary variable to refer to its members multiple times.
За счет исключения повторяющихся определяющих выражений код становится более понятным. You make your code more readable by eliminating repetitive qualifying expressions.
Тип данных у objectExpression может быть любым типом класса или структуры или даже простейшим типом Visual Basic (например Integer ). The data type of objectExpression can be any class or structure type or even a Visual Basic elementary type such as Integer . При результат вычисления выражения objectExpression не является объектом, можно только считывать значения его членов или вызвать методы. При попытке присвоения значений членам структуры, используемым в операторе With. End With , возникает ошибка. If objectExpression results in anything other than an object, you can only read the values of its members or invoke methods, and you get an error if you try to assign values to members of a structure used in a With. End With statement. Это та же самая ошибка, которая возникает, если сразу после вызова метода, возвращающего структуру, попытаться обратиться к члену результата функции и присвоить ему значение, например GetAPoint().x = 1 . This is the same error you would get if you invoked a method that returned a structure and immediately accessed and assigned a value to a member of the function’s result, such as GetAPoint().x = 1 . Проблема в обоих случаях заключается в том, что структура существует только в стеке вызовов — в таких ситуациях не существует способа записи измененного члена структуры в некоторое расположение таким образом, чтобы весь остальной код программы мог видеть это изменение. The problem in both cases is that the structure exists only on the call stack, and there is no way a modified structure member in these situations can write to a location such that any other code in the program can observe the change.
Выражение objectExpression вычисляется один раз при входе в блок. The objectExpression is evaluated once, upon entry into the block. Переназначение выражения objectExpression изнутри блока With невозможно. You can’t reassign the objectExpression from within the With block.
Внутри блока With к методам и свойствам только указанного объекта можно обращаться без их полного описания. Within a With block, you can access the methods and properties of only the specified object without qualifying them. Можно использовать методы и свойства других объектов, однако необходимо полностью указывать их имена. You can use methods and properties of other objects, but you must qualify them with their object names.
Оператор With. End With можно разместить внутри другого такого оператора. You can place one With. End With statement within another. Вложенные операторы With. End With могут быть сложны для понимания, если указываемые объекты не очевидны из контекста. Nested With. End With statements may be confusing if the objects that are being referred to aren’t clear from context. Необходимо указывать полную ссылку на объект, находящийся во внешнем блоке With , при ссылке на него из внутреннего блока With . You must provide a fully qualified reference to an object that’s in an outer With block when the object is referenced from within an inner With block.
Переходы внутрь блока операторов With из другой части программы запрещены. You can’t branch into a With statement block from outside the block.
Если блок не содержит цикла, операторы выполняются только один раз. Unless the block contains a loop, the statements run only once. Возможно вложение структур управления различных типов. You can nest different kinds of control structures. Дополнительные сведения см. в разделе вложенные структуры управления. For more information, see Nested Control Structures.
Ключевое слово With можно также использовать в инициализаторах объектов. You can use the With keyword in object initializers also. Дополнительные сведения и примеры см. в разделе инициализаторы объектов: именованные и анонимные типы и анонимные типы. For more information and examples, see Object Initializers: Named and Anonymous Types and Anonymous Types.
Если блок With используется исключительно для инициализации свойств или полей только что созданного экземпляра объекта, рекомендуется использовать для этой цели инициализатор объекта. If you’re using a With block only to initialize the properties or fields of an object that you’ve just instantiated, consider using an object initializer instead.
Пример Example
В следующем примере в каждом блоке With выполняется несколько операторов для одного объекта. In the following example, each With block executes a series of statements on a single object.
Пример Example
В следующем примере показаны вложенные операторы With…End With : The following example nests With…End With statements. Во вложенном операторе With этот синтаксис относится к внутреннему объекту. Within the nested With statement, the syntax refers to the inner object.
Добрый день!
Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.
Предполагается, что вы знаете основы 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, который записывается при изменении высоты ячейки по содержимому (двойной клик на границе слева), на самом деле не работает.
Предлагаю знатокам поделиться своим опытом, дать советы в комментариях. Спасибо за внимание, удачных разработок вам.