Архив

Публикации с меткой ‘Что это? или основы Excel’

Основные понятия условного форматирования и как его создать

 

Условное форматирование — достаточно малоиспользуемый инструмент Excel. Но это как раз тот инструмент, при помощи которого можно изменить форматирование ячеек(цвет заливки, шрифт, границы) в зависимости от заданного условия, не прибегая к помощи Visual Basic for Applications.
Условное форматирование может значительно упростить выделение определенных ячеек или диапазона ячеек и визуализацию данных с помощью гистограммы, цветовых шкал и наборов значков. Оно изменяет внешний вид диапазона ячеек на основе указанного условия (или критерия). Если условие выполняется, то диапазон ячеек форматируется в соответствии с заданным для условия форматом; если условие не выполняется, то диапазон ячеек не форматируется.
Например, можно выделить ячейку с текущей датой; ячейку с числом, входящим в указанный диапазон; ячейка с определенным текстом и т.п.
Условное форматирование можно применить к диапазону ячеек, таблице или отчету сводной таблицы Excel.

Примечания:

  1. При создании условного форматирования можно ссылаться на другие ячейки только в пределах одного листа; нельзя ссылаться на ячейки других листов одной и той же книги или использовать внешние ссылки на другую книгу;
  2. При изменении цвета заливки ячеек, цвета шрифта, границ, форматирования текста при помощи условного форматирования — изменения, сделанные при помощи стандартного форматирования не будут отображаться в ячейках, форматы которых были изменены условным форматированием.

 
В статье рассмотрим:



 
ГДЕ РАСПОЛОЖЕНО УСЛОВНОЕ ФОРМАТИРОВАНИЕ И КАК СОЗДАТЬ
Для создания условного форматирования необходимо:

  1. Выделить ячейки для применения условного форматирования
  2. В меню выбрать
    • Excel 2003: Формат(Format)-Условное форматирование(Conditional formatting);
    • Excel 2007-2010: вкладка Главная(Home)-Условное форматирование(Conditional formatting)
  3. Выбрать одно из предустановленных правил (в Excel 2003 это значение(Cell Value Is)) или создать свое (в Excel 2003 это возможно посредством пункта формула(Formula Is));
  4. Выбрать способ форматирования ячеек: цвет заливки, цвет шрифта, формат отображения, границы и т.д.
  5. Подтвердить нажатием кнопки ОК

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

Условное форматирование в Excel 2003:
УФ 2003
Условное форматирование в Excel 2007-2010:
УФ 2010


 
ПРЕДУСТАНОВЛЕННЫЕ ПРАВИЛА
Для Excel 2003 предустановленные правила ограничиваются списком, имеющемся в пункте значение(Cell Value Is), который в более поздних версиях называется Правила выделения ячеек:
 

Правила выделения ячеек (Highlight Cells Rules)
Правила выделения ячеек
В Excel 2003 эти правила содержат условия:
Между, Вне, Равно, Не равно, Больше, Меньше, Больше или равно, Меньше или равно
between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to

В Excel 2007-2010: эти правила содержат условия:
Больше, Меньше, Между, Равно, Текст содержит, Дата, Повторяющиеся значения
Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, Duplicate Values
Как видно, по большей части названия пунктов говорят за себя названиями, и не нуждаются в подробных описаниях их функционала. Чуть более подробно можно рассмотреть лишь Дата и Повторяющиеся значения из набора правил версий Excel 2007 и новее.
Дата:
Дата
Список содержит несколько значений: Вчера, Сегодня, Завтра, За последние 7 дней, На прошлой неделе, На текущей неделе, На следующей неделе, В прошлом месяце, В этом месяце, В следующем месяце
Yesterday, Today, Tomorrow, In the last 7 days, Last week, This week, Next week, Last month, This month, Next month
Соответственно, при выборе необходимого условия даты в указанном диапазоне, соответствующие условию, будут отформатированы.

Повторяющиеся значения:
Повторяющиеся значения
Если в выпадающем списке выбрать повторяющиеся, то выделены будут все значения, которые встречаются в диапазоне более одного раза.
Если выбрать уникальные, то выделены будут все значения, которые встречаются только один раз.
 

Правила отбора первых и последних значений (Top/Bottom Rules)
Правила отбора первых и последних значений
Отсутствует в Excel 2003

Содержит условия:
Первые 10 элементов, Первые 10%, Последние 10 элементов, Последние 10%, Выше среднего, Ниже среднего
Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, Below Average
 

Гистограммы (Data Bars)
Гистограммы
Отсутствует в Excel 2003

Сплошная заливка(Solid fill) и Градиентная заливка(Gradient fill). Отличаются между собой визуализацией бара. Лично мне визуально больше нравится градиентная. Для чего их можно применять: например, в столбце последовательно записаны данные по продажам за месяц и необходимо наглядно отобразить их разницу между собой.

Что важно знать при применении данных условий. Они работают только при применении к диапазону ячеек с числовыми данными. 100%-му заполнению шкалы соответствует максимальное значение среди выделенных ячеек. Т.е. ячейка с максимальным значением будет заполнена полностью, а остальные ячейки будут заполнены относительно этой ячейки.
 

Цветовые шкалы (Color Scales)
Цветовые шкалы
Отсутствует в Excel 2003

Работает по тому же принципу, что и Гистограммы(Data Bars): работает на основе числовых значений выделенных ячеек, но закрашивает не часть ячейки методом шкалы, а всю ячейку, но с различной интенсивностью или цветом. Можно создать условие, при котором ячейка с максимальной продажей будет закрашена самым насыщенным цветом, а минимальная — практически незаметно:
Цветовые шкалы
или добавить к этому еще различие по цветам:

 

Наборы значков (Icon Sets)
Наборы значков
Отсутствует в Excel 2003

