Создание собственной функции

Если же из указанного списка вы не нашли нужную функцию, то вам нажна 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") = "" - очищаем ячейки от нолей.

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

 

Заставка приветствия
реализованная на UserForm

· Видеоурок по данной теме

И вот мы сделали программу, которой все пользуются. Но хотелось бы отличиться и приблизить программу к настоящей, полноценной.
Многие замечали, что при открытии большинства программ, нам появляется заставка приветствия (например у Total Commander, AIMP и т.д.). Давайте сделаем подобие, пусть при открытии книги нам на 2 секунды появляется такая вот картинка.

Для этого создадим новый документ, в котором создадим новую форму с именем Zstvk.

Теперь фоном этой формы сделаем нашу картинку. В профиле формы выбираем пункт Picture. И загрузим выбранную картинку. Придаём форме необходимые размеры, пишем в шапке "Приветствие" (строка Caption в профиле).

Чтобы форма появлялась при открытии книги необходимо добавить в книгу макрос, который срабатыват при открытии книги. Производим двойной щелчок мыши по вкладке "Эта книга" и добавляем туда код, который будет вызывать форму.

Private Sub Workbook_Open()     Zstvk.Show End Sub

Для того, чтобы форма появлялась на 2 секунды воспользуемся таймером.
Перейдём в код формы и выберем макрос, который срабатывает при активации формы.

Теперь добавим в этот код таймер.

Private Sub UserForm_Activate()     Application.OnTime Now + TimeValue("00:00:02"), "Vigruzit" End Sub

Vigruzit - это имя макроса, который сработает через 2 секунды. При помощи этого макроса заставка будет автоматически закрываться. Для этого добавим в книгу новый модуль и создадим в нём вот такой макрос.

Sub Vigruzit()     Unload Zstvk End Sub

Ну вот, всё готово. Сохраняем книгу закрываем и открываем. При этом нам появляется созданное окно приветствия.

 

Выпадающий список
ComboBox на UserForm

· Видеоурок по данной теме

Создадим в проекте форму с выподающим списком, в которой будет указан список некоторых сотрудников, которых необходимо "обработать".

Работа с ComboBox

Итак, добавим форму - пункт меню Insert/UserForm, и назовём её к примеру Vibor.
Теперь из панели инструментов добавим на неё ComboBox. Придаём ему необходимые размеры и расположение на форме.
Ну и кнопку добавим на форму, которая и будет обрабатывать данные выпадающего списка.

Выделим мышкой добавленный ComboBox и зададим ему имя Sotrudniki, дабы понятно было.

Добавим на лист в столбец А список произвольных не повторяющихся имён и кнопку, которая будет вызывать созданную форму.
Ниже показан код кнопки находящейся, на листе.

   Private Sub CommandButton1_Click()       Vibor.Show   End Sub

Ну вот, сотрудники готовы. Теперь их как-то надо загрузить в ComboBox, который находится на форме. Для этого заходим в код формы. Щёлкаем правой кнопкой мыши по ней в окне Project-VBAProject и выбираем пункт View Code.

Выбираем из выподающего списка UserForm и Initialize. Это тот код который будет инициализировать форму Vibor при её загрузке.

Теперь воспользуемся обычным циклом, который будет перебирать имена и записывать их в Combobox-Sotrudniki.
Код будет выглядеть таким образом:

   Private Sub UserForm_Initialize()   Dim Imena As String   Dim a As Byte    For a = 2 To 9 Step 1       Imena = Cells(a, 1)       Sotrudniki.AddItem Imena   Next a   End Sub

Imena - это строковая переменная, в которую записываются имена;
a - это числовая переменная, которая равна количеству имён (от 2 до 9, так как имена начинаются со второй строки и заканчиваются девятой строкой);
Sotrudniki.AddItem Imena - добавляет в выпадающий список имена из переменной Imena;
Cells(a, 1) - ячейка с переменной строкой и постоянным столбцом А, из которой заносятся имена в переменную Imena;

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

Для того чтобы орабоать выбранное имя перейдём в код кнопки ОК и разместим следующий код.

   Private Sub CommandButton1_Click() 'Кнопка Ок   If Sotrudniki.Text = "" Then         MsgBox "Сотрудник не выбран", vbCritical, "Ошибка"   Else         MsgBox "Производим обработку данных сотрудника " & _           Sotrudniki.Text, vbExclamation, "Пример"   End If   End Sub

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