Разработка многопользовательских приложений на vba excel

Принципы разработки приложений на VBA

15.1. Среда разработки – редактор Visual Basic

Независимо от приложения в редактор VB можно перейти:

• нажав кнопку Visual Basic в группе Код вкладки Разработчик;
• с помощью функциональных клавиш Alt-F11.

Возврат в офисный документ из редактора Visual Basic выполняется:

• последней командой меню View, которая указывает на название офисного приложения, например, командой Microsoft Excel;
• нажатием на первую кнопку стандартной панели инструментов Visual Basic, определяющую приложение;
• клавишами Alt-F11 или Alt-Q.

Окна редактора VB

Основные компоненты разработки, отладки и запуска программ – это окна редактора VB. На рис.15.1 показан вид среды разработки MS Excel. Среда разработки в других офисных приложениях выглядит примерно так же.

Project Window (окно проекта)

Программные компоненты документа (модули, процедуры, формы) объединяются в проект, который сохраняется на диске вместе с документом MS Office.

Проект на VBA нельзя создать независимо от документа. Каждому документу соответствует проект с именем, состоящим из двух частей: первая часть – VBAProject для MS Excel и MS PowerPoint и Project для MS Word, вторая часть – название документа в скобках. Подобное имя присваивается проекту по умолчанию.

Основное предназначение окна проекта (рис.15.1 и рис.15.2) – обеспечение навигации в проекте. Проект имеет иерархическую структуру, представляющую в виде папок все составляющие проекта: модули (Modules), формы (Forms), объекты приложения (например, Microsoft Excel Objects для MS Excel), пользовательские классы (Classes) и ссылки на другие проекты (References).

Показать окно проекта можно при помощи:

• нажатия на кнопку стандартной панели инструментов VBA;
• команды Project Explorer меню View;
• клавиш Ctrl+R.

Окно кода программы (Code)

В окне кода программы (рис.15.3) отображаются тексты процедур объекта, выделенного в окне проекта. Программа, управляющая объектом проекта, располагается на связанном с объектом процедурном листе, называемом «модуль – обработчик событий». Программы, не связанные с конкретными объектами, располагаются в стандартных модулях папки проекта Modules. В дальнейшем будем использовать термин «модуль», имея в виду, что все сказанное распространяется на модуль – обработчик событий и на стандартный модуль, если иное не оговорено особо.

• двойным щелчком на элемент в окне проекта;
• командой Code из меню View или нажатием клавиши F7 после выделения элемента проекта.

Редактор программ позволяет просматривать и редактировать процедуры модуля. Процедура – это последовательность операторов языка, начинающаяся с оператора Sub и заканчивающаяся оператором End Sub. Все операторы, заключенные между этими двумя операторами, составляют тело процедуры. Дополнительно к процедурам в начале модуля могут располагаться инструкции компилятору, операторы объявления глобальных и модульных переменных или переменных пользовательских типов.

В окне программы может отображаться текст одной процедуры (procedure view) или текст модуля целиком (full-module view). Переключение режимов просмотра выполняется нажатием соответствующей кнопки в левом нижнем углу окна программы (рис.15.3).

Вверху окна программы расположены два списка: список объектов слева и список процедур справа. Содержание списков зависит от типа объекта, для которого высвечено окно программы.

Если это стандартный модуль, то список объектов содержит только одну строку – General, а список процедур – строку Declarations и перечень процедур модуля.

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

Если окно программы отображает процедурный лист для формы или объекта документа (рис.15.3), то дополнительно к строке General список объектов содержит перечень объектов внутри выбранной формы или объекта документа. При выделении объекта в списке справа отражается перечень событий, связанных с выделенным объектом. На рис.15.3 в окне программы, открытом для рабочей книги, в списке объектов выделен объект Listbox с именем lst_sheet, и для него открыт перечень событий.

В отличие от текстовых редакторов редактор кода программы (или просто редактор кода) не только отображает вводимый текст, но и автоматически вносит изменения. Настройка окна редактора кода выполняется на вкладке Editor команды Tools-Options (рис.15.4).