Служит все для тех же целей, что и шкалы и гистограммы, но имеет менее гибкую систему отображения различий. Отражает различия между значениями ячеек по 2-х, 3-х, 4-х или 5-ти ступенчатой системе. Это значит, что если выбран набор из 3-х значков, то разница между минимальным и максимальным значением будет поделена на 3 и каждая третья часть будет со своим значком. Более наглядно можно увидеть, применив данное условие к числам от 1 до 9:

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



 
ИСПОЛЬЗОВАНИЕ ФОРМУЛЫ ДЛЯ ОПРЕДЕЛЕНИЯ ЯЧЕЕК ДЛЯ ФОРМАТИРОВАНИЯ
Формулы в качестве условий для условного форматирования как правило применяются в случаях, когда форматирование должно быть несколько сложнее, чем предусмотренные предустановленными условиями. Позволяет выделять различия между ячейками, минимальные и максимальные, да и вообще любые, которые можно выразить при помощи встроенных функций листа. А их не одна сотня, плюс всевозможные комбинации. Особенно ценно данное условие для Excel 2003 и ранних версий, т.к. предустановленных правил там почти нет. И формулы могут этот недостаток исправить.
Для создания подобного условия необходимо:

  1. Выделить ячейки для применения условного форматирования
  2. В меню выбрать
    • Excel 2003: Формат(Format)-Условное форматирование(Conditional formatting)- формула;
    • Excel 2007-2010: вкладка Главная(Home)-Условное форматирование(Conditional formatting)-Создать правило(New rule)-Использовать формулу для определения форматируемых ячеек(Use a formula to determine which cells to format)
  3. Вписать в поле необходимую формулу (Сборник формул для условного форматирования)
  4. Выбрать способ форматирования ячеек: цвет заливки, цвет шрифта, формат отображения, границы и т.д.
  5. ОК

Если необходимо выделять форматированием не только конкретную ячейку, удовлетворяющую условию, а всю строку таблицы на основе ячейки одного столбца, то в пункте 1 выделяем не столбец, а всю таблицу, а ссылку на столбец с критерием закрепляем:

=$A1=МАКС($A$1:$A$20)

при выделенном диапазоне A1:F20(диапазон применения условного форматирования), будет выделена строка A7:F7, если в ячейке A7 будет максимальное число.



 
ПОИСК ЯЧЕЕК С УСЛОВНЫМ ФОРМАТИРОВАНИЕМ
Если к одной или нескольким ячейкам на листе применено условное форматирование, можно быстро найти их для копирования, изменения или удаления условного формата.

Поиск всех ячеек с условным форматированием

  1. Выделить любую ячейку на листе;
  2. Нажать F5- Выделить(Special); или же перейти на вкладку Главная(Home)- группа Редактирование(Editing)- Найти и выделить(Find & Select)- Выделение группы ячеек(Go To Special);
  3. В появившемся окне выбрать Условные форматы(Conditional formats);
  4. Нажать ОК.

Поиск ячеек с одинаковым условным форматированием

  1. Выделить ячейку с необходимым условным форматированием;
  2. Нажать F5- Выделить(Special); или же перейти на вкладку Главная(Home)- группа Редактирование(Editing)- Найти и выделить(Find & Select)- Выделение группы ячеек(Go To Special);
  3. В появившемся окне выбрать Условные форматы(Conditional formats);
  4. Выбрать пункт этих же(Same) в группе Проверка данных(Data validation);
  5. Нажать ОК.


 
РЕДАКТИРОВАНИЕ УСЛОВИЙ УСЛОВНОГО ФОРМАТИРОВАНИЯ
Excel 2003:

  1. Выделить диапазон ячеек, из которых требуется удалить условное форматирование;
  2. Формат(Format)-Условное форматирование(Conditional formatting);
  3. Изменить условие и нажать ОК.

Excel 2007-2010:

  1. Выделить диапазон ячеек, таблицу или сводную таблицу, условное форматирование которых требуется изменить;
  2. Вкладка Главная(Home)- группа Стили- Условное форматирование(Conditional formatting)- Управление правилами(Manage Rules);
  3. Выбрать необходимое правило, условное форматирование которого необходимо изменить
  4. Нажать кнопку Изменить правило(Edit Rule)


 
УДАЛЕНИЕ УСЛОВНОГО ФОРМАТИРОВАНИЯ

Удаление условного форматирования со всего листа

Вкладка Главная(Home)(Home)- группа Стили(Styles)- Условное форматирование(Conditional formatting)- Удалить правила(Clear Rules)- Удалить правила со всего листа(Clear Rules from Entire Sheet).

Удаление условного форматирования из диапазона ячеек, таблицы или сводной таблицы
Excel 2003:

  1. Выделить диапазон ячеек, из которых требуется удалить условное форматирование;
  2. Формат(Format)-Условное форматирование(Conditional formatting)- кнопка Удалить(Delete);
  3. Отметить галочками условное форматирование, которое необходимо удалить и нажать ОК.

Excel 2007-2010:

  1. Выделить диапазон ячеек, таблицу или сводную таблицу, из которых требуется удалить условное форматирование;
  2. Вкладка Главная(Home)- группа Стили- Условное форматирование(Conditional formatting)- Удалить правила(Clear Rules);
  3. Выбрать элемент, условное форматирование из которого необходимо удалить: Удалить правила из выделенных ячеек(Clear Rules from Selected Cells), Удалить правила из этой таблицы(Clear Rules from This Table) или Удалить правила из этой сводной таблицы(Clear Rules from This PivotTable).

Так же для Excel 2007-2010 можно удалить только определенное правило из указанных ячеек:

  1. Выделить диапазон ячеек, таблицу или сводную таблицу, условное форматирование которых требуется изменить;
  2. Вкладка Главная(Home)(Home)- группа Стили(Styles)- Условное форматирование(Conditional formatting)- Управление правилами(Manage Rules);
  3. Выбрать необходимое правило, условное форматирование которого необходимо изменить
  4. Нажать кнопку Удалить правило(Delete Rule)

