Макросы в Excel. Простая математика.

Простая математика

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

VBA решает этот вопрос без проблем, потому что мы сразу будем заносить значение в ячейку, а не формулу.

Ну что ж, давайте посмотрим как вычислить элементарное.

Сложение

Сложим две ячейки. При использовании вычислительной техники нельзя забывать одно правило. Если по простому на бумаге все формулы пишут обычно в таком виде:

A + B = C

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

C = A + B

Так вот, наше сложение двух ячеек будет смотреться так:

Sub Primer()

Cells(1, 1) = Cells(1, 2) + Cells(1, 3)

End Sub

Это вырвжение говорит о том, что результат сложения ячеек В1 и С1 записывается в ячейке А1.

Сложим несколько ячеек:

Sub Primer()

Cells(1, 1) = Cells(1, 2) + Cells(1, 3) + Cells(1, 4)

End Sub

Возможно ли прибавить некоторую цифру к получившейся сумме? Конечно можно:

Sub Primer()

Cells(1, 1) = Cells(1, 2) + 8

End Sub

Вычитание

Вычитание аналогично как исложение:

Sub Primer()

Cells(1, 1) = Cells(1, 2) - Cells(1, 3)

End Sub

Sub Primer()

Cells(1, 1) = Cells(1, 2) - Cells(1, 3) - Cells(1, 4)

End Sub

Sub Primer()

Cells(1, 1) = Cells(1, 2) - 6

End Sub

Умножение

Умножение аналогично как исложение и вычитание:

Sub Primer()

Cells(1, 1) = Cells(1, 2) * Cells(1, 3)

End Sub

Sub Primer()

Cells(1, 1) = Cells(1, 2) * Cells(1, 3) * Cells(1, 4)

End Sub

Sub Primer()

Cells(1, 1) = Cells(1, 2) * 4

End Sub

Деление

Деление всё аналогично:

Sub Primer()

Cells(1, 1) = Cells(1, 2) / Cells(1, 3)

End Sub

Sub Primer()

Cells(1, 1) = Cells(1, 2) / Cells(1, 3) / Cells(1, 4)

End Sub

Sub Primer()

Cells(1, 1) = Cells(1, 2) / 2

End Sub

Возникает один вопрос: А можно ли смешать эти действия?
Можно.

Sub Primer()

Cells(1, 1) = (Cells(1, 2) + Cells(1, 3)) * 2 - Cells(1, 4) / 6

End Sub

При работе с арифметическими операциями не забываем только это правило:

C = A + B

Ещё один важный момент при работе с переменными - это описание переменных. Об этом в уроке ниже.

Возможно кто-то задумался, а можно ли сразу задать нашей переменной некоторое значение? И это можно, но только называется это уже не переменная, а константа, которая описывается так:

Const ИМЯConst = ...

или

Const A As Integer = 2323

При этом описывать её как переменную не следует. Наш код будет выглядеть, например, так:

Const AConst = 10

Sub Primer()

Dim C As Long

Dim B As Integer

B = 2

C = AConst + B

End Sub

или

Const A As Integer = 10

Sub Primer()

Dim C As Long

Dim B As Integer

B = 2

C = AConst + B

End Sub

Существует ещё такой тип описания переменной:

Private A As Integer

Но данное объявление переменной, видимо только в своём модуле.

Private A As Byte

Sub Primer()

Dim B As Integer

B = 2

A = A + B

End Sub

Без логики никуда. Всё программирование это логика. В VBA-Excel так же есть свои логические операторы. Ниже приведена таблица операторов их синтаксис, описание и таблица истинности.

Теперь рассмотри как они применяются на практике

Оператор AND

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

Private Sub CommandButton1_Click()

If Cells(3, 9) = 1 And Cells(3, 10) = 1 Then

Cells(3, 11) = 1

Else

Cells(3, 11) = 0

End If

End Sub

Теперь надо прояснить ситуацию. Если в ячейке Cells(3, 9) и ячейке Cells(3, 10) записана еденица, то в ячейке Cells(3, 11) так же записывается еденица, в противном случае в ячейке Cells(3, 11) прописывается ноль (в примере есть все логические примеры, можете скачать).

