Создание собственной функции
Если же из указанного списка вы не нашли нужную функцию, то вам нажна UDF. UDF (User Defined Function) - это функция, которую Вы создаете сами при помощи VBA. А теперь создадим свою собственную функцию, на основе VBA. Разберём на простом примере, в котором функция должна будет прибавлять НДС к некоторой сумме.
Заходим в окно VB (Alt+F11), нажимаем Insert и добавляем новый модуль Module.
Синтаксис записи функции в VB следующий
Function name [(arglist)] [As type]
[statements]
[name = expression]
[Exit Function]
[statements]
[name = expression]
End Function
Function - объявление функции name - имя функции (подчиняются тем же правилам, что и имена в процедурах Sub); [(arglist)] [As type] - аргументы функции и их тип (иногда их не указывают и они принимают значение variant); [statements] - Любая группа инструкций для выполнения в рамках процедуры Function; [name = expression] - присвоение функции значение, которое она должна возвращать; [Exit Function] - В случае, если результат достигнут раньше, то можно выйти из функции, первоначально присвоив name нужное значение; End Function - конец функции.
Теперь создадим небольшую табличку, в которой мы и будем применять нашу функцию.
В столбик Цена с НДС мы запишем нашу функцию, которую мы создадим. Теперь в новом созданном модуле запишем следующий код:
Function Cena_S_NDS(Cena_bez_NDS As Long)
Cena_S_NDS = Cena_bez_NDS * 1.18
End Function
Из данной процедуры видно, что имя нашей функции Cena_S_NDS, которая присваивает значение цены без НДС и умножает на 1,18. Вот вообщем, то и вся формула. Теперь нам надо её применить в созданной таблице. Для этого ставим курсор в столбец Цена с НДС и нажимаем на строке формул на функцию.
После этого появится окно со всеми функциями, которые имеются в данном документе. Мы выбираем из выпадающего списка пункт "Определённые пользователем".
При этом появится функция, которую мы создали.
Выбираем созданную функцию и нажимаем ОК. Теперь нам появилось новое окно, в котором необходимо указать переменную, которую мы объявили. В данном случае - это Цена без НДС.
Ну вот теперь копируем получившуюся формулу в каждую ячейку таблицы, где необходимо посчитать НДС и дело сделано.
Цифры прописью
Теперь рассмотрим другой пример. Создадим функцию, которая будет определять цифры от 1 до 10 и записывать их прописью.
Для этого в модуле запишем следующий код:
Function Cifra(C As Variant)
Select Case C
Case 1
Cifra = "Один"
Case 2
Cifra = "Два"
Case 3
Cifra = "Три"
Case 4
Cifra = "Четыре"
Case 5
Cifra = "Пять"
Case 6
Cifra = "Шесть"
Case 7
Cifra = "Семь"
Case 8
Cifra = "Восемь"
Case 9
Cifra = "Девять"
Case 10
Cifra = "Десять"
Case Else
Cifra = "Введены некорректные данные"
End Select
End Function
В связи с тем, что пользователь может записать вместо цифры буквы, то описываем переменную Сзначением Variant. Теперь остаётся только в ячейку В2 записать нашу функцию и всё будет готово.
Безусловно всем хотелось отобразить процесс выполнения макроса, сделать вот такой или что-то на подобии Progressbar.
Рассмотрим это на простом примере. Пусть у нас будет некоторый цикл, который будет заполнять ячейки на листе какими-то данными. А прогрессбар будет нам отображать процесс выполнения, тобишь мы сможем проследить сколько осталось времени до конца выполнения макроса.
Создадим форму, которую назовём Progressbar, на неё добавим два объекта - Label1 и Label2. Label1 закрасим, например, в синий цвет и удалим весь текст из него - это и будет наша шкала, которая будет отображать процесс загрузки. В Label2 напишем Процесс загрузки данных:
Теперь создадим на листе 1 кнопку Загрузить данные, в коде которой будем вызывать созданную нами форму.
Private Sub CommandButton1_Click()
Progressbar.Show
End Sub
Элементы формы Label находятся на панели инструментов Toolbox
Теперь добавим в книгу модуль, в котором будет написан код, процесс выполнения которого мы и будем отслеживать.
Итак, пусть это будет обыкновенный цикл, который ставит ноли в ячейки (в очень много ячеек). Например такой:
Sub Zapolnenie()
Dim a As Byte
Dim b As Byte
For a = 1 To 250
For b = 1 To 250
Worksheets("Лист1").Cells(a, b) = 0
Next b
Next a
End Sub
Теперь наша задача сделать так, чтобы нам появлялась форма и показывала процесс выполнения макроса.
Хочу обратить внимание на то, что я не случайно взял диапазон от 1 до 250. 250 - это ширина Label1 (синего цвета). Т.е. при выполнения выше указанной процедуры значение a будет передаваться на форму и будет отвечать за ширину Label1. Тобишь при увеличении в цикле значения абудет увеличиваться ширина Label1, т.е. она будет показывать процесс выполнения макроса.
Но пока что кнопка Загрузить данные никак не связана с самим циклом. Для этого мы в коде формы должны записать процедуру, которая будет запускать цикл Zapolnenie расположенный на модуле. Т.е. при запуске формы надо как-то активировать форму, для этого используем команду UserForm_Activate в названии макроса.
Теперь при активации формы у нас будет запускаться макрос с названием Zapolnenie
Private Sub UserForm_Activate()
Call Zapolnenie
End Sub
Изменим код на модуле. Добавим несколько строк, которые и будут передавать данные на загрузившуюся форму. Конечный код будет выглядеть так:
Sub Zapolnenie()
Dim a As Byte
Dim b As Byte
For a = 1 To 250
Progressbar.Label1.Width = a
Progressbar.Repaint
For b = 1 To 250
Worksheets("Лист1").Cells(a, b) = 0
Next b
Next a
Worksheets("Лист1").Range("A1:IP250") = ""
Unload Progressbar
End Sub
Как видно из кода мы добавили три строки.
Progressbar.Label1.Width = a - передаёт значение на форму и ширина Label1 изменяется; Progressbar.Repaint - обновляем форму. Если это упустить, то мы не сможем наблюдать за процессом заполнения; Unload Progressbar - выгружаем форму после завершения загрузки данных; Worksheets("Лист1").Range("A1:IP250") = "" - очищаем ячейки от нолей.
Ну вот. Теперь всё работает так как мы хотели. Если кто-то что-то не понял, всегда можно скачать пример и разобраться. В видеоролике продемонстрировано как это всё работает