Так же см.:
Сборник формул для условного форматирования
Заменить условия УФ реальными
Удаление УФ из листа/книги
Замена Условного форматирования обычным

Что такое гиперссылка?

 

Гиперссылка — это не просто отображение адреса на ресурс — это активная ссылка, кликнув на которую у Вас либо откроется веб-адрес в браузере по умолчанию, либо будет открыт файл, на который ведет ссылка, либо будет открыта директория(папка), к которой ведет ссылка.
Установить можно двумя способами.
Первый и самый распространенный: правый клик мыши по ячейке-Гиперссылка. Откроется окно добавления гиперссылки.

Там Вы сможете выбрать вид ссылки:

  • на файл или веб-страницу. Если на веб-страницу, то необходимо в поле «Адрес» указать адрес веб-страницы. Если ссылка на файл — просто выбрать файл в диспетчере файлов.
  • местом в документе. Выбираете необходимый лист и адрес ячейки, на которую хотите переместиться по нажатии гиперссылки.
  • новым документом. Создается новый документ и сразу ссылка на него. При нажатии гиперссылки будет открыт этот файл.
  • электронной почтой. Указываете адрес электронной почты и тему письма по умолчанию. По нажатии ссылки будет создано письмо на указанный адрес электронной почты и с указанной темой.

Создание кодом гиперссылки выглядит следующим образом:

ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), _
                               Address:="http://www.excel-vba.ru/", _
                               TextToDisplay:="http://www.excel-vba.ru/"
ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), _
                               Address:="http://www.excel-vba.ru/", _
                               TextToDisplay:="http://www.excel-vba.ru/"