Но это не значит, что оно всегда должно начинаться с условия Если то. И не обязательно чтобы в выражении было два входных значения (в данном случае Cells(3, 9) = 1, Cells(3, 10)). Их модет быть столько сколько вы захотите. Например так:

Private Sub CommandButton1_Click()

If Cells(3, 9) = 1 And Cells(3, 10) = 1 And Cells(3, 11) = 1 Then

Cells(3, 12) = 1

Else

Cells(3, 12) = 0

End If

End Sub

Оператор OR

Тут всё аналогично, начинаем с Если и заканчиваем То. И также входных переменных может быть более двух.

Private Sub CommandButton2_Click()

If Cells(8, 9) = 1 Or Cells(8, 10) = 1 Then

Cells(8, 11) = 1

Else

Cells(8, 11) = 0

End If

End Sub

Оператор NOT

В случае с НЕ нам достаточно одной входной переменной для получения необходимого результата на выходе.

Private Sub CommandButton3_Click()

If Not Cells(13, 9) = 1 Then

Cells(13, 11) = 1

Else

Cells(13, 11) = 0

End If

End Sub

В этом случае у нас получается всё в точности противоположно истинне. Если в Cells(13, 9) записана еденица, то получается ноль, в противном случае - еденица (так сказать некий закон подлости).

Оператор XOR

Это что-то вроде оператора OR (или), но тут нет истинности при одинаковых значениях переменных.

Private Sub CommandButton4_Click()

If Cells(16, 9) = 1 Xor Cells(16, 10) = 1 Then

Cells(16, 11) = 1

Else

Cells(16, 11) = 0

End If

End Sub

Оператор EQV

Данный оператор так же опереирует двумя и более переменными.

Private Sub CommandButton5_Click()

If Cells(21, 9) = 1 Eqv Cells(21, 10) = 1 Then

Cells(21, 11) = 1

Else

Cells(21, 11) = 0

End If

End Sub

Но если Вы экспериментируете с несколькими переменными, то результат будет совершенно не ожиданный. Вот попробуйте. Это булева алгебра и её не обманешь :-)

Оператор IMP

Данный оператор тоже можно записать через условие Если То.

Private Sub CommandButton6_Click()

If Cells(26, 9) = 1 Imp Cells(26, 10) = 1 Then

Cells(26, 11) = 1

Else

Cells(26, 11) = 0

End If

End Sub

У кого-то может возникнуть вопрос: А можно ли эти операторы соединить в одном логическом выражении? Конечно можно.

Sub Primer()

A = 1

B = 0

C = 1

If A = 1 And B = 1 Or C = 1 Then

MsgBox "Истина 1", vbInformation, "Истина"

Else

MsgBox "Ложь 0", vbInformation, "Ложь"

End If

End Sub

МОДУЛЬ

Модуль - это лемент, имеющий собственное имя и состоящий из одной или нескольких процедур и объявлений, общих для всех процедур модуля. Существует два типа модулей - это стандартный модуль имодуль класса.
Некоторые ещё выделяют третий тип модуля - это Модули формы. Но я не отношу этот тип к модулям, потому что это не модуль, а форма. Хотя какая разница как их называть и куда относить, главное чтоб работало всё и принцип был понятен что к чему.

Стандартный модуль

Стандартный модуль является самым популярным в VBA, раньше его называли модуль кода. Другими словами в стандартном модуле должны находиться все те процедуры, которые не относятся к событию книги или листа (о событиях книги и листа будет в уроках ниже). Тот код имя которому придумали лично Вы. Например, если вы добавляете на лист кнопку, и щёлкаете по ней два раза ЛКМ вы попадаете именно на тот лист в VBA, на котором находится кнопка (т.е. эта процедура от события листа). И носить она будет например такое название CommandButton1_Click():

Private Sub CommandButton1_Click()

............

End Sub

А вот если мы напишем макрос с таким именем:

Sub Primer()

Dim i As Byte

Dim r As Integer

For i = 1 To 100 Step 1

r = r + i

Next i

MsgBox "Сумма чисел от 1 до 100 равняется " & r, vbInformation, "Сумма"

End Sub

То эту процедуру уже помещаем на модуль. Так как этот код не является ни чьим событием, а мы его сами придумали, и запускать мы его так же будем тогда, когда захочем мы, а не от какого-то события, как например, с кнопкой на листе, которая нам создаёт это событие.