• изменяет написание ключевых слов, устанавливая прописную букву в начале слова;
• проверяет синтаксис операторов;
• высвечивает подсказку по функциям, свойствам и методам объекта;
• меняет цвет слов для команд, комментариев, синтаксических ошибок в соответствии с установками, сделанными на вкладке Editor Format.

Текст (код) программы можно создать в любом текстовом редакторе и вставить его в модуль, используя команды копирования и вставки.

Окно свойств объектов (Properties Window)

Свойство – это некий атрибут объекта, например, его название. Окно свойств вызывается командой Properties меню View или нажатием на пиктограмму . Окно свойств высвечивает и позволяет изменять свойства объекта, выделенного в окне проекта или в окне формы. Если выделен стандартный модуль или проект, то в окне свойств отображается его единственное свойство – Name (имя). Если же выделен, например, объект Userform, то в окне свойств (рис.15.5) показана таблица с перечнем его свойств.

Свойства можно просматривать в алфавитном порядке (вкладка Alphabetic) или с группировкой по категориям (вкладка Categorized ).

Свойства объектов можно изменять в режиме конструктора (Design time) или в режиме выполнения программы (Run time). Некоторые свойства объектов можно менять в любом режиме, а отдельные свойства подлежат изменению только в режиме конструктора или только в режиме выполнения программы. Если свойства объектов меняются только в режиме выполнения, то они никогда не видны в окне свойств объекта.

Остальные окна редактора Visual Basic, перечисленные ниже, рассматриваются в разделах, которые связаны с высвечиванием информации в них:

Разработка приложения на visual basic

Курсовое проектирование по информатике:

«Разработка приложения для обработки экономической информации в среде Microsoft Excel на языке Visual Basic for Applications».

Содержание.

Алгоритмы подпрограмм, тексты программ на языке Visual Basic.

Часть1………………………………………………………………………………………….……6

Задание2………………………………………………………………………………………….…..9

Задание3………………………………………………………………………………………….…12

Задание4……………………………………………………………………………………….……15

Часть2……………………………………………………………………………………….……16

Задание1………….………………………………………………………………………….……..17

Задание2……………………………………………………………………………………………20

Задание3……………………………………………………………………………………………21

Заключение………………………………………………………………………………………22

Введение.

Данный курсовой проект предназначен для развития навыков в области разработки программного обеспечения для обработки экономической информации с помощью средств Visual Basic for Applications. Курсовая работа должна отвечать следующим требованиям:

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

· Создание удобного пользовательского интерфейса.

· Возможность работы в режиме диалога.

· Обработка массива методами структурного программирования.

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

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

Проект состоит из трех пользовательских форм: одна выводит информацию о студенте, две другие соответствуют заданиям первой и второй части курсовой. Все программы содержатся в процедурах нажатия кнопок. Курсовая работа состоит из двух частей. Все задания сопровождаются текстами на языке Visual Basic, пояснительными комментариями, результатами работы программы. К первой все задания сопровождаются алгоритмами. Задания 1,2,3,4 части1 и задание3 части2 выполняются с помощью написанных на языке Visual Basic программ. Задания 1 и 2 части2 выполняются с помощью средств Excel. Сама курсовая работа разработана в системе Office2000, поэтому на некоторых старых версиях Office97 могут появляться ошибки. Курсовой проект размещен на диске А в файле «Курсовая_17». Диск А приложен к оформленной курсовой работе.

Переменные проекта

Первоначально есть информация о расчете заработной платы 20 работников предприятия 20 человек, которая находится в таблице Excel. Для работы с этими данными в проекте задается собственный тип пользователя dann, который состоит из значений следующего типа:

fio As String * 20 – ФИО работников предприятия, строковое значение, длина максимум 20 символов.

Pol As String – пол работников, строковое значение.

stag As Single – стаж работников, тип Single.

otd As String – отдел, строковое значение.

dolg As String – должность, строковое значение.

okl As Integer – оклад, тип Integer, целое значение.