Range("A1") — это ячейка, в которой будет создана гиперссылка.
Address(http://www.excel-vba.ru/) — адрес страницы(или путь к файлу или директории), который будет открыт по клике на ячейку.
TextToDisplay(http://www.excel-vba.ru/) — это текст, который будет отображаться в ячейке. Может отличаться от адреса самой ссылки.

Второй способ: через функцию ГИПЕРССЫЛКА. Этот способ хорош, если Вам надо создать много гиперссылок на листе:

=ГИПЕРССЫЛКА("www.excel-vba.ru";"переход на сайт")

www.excel-vba.ru — адрес страницы(или путь к файлу или директории), который будет открыт по клике на ячейку.
переход на сайт — текст, который будет отображаться в ячейке. Может отличаться от текста самой ссылки.
Сама функция расположена в категории Ссылки и массивы диспетчера функций.
При применении такого способа создания гиперссылки Вы можете далее указать помимо основного адреса еще и указание на конкретную страницу через ссылку на ячейку:

=ГИПЕРССЫЛКА("http://www.excel-vba.ru/"&B1;"переход на сайт")

в ячейке B1 — chto-umeet-excel/. Теперь при клике на ячейку в браузере по умолчанию откроется страница http://www.excel-vba.ru/chto-umeet-excel/
Нетрудно догадаться, что таких ссылок можно создать множество простым копированием формулы в другие ячейки и что немаловажно — в столбце В можно записывать разные окончания ссылок. Если вдруг сайт поменял адрес, но система ссылок осталась прежней — Вам надо будет лишь заменить основной адрес и все. То же самое можно отнести и к файлам на диске: если поменялась лишь корневая директория — то просто меняем её в формуле.

Так же можно создать гиперссылку, переходящую на определенную ячейку на листе:

=ГИПЕРССЫЛКА("#Лист2!D4";"Перейти в ячейку D4 Лист2")

Ссылку на ячейку можно указывать динамически, ссылаясь на определенную ячейку, не вписывая её адрес(на примере D2):

=ГИПЕРССЫЛКА("#Лист2!"&ЯЧЕЙКА("адрес";D4);"Перейти в ячейку "&ЯЧЕЙКА("адрес";D4)&" Лист2")

Можно указать ссылку на ячейку листа в другой книге:

=ГИПЕРССЫЛКА("#'[Другая книга.xls]Лист2'!"&ЯЧЕЙКА("адрес";D4);"Перейти в ячейку "&ЯЧЕЙКА("адрес";D4)&" Лист2 Другая книга.xls")

Важно: Книга, на которую ссылается функция ГИПЕРРСЫЛКА должна быть открыта. Если в имени книги или листа содержатся пробелы, то обязательно в начале имени книги (перед [ ) и после имени листа ставить апостроф('). Я специально привел пример в последней функции именно с пробелом - для наглядности.

Так же см.:
Как массово изменить гиперссылки?
Как получить адрес гиперссылки из ячейки
Как сделать гиперссылку на процедуру?

Что такое модуль? Какие бывают модули?

 

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

Модуль листа(Лист1 или Sheet1) - на рис.2: Лист1(Лист1),Лист2(Лист2),Лист3(Лист3). Для каждого листа книги имеется свой отдельный модуль. Попасть в модуль листа проще, чем в остальные модули. Для этого надо просто щелкнуть правой кнопкой мыши по ярлычку листа и выбрать из контекстного меню пункт Исходный текст(View Code)(рис.1).

Перейти в модуль листарис.1

Можно и более трудным путем пойти — через редактор VBA: Alt+F11 и в окне Проводника объектов дважды щелкнуть по объекту с именем листа(рис.2).

Объекты проектарис.2
Если данное окно у Вас не отражается нужно нажать Ctrl+R либо в меню редактора VBA-View-Project Explorer(рис.3)
Проводник объектоврис.3

В модуле листа содержатся встроенные событийные процедуры, каждая из которых отвечает за обработку определенного события на этом листе. Названия данных процедур носят достаточно информативные имена и не думаю, что нуждаются в расшифровке(Change — изменения значений ячеек на листе, SelectionChange — изменение адреса выделенной ячейки/области и т.д.). Посмотреть их можно так: выбираете в списке объектов(на рис.4 помечен 1) Worksheet, а в правом окне выбора процедур(на рис.4 помечен 2) Вы найдете все процедуры, доступные для выбранного листа. Процедуры, события для которых уже используются, выделены жирным шрифтом.

Окно выбора процедур и объектоврис.4


Модуль книги(ЭтаКнига или ThisWorkbook) — на рис.2: ЭтаКнига. В модуль книги можно попасть только через редактор VBA описанным выше способом. Двойной щелчок по ЭтаКнига(ThisWorkbook). В модуле книги так же содержатся «встроенные» событийные процедуры. Так же как и для листа выбираете в списке объектов, только не Worksheet, а Workbook. В правом окне выбора процедур Вы найдете все процедуры, доступные для объекта ЭтаКнига.

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

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "Адрес измененной ячейки: " & Target.Address, vbInformation, "Excel-VBA"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "Адрес измененной ячейки: " & Target.Address, vbInformation, "Excel-VBA"
End Sub

Примечание: для всех кодов, приведенных на сайте, достаточно просто открыть необходимый модуль(книги или листа) и вставить предложенный код. Корректировка может понадобиться только в случаях, когда в модуле Листа или Книги Вашего файла уже имеется код в необходимой событийной процедуре.


Стандартный модуль(Module) - на рис.2 Module1.Для стандартных модулей нет предопределенных событийных процедур, поэтому в них процедуры пишутся полностью вручную(макрорекордер записывает макросы также именно в эти модули). В основном именно в стандартных модулях содержится большая часть кодов. Они предназначены для хранения основных процедур и Public переменных, которые могут быть доступны впоследствии из любого модуля. Как создать стандартный модуль: в окне проводника объектов щелкаем правой кнопкой мыши-Insert-Module. При записи макрорекордером модули создаются автоматически.

Модули форм(UserForm) — на рис.2 UserForm1. Содержаться внутри Пользовательской формы(UserForm) и её объектов. В Пользовательских формах в основном все завязано именно на событийных процедурах самой формы и на дочерних объектах этой формы(Кнопки, ТекстБоксы, КомбоБоксы и т.д.). Очень удобно использовать Пользовательские формы в своих приложения для, так сказать, общения с пользователем. Т.к. через формы очень удобно отслеживать действия пользователя и можно запретить доступ к листам с данными, путем их скрытия. Создается форма так же как и модуль: в окне проводника объектов щелкаем правой кнопкой мыши-Insert-UserForm.

Модуль класса(ClassModule) — на рис.2 Class1. В большинстве случаев создается специально для отслеживания событий различных объектов. Вряд ли понадобиться начинающим изучение VBA, хотя все зависит от поставленной задачи. В любом случае, перед работой с модулями классов лучше научиться хоть чуть-чуть работать с обычными модулями и самостоятельно писать продедуры. Создается: в окне проводника объектов щелкаем правой кнопкой мыши-Insert-Class Module. Подробнее про модули классов можно почитать в этой статье: Работа с модулями классов

Для того, чтобы создать новый модуль(Module), модуль класса(ClassModule) или пользовательскую форму(UserForm) надо просто в окне Проводника объектов(Project Explorer) щелкнуть правой кнопкой мыши, выбрать пункт Insert и затем тип добавляемого объекта(ModuleClassModuleUserForm).
 

Также см.:
Копирование модулей и форм из одной книги в другую

 

Что такое макрос и где его искать?

 

Наверное, многие слышали это слово «макрос«, но не все имеют точное представление что это, если заглянули на эту страничку.
Макрос — это макрокоманда(так звучит в правильном переводе с англ.языка), содержащая последовательность действий, записанных пользователем. Запись производится с помощью встроенного в пакет Microsoft Office языка программирования — Visual Basic for Application(VBA).

Зачем же нужны макросы? А нужны они для того, чтобы избавить Вас от рутинного выполнения одних и тех же действий. Например, Вам каждый день приходиться копировать данные, расположенные в одном и том же месте в другое место по несколько раз. Скучно и утомительно. Вы просто можете записать один раз все эти действия макрорекордером, а в дальнейшем только вызывать записанный макрос и он все сделает за Вас. Что немаловажно, для использования макрорекордера и записи макроса не надо обладать никакими навыками программирования. Надо просто уметь нажать кнопку. А какую именно я сейчас и расскажу.
Для пользователей Excel 2003 запись макроса доступна из: Сервис-Макрос-Начать запись(рис.1)

Запись макроса в Excel 2003рис.1

Для пользователей Excel 2007 можно вынести на ленту панель «Разработчик» — Меню- Параметры Excel- Основные- Показывать вкладку «Разработчик» на ленте(рис.2) — и оттуда вызывать запись макроса, а можно записывать и непосредственно из строки состояния. Для Excel 2010 панель «Разработчик» выносится несколько иначе: ФайлПараметрыНастройка ленты — ставим галочку напротив Разработчик

Вкладка Разработчикрис.2

Для этого нужно убедиться в том, что в настройках Строки состояния стоит галочка напротив «Запись макроса«(рис.3). Если данная галочка включена, то в строке состояния будет значек, отображающий текущее состояние записи(Нет записи — рис.4, Идет запись — рис.5).

Строка состояниярис.3

Нет записирис.4

Идет записьрис.5

Перед записью Вы можете дать имя записываемому макросу и назначить сочетание клавиш, при нажатии которых этот макрос будет запускаться. Вы нажали кнопку записи и у Вас появилось окно, как на рис.6.
Диспетчер записи макросарис.6

Имя макроса — не должно содержать пробелов, запятых, двоеточий и т.п. Допустимы символы кириллицы, латиницы, цифры и нижнее подчеркивание(я предпочитаю давать макросам имена, содержащие только буквы латиницы). Имя макроса всегда должно начинаться с буквы. Лучше давать макросу сразу понятное имя, отражающее примерную суть того, что он делает.
Сочетание клавиш — можно назначить вызов макроса сочетанием клавиш. Доступно назначение любой цифры или буквы в сочетании с Ctrl.
Сохранить в — выбираете место хранения записываемого макроса. Доступны варианты:

  • Эта книга — макрос будет записан в той же книге, из которой была запущена запись. Записанный макрос будет доступен из приложения только если книга открыта.
  • Новая книга — будет создана новая книга, в которой записан макроса. Записанный макрос будет доступен из приложения только если книга открыта.
  • Личная книга макросов — самый интересный вариант. Если его выбрать, то макрос будет сохранен в отдельной книге PERSONAL.XLS. Макросы, записанные в эту книгу доступны из всех открытых книг Excel и эта книга подключается автоматически при запуске самого Excel. Т.е. однажды записав где-то макрос в эту книгу — он теперь будет доступен независимо от того, открыта ли книга, из которой был записан макрос или нет. Изначально эта книга отсутствует и создается в момент первого обращения к ней(т.е. как только Вы первый раз выбрали Сохранить в личную книгу макросов).

После записи макроса Вы сможете запустить его из любой версии Excel, нажав сочетание клавиш Alt+F8 и выбрав из списка(рис.7).

Вызов макросарис.7
Также это окно можно вызвать и через панель:

в Excel 2007-2010вкладка «Разработчик»-Макросы;
в Excel 2003Сервис-Макрос-Макросы.

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

Примечание: необходимо помнить, что макрос записывает АБСОЛЮТНО ВСЕ Ваши действия — ошибки, прокручивание экрана, переключение между листами, выделение ячеек и т.д. Так что перед записью своих действий советую сначала тщательно продумать все свои действия, чтоб в дальнейшем записать только то, что нужно записать. Это сократит как сам код, так и время его выполнения.

Также см.:
?Почему не работает макрос?
?Как создать кнопку для вызова макроса на листе?

Почему столбцы стали цифрами или как сменить стиль ссылок

 

Вы открыли файл, а в нем заголовки столбцов вдруг стали не буквами, а….Цифрами! Но и это не все беды…Теперь еще и ссылки на ячейки в формулах совершенно непонятного вида — R[34]C[-1](см.рис.2) и т.п. Почему это случилось и что делать? Как вернуть прежний вид?

рис.1

рис.2

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

Для Excel 2003:
Сервис-Параметры-вкладка Общие-Стиль ссылок R1C1. Снять галку.

Для Excel 2007:
Меню-Параметры Excel-вкладка Формулы-Стиль ссылок R1C1. Снять галку.

Для Excel 2010:
Файл-Параметры-вкладка Формулы-Стиль ссылок R1C1. Снять галку.

Вот и все. Теперь все как и прежде.

Если Вам часто приходится менять стиль ссылок, то это можно быстро делать при помощи макроса:

Private Sub Change_ReferenceStyle()
    If Application.ReferenceStyle = xlA1 Then
        Application.ReferenceStyle = xlR1C1
    Else
        Application.ReferenceStyle = xlA1
    End If
End Sub
Private Sub Change_ReferenceStyle()
    If Application.ReferenceStyle = xlA1 Then
        Application.ReferenceStyle = xlR1C1
    Else
        Application.ReferenceStyle = xlA1
    End If
End Sub

Даннй код нужно поместить в стандартный модуль. Если хотите пользоваться кодом в любой книге, то расположить его необходимо в модуле личной книги макросов — PERSONAL.XLS. А потом уже можете назначить макросу сочетания клавиш для более удобного вызова.

Если пройти по ссылкам ниже то можно скачать надстройку, которая добавляет на панель одну кнопку. По нажатии кнопки меняется стиль ссылок.

Так же см.:
Сменить стиль отображения ссылок
Сменить стиль ссылок

Variable not defined или что такое Option Explicit и зачем оно нужно?

 

Option Explicit - начинающие программировать в Visual Basic могут увидеть данную строку в чужом коде, либо случайно в своем. Хотя кто-то может быть уже знает, что это и зачем и использует данное объявление намеренно. Я же постараюсь максимально подробно описать смысл этой строки и её полезность для кода в первую очередь для тех, кто еще не знает для чего она.

Строка данная записывается в самом начале модуля, самой первой строкой. Перед этой строкой ничего более не может быть записано, кроме, разьве что других подобных строк(есть еще другие :-))

Собственно что же делает эта строка? А делает она следующее: она принуждает Вас объявлять переменные(если не знаете смысл объявления переменных — читайте здесь). Если какая-либо переменная внутри выполняемой процедуры не объявлена — Вы увидите такое вот сообщение:

рис.1

так же редактор VBA выделит ту переменую, которая не объявлена. Первое время это может раздражать. Да и вообще: зачем это? Вы и без всех этих объявлений неплохо жили. А вот зачем. Во-первых: объявление переменных считается хорошим тоном при программировании; во-вторых: правильное присвоение типов недурно экономит память; ну и в-третьих: это помогает избежать неявных ошибок кода при несовпадении типов данных.

Но на этом полезность данной строки не заканчивается. Ниже приведен листинг кода:

Sub Check_Variables()
    Dim a As String
    a = "Привет от www.excel-vba.ru"
    MsgBox а, vbInformation
End Sub
Sub Check_Variables()
    Dim a As String
    a = "Привет от www.excel-vba.ru"
    MsgBox а, vbInformation
End Sub

Выполните данный код без строки Option Explicit. Какое значение выдаст MsgBox? Ничего. Что за странность? Ведь явно видно, что переменной присвоено значение текста. Ничего больше не происходит. Но переменная все равно пуста. Мистика…А теперь запишите первой строкой в модуле Option Explicit:

Option Explicit
Sub Check_Variables()
    Dim a As String
    a = "Привет от www.excel-vba.ru"
    MsgBox а, vbInformation
End Sub
Option Explicit
Sub Check_Variables()
    Dim a As String
    a = "Привет от www.excel-vba.ru"
    MsgBox а, vbInformation
End Sub

Запустите код. И что же видите? Видите сообщение, показанное на рис.1 и выделенную переменную «а», в последней строке. Что это означает? Это означает, что переменная «а» у нас не объявлена. А все потому, что первой строкой (Dim a As String) я объявил переменную на английском языке, а в последней строке я записал её на русском. А для кода это разные символы. Если разглядеть логику работы VBA — первую «а» он видит как переменную с присвоенным типом String. И ей мы задаем значение. А вторую…Вторую он не находит в объявленных переменных, не находит в функциях и сам инициализирует её как новую переменную с типом данных Variant. И, что вполне логично, со значением Empty, т.е. ничего, т.к. этой переменной мы никаких значений не присваивали.

А теперь представьте себе, что Вы написали кучу длинного кода, строк на 100 или более. Конечно, Option Explicit Вы не используете. И вот Вы тестируете код, но он работает как-то не так…Где-то что-то неверно выполняется. И Вы начинаете пошагово ковыряться в листинге и искать ошибку…А ведь все может быть и проще: где-то в коде Вы могли банально опечататься и присвоить таким образом значение переменной, на которую Вы и не рассчитывали. А если использовать Option Explicit, то такая опечатка будет сразу обнаружена еще до выполнения кода и, что немаловажно — подсвечена. Так что Вам даже не придется её искать, а останется лишь исправить ошибку.

Так что думаю, не стоит недооценивать значимость строки Option Explicit при написании кодов. В довершение хотелось бы Вас обрадовать, что вписывание данной строки в начало каждого модуля можно сделать автоматическим: поставить в опциях редактора галочку: Tools-Options-вкладка Editor-Require Variable Declaration. Теперь во всех новых созданных модулях строка Option Explicit будет создаваться самим редактором VBA автоматически. К сожалению, в уже имеющихся модулях Вам придется проставить данную строку самим вручную. Но это того стоит, поверьте.

Также см.:
?Что такое переменная и как правильно её объявить?

Как Excel воспринимает данные?

 

На первый взгляд все просто: есть числа, есть текст, есть дата и время. Но на самом деле для Excel этот список чуть меньше. Для Excel существует либо текст, либо число. Напрашивается вопрос: Как так? Ведь мы же видим в ячейках и даты и время. Да, видим. Но видим мы время именно временем только потому, что сам Excel нам так показывает данные в ячейке. На самом деле в ячейке числовое значение.

Вот например: занесите в ячейку число 43587, а затем присвойте этой ячейке формат — Дата ДД.ММ.ГГГГ«(кто не знает как это сделать: щелкаем правой кнопкой мыши по ячейке-Формат ячеек-вкладка Число; либо просто выделяем нужную ячейку — Ctrl+1). И что мы видим? Дату — 02.05.2019. Т.е. 43587 в переводе на дату равно 02.05.2019. Исходя из этого можно догадаться, что 43587 — это количество дней. В общем 1 — это одни целые сутки. Но ведь с  01.01.0001(от рождества Христова) прошло гораздо больше дней, чем 43587 — свыше 733000. Правильно. Цифра 1 для Excel равна дате 01.01.1900 года, а не как не первого января первого года. 01.01.1900 — это начальная точка отсчета времени для Excel. Однако здесь тоже не без сюрпризов. Для компьютеров под управлением Macintosh в Excel отсчет начинается с 01.01.1904. Для корректной совместимости с такими компьютерами в Excel предусмотрена возможность включения исчисления дат 1904. Для Excel 2003: Сервис-Параметры-Вычисления-Использовать систему дат 1904; для Excel 2007: Меню-Параметры Excel-Дополнительно-Использовать систему дат 1904.

То же самое и со временем, но с одной небольшой разницей. Т.к. целые числа для Excel это даты, то они уже не могут быть временем. Следовательно временем является дробная часть чисел. Например 0,5 будет равно 12:00, а 0,124 — 2:58:34.

Следовательно, если записать в ячейку 43587,124, то в перевод на дату/время это будет - 02.05.2019 2:58:34.

Именно то, что Excel хранит данные таким образом, позволяет нам производить различные математические операции с датой и временем(складывать, вычитать, умножать и т.д.).

С текстом никаких интересных особенностей — текст он и есть текст.

Правда есть и нюансы: когда Вы будете записывать в ячейку число, например -2.1, то Excel непременно преобразует его в дату — 02.янв. Чтобы Excel так над Вами не издевался, самый простой способ — это перед внесением данных установить формат ячейки — Текстовый.  Или ставить перед такими числами апостроф — ’2.1.

Надеюсь эта информация была для Вас полезной…

Что такое переменная и как правильно её объявить?

 

Переменная — это некий контейнер, в котором VBA хранит данные. Если подробнее, то это как коробочка, в которую Вы можете положить что-то на хранение, а затем по мере необходимости достать. Только в данном случае в переменной мы храним число, строку или иные данные, которые затем можем извлекать из неё и использовать в коде по мере необходимости.

Требования к переменным:

В качестве имен переменных можно использовать символы букв и числа, но первой в имени переменной всегда должна быть буква. Не допускается использование точки, запятой, пробела и иных знаков препинания, кроме нижнего подчеркивания. Длина имени не должна превышать 254 символов. Так же нельзя использовать в качестве имен для переменных зарезервированные константы редактора VBA(например Sub, Msgbox, ubound, Date и т.п.). Так же для переменных неважен регистр букв.

Теперь рассмотрим типы данных, которые можно хранить в переменных:

Тип данных Занимает байт в памяти Пределы значений
Byte 1 Целые числа от 0 до 255
Boolean 2 True или False
Integer 2 Целые числа от (-32768) до 32768
Long 4 Целые числа от (-2147483648) до 2147483647
Single 4 От (–3.402823Е+38) до (–1.401298Е-45) и от 1.401298Е-45 до 3.402823Е+38
Double 8 От ±1.79769313486232Е+308 до ±4.94065645841247Е-324
Decimal 12 От ±79228162514264337593543950335 без десятичных знаков до ±7,9228162514264337593543950335 с 28-ю знаками после запятой
Currency 8 От (–922337203685477.5808) до 922337203685477.5807
Date 8 От 01.01.100 до 31.12.9999(не надо путать с датами в Excel — 01.01.1900 до 31.12.2078)
String 1 От 0 до 65400 символов для фиксированных строк и чуть более 2 млрд. для строк переменной длины
Object 4 Любой объект
Array Определяется кол-вом и размером элементов -
Variant от 16-ти Любой из встроенных типов данных

Как видно из таблицы больше всего памяти занимает Variant. Притом это если хранит числовые данные. Если же такая переменная будет хранить данные строкового типа(текст), то размер занимаемой памяти будет измеряться уже начиная с 22 байт + длина строки, хранящейся в переменной. Чем больше памяти занимает переменная, тем дольше она инициализируется в памяти и тем медленне код будет выполняться. Вот поэтому и Важно явно задавать тип данных, хранимых в переменной — это называется объявить переменную.

Тип данных Decimal больше не используется, поэтому объявить переменную данного типа в VBA не получится — подобная попытка приведет к синтаксической ошибке. Для работы с данными типа Decimal переменную необходимо изначально объявить как Variant или вообще без типа (например Dim a), т.к. тип данных Variant используется в VBA по умолчанию и принимает любой тип данных.

Так как же объявлять переменные? На самом деле все очень просто. Это делается при помощи операторов области действия: Dim, Public,Static и оператора присвоения типа As. Самый распространенный оператор — Dim. Его и возьмем в качестве примера. Синтаксис объявления:

[оператор области действия] Имя_переменной As [тип данных]
[оператор области действия] Имя_переменной As [тип данных]

Очень частая ошибка при объявлении переменных, совершаемая начинающими изучать VBA:

Dim MyVar1, MyVar2, MyVar3 As Integer
Dim MyVar1, MyVar2, MyVar3 As Integer

Вроде бы исходя из логики всем переменным присвоен тип данных Integer. Но это ошибочное суждение. Тип Integer присвоен только последней переменной, к которой он «привязан» оператором AsMyVar3. Все остальные переменные имеют тип данных Variant. Т.е. если Вы не задаете переменной тип хранимых данных явно(т.е. не указываете для неё тип данных), то VBA сам присваивает такой переменной тип данных Variant, т.к. он может хранить любой тип данных. А вот так выглядит правильное присвоение типа данных:

Dim MyVar1 As Integer, MyVar2 As Integer, MyVar3 As Integer
Dim MyVar1 As Integer, MyVar2 As Integer, MyVar3 As Integer

Думаю, смысл ясен. Это и есть объявление переменных. Т.е. сначала идет оператор области действия (Dim, Public,Static), сразу за ним имя переменной, затем опертаор As и тип. Теперь разберемся с Dim, Public и Static.
Но это не все. Некоторые типы переменным можно присваивать еще короче — даже без оператора As:

Dim MyVar1%, MyVar2%, MyVar3%
Dim MyVar1%, MyVar2%, MyVar3%

Всего шесть типов, которые можно объявить подобным методом:
! — Single
# — Double
$ — String
% — Integer
& — Long
@ — Currency
На что стоит обратить внимание, при объявлении переменных подобным образом: между имененм переменной и знаком типа не должно быть пробелов.
Я лично предпочитаю использовать первый метод, т.е. полное указание типа. Это читабельнее и понятнее. Но это мои предпочтения и в своих кодах Вы вправе использовать удобный Вам метод — ошибки не будет.

Теперь разберемся с операторами области действия:

Dim — данный оператор используется для объявления переменной, значение которой будет храниться только в той процедуре, внутри которой данная переменная объявлена. Во время запуска процедуры такая переменная инициализируется в памяти, Вы можете использовать её значение внутри только этой процедуры и по завершению процедуры переменная выгружается из памяти(обнуляется) и данные по ней теряются. Переменную, объявленную подобным образом еще называют локальной переменной. Однако с помощью данного оператора можно объявить переменную, которая будет доступна в любой процедуре модуля. Необходимо объявить переменную вне процедуры — в области объявлений(читать как первой строкой в модуле, после строк объявлений типа — Option Explicit). Тогда значение переменной будет доступно в любой процедуре лишь того модуля, в котором данная переменная была объявлена. Такие переменные называются переменными уровня модуля.

Static — данный оператор используется для объявления переменной, значение которой предполагается использовать внутри процедуры, но не теряя значения данной переменной по завершении процедуры. Переменные данного типа обычно используют в качестве накопительных счетчиков. Такая переменная инициализируется в памяти при первом запуске процедуры, в которой она объявлена. По завершении процедуры данные по переменной не выгружаются из памяти, но однако они не доступны в других процедурах. Как только Вы запустите процедуру с этой переменной еще раз — данные по такой переменной будут доступны в том виде, в котором были до завершения процедуры. Выгружается из памяти такая переменная только после закрытия проекта(книги с кодом).

Public — данный оператор используется для объявления переменной, значение которой будет храниться в любой процедуре проекта. Переменная, объявленная подобным образом, должа быть объявлена вне процедуры — в области объявлений. Такая переменная загружается в память во время загрузки проекта(при открытии книги) и хранит значение до выгрузки проекта(закрытия книги). Использовать её можно в любом модуле и любой процедуре проекта. Важно: объявлять подобным образом переменную необходимо в стандартном модуле. Такие переменные называются переменными уровня проекта.

Операторы области действия так же могут применяться и к процедрурам. Для процедур доступен еще один оператор области действия — Private. Объявленная подобным образом процедура доступна только из того модуля, в котором расположена.

Как правильно назвать переменную:

«Что самое сложное в работе программиста? — выдумывать имена переменным.» :-) А ведь придумать имя переменной тоже не так-то просто. Можно, конечно, давать им имена типа: a, d, f, x, y и т.д. Но стоит задуматься: а как Вы с ними будете управлятся в большом коде? Код строк на 10 еще потерпит такие имена, а вот более крупные проекты — не советовал бы я в них оперировать такими переменными. Вы сами запутаетесь какая переменная как объявлена и какой тип данных может хранить и что за значение ей присвоено. Поэтому лучше всего давать переменным осмысленные имена и следовать соглашению об именовании переменных. Что за соглашение? Все очень просто: перед основным названием переменной ставится префикс, указывающий на тип данных, который мы предполагаем хранить в данной переменной. Про имеющиеся типы данных я уже рассказал выше. А ниже приведена примерная таблица соответствий префиксов типам данных:

Префикс Тип хранимых данных
b Boolean
bt Byte
i Integer
l Long
s Single
d Double
c Currency
dt Date
str String
obj Object
v Variant

Лично я немного для себя её переделал, т.к. некоторые обозначения мне кажутся скудными. Например Double я обозначаю как dbl, а Single как sgl. Это мне кажется более наглядным.

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

DefBool B
Sub test()
    Dim bCheck
End Sub
DefBool B
Sub test()
    Dim bCheck
End Sub

Автоматически переменной bCheck будет присвоен тип Boolean, т.к. она начинается с буквы b — регистр здесь не имеет значения(впрочем как в VBA в целом). Оператор Def задается в области объявления. Можно задать не одну букву, а целый диапазон букв:

DefBool B-C
Sub test()
    Dim bCheck, cCheck
End Sub
DefBool B-C
Sub test()
    Dim bCheck, cCheck
End Sub

Но я считаю, что одной буквы для одного типа вполне достаточно. Можно задать сразу несколько операторов Def.

DefBool B
DefStr S
Sub test()
    Dim bCheck, sCheck
End Sub
DefBool B
DefStr S
Sub test()
    Dim bCheck, sCheck
End Sub

Ниже приведен полный перечень операторов типов и данные, которые задает каждый из них:
DefBool — Задает тип Boolean
DefByte — Задает тип Byte
DefCur — Задает тип Currency
DefDate — Задает тип Date
DefDbl — Задает тип Double
DefInt — Задает тип Integer
DefLng — Задает тип Long
DefObj — Задает тип Object
DefSng — Задает тип Single
DefStr — Задает тип String
DefVar — Задает тип Variant
По умолчанию в VBA применена инструкция DefVar для всех переменных.

Ну и немаловажный момент это непосредственно осмысленное имя переменной. Имя переменной должно примерно отражать то, что в ней будет храниться. Например, Вы создаете отчет и Вам надо объявить две переменные: одна имя листа, другая имя книги. Можно было сделать так: str1, str2. Коротко, здорово. Но если подумать — и как можно понять, какая из этих переменных что хранит? Никак. Надо просматривать код и вспоминать, какой переменной какое значение было присвоено. Не совсем удобно, правда? А если бы Вы задали имена так: strBookName, strSheetName, то вроде как более понятно, что мы в них будем хранить.Это удобно не только вам самим при работе с кодом, но и другим людям, которые, возможно в будущем будут пользоваться Вашим кодом. Им будет удобнее читать код, если он будет оформлен грамотно, а переменные названы осмысленно. И не стоит экономить на длине имени — имя должно быть понятным. Без фанатизма, конечно :-). Хоть VBA и позволяет нам создавать переменные длиной до 254 символов, но читать такую переменную так же неудобно, как и с одним символом. Но здесь уже все зависит от Ваших предпочтений и фантазии.

Небольшое дополнение: лучше привыкать давать названия переменным на латинице(т.е. английский алфавит), т.к. для VBA английский язык «родной» и лучше использовать его.

Небольшой пример использования переменных в кодах:

Dim sAddress As String, sNewAddress As String, sShName As String
'выделяем ячейку D9
Range("D9").Select
'назначаем переменной адресс выделенных ячеек
sAddress = Selection.Address
'показываем сообщение
MsgBox "Адрес выделенной области: " & sAddress, vbInformation, "www.excel-vba.ru"
'назначаем другой переменной значение адреса ячейки A1
sNewAddress = "A1"
'выделяем ячейку, заданную переменной sNewAddres
Range(sNewAddress).Select
MsgBox "Адрес выделенной области: " & sNewAddress, vbInformation, "www.excel-vba.ru"
'задаем значение переменной
sShName = "excel-vba"
'переименовываем активный лист на имя, заданное переменной
ActiveSheet.Name = sShName
Dim sAddress As String, sNewAddress As String, sShName As String
'выделяем ячейку D9
Range("D9").Select
'назначаем переменной адресс выделенных ячеек
sAddress = Selection.Address
'показываем сообщение
MsgBox "Адрес выделенной области: " & sAddress, vbInformation, "www.excel-vba.ru"
'назначаем другой переменной значение адреса ячейки A1
sNewAddress = "A1"
'выделяем ячейку, заданную переменной sNewAddres
Range(sNewAddress).Select
MsgBox "Адрес выделенной области: " & sNewAddress, vbInformation, "www.excel-vba.ru"
'задаем значение переменной
sShName = "excel-vba"
'переименовываем активный лист на имя, заданное переменной
ActiveSheet.Name = sShName

Также см.:
Variable not defined или что такое Option Explicit и зачем оно нужно?
Что такое модуль? Какие бывают модули?