Вставить модуль в документ очень просто. Заходим в окно VB и нажимаем на панели управления кнопкуInsert / Module.

После этого у Вас появится папка Modules с содержащимся в ней модулем. Модулей в книге может быть столько сколько вы захотите.

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

Модуль класса

Модуль класса - это специальный тип модуля VBA, который можно добавить в наш проект. Этот модуль позволяет создавать новый класс объектов. При добавлении этого модуля программист уже должен понимать, что это уже управление объектами. Модуль класса позволяет создавать новые объекты, а также соответствующие для них свойства, методы и события.
Добавить этот модуль к себе в проект не сложно. Заходим в Insert / Class Module

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

В видеоуроке показано как добавлять модули в проект и как их переименовывать.

Запуск макроса
Привязка макроса к произвольному объекту

Выполненить макрос возможно не только нажатием кнопок F5, F8 или по команде какой-то кнопочки или галочки. Выполнение макроса возможно привязать к произвольному объекту находящемуся на вашем листе. Делается это очень просто. Вставляется какая-нибудь фигура или картинка.

Дальше объект редактируем. Придаём ему нужный размер, заливку, оттенки, тени, изменить или добавить текст на объектк и т.п. Красивую настройку можно сделать нажав на правую кнопку мыши на объекте и выбрать пункт Формат фигуры или Формат рисунка. В видеоролике показано как можно сделать красивую кнопку.

Ну вот, красоту навели на объекте, теперь нам надо как-то привязать к нему макрос. Чтобы мы нажали на картинку и у нас запустился соответствующий макрос. Нажимаем опять же на объекте правой кнопкой мыши и выбираем пункт Назначить макрос.

После этого откроется окно, в котором будет список всех ваших макросов. Указываем необходимый макрос из этого списка и нажимаем ОК. Теперь указанный макрос будет запускаться при нажатии на эту фигуру ЛКМ.

Автоматическая запись макроса
Макрорекордер

Запись макроса - это очень полезная штука, которую придумал Microsoft. Вот например, Вы постоянно выполняете одну и ту же работу в книге Excel, и это вам уже надоело. Вот бы придумать такую штуку чтобы один раз показал как делается, а потом всё само на автомате, нажал кнопочку и сидишь такой довольный косынку раскладываешь, а программа сама работает. Вот именно это и придумали в Microsoft, только назвали всё это дело - Запись макроса.

Хорошо, что некоторые уже поняли о чём идёт речь. Так давай те же посмотрим что это такое и как им поскорее научиться пользоваться.

Запись макроса можно включить двумя способами:
1 - Заходим в вкладку разработчик и нажимаем пиктограмму Запись макроса:

2 - это нажимаем пиктограмму в нижнем левом углу окна книги:

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

Имя макроса - как всегда пишем латинскими буквами, можно добавить цифры (о том как можно называть макросы рассказывалось тут).
Сочетание клавишь - вы можете назначить некое сочетание клавишь и при их нажатии будет запускаться записанный макрос (а не с кнопочки или галочки).
Сохранить в - Вы сразу можете указать где будет храниться этот макрос. Если записанный макрос вам нужен только в этом документе, то можете сохранить его в текущей книге, а если нет, то можете выбрать другой вариант - новая книга или личная книга макросов.
Описание - говорит само за себя. Можете описать его предназначение и какую именно он будет выполнять операцию, дабы не забыть через некоторое время.

Заполняете поля и нажимаете кнопку ОК. После этого происходит запись макроса, т.е. всё то, что вы будете писать в книге, производить заливку ячеек и т.п. у Вас будет записываться.
Остановить запись возможно так же двумя способами:

Или нажав на пиктограмму в левом нижнем углу книги

Существует два вида записи макросов - это запись с прямыми ссылками и запись с относительными ссылками.
Вот теперь давайте по подробнее.

Запись макроса с прямыми ссылками

Запись макроса с прямыми ссылками означает, что все наши действия, которые мы записали точно так же повторятся при воспроизведении макроса. Например, мы нажали Запись макроса и записали в ячейку А1 слово "Привет", в ячейку В1 "Пока", а в ячейку С1 "Привет". После этого остановили запись. Потом нажали выполнение записанного макроса, перед этим очистив ячеки и убрав курсор на совершенно другую ячйку, и у нас снова в Ячеке А1 появилось слово "Привет", в ячейке В1 "Пока", а в ячейке С1 "Привет". И куда бы мы не перемещли курсор и как бы мы не запускали макрос у нас запись будет производиться только в ячейках A1, B1 и C1.

При такой записи макрорекордер записывает абсолютно все действия, которые вы выполняете в ходе записи макроса, включая даже то действие, как вы дёргаете за линейку прокрутки. В окне VB выглядеть это будет, например, так:

Sub PrivetPoka()

'

' PrivetPoka Макрос

'

'

Range("A1").Select

ActiveCell.FormulaR1C1 = "Привет"

Range("B1").Select

ActiveCell.FormulaR1C1 = "Пока"

Range("C1").Select

ActiveCell.FormulaR1C1 = "Привет"

End Sub

На этом примере видно, что первоначально у нас происходит выделение ячейки и только потом занесение в неё текста. Поэтому как бы мы не старались выполнять макрос на других ячейках и занести в них текст у нас ничего не выйдёт, всё равно будет записываться в тех ячейках, в которых мы заносили текст при выполнении записи макроса. Об этом ярко выражено демонстрируют прямые ссылки (Range("A1").Select, Range("B1").Select, Range("C1").Select) в коде примера.

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

Запись макроса с относительными ссылками

Запись макроса с относительными ссылками происходит немного подругому. Если для того чтобы наш макрорекордер записывал прямые ссылки никаких дополнительных оперций производить не надо, кроме того как нажать кнопку запись макроса. То при записи относительными ссылками необходимо включить эту функцию и делается это нажатием на пиктограмму Относительные ссылки на вкладке Разработчик.

При наведении на пиктограмму можно прочитать в подсказке суть этой записи. Но я всё таки попробую объяснить более подробней.
Итак, при включении данной опции записи у нас происходит запись макроса следующим образом. Если при записи прямыми ссылками у нас чётко прописывался адрес ячейки (А1), то в случае с записью относительной ссылки у нас запишется ссылка на начальное положение курсора, относительно которой будет происходить дальнейший отсчёт. Например у нас курсор находится в ячейке А1, мы написали в ней "Привет", затем переместили курсор в ячейку А2 и в ней записали "Пока", после этого переместили курсор на А3 и записали в ней "Привет". После этого мы останавливаем запись макровса и пытаемся его выполнить. Ставим курсор в ячейку А1 и унас автоматически в ячейке А2 записывается "Пока", а в ячейке А3 записывается "Привет". После этого перемещаем курсор в ячейку В1 и снова нажимаем выполнить макрос. При этом у нас в ячейке В1 будет "Привет", в ячейке В2 "Пока", в В3 "Привет". Это всё потому, что макрорекордер записывает относительные ссылки, т.е. ссылки произведённые относительно первоначального состояния курсора. Выглядеть этот код будет например так:

Sub PrivetPoka()

'

' PrivetPoka Макрос

'

'

ActiveCell.FormulaR1C1 = "Привет"

ActiveCell.Offset(0, 1).Range("A1").Select

ActiveCell.FormulaR1C1 = "Пока"

ActiveCell.Offset(0, 1).Range("A1").Select

ActiveCell.FormulaR1C1 = "Привет"

End Sub

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

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

У некоторых людей наверное сложилось такое впечатление, что можно и не изучать программирование Excel дальше, какие-то операторы, функции и прочую лабуду, если можно просто записать всю последовательность работы прямыми или относительными ссылками и потом это использовать. Может и так. Может кому-то больше и не надо. Но хочу сказать, что запись макроса и придумана для людей, которые не желают учиться. Которым просто нужен быстрый результат. Какую-то полноценную и интересную программу через макрорекордер сделать никогда не получится.
Лично я пользуюсь этой функцией только когда мне необходимо произвести составление какого-то шаблона таблицы и её оформление. Но после этого обязательно произвожу ручное редактирование записанного макроса, потому что мкрорекордер записывает очень много промежуточного, "ненужного", кода, который можно упростить и уменьшить раз в 10.

Определение активных составляющих книги

Мне кажется, многие загонялись таким вопросом как определить активную ячейку (на которой находится курсор), строку или столбец. Или же хотелось получить адрес в формате А1, В4 и т.д. Вот сегодня я и покажу как это сделать, потому что очень удобно, когда ты знаешь активные элементы книги.

Номер активной строки

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

Sub Stroka()

s = ActiveCell.Row

MsgBox "Активная строка под номером " & s, vbInformation, "Активная строка"

End Sub

Номер активного столбца

Аналогично определению строки столбец определяется по такому же принципу, только в конце добавляется не строка, а столбец:

Sub Stolbec()

s = ActiveCell.Column

MsgBox "Активная столбец под номером " & s, vbInformation, "Активный столбец"

End Sub

Определяем номер последней заполненной строки

Stroka = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, _

SearchOrder:=xlByRows).Row

или

Stroka = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1

Определяем номер последнего заполненного столбца

Stolbec = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, _

SearchOrder:=xlByRows).Column

или

Stolbec = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1

Определяем активную ячейку

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

Sub yacheika()

sk = ActiveCell.Row

st = ActiveCell.Column

MsgBox "Активная ячейка имеет координаты Cells(" & sk & "," & st & ")", _

vbInformation, "Активная ячейка"

End Sub

Адрес активной ячейки

Многие думали, а как получить адрес активной ячейки? Очень просто, делается это так:

Sub adres()

A = ActiveCell.Address

MsgBox "Абсолютный адрес активной ячейки - " & A, vbInformation, "Адрес"

End Sub

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

Sub adress()

A = ActiveCell.Address(0, 0)

MsgBox "Относительный адрес активной ячейки - " & A, vbInformation, "Адрес"

End Sub

В скобках оператора Address указано какое именно значение должно быть относительным, а какое абсолютным. Если Address(0,0), то и строка и столбец записаны относительными (первый ноль отвечает за строку, второй ноль - это столбец). Если в строке поставить вместо ноля еденицу, то строка будет абсолютным значением, а столбец относительным. Причём значения следующих записей равнозначны:

Address(1, 0) = Address(True, False)

Адрес выделенного диапазона ячеек

Адрес выделенного диапазона можно получить следующим образом

Sub diapazon()

a = Selection.Rows.Address(0, 0) 'Через строку

b = Selection.Columns.Address(0, 0) 'Через столбец

c = Selection.Address(0, 0)

MsgBox "Выделенный диапазон - " & a, vbInformation, "Адрес"

End Sub

При чём адрес выделенного диапазона можно получить как через столбец, так и через строку. И значения a, b и c в выше приведённом примере абсолютно одинаковые.

Координаты выделенного диапазона

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

Sub Kdiapazon()

a = Selection.Row

b = Selection.Column

K1 = Cells(a, b).Address(0, 0)

aa = a + Selection.Rows.Count - 1

bb = b + Selection.Columns.Count - 1

K2 = Cells(aa, bb).Address(0, 0)

MsgBox "Первая координата выделенного диапазона - " & K1 & vbNewLine & _

"Вторая координата выделенного диапазона - " & K2, vbInformation, "Координаты"

End Sub

a - Определяет номер первой строки выделенного диапазона;
b - Определяет номер первого столбца выделенного диапазона;
K1 - Определяем первую координату выделенного диапазона;
aa - Определяем номер последней строки выделенного диапазона;
bb - Определяем номер пследнего столбца выделенного диапазона;
K2 - Определяем вторую координату выделенного диапазона;

Selection.Rows.Count - определяет количество строк в выделенном диапазоне;
Selection.Columns.Count - определяет количество столбцов в выделенном диапазоне;

У многих возник вопрос: А почему вычитаем единицу? Всё потому, что первая координата входит в выделенный диапазон и нам её надо не потерять.

Имя активной книги

Имя активной книги возможно вычислить такой записью:

Sub WorkbookName()

Name = ActiveWorkbook.Name

MsgBox "Имя активной книги - " & Name, vbInformation, "Имя активной книги"

End Sub

Имя активного листа

Имя активного листа можно определить так:

Sub SheetsName()

Name = ActiveSheet.Name

MsgBox "Имя активного листа - " & Name, vbInformation, "Имя активного листа"

End Sub