nadb As Integer – надбавка к окладу, тип Integer, целое значение.

rakoe As Single – районный коэффициент, тип Single.

kvid As Single – сумма к выдаче, тип Single.

Значения Fio, Pol, Stag, otd, dolg, okl, nadb, rakoe считываются из электронной таблицы Excel, значения kvid вычисляются в процессе работы программы.

Далее заданы следующие переменные, которые используются в проекте:

Public i, j, k, m As Integer – переменные i, j используются для работы в циклах, переменные m и k используются при выводе данных в электронную таблицу.

Public n As String – в переменную n заносится значение отдела при группировке информации по отделам.

Public info(21) As dann – массив типа dann, в нем содержатся данные о 20 работниках предприятия.

Public p As dann – переменная типа dann, используется при упорядочивании информации.

Все эти переменные являются глобальными, используются в нескольких программах и описываются как Public перед модулем1.

Основной алгоритм проекта

Первоначально происходит считывание данных из электронной таблицы, заполняются 20 значений массива info. Затем вычисляются значения сумм к выдаче для каждого работника предприятия. Суммы к выдаче вычисляются путем складывания оклада работника, его надбавки и умножения полученной суммы на районный коэффициент. Полученные значения выводятся в электронную таблицу. После этого открываются пользовательские формы, на которых пользователь должен нажать какую-либо кнопку, т. е. открывается кнопочное меню и нажатие любой кнопки соответствует выбору определенного условия. При нажатии на определенные кнопки запускаются подпрограмма1 и подпрограмма2 (см. ниже) или же может открыться новое кнопочное меню, при нажатии на одну из кнопок которого запускается подпрограмма3, подпрограмма4, рисуется диаграмма или же завершается работа с проектом.

Алгоритмы подпрограмм, тексты программ на языке Visual Basic и инструкция пользователя.

При нажатии какой-либо кнопки запускается необходимая программа, которая содержится в процедуре нажатия кнопки.

Часть1.

Первоначально имеются сведения о 20 работниках предприятия, которые расположены на листе «Данные» электронной таблицы Excel.

Обновляемый многопользовательский макрос

Я не умею программировать, но очень люблю!
Немного измененная цитата художника Васи Ложкина.
Статьей хочу поделиться опытом поднятия многопользовательской системы на VBA Excel.
На момент принятия решения о создании гибкого приложения, было порядка 7 макросов, работающих по большим объемам (несколько файлов от 20 тыс. строк до 370 тыс. строк), весящих от 50 килобайт до 12 мегабайт, каждый из которых был написан в соответствии со знаниями существующими на момент написания. Каждый макрос изменялся, дописывался, исправлялся в части ошибок, а учитывая, что этими макросами пользовались более 60 человек, не все из которых отслеживали изменения, постоянно дергали меня показывая очередную ошибку, которую я уже исправил и выслал на всех. Объяснять толпе народу как правильно пользоваться макросами, я бросил сразу, так как кто то не услышит, кто то не поймет о чем речь, кто-то возьмет уже отформатированную таблицу в работу с макросами, а я не могу предугадать кто и как изменяет таблицы.

Необходимо было сделать один код, а не 60 копий каждого изменения, высланного по почте.

Решение быстро пришло в голову, а гугл быстро выдал мне результаты по программному изменению кода VBA, которая в последствии была откатана на практике. Итак, текст без картинок не интересен, вот первая, это структура приложения, которая выглядет так:

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

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

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

Итак, практическая часть.

Вешаем событие на открытие книги:

Код не хитрый, концепция одна, дать пинок для дальнейших действий
Процедура create_module_for_file:

Три строки три действия, в начале нам необходимо подобрать файл с модулем. За это отвечает следующая функция:

Далее создаем модуль:

И пишем в модуль код из файла

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

По коду:
-для начала удалим, на случай если будет обновление:

Панель создали, теперь создаем непосредственно меню, которое будет создавать кнопки непосредственно по каждому файлу с макросами.

