Курсовая работа на тему пользовательские функции VBA

Новоуренгойский филиал Профессионального образовательного учреждения

«Уральский региональный колледж»

КУРСОВАЯ РАБОТА

Пользовательские функции VBA

МДК 01.02 ПРИКЛАДНОЕ ПРОГРАММИРОВАНИЕ

09.02.03 Программирование в компьютерных системах

Обучающегося гр. П-370           ________          Кутепова Даниила Геннадьевича

16.11.2020

Оценка за выполнение и защиту курсовой работы                         _____________

Проверил                                   __________          Карасова Аида Абдулкадыровна

21.11.2020

Новый Уренгой, 2020

СОДЕРЖАНИЕ

ВВЕДЕНИЕ                                                                                                3

1.АНАЛИЗ ПРЕДМЕТНОЙ ОБЛАСТИ                                                           4

1.1 Программирование базы данных на VBA                                  6

1.2 Среда разработки VBA (Visual Basic for Applications                               7

1.3 Типы данных в языке VBA                                                                          9

1.4 Функции языка VBA                                                                                    9

2. КОНЦЕПТУАЛЬНОЕ ПРОЕКТИРОВАНИЕ                                              11

2.1. Пользовательские функции VBA                                                              11

 2.2. Создание пользовательской функции VBA                                            15

2.3. Пользовательские функции в расчетах                                                    16

ЗАКЛЮЧЕНИЕ                                                                                                 18

СПИСОК ЛИТЕРАТУРЫ                                                                                19

Введение

В своей курсовой работе я расскажу о пользовательских функциях и их создания в Visual Basic for Applications.

Целью курсовой работы является изучение программирования базовых алгоритмов на языке Visual Basic for Applications (MS Excel), получение знаний и навыков по работе с объектами в офисных программах MS Excel и MS Word.

Для достижения цели были проставлены следующие задачи:

1. Рассмотреть предметную область.                                                                                                                                   2. Определить назначение пользовательских функций.                                                                                3. Показать технологию создания пользовательских функций.

1.Анализ предметной области

VBA (Visual Basic for Applications) — это диалект языка Visual Basic, расширяющий возможности Visual Basic и предназначенный для работы с приложениями Microsoft Office и другими приложениями от Microsoft и третьих фирм.

В принципе, как это не удивительно, при программировании в Office можно вполне обойтись без языка VBA. Подойдет любой COM-совместимый язык, например, обычный Visual Basic, VBScript, JScript, C++, Delphi, Java и т.п. Можно использовать и .NET-совместимые языки программирования — VB.NET, C# и т.п. Все возможности объектных моделей приложений Office вполне можно будет использовать. Например, если сохранить следующий код в файле с расширением *.vbs и запустить его на выполнение, то будет запущен Word, в нем открыт новый документ и впечатан текст:

Главная причина проста — язык VBA встроен в приложения Office (и не только), и код на языке VBA можно хранить внутри документов приложений Office — документах Word, книгах Excel, презентациях PowerPoint и т.п. Конечно же, этот код можно запускать оттуда на выполнение, поскольку среда выполнения кода VBA (на программистском сленге — хост) встроена внутрь этих приложений.

В настоящее время VBA встроен:

•        во все главные приложения MS Office — Word, Excel, Access, PowerPoint, Outlook, FrontPage, InfoPath;

•        в другие приложения Microsoft, например, Visio и M icrosoft Project;

•        в более чем 100 приложений третьих фирм, например, CorelDraw и CorelWordPerfect Office 2000, AutoCAD и т.п.

У VBA есть также множество других преимуществ:

VBA — универсальный язык. Освоив его, вы не только получите ключ ко всем возможностям приложений Office и других, перечисленных выше, но и будете готовы к тому, чтобы:

•        создавать полноценные приложения на Visual Basic (поскольку эти языки — близкие родственники);

•        использовать все возможности языка VBScript (это — вообще урезанный VBA). В результате в вашем распоряжении будут универсальные средства для создания скриптов администрирования Windows (об этом — в конце курса), для создания Web-страниц (VBScript в Internet Explorer), для создания Web-приложений ASP, для применения в пакетах DTS и заданиях на MS SQL Server, для создания серверных скриптов Exchange Server и многое-многое другое.

VBA изначально был ориентирован на пользователей, а не на профессиональных программистов (хотя профессионалы пользуются им очень активно), поэтому создавать программы на нем можно очень быстро и легко. Кроме того, в Office встроены мощные средства, облегчающие работу пользователя: подсказки по объектам и по синтаксису, макрорекордер и т.п.

При создании приложений на VBA вам, скорее всего, не придется заботиться о установке и настройке специальной среды программирования и наличии нужных библиотек на компьютере пользователя — MS Office есть практически на любом компьютере.

Несмотря на то, что часто приложения VBA выполняются медленнее, чем бы вам хотелось, они не ресурсоёмки и очень хорошо работают, например, на сервере терминалов. Но, как правило, для программ на VBA особых требований про производительности и нет: для написания игр, драйверов, серверных продуктов они не используется. По моему опыту, чаще всего проблемы с производительностью VBA-приложений — это не проблемы VBA, а проблемы баз данных, к которым они обращаются. Если проблемы действительно в VBA (обычно тогда, когда вам требуется сложная математика), то всегда есть возможность написать важный код на C++ и обращаться к нему как к обычной библиотеке DLL или встраиваемому приложению (Add-In) для Word, Excel, Access и т.п.

Программы на VBA по умолчанию не компилируются и поэтому вносить в них исправления очень удобно. Не нужно разыскивать исходные коды и перекомпилировать программы.

В среде программистов-профессионалов считается, что самый короткий путь "с нуля" и программ типа "Hello, World" до профессиональных программ, которые делаются под заказ — именно через связку Office- VBA (а конечно, не через C++, Java или Delphi).

1.1 Программирование базы данных на VBA.

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

Программное обеспечение, которое отвечает за выполнение основных действий по извлечению информации, которая содержится в одном или нескольких файлах базы данных, называется ядром базы данных. Ядро базы данных часто называют просто базой данных, хотя согласитесь, что называть программное обеспечение, управляющее информацией в базе данных, и саму базу данных одним термином несколько нелогично.

Хотя эта программа и предназначена для выполнения различных функций с базами данных, Access нельзя назвать ядром базы данных. Access- это "клиент" (front end), который показывает вам, какие именно команды и данные доступны, а также воспринимает ваши инструкции по отношению к информации, такие как ее изменение, добавление или удаление. Все эти инструкции передаются серверной части (back end) СУБД.

Стандартным ядром баз данных для Access является Microsoft Jet. Стандартные файлы баз данных Access (вы всегда сможете определить их по расширению .mdb) на самом деле являются файлами Jet. Работая через Jet, другие инструменты разработки, такие как Visual Basic, могут получить из файлов баз данных все необходимые сведения.

Jet - предназначено для обслуживания индивидуальных пользователей и небольших рабочих групп. Для решения более серьезных задач, уровня предприятия, например, предназначены другие ядра баз данных. Сюда относятся еще один программный продукт компании Microsoft - SQL Server, а также предложения от других ведущих поставщиков, таких как Oracle и Informix. Версия SQL Server для разработчика, которая называется SQL Server 2000 Desktop Engine, поставляется в составе Access 2002. Она позволяет вам разрабатывать базирующиеся на технологиях SQL Server приложениях баз данных прямо на своем компьютере, а затем передавать их "настоящей" версии SQL Server, работающей в сети.

В любом случае, всегда можно запутаться, когда дело касается программного обеспечения клиентной и серверной частей СУБД. Работая с Access или любым другим VBА -приложением, вы можете взаимодействовать с различными ядрами баз данных, руководствуясь прежде всего вашими конкретными потребностями. Access предоставляет больше возможностей программисту баз данных, но вы можете использовать Word, CorelDraw или любое другое поддерживающее VBA приложение, и получить при этом не менее превосходные результаты.

1.2 Среда разработки VBA (Visual Basic for Applications)

Из всего множества различных алгоритмически полных языков программирования в данном курсе рассматривается ориентированный на офисное программирование язык Visual Basic (VB). Более трёх десятилетий тому назад фирма Microsoft приняла решение оснащать популярные офисные приложения – такие как MS Excel, MS Word и другие – встроенными средствами высокоуровневого программирования. С помощью этого программирования предполагалось создавать так называемые макросы (макрокоманды). Назначение этих макросов – повысить функциональные возможности электронных таблиц Excel или документов Word. Макросы – это дополнительные инструменты, не входящие в стандартный арсенал приложений MS Excel, MS Word и других, которые позволяют более гибко, наглядно и быстро решать те или иные офисные задачи. Простой пример: в бухгалтерское приложение могут быть добавлены кнопки, позволяющие переводить денежные величины, представленные в цифровой форме, в форму «прописи», и наоборот (23 133 500 р. 40 к.<=> «двадцать три миллиона сто тридцать три тысячи пятьсот рублей 40 коп.). В современных версиях указанных приложений фирмы Microsoft используется язык VBA (Visual Basic for Applications) – ставший классическим диалект языка VB6 [1], разработанного этой фирмой в конце 90-х годов для создания автономных Windows-приложений. В языке VBA (в отличие от VB6) отсутствуют многие возможности, необходимые для автономных приложений, например, графика. Эти возможности с лихвой заменяются собственным богатым арсеналом средств указанных приложений, не требующих никакого программирования, средств построения 2D и 3D диаграмм. Но программирование может понадобиться даже в случае работы со «стандартным арсеналом», например, если появится необходимость нестандартным способом управлять указанными диаграммами: вращать их, динамично изменять параметры функций и т.д.

1.3 Типы данных в языке VBA

Можно утверждать, что в большинстве случаев на языке VBA for Excel, VBA for Word и т.д. создаются программы, оформляемые как макрокоманды (макросы), предназначенные для обработки данных. Данные могут быть разного типа – как числового, так и нечислового. При этом, как правило, невозможно производить действия, предназначенные для данных одного типа, для данных другого типа. Например, нельзя применять арифметические операции к данным строкового типа; нельзя применять логические операции к числовым данным. Впрочем, «неожиданно» можно, например, удачно применить арифметическую операцию сложения не к числам, а к датам. Бывает и так, что результат какого-то действия выходит за пределы диапазона возможных значений, определяемых типом этого результата. Поэтому перед началом преобразования алгоритма в программу важно внимательно изучить используемые в языке VBA типы данных.

1.4 Функции языка VBA

Функция в программировании, или подпрограмма — фрагмент программного кода, к которому можно обратиться из другого места программы. В большинстве случаев с функцией связывается идентификатор, но многие языки допускают и безымянные функции. С именем функции неразрывно связан адрес первой инструкции (оператора), входящей в функцию, которой передаётся управление при обращении к функции. После выполнения функции управление возвращается обратно в адрес возврата — точку программы, где данная функция была вызвана.

Функция может принимать параметры и должна возвращать некоторое значение, возможно пустое. Функции, которые возвращают пустое значение, часто называют процедурами. В некоторых языках программирования объявления функций и процедур имеют различный синтаксис, в частности, могут использоваться различные ключевые слова.

Функция должна быть соответствующим образом объявлена и определена. Объявление функции, кроме имени, содержит список имён и типов передаваемых параметров (или: аргументов), а также, тип возвращаемого функцией значения. Определение функции содержит исполняемый код функции. В одних языках программирования объявление функции непосредственно предваряет определение функции, в то время как в ряде других языков необходимо сначала объявить функцию, а уже потом привести её определение.

В языке VBA существует несколько видов выражений и функций. Здесь же будет рассмотрен только один вид выражений, которые называются строковыми (или текстовыми), функции обработки строк и маленькое подмножество так называемых системных функций: функция InputBox – ввода данных пользователем, а также функция MsgBox – выдачи сообщения пользователю. Отметим, что функция MsgBox может использоваться не только как функция, но и как встроенная процедура.

2. КОНЦЕПТУАЛЬНОЕ ПРОЕКТИРОВАНИЕ

2.1 Пользовательские функции VBA

Пользовательские функции (как и макросы) записываются на языке программирования Visual Basic для приложений (VBA). Они отличаются от макросов двумя вещами. Во-первых, в них используются процедуры Function, а не Sub. Это значит, что они начинаются с оператора Function, а не Sub, и заканчиваются оператором End Function, а не End Sub. Во-вторых, они выполняют различные вычисления, а не действия. Некоторые операторы (например, предназначенные для выбора и форматирования диапазонов) исключаются из пользовательских функций. Для создания функций и макросов используется редактор Visual Basic (VBE), который открывается в отдельном окне, (рис. 1)

(рис. 1). Редактор VBA

2.2 Пользовательские функции в расчетах.

- Выделить ячейку для результата на листе Excel.

- На вкладке “Формулы” [Formulas], вставить функцию [Insert Function].

- Выбрать категорию определенные пользователем, [User Defined] и найти в ней функцию.

- Заполнить аргументы функции, ОК.

(рис. 2). Пользовательская функция

Использование пользовательских функций в рабочих листах.

1.     Набрать имя функции;

2.     Вывод intellisense.(рис.3)

                               (рис. 3) Вывод intellisense

Для того, чтобы функция отображалась на рабочем листе, она должна быть функцией Public. Также можно использовать диалоговое окно «Вставить функцию» для вставки пользовательской функции. Это будет работать только для публичных функций. На вкладке «Данные», нажать «Вставить функцию».(рис. 4)

(рис 4) Панель инструментов

В диалоговом окне «Вставка функции» выберите «Определено пользователем» в качестве категории. Эта опция отображается только тогда, когда есть функция в редакторе VB (и функция Public).(рис.5)

      (рис. 5) Диалоговое окно

 Выбрать функцию из списка всех общедоступных пользовательских функций. Нажать кнопку ОК вышеуказанные шаги вставят функцию в лист. Он также отображает диалоговое окно «Аргументы функции», которое предоставит подробную информацию об аргументах и результате. (рис.6)

(рис.6) Итоговое диалоговое окно.

2.3 Создание пользовательской функции VBA.

 Для создания функции VBA необходимо выполнить следующие шаги:

1.     Объявить процедуру как функцию;

2.      Определить ее аргументы и их тип данных;

3.      Добавить код для расчета желаемого значения;

Необходимо создать функцию, которая может возвращать название дня недели из значения даты, эту функцию VBA, используя следующие шаги.

Прежде всего, нужно открыть редактор VB с помощью сочетания клавиш ALT + F11 или перейти на вкладку «Разработчик» и нажать кнопку «Visual Basic». (рис. 7)

         (рис.7. Панель инструментов)

Вставка модуля: щелкнуть правой кнопкой мыши в окне проекта VBA, затем перейти к Insert и после этого нажать «Module». (рис. 8)

(рис. 8. Module)

Следующим шагом является определение имени для функции, с помощью функции «myDayName». У меня получилось «Function mydayName». (рис. 4)

(рис. 9). Function mydayName

Поскольку происходит создание функцию VBA, то используется слово «Function». Excel понимает, что этот код следует рассматривать как функцию.

После этого, нужно определить аргументы для пользовательской функции. Нужно открыть начальные скобки «InputDate As Date». (рис. 10)

(рис. 10) Функция InputDate As Date

InputDate — имя аргумента, а date — тип данных. В первую очередь определить тип данных для аргумента.

При закрытии скобки «As String». Определяется тип данных результата, возвращаемого функцией. Так как нужно, чтобы название дня недели было текстовым, его тип данных должен быть «String». (рис. 11)

(рис. 11) Функция string.

На этом этапе имя функции, ее аргумент, тип данных аргумента и тип данных функции определены. (рис. 12)

(рис. 12) Определение функции.

 «Function» и «End Function» нужно составить расчет.

В Excel есть функция рабочего листа под названием «Text», нужно использовать ее здесь. Для этого поможет следующий код:

1 MyDayName = WorksheetFunction.Text (InputDate, “dddddd”).

С помощью этого кода определяется значение, которое должно быть возвращено функцией. (рис. 13)

(рис. 13) Реализация функции.

 Закрывается редактор VB и возвращает к рабочему листу. В ячейке B2 я ввел «= myDayName (A2)», Enter,  название дня недели. (рис. 14)

(рис. 14) Результат функции.

Заключение

В данной работе приведён пример базовых алгоритмов на языке Visual Basic for Applications (MS Excel), получены знания и навыки по работе с объектами в офисных программах MS Excel и MS Word.

Для этого были решены следующие задачи:

1. Рассмотреть предметную область.                                                                                                                                   2. Определить назначение пользовательских функций.                                                                                3. Показать технологию создания пользовательских функций.

Источниками информации для раскрытия данной темы послужили методические пособия и другие.

VBA - это инструмент, управляемый событиями, что означает, возможность использовать его, чтобы сообщить компьютеру, что нужно инициировать действие или последовательность действий. При тестировании задач  не было обнаружено ошибок и недочётов. В следствии этого можно сделать выводы, что поставленные цели и задачи были выполнены, навыки изучены и закреплены.

СПИСОК ИСПОЛЬЗУЕМОЙ ЛИТЕРАТУРЫ

1.     https://excelpedia.ru/makrosi-v-excel/sozdanie-polzovatelskoj-funkcii

2.     https://support.microsoft.com/ru-ru/office/создание-пользовательских-функций-в-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f

3.     https://www.planetaexcel.ru/techniques/3/59/

4.     https://baguzin.ru/wp/polzovatelskie-funktsii-vba/

5.     https://astarcode.ru/?yclid=6765227525692745548

6.     https://www.cyberforum.ru/vba/thread1009889.html