Решаем счётные задачи с помощью Excel VBA
Visual Basic for Applications (VBA) – диалект языка Visual Basic, включённый в состав пакета Microsoft Office. Программы на VBA, называемые макросами, могут выполняться прямо из документа Word или Excel, используя при этом в качестве интерфейса пользователя стандартные для Windows кнопки, поля ввода, списки, окна диалога или переключатели.
Изучение языка - тема отдельная, хотя он совсем несложен. В этой заметке я покажу лишь самые очевидные вещи - как ввести данные и вывести результаты работы двумя основными способами - с помощью окон диалога и непосредственно в ячейки рабочего листа Excel.
Перед началом работы:
1. Включите настройку Кнопка Office - Параметры Excel (или Word) - Основные - "Показывать вкладку Разработчик на ленте". В последних версиях офиса настройка может называться иначе, но она там есть :)
2. На вкладке Разработчик нажмите кнопку Безопасность макросов и разрешите выполнение макросов:
параметры макросов
В противном случае придётся каждый раз разрешать выполнение макросов при открытии документа.
Когда цикл разработки окончен, лучше вернуть настройку на место, чтобы не открыть вирусный документ, полученный откуда-нибудь со стороны.
3. Нажмите вкладку Разработчик – Макросы, дайте новой программе имя и нажмите кнопку Создать:
создание макроса
Откроется редактор Visual Basic, в котором можно писать, отлаживать, выполнять и сохранять программы.
На скрине ниже показана программа, позволяющая вычислить, сколько процентов составляет значение A от B.
пример программы
Вот листинг почти программки такого же типа, только ещё проще.
Sub Project1() Dim A, B, C As Double A = Val(InputBox("Ввод A", "Введите A", 0)) B = Val(InputBox("Ввод B", "Введите B", 0)) C = Sqr(A ^ 2 + B ^ 2) MsgBox "Ответ =" & C End Sub
В простых случаях нам достаточно с помощью окна InputBox
получить значение переменной (третьим аргументом ей можно дать значение по умолчанию),
при необходимости проверить, корректно ли введены данные (так как введённая в InputBox
величина возвращается в виде строки, можно получить её числовое значение функцией Val
или узнать, введено ли вообще числовое значение функцией IsNumeric
), произвести расчёты и
вывести результаты в новом окне сообщения, полученном функцией MsgBox
. Её первым аргументом мы передаём строку, выводимую в окне, её можно получить сложением строк в двойных кавычек и/или числовых значений, которые нужно преобразовать к строковым функцией Str
.
Теперь можно нажать зелёный треугольничек или клавишу F5
в редакторе VBA, чтобы запустить программу.
Если доступно несколько программ или текстовый курсор не установлен внутри программы, компьютер может попросить выбрать нужную по имени:
запуск программы из редактора Visual Basic
Чтобы макросы не пропали, при первом сохранении рабочей книги нужно выбрать пункт меню "Сохранить как" и указать в списке "Тип файла" значение "Книга Excel с поддержкой макросов (*.xlsm)".
Обычно мы хотим запускать программу не из Visual Basic, а прямо из документа, например, нажимая кнопку.
Чтобы встроить кнопку непосредственно в документ Word или Excel, действуем так:
1. На вкладке разработчика нажмём кнопку "Режим конструктора" и выберем нужный элемент управления, например, кнопку:
выбор инструмента "Кнопка"
2. Потом курсором-крестиком "нарисуем" кнопку в документе и нажмём "Создать" в окне "Назначить макрос объекту", чтобы кнопке была назначена пустая процедура-обработчик её основного события (то есть, нажатия):
добавление кнопки на лист
3. После этого можно запрограммировать процедуру обработки нажатия нашей кнопки.
Обращаться к ячейкам Excel из программы VBA тоже очень легко, вот несколько примеров:
Range("B1").Value = 2018 'Поместить в ячейку B1 текущего листа значение 2018 Range("A1:A10").Select Range("A1:A10").Value = "Программа" 'Выделить и заполнить словом "Программа" ячейки A1:A10 Range("C2").Font.Size = 18 'В ячейке С2 установить размер шрифта 18 For N = 1 To 10 Range("D" & N).Value = _ Range("D" & (N + 1)).Value Next 'Сдвинуть вверх ячейки от D1 до D10 (пропадает первое значение) Sheets("Лист1").Cells(1, 1) = 13 ^ 64 'Записать в ячейку A1 листа "Лист1" большое числовое значение
Ну и немного более законченного кода.
Попробуйте скопировать в VBA и выполнить эти 2 несложных программы, и начальный опыт программирования в нём у Вас появится :)
Первая программа может быть назначена кнопке и позволяет ввести из столбца A текущего рабочего листа столько числовых значений, сколько их там набрано, но не больше 100.
Полученные значения заносятся в массив A, заполнение прекращается по достижении пустой ячейки, ячейки, заполненной не числом или когда набрано 100 элементов.
Затем от введённых чисел рассчитывается сумма и записывается в ячейку B6.
Sub Кнопка5_Щелчок() 'Кнопка Массив Dim A(100) As Double Dim N As Integer N = 0 For I = 1 To 100 V = Range("A" & I).Value If (IsEmpty(V) Or IsNumeric(V) = False Or N > 100) Then MsgBox "Введено чисел : " & N Exit For End If A(I) = V N = N + 1 Next I Dim S As Double S = 0 For I = 1 To N S = S + A(I) Next I Range("B6").Value = S End Sub
Вторая программа предполагает, что в ячейках B12 и B13 рабочего листа записаны 2 даты. Это могут быть строки, интерпретируемые Вашим Excel как даты, например, 01.01.2001
или даты, полученные формулой, скажем, =СЕГОДНЯ()
По нажатию кнопки даты проверяются, если двух дат не найдено, выводится сообщение и программа завершается.
В противном случае мы вычисляем и выводим в ячейку B16 количество дней между датами, а в C12 и C13 - дни недели по русски. Добавьте небольшое оформление и получите простейший калькулятор дат:
Sub Кнопка4_Щелчок() 'Кнопка Вычислить Dim d1, d2 As Date d1 = Range("B12").Value d2 = Range("B13").Value 'Обработка ошибок ввода (IsNumeric для чисел) If (IsDate(d1) = False Or IsDate(d2) = False) Then MsgBox "Введите 2 даты", vbOKOnly, "Ошибка" Exit Sub End If 'Расчёты Days = DateDiff("d", d1, d2, vbMonday) Range("B16").Value = Days 'Прошло дней Dim A(7) As String A(1) = "Пн": A(2) = "Вт": A(3) = "Ср" A(4) = "Чт": A(5) = "Пт": A(6) = "Сб" A(7) = "Вс" wd1 = Weekday(d1, vbMonday) 'Дни недели Range("C12").Value = A(wd1) wd2 = Weekday(d2, vbMonday) Range("C13").Value = A(wd2) End Sub
пример "интерфейса" для макроса VBA
09.05.2018, 10:56 [11868 просмотров]