После того как меню создано, добавляются кнопки. Меню строилось по принципу файл -> одна кнопка. В свою очередь по одному файлу с макросом может быть несколько процедур, которые необходимо вызывать, вот в под меню их и суем. Данный код создает 3 кнопки:

Результат примерно вот:

В режиме написания изменил концепцию работы. Задача стояла в использовании последней версии кода при каждом нажатии «выполнить макрос». Учитывая эти обстоятельства, к клиенту будут загружаться определенные файлы, назовем их индексным файлам, то есть постоянно присутствующим после открытия файла модулям, это «menu» и «list action», а непосредственно файлы макросов будут подгружаться по нажатию кнопки. В чем их смысл: файл меню формирует меню 🙂

В меню мы видим событие, ссылающееся на процедуру «action1…x», которая сидит во втором файле «list_action». Посмотрим на его содержимое

Что мы делаем, во-первых нам необходимо удалить модуль для того что бы его заного загрузить и использовать, мало ли код изменен. Далее мы открываем файл, создаем модуль и пишем в него код из файла.
После этих манипуляций нам необходимо вызвать процедуру, выполняющую манипуляции, которые подразумевались нажатием на кнопку выполнения процедуры.
Здесь интересно)
Если мы укажем имя процедуры, которая находится непосредственно в коде выше, у нас процедура action11 ругнется на то что не может найти процедуру «kart_view», и правильно, ведь по факту ее на момент нажатия кнопки исполнения процедуры нет, на этот случай использован лайфхак, создается функция, которая в свою очередь вызывает эту процедуру, код вот:

То есть на момент вызова «kart_view» из «action11a», данная процедура уже будет загружена в модуль, vba подвоха не видит.
Получаемый итог: жмем на кнопку, загружается код в модуль, выполняется. В случае повторного выполнения модуль удаляется, грузится заново, выполняется. При закрытии файла книга отчищается от всего, для того что бы не занимать место и спокойно лежать до следующего открытия.
Еще пару слов по обработке больших файлов, думаю будет интересно. Как говорилось, файлы используются большого объема, более 300 тыс.строк, из которых необходимо выбрать, как правило, тыс. 10-30 и работать с ними.
Если бы меня с пол года назад спросили как сделать выборку, я бы не ведая сказал бы цикл с условием. В последствии было перепробовано много способов, опишу каждый, так, для инфо, кроме тех что можно реализовать стандартными функциями excel.
Исходные данные: есть таблица 300 тыс строк, известно 10 тыс значений, которые нам нужны.
1) Вначале я попытался сделать выборку циклом

Что получаем, если не завершать цикл, получается что нам 10000 раз необходимо перебрать 300000 строк для поиска результата. Отрабатыва vba будет примерно 500 строк в секунду, итого
3000000000/500 = 6000000 секунд, (100000 минут или 1666,66 часов или 69 дней или 9 недель или …)
А результат как правило нужен здесь и сейчас, ну или через пару минут максимум, так что сразу нет.
2) Второй используемый мною способ – работа с mysql сервером
Для работы с mysql сервером, который уже был, для другого проекта, был необходим драйвер mysql ODBC, их мне было известно 2, версия 3.51, и версия 5.1 Выбор пал на 3.51, так как второй, по обсуждениям его работы на форумах, не очень хорошо работал с кодировками, а базу хотелось в utf8
Драйвер мне поставили, и первая кочка, на которой я споткнулся – права доступа

То есть нужна учетная запись для подключения из вне, в нашем случае это будет vba-макрос который матерился так:

Покурив мануалы sql, нашел это

То есть, по запросу выше, создается учетная запись для любого пользователя с рут правами.
Естественно рут прав мне никто не дал и проект использующий mysql сервер для хранения данных, был «заморожен» так как в перспективе нужно было писать в базу данные, у меня было только чтение.
Для оценки эффективности: один и тот же макрос используя переборы с помощью циклов работал 59 секунд, с sql удалось сделать все то же самое за 3,5-4 секунды, то есть реально удалось ускориться более чем в 10 раз.
По коду хорошая статья вот здесь: egregors.blogspot.ru/2013/05/mysql-vba-excel-mysql.html
3) Продолжая курить тему sql, начал работу по работе с access.
Код получается адовый, но рабочий.

Данный способ работает медленнее чем mysql, почему то, но вполне приемлем для использования. Но не мне…
Отказался я от него в первый же раз, когда нужна была система при котором пользователь в определенной папке создает базу и пишет туда 2 таблицы по 300 тыс.строк, для их сравнения и вычисления. При тестах у меня получилась база размером около 600 метров, учитывая что в перспективе этим макросом одновременно может воспользоваться 50 человек, получается папка размером в примерно 25-30 гигов, за которую меня админы четвертовали бы сразу.
4) Собственно способ на котором я остановился. Да, работает медленнее второго и третьего способа, но данные недостатки компенсируются функциональностью. Sql запрос по книге

Вот… Если брать условия примера, на вскидку где то секунд 10 соберется строка для запроса, секунд 25 повисит запрос с выборкой, секунд 10 на отработку в зависимости от необходимого результата.
Как то так.

VBA Excel. Начинаем программировать с нуля

Первое знакомство с редактором VBA Excel, создание процедур (подпрограмм) и написание простейшего кода, работающего с переменными и ячейками рабочего листа.

Знакомство с редактором VBA

1. Создайте новую книгу Excel и сохраните ее как книгу с поддержкой макросов с расширением .xlsm. В старых версиях Excel по 2003 год – как обычную книгу с расширением .xls.
2. Нажмите сочетание клавиш «левая_клавиша_Alt+F11», которое откроет редактор VBA. С правой клавишей Alt такой фокус не пройдет. Также, в редактор VBA можно перейти по ссылке «Visual Basic» из панели инструментов «Разработчик» на ленте быстрого доступа. Если вкладки «Разработчик» на ленте нет, ее следует добавить в настройках параметров Excel.

В левой части редактора VBA расположен проводник проекта, в котором отображены все открытые книги Excel. Верхней строки, как на изображении, у вас скорее всего не будет, так как это – личная книга макросов. Справа расположен модуль, в который записываются процедуры (подпрограммы) с кодом VBA. На изображении открыт модуль листа, мы же далее создадим стандартный программный модуль.

1. Нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку после значка Excel на панели инструментов.

После нажатия кнопки «Module» вы увидите ссылку на него, появившуюся в проводнике слева.

Первая программа на VBA Excel

Добавляем на стандартный модуль шаблон процедуры – строки ее начала и завершения, между которыми мы и будем писать свою первую программу (процедуру, подпрограмму).

1. Откройте стандартный модуль двойным кликом по его ссылке в проводнике. Поместите в него курсор и нажмите кнопку «Procedure…» во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.

В результате откроется окно добавления шаблона процедуры (Sub).

1. Наберите в поле «Name» имя процедуры: «Primer1», или скопируйте его отсюда и вставьте в поле «Name». Нажмите кнопку «OK», чтобы добавить в модуль первую и последнюю строки процедуры.

Имя процедуры может быть написано как на латинице, так и на кириллице, может содержать цифры и знак подчеркивания. Оно обязательно должно начинаться с буквы и не содержать пробелы, вместо которых следует использовать знак подчеркивания.

1. Вставьте внутрь шаблона процедуры следующую строку: MsgBox «Привет» .

Функция MsgBox выводит информационное сообщение с указанным текстом. В нашем примере – это «Привет».

1. Проверьте, что курсор находится внутри процедуры, и запустите ее, нажав клавишу «F5». А также, запустить процедуру на выполнение можно, нажав на треугольник (на изображении под пунктом меню «Debug») или на кнопку «Run Sub/UserForm» во вкладке «Run» главного меню редактора VBA Excel.

Если вы увидели такое сообщение, как на изображении, то, поздравляю – вы написали свою первую программу!

Работа с переменными

Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «’» (апостроф).

Пример 2
Присвоение переменным числовых значений: