Ввод в код макроса функций и процедур.
Теперь усложним код макроса, задав ему два параметра:
1. Название месяца отчета – запрашиваем у пользователя
2. Последнюю строку отчета БДР (в случае, если она плавающая) – рассчитывается в коде макроса.
Функция InputBox.
Чтобы запросить у пользователя месяц отчета, воспользуемся функцией «Inputbox». Суть ее: вывести диалоговое окно, в котором пользователь может самостоятельно задать значение переменной, используемой в коде.
Синтаксис функции InputBox:
| Zapros = InputBox("Текст запроса", <"Текст в шапке окна">, <Значение по умолчанию>,…) |
где Zapros – введенная Вами переменная (имя придумываете Вы сами), а в скобках через запятую перечисляются аргументы функции. Причем первый аргумент является обязательным (то есть Вы обязаны сопровождать любое диалоговое окно каким-то пояснительным тестом, чего Вы ждете от пользователя), а следующие аргументы обязательными не являются и их можно не указывать.
То есть: если Вы хотите задать значение по умолчанию, но не хотите писать текст в шапке диалогового окна, то разделите первый и третий аргументы двумя запятыми, как это будет сделано в нашем примере (см. ниже).
F ВАЖНО: для удобства присвойте полученное значение функции InputBox какой-нибудь введенной Вами переменной. Это особенно полезно, если в коде макроса Вы будете несколько раз использовать значение этой функции.
В нашем примере мы присвоим результат вызова функции InputBox переменной Mes.
F ВАЖНО: имена вводимых Вами переменных не должны совпадать с уже занятыми VBA словами под название объектов, свойств или функций! Например, нельзя завести свою переменную Range или Cells – компилятор VBA предупредит Вас, что делать этого нельзя и не запустит макрос, пока Вы не устраните ошибку (см. рис. 6).
Рисунок 6. Пример ошибки при заведении переменной
Пользователь ввел свою переменную Month, имя которой совпадает со встроенной в VBA функцией, компилятор выдает ошибку.
Не забывайте: любой текст в коде должен быть взят в кавычки! В противном случае компилятор VBA будет воспринимать этот текст как команды и будет выдавать ошибку, так как не сможет их обработать!
Итак, приступим к редактированию кода макроса. Добавим в самое начало кода макроса строки:
---------------------------------------------------
' Запрашиваем у пользователя месяц отчета
Mes = InputBox("Введите месяц отчета", , "Январь")
---------------------------------------------------
Теперь при запуске макроса будет выводиться диалоговое окно, в котором пользователь самостоятельно сможет указать, за какой месяц этот отчет:
С помощью функции InputBox можно запросить у пользователя и длину отчета. Но мы научим Excel высчитывать ее самостоятельно. Для этого нам потребуется процедура WHILE.
Процедура WHILE.
Процедура WHILE используется в коде, если Вам надо сделать одинаковые действия несколько раз подряд до тех пор, пока выполняется какое-либо условие.
| Синтаксис процедуры While |
| Do While<условие, которое должно быть верным для выполнения процедуры> |
| <список команд> |
| Loop |
Условие может состоять из одного выражения или содержать несколько выражений, перечисленных через оператор AND (то есть оба условия должны быть выполнены) или оператор OR (достаточно выполнения только одного из перечисленных условий). Также условие обязательно должно содержать переменную-счетчик (например, это может быть номер строки или столбца ячейки, значения которой Вы проверяете).
В список команд обязательно должна входить команда наращивания переменной-счетчика, иначе процедура зациклится (так как она постоянно будет сравнивать одно и то же значение) и макрос придется прерывать принудительно.
F Если после запуска макроса Вы видите, что макрос ушел в цикл, прервать его можно при помощи комбинации клавиш Ctrl+Break. И далее выбираете Ваши дальнейшие действия: или просто прекратить макрос (кнопка «End») или зайти в код макроса и исправить ошибку (кнопка «Debug»)
F Также чтобы макрос не уходил в цикл, рекомендуется включить в блок условий «защиту от дурака». Например, добавляем в условие проверку, чтобы значение счетчика не превышало определенной, заведомо достаточной для нас величины.
Рассмотрим применение процедуры While для поиска конца отчета БДР.
Как видно на рисунке 1, последняя строка отчета имеет код «500». Напишем процедуру, которая будет проверять значения в ячейках столбца «А» и остановит свои действия, когда найдет ячейку с кодом «500».
Обратите внимание:Excel иногда воспринимает числа как текст, поэтому включим два условия проверки значения ячейки и добавим «защиту от дурака» на случай, если в форме отчета случайно затрется код последней строки.
Помним, что все текстовые значения надо брать в кавычки. Числа записываем без кавычек.
---------------------------------------------------
' Ищем последнюю строку отчета
Dim Row_End As Integer ' Вводим переменную «счетчик номера строки»
Row_End = 1 ' Присваиваем ей номер 1
‘ Начинаем процедуру поиска последней строки отчета по коду «500»
Do While Cells(Row_End, 1).Value <> "500" And Cells(Row_End, 1).Value <> 500 And Row_End < 100
‘ До тех пор, пока код не равен 500 и номер строки меньше 100, выполняем наращивание счетчика
Row_End = Row_End +1
Loop
--------------------------------------------------
Таким образом, процедура While нам была нужна только для наращивания счетчика – никаких дополнительных действий она не производит. Запомните этот прием! Он часто нужен в кодировании!
Теперь заменим в изначальном коде макроса номер последней ячейки (34) на переменную Row_End. Число 34 было частью текста «А1:С34», а теперь нам надо часть текста заменить на переменную. Делается это с помощью символа амперсанд «&» по такому правилу:
| «текст» & переменная & «текст» |
В нашем случае ссылка на диапазон «А1:С34» перепишется как: «А1:С» & Row_End.
Кроме того, вспоминаем, что и название отчета "Бюджет на январь" у нас тоже теперь содержит параметр и будет записываться в коде как: "Бюджет на "& Mes. Не забывайте оставлять пробелы в кавычках перед переменной-словом – иначе текст сольется!
Еще раз взглянем на получившийся код макроса:
---------------------------------------------------
Sub Форматирование_БДР()
' Макрос выделяет жирным курсивом итоги, форматирует отчет на печать
'-----------------
' Запрашиваем у пользователя месяц отчета
Mes = InputBox("Введите месяц отчета", , "Январь")
' Ищем последнюю строку отчета
Dim Row_End As Integer ' Вводим переменную «счетчик номера строки»
Row_End = 1 ' Присваиваем ей номер 1
' Начинаем процедуру поиска последней строки отчета по коду «500»
' Критичным значением для «защиты от дурака» принимаем номер строки 100
Do While Cells(Row_End, 1).Value <> "500" And Cells(Row_End, 1).Value <> 500 And Row_End < 100
' До тех пор, пока код не равен 500 и номер строки меньше 100, выполняем наращивание счетчика
Row_End = Row_End +1
Loop
' Выделяем столбцы и ставим фильтр на КОД
Columns("A:C").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$C$" & Row_End).AutoFilter Field:=1, Criteria1:="<>*.*", _
Operator:=xlAnd
' Выделяем отфильтрованный диапазон полужирным шрифтом, снимаем фильтр
Range("A1:C" & Row_End).Select
Selection.Font.Bold = True
Selection.AutoFilter
' Форматируем на печать: верхний колонтитул, центрирование по горизонтали, масштаб 75%
With ActiveSheet.PageSetup
.CenterHeader = "Бюджет на " & Mes
.CenterHorizontally = True
.Zoom = 75
End With
End Sub
---------------------------------------------------
Теперь Ваш макрос стал более универсальным в использовании.
Добавим в него еще одну «защиту от дурака». Если нам принципиально, правильно ли макрос нашел последнюю строку отчета, после окончания процедуры While (строка Loop) можно добавить блок проверки значения Row_End и запроса у пользователя подтверждения на продолжение макроса.
Для этого изучим функцию MsgBox, процедуру IF и команду Exit Sub.
Функция MsgBox.
MsgBox – это еще один способ общения с пользователем: сообщения ему какой-то информации по ходу выполнения макроса или запрос у него подтверждения по дальнейшим действиям макроса путем нажатия на кнопки вида «Yes», «No», «ОК», «Cancel».
Она имеет два вида записи:
1. Мы просто сообщаем пользователю какую-то информацию: аргументы функции перечисляются сразу же за функцией, без использования скобок:
| MsgBox "Текст сообщения", <Вид кнопок>, <"Текст в шапке окна">,… |
В этом случае на экране просто отобразится диалоговое окно и после нажатия пользователем кнопки «ОК» продолжится выполнение макроса.
Как и в случае с функцией InputBox, обязательным здесь является только первый аргумент – Текст сообщения. Остальные аргументы можно даже не указывать, сократив вид записи функции до вида:
| MsgBox «Текст сообщения» |
2. Нам важно, что ответил пользователь, мы записываем его ответ в значение какой-то переменной. В этом случае аргументы функции заключаются в скобки, а перед функцией обязательно должна стоять переменная, в которую мы записываем, на какую кнопку нажал пользователь:
| YesNo = MsgBox("Текст сообщения", <Вид кнопок>, <" Текст в шапке окна ">,…) |
Варианты вида кнопок:
· vbOKOnly – только кнопка «ОК»
· vbOKCanel – кнопки «ОК» и «Cancel»
· vbYesNo – кнопки «Yes» и «No»
· vbYesNoCancel – кнопки «Yes», «No» и «Cancel»
Соответственно в зависимости от нажатой кнопки значения функции MsgBox могут быть vbOK, vbCancel, vbYes или vbNo.
Процедура IF.
С помощью данной процедуры можно разбить команды кода на две ветки в зависимости от выполнения заданного условия.
| Синтаксис процедуры IF |
| If<условие, которое должно быть верным> Then |
| <список команд при выполнении указанного условия> |
| else |
| <список команд при невыполнении указанного условия > |
| End If |
Обязательным к заполнению является только условие. Список команд в обоих блоках является необязательным к заполнению: в зависимости от Ваших задач, Вы можете заполнить оба блока или только один из блоков. Условие может состоять из одного выражения или содержать несколько выражений, перечисленных через оператор AND (то есть оба условия должны быть выполнены) или оператор OR (достаточно выполнения только одного из перечисленных условий).
Команда Exit Sub.
Данная команда прекращает выполнение макроса. Обычно ее используют в ветках процедуры If при проверке допустимости дальнейшего исполнения макроса. Обратите внимание: ни одно действие после команды Exit Sub выполнено не будет. Поэтому если Вы хотите сообщить пользователю о принудительном прекращении процедуры, команду MsgBox нужно расположить выше команды Exit Sub.
Итак, объединим новые знания в код «защиты от дурака». Запишем его после строки Loop.
---------------------------------------------------
' Проверяем, дошла ли процедура While до «критичной» строки 100 и
' спрашиваем подтверждение о продолжении выполнения макроса
If Row_End = 100 Then
YesNo = MsgBox(«Поиск последней строки отчета дошел до 100, продолжаем?», vbYesNo)
If YesNo = vbNo Then ' Если нажата кнопка «No», то…
MsgBox "Процедура прервана пользователем" '1. выдаем сообщение для пользователя,
Exit Sub '2. останавливаем макрос
End If
End If
---------------------------------------------------
Обратите внимание
F В нашем коде прошло вложение одной процедуры IF в другую:
1. Сначала мы проверили, дошел ли счетчик Row_End до значения 100.
2. Если дошел, мы спрашиваем у пользователя, продолжать ли нам выполнение макроса
3. Если пользователь ответил «нет» - мы останавливаем макрос.
F В обеих процедурах IF мы опустили второй блок (else): если Row_End не достиг значения 100 или пользователь ответил «да» - макрос просто покинет процедуру IF и продолжит выполнять команды, написанные ниже.
F Каждая процедура IF должна заканчиваться командой End If. Если количество строк с командой IF не будет соответствовать количеству строк с командой End If – компилятор VBA сообщит об ошибке, и Вам придется искать, где Вы потеряли конец процедуры IF. Поэтому рекомендуется сразу писать обе строки, а потом уже наполнять процедуру командами.
F Также для удобства визуального восприятия кода макроса рекомендуется каждую вложенную процедуру начинать с отступа: чтобы Вам визуально было понятно, где заканчивается вложенная процедура и продолжается список команд процедуры верхнего уровня.
Подведем итоги:
Итак, мы с Вами научились:
1. Записывать макросы через команду «Вид» - «Макросы» - «Запись макрос»
2. Редактировать автоматически записанный макрос, удалив из него лишние команды
3. Унифицировать код макроса, вводя в него переменные, которые макрос запрашивает у пользователя или рассчитывает самостоятельно
4. Изучили функции InputBox и MsgBox, процедуры WHILE и IF, команду Exit Sub.
В следующем номере мы познакомимся с процедурой FOR, запишем макрос суммирования результирующих статей БДР, научимся тестировать макросы и выносить кнопки вызова макроса на панель быстрого доступа
Макросы: продолжение.
В предыдущем номере мы научились записывать и редактировать несложные макросы с помощью процедур WHILE и IF, функций InputBox и MsgBox и команды Exit Sub. В этом номере мы изучим процедуру FOR, запишем макрос суммирования результирующих статей БДР, научимся тестировать макросы и выносить кнопки вызова макроса на панель быстрого доступа.
Задача 2.Ваша информационная система выдает отчет «Бюджет на месяц» без формул суммирования итогов (то есть все данные представлены значениями), а мы хотим вносить изменения в выданный информационной системой бюджет – следовательно, надо проставить формулы в результирующих ячейках. Напишем макрос, который расставляет формулы Суммы в строках, выделенных полужирным шрифтом (то есть этот макрос надо запускать после макроса «Форматирование_БДР», рассмотренного нами в предыдущем номере).
Рисунок 7. Иллюстрация к задаче 2.
Для этого познакомимся с еще одной часто используемой в кодах процедурой FOR
Процедура FOR.
Если Вам надо сделать одинаковые действия известное количество раз подряд (например, отформатировать ячейки в диапазоне со 2 по 5 строку), то можно запустить процедуру FOR.
| Синтаксис процедуры IF |
| Fori = A To B |
| <список команд > |
| Nexti |
Где i – заданная Вами переменная-счетчик, А и В – диапазоны значений, которые эта переменная будет принимать по очереди. Эта процедура так же допускает вложения внутрь себя других процедур.
По смыслу процедура FOR очень схожа с процедурой While. Однако есть ряд существенных отличий между этими процедурами.
Процедура IF самостоятельно наращивает переменную-счетчик, и в список команд не надо добавлять ее наращивание, как мы это делали для процедуры While. Вторым важным отличием этой процедуры от процедуры While является то, что длина диапазона (значение переменной В) не может быть изменена после начала работы процедуры. Однако есть способ использовать процедуру For, не зная заранее длину диапазона, для которого она будет применена. Об этом – чуть позже.
А пока потренируемся использовать процедуру FOR на легком примере.
Отформатируем в нашем примере блок «Выручка»: увеличим размер шрифта с 10 до 11:
Сначала запишем автоматический макрос-команду изменения шрифта:
· Встаем в ячейку А2.
· Нажимаем кнопку записи макроса, даем понятное ему название и описание.
· Увеличиваем размер шрифта (выбираем на панели в закладке «Главная» размер шрифта «11»).
· Останавливаем макрос (синий квадратик внизу слева)
· Нажимаем Alt+F8 (или на вкладке «Вид» кнопку «Макросы»), находим наш новый макрос, жмем кнопку «Изменить» и попадаем в редактор кода макроса.
· Находим в коде макроса нужную нам строку, остальное можем удалить.
· Обратите внимание: автоматически в код макроса записались все параметры, отраженные на закладке «Шрифт» диалогового окна «Формат ячеек» (вызывается нажатием клавиш ctrl+1). Можете попробовать самостоятельно сопоставить строки макроса с пунктами этой закладки.
---------------------------------------------------
Sub Шрифт_11()
' Увеличивает размер шрифта до 11
With Selection.Font
.Name = "Times New Roman"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
End Sub
---------------------------------------------------
Так как нам нужно было изменить только одно свойство объекта Selection.Font, то мы можем отказаться от использования процедуры With и сократить код до 1 строки:
---------------------------------------------------
Sub Шрифт_11()
' Увеличивает размер шрифта до 11
Selection.Font.Size = 11
End Sub
---------------------------------------------------
Встаньте в ячейку А3 и убедитесь, что после редактирования макрос работает корректно (комбинация клавиш «Alt+F8» или закладка «ВИД», кнопка «Макросы» - далее находим наш новый макрос и нажимаем «Выполнить»).
Теперь добавим в макрос цикл с помощью двух вложенных процедур FOR
---------------------------------------------------
For Row_count = 2 To 5 ' Перебираем строки
For Col_count = 1 To 3 ' Перебираем столбцы
Cells(Row_count, Col_count).Select ' Выделяем ячейку в строке Row_Cont и столбце Col_count
Selection.Font.Size = 11 ' Форматируем объект Selection (шрифт = 11)
Next Col_count ' Следующий номер столбца
Next Row_count ' Следующий номер строки
---------------------------------------------------
Запускаем макрос и проверяем корректность его исполнения.
Мы рассмотрели процедуру For, где в качестве переменной выступало число. Давайте рассмотрим процедуру For, где в качестве переменных выступают Объекты VBA.
Процедура FOR для Объектов.
Как мы уже знаем, объектами в VBA для Excel выступают Ячейки, Листы, Книги. Если Вы хотите использовать процедуру FOR для объектов, Вы должны обязательно «представить» VBA переменную, которую Вы будете использовать как объект.
Если Вы могли заметить, до этого мы не загружали код «представлением» VBA каждой введенной переменной и присвоением ей типа. Потому что компилятор VBA в состоянии автоматически распознать переменную (если ее название не совпадает с названием стандартных функций и процедур в VBA) и присвоить ей тип в зависимости от того, какое значение мы в нее записали. Однако если код макроса достаточно длинный, то полезно прописать в начале макроса все используемые переменные с комментарием, для чего мы их ввели (чтобы в дальнейшем быстро разобраться в написанном ранее коде). Это правило хорошего тона программирования. Но для небольших макросов, код которых несложен в понимании, этого можно и не делать.
Заводить переменную можно в любом месте кода: главное это сделать перед ее первым упоминанием в коде. Ввод переменных осуществляется через команду:
| Dim имя_переменной_без_пробелов As <тип переменной> |
Если Вы сомневаетесь, какой тип присвоить переменной, то его можно не указывать, за исключением объектов – их нужно обозначать всегда, даже если код макроса будет состоять их одной команды – в противном случае компилятор VBA просто не поймет Ваших команд.
| Синтаксис процедуры FOR для Объектов |
| DimObj _Name As Object For EachObj_Name Inкласс_объекта |
| <список команд > |
| NextObj_Name |
Примеры класса объектов:
· Workbooks – все открытие книги в одном сеансе Excel
· Worksheets – все листы книги, из которой запущен макрос
· Selection – ячейки из выделенного диапазона.
Перепишем блок кода форматирования шрифта с использованием процедуры FOR для Объектов:
---------------------------------------------------
Dim Obj_Cell As Object ' Вводим переменную типа Объект
Range(“A2:C5”).Select ' Выделяем диапазон, который надо форматировать
For Each Obj_Cell In Selection ' Перебираем все ячейки из диапазона
Obj_Cell.Font.Size = 11 ' Форматируем объект
Next Obj_Cell ' Следующая ячейка
---------------------------------------------------
Как видите, использовав процедуру FOR для Объектов, мы свели две вложенные процедуры FOR до одной. Именно эту процедуру мы будем использовать для выполнения задачи №2.
Итак, вернемся к выполнению задачи №2. Мы уже знаем, как проверять условия и как писать процедуры циклов. Осталось только научиться писать формулы в ячейки. Для этого запишем макрос с нашими действиями по добавлению формулы Суммы в ячейку «С2» и разберем его код:
· Встаем в ячейку «С2».
· Нажимаем кнопку записи макроса, даем понятное ему название и описание.
· Записываем в ячейке формулу: «= СУММ(C3:C5)».
· Останавливаем макрос (синий квадратик внизу слева)
· Нажимаем Alt+F8 (или на вкладке «Вид» кнопку «Макросы»), находим наш новый макрос, жмем кнопку «Изменить» и попадаем в редактор кода макроса.
Нас в этом коде интересует строка:
| ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[3]C)" |
VBA использует форму записи в относительных ссылках. Такую же форму записи вы можете увидеть и на рабочих листах Excel, если установите тип ссылок «R1С1» в блоке «Формулы» - «Работа с формулами» диалогового окна «Параметры Excel» (вызывается нажатием кнопки «Параметры» закладки «Файл/Office» на панели инструментов), – иногда это помогает при написании кода макроса.
Разберем их смысл:
· R[i] – на сколько строк ниже текущей ячейки находится нужная нам для формулы ячейка (следовательно, отрицательные значения нас отправляют в ячейки выше текущей; если значение не указано – значит, ссылка идет на ячейку в той же строке, что и ячейка с формулой).
· C[i] – на сколько столбцов вправо находится упомянутая в формуле ячейка (отрицательное – влево, нет номера – в том же столбце).
Мы с Вами записывали формулу в ячейке «C2», значит, сумма захватит диапазон «С3:С5».
Итак, теперь давайте подумаем, как нам научить Excel, чтобы он сам понимал, до какой ячейки ему вести суммирование. И как макрос должен понимать, в какой ячейке ему прописывать формулу, а какую пропускать?
Распишем алгоритм действий по распознанию результирующих строк:
1. Смотрим на ячейку «С2» – она выделена полужирным шрифтом, и мы понимаем, что это результирующая строка
2. Пробегаем глазами до следующей полужирной ячейки: то есть пропускаем все ячейки, которые не выделены полужирным шрифтом.
3. Фиксируем ячейку «С6» как следующую результирующую ячейку.
4. Понимаем, что ячейка «С2» является суммой ячеек «С3:С5».
F ИТОГО: начало формулы суммирования всегда R[1] (то есть следующая за выделенной полужирным шрифтом ячейкой), а конец (для ячейки «С2» это было R[3])– это сдвижка от текущей ячейки на «6 (номер строки следующей результирующей ячейки) - 2 (номер строки ячейки, в которой записывается формула) – 1»
F Получаем следующую команду для кода макроса:
| End_Sum = Sum_Next – Sum_Cur – 1 Cells(Sum_Cur, 3).FormulaR1C1 = "=SUM(R[1]C:R[" & End_Sum &"]C)" |
Где
Cells(Sum_Cur, 3) – замена в изначальном коде макроса объекта ActiveCell на объект с переменным номером строки (Sum_Cur) и неизменным номером столбца (3)
Sum_Cur – номер строки ячейки, в которую идет запись формулы
End_Sum – относительная сдвижка формулы вниз
Sum_Next – номер строки со следующей результирующей ячейкой
Теперь придумаем условие, как Excel сможет отличить результирующие ячейки от других. Они в отчете визуально выделены полужирным шрифтом. И нам надо научиться записывать это условие в код.
F Запишем макрос, который покажет, как выглядит условие на полужирный шрифт ячейки:
a. Встаем на любую ячейку книги, нажимаем запись макроса.
b. Изменяем шрифт ячейки на «полужирный»
c. Останавливаем запись макроса и находим в коде нового макроса нужную нам строку:
| Selection.Font.Bold = True |
Пишем процедуру суммирования результирующих строк по признаку полужирного шрифта:
---------------------------------------------------
Sub Сумма_итогов() ' Начало процедуры, Имя
Range(“C2:C34”).Select ' Выделяем диапазон, с которым будем работать
Dim Obj_Cell As Object ' Вводим переменную типа Объект
Sum_Cur = 0 ' Задаем начальное значение переменной Sum_Cur
For Each Obj_Cell In Selection ' Перебираем все ячейки из диапазона
If Obj_Cell. Font.Bold = True Then ' Проверяем шрифт ячейки, для полужирных делаем:
' Проверяем, что это первый шаг цикла (значение Sum_Cur еще не задано)
If Sum_Cur = 0 Then
' Для первой итерации задаем Sum_Cur и переходим к поиску следующей результирующей ячейки
‘ (чтобы получить значение переменной Sum_Next)
Sum_Cur = Obj_Cell.Row
' Выходим из этой ветки сразу к пункту «Next Obj_Cell»
Else
' Если нашли уже следующую результирующую ячейку (Sum_Cur>0),
' задаем значение Sum_Next и End_Sum, после чего мы уже сможем написать формулу в строке Sum_Cur
Sum_Next = Obj_Cell.Row
End_Sum = Sum_Next – Sum_Cur – 1
' проверяем, что End_Sum >=1 (чтобы формула не выдала цикл):
If End_Sum >= 1 Then
' Пишем в текущей результирующей ячейке формулу суммы:
Cells(Sum_Cur, 3).FormulaR1C1 = "=SUM(R[1]C:R[" & End_Sum &"]C)"
' Если End_Sum<1, то ничего не делаем. Блок Else нам здесь не нужен.
End If
' Присваиваем следующей результирующей ячейке (в строке Sum_Next) статус «текущей»:
Sum_Cur = Obj_Cell.Row
' Выходим из этой ветки к пункту «Next Obj_Cell»
End If
' Для нежирных ячеек – ничего не делаем. Блок Else не заполняем.
End If
Next Obj_Cell ' Переходим к следующей ячейке
End Sub ' Конец процедуры
---------------------------------------------------
Запускаем макрос, смотрим на результат: макрос не затер значения в строках с кодом 405 и 406 (так как у этих строк нет расшифровки) – это правильно. Однако не проставил он формулы и в строках с кодом 300, 400 и 500 – но там должны быть другие формулы, добавить которые в код макроса предлагается читателям самостоятельно для закрепления прочитанного материала.
Подсказка: для расчета операционных расходов воспользуйтесь формулой СУММЕСЛИ(), задав критерий проверки по коду вида: "<>*.*"
F Этот макрос будет работать только для диапазона «C2:C34». Чтобы сделать его универсальным в использовании, уберем из кода первую строку макроса (Range(“C2:C34”).Select). Но тогда надо помнить, что перед запуском макроса Вам надо выделить диапазон, к которому Вы хотите применить этот макрос. Чтобы защитить себя от забывчивости, давайте добавим в макрос «напоминалку» для себя.
Заменяем в макросе «Сумма_итогов()» строку «Range(“C2:C34”).Select» на строки:
---------------------------------------------------
YesNo = MsgBox(«Вы выделили диапазон, в котором надо проставить суммы?», vbYesNo)
If YesNo = vbNo Then
MsgBox "Выделите диапазон, в котором надо проставить суммы, и запустите макрос повторно"
Exit Sub
End If
---------------------------------------------------
Теперь мы получили макрос, который будет работать в любом выделенном диапазоне любого столбца (нет привязки к столбцу «С»). И таким образом мы убрали из кода макроса константу с длиной массива. Правда, использование этого макроса требует определенной дисциплины от пользователя: он должен помнить, что перед запуском макроса, надо выделять диапазон, к которому он хочет применить макрос.