Решаем счётные задачи с помощью 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 [11873 просмотра]