готовые формы vba excel

Атанас Йонков Блоггер, Веб-разработчик
yonkov.atanas@gmail.com

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

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

Table of Contents

Как включить макросы в Excel

1. Копирование данных из одного файла в другой.

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

2. Отображение скрытых строк

Иногда большие файлы Excel можно содержать скрытые строки для большей ясности И для лучшего удобства пользователей. Вот один макрос, который отобразит все строки из активной рабочей таблицы:

3. Удаление пустых строк и столбов

4. Нахождение пустых ячеек

13. Создание сводной таблицы

14. Отправка активного файла по электронной почте

Мой любимый код VBA. Он позволяет вам прикреплять и отправлять файл, с которым вы работаете, с предопределенным адресом электронной почты, заголовком сообщения и телом сообщения! Сначала Вам нужно сделать референцию в Excel на Microsoft Outlook (в редакторе Excel VBA, нажмите tools => references и выберите Microsoft Outlook).

15. Вставка всех графиков Excel в презентацию PowerPoint

Очень удобный макрос, который позволяет вам добавлять все ваши графики Excel в презентацию Powerpoint одним щелчком мыши:

16. Вставка таблицы Excel в MS Word

Таблицы Excel обычно помещаются внутри текстовых документов. Вот один автоматический способ экспорта таблицы Excel в MS Word:

17. Извлечение слов из текста

Мы можем использовать формулы, если хотим извлечь определенное количество символов. Но что, если мы хотим извлечь только одно слово из предложения или диапазон слов в ячейке? Для этого мы можем сами создать функцию Excel с помощью VBA. Это одна из самых удобных функций VBA, поскольку она позволяет создавать собственные формулы, которые отсутствуют в MS Excel. Давайте продолжим и создадим две функции: findword() и findwordrev():

Отлично, мы уже создали две новые функции в Excel! Теперь попробуйте использовать их в Excel. Функция = FindWordRev (A1,1) берет последнее слово из ячейки A1. Функция = FindWord (A1,3) берет третье слово из ячейки A1 и т. Д.

18. Защита данных в MS Excel

Иногда мы хотим защитить данных нашего файла, чтобы только мы могли его изменять. Вот как это сделать с VBA:

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

Источник

Автоматизация рутины в Microsoft Excel при помощи VBA

В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2007/2010 (для более старых версий изменяется лишь интерфейс — код, скорее всего, будет таким же) для автоматизации различной рутины.

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

Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, FireStorm.

Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM.

Поэтому, увы, будем учить Visual Basic.

Чуть-чуть подготовки и постановка задачи

Итак, поехали. Открываем Excel.

Для начала давайте добавим в Ribbon панель «Разработчик». В ней находятся кнопки, текстовые поля и пр. элементы для конструирования форм.

Теперь давайте подумаем, на каком примере мы будем изучать VBA. Недавно мне потребовалось красиво оформить прайс-лист, выглядевший, как таблица. Идём в гугл, набираем «прайс-лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста):

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

Результат, которого хотим добиться, выглядит примерно так:

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

Кодим

Для начала требуется создать кнопку, при нажатии на которую будет вызываться наша програма. Кнопки находятся в панели «Разработчик» и появляются по кнопке «Вставить». Вам нужен компонент формы «Кнопка». Нажали, поставили на любое место в листе. Далее, если не появилось окно назначения макроса, надо нажать правой кнопкой и выбрать пункт «Назначить макрос». Назовём его FormatPrice. Важно, чтобы перед именем макроса ничего не было — иначе он создастся в отдельном модуле, а не в пространстве имен книги. В этому случае вам будет недоступно быстрое обращение к выделенному листу. Нажимаем кнопку «Новый».

И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code».

Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:

Напишем Hello World:

Sub FormatPrice()
MsgBox «Hello World!»
End Sub

И запустим либо щелкнув по кнопке (предварительно сняв с неё выделение), либо клавишей F5 прямо из редактора.

Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.

Примеры синтаксиса

Dim res As sTRING ‘ Регистр в VB не важен. Впрочем, редактор Вас поправит
Dim i As Integer
‘ Цикл всегда состоит из нескольких строк
For i = 1 To 10
res = res + CStr(i) ‘ Конвертация чего угодно в String
If i = 5 Then Exit For
Next i

Dim x As Double
x = Val( «1.234» ) ‘ Парсинг чисел
x = x + 10
MsgBox x

On Error GoTo Err ‘ При ошибке перейти к метке Err
x = 5 / 0
MsgBox «OK!»
GoTo ne

ne:
On Error GoTo 0 ‘ Отключаем обработку ошибок

‘ Циклы бывает, какие захотите
Do While True
Exit Do

Loop ‘While True
Do ‘Until False
Exit Do
Loop Until False
‘ А вот при вызове функций, от которых хотим получить значение, скобки нужны.
‘ Val также умеет возвращать Integer
Select Case LengthSqr(Len( «abc» ), Val( «4» ))
Case 24
MsgBox «0»
Case 25
MsgBox «1»
Case 26
MsgBox «2»
End Select

Грабли-1. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1252 Latin-1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin-1, а потом открыть в 1251.

Грабли-2. Т.к. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно.

Грабли-3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции.

Еще немного дополнительных функций, которые могут пригодится: InPos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN.

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

Кодим много и под Excel

В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами.

Sub FormatPrice()
Sheets( «result» ).Cells.Clear
Sheets( «data» ).Activate
End Sub

Работа с диапазонами ячеек

Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.

Примеры работы с Range

Sheets( «result» ).Activate
Dim r As Range
Set r = Range( «A1» )
r.Value = «123»
Set r = Range( «A3,A5» )
r.Font.Color = vbRed
r.Value = «456»
Set r = Range( «A6:A7» )
r.Value = «=A1+A3»

Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:

Для упрощения работы рекомендую определить следующие функции-сокращения:

Function GetCol(Col As Integer ) As String
GetCol = Chr(Asc( «A» ) + Col)
End Function

Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».

Глобальные переменные

Option Explicit ‘ про эту строчку я уже рассказывал
Dim CurRow As Integer
Const GroupsCount As Integer = 2
Const DataCount As Integer = 3

FormatPrice

Sub FormatPrice()
Dim I As Integer ‘ строка в data
CurRow = 1
Dim Groups(1 To GroupsCount) As String
Dim PrGroups(1 To GroupsCount) As String

Теперь надо заполнить массив Groups:

На месте многоточия

И создать заголовки:

На месте многоточия в предыдущем куске

For I2 = 1 To GroupsCount
If Groups(I2) <> PrGroups(I2) Then
Dim I3 As Integer
For I3 = I2 To GroupsCount
AddHeader I3, Groups(I3)
Next I3
Exit For
End If
Next I2

Не забудем про процедуру AddHeader:

Перед FormatPrice

Теперь надо перенести всякую информацию в result

Подогнать столбцы по ширине и выбрать лист result для показа результата

После цикла в конце FormatPrice

Sheets( «Result» ).Activate
Columns.AutoFit

Всё. Можно любоваться первой версией.

Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:

Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:

Поэтому чуть-чуть меняем код с добавлением стиля границ:

Select Case Ty
Case 1 ‘ Тип
.Font.Bold = True
.Font.Size = 16
.Borders(xlTop).Weight = xlThick
Case 2 ‘ Производитель
.Font.Size = 12
.Borders(xlTop).Weight = xlMedium
End Select
.Borders(xlBottom).Weight = xlMedium ‘ По убыванию: xlThick, xlMedium, xlThin, xlHairline
End With
CurRow = CurRow + 1
End Sub

Осталось лишь добится пропусков перед началом новой группы. Это легко:

В начале FormatPrice

Dim I As Integer ‘ строка в data
CurRow = 0 ‘ чтобы не было пропуска в самом начале
Dim Groups(1 To GroupsCount) As String

В цикле расстановки заголовков

If Groups(I2) <> PrGroups(I2) Then
CurRow = CurRow + 1
Dim I3 As Integer

В точности то, что и хотели.

Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки «ID, Название, Цена» в результат. Подсказка: CurRow = 0 CurRow = 1.

Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.

Спасибо за внимание.

Буду рад конструктивной критике в комментариях.

UPD: Перезалил пример на Dropbox и min.us.

UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.

Источник

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

Введение

В принципе, существует великое множество языков программирования (Pascal, Fortran, C++, C#, Java, ASP, PHP. ), но для всех программ пакета Microsoft Office стандартом является именно встроенный язык VBA. Команды этого языка понимает любое офисное приложение, будь то Excel, Word, Outlook или Access.

Способ 1. Создание макросов в редакторе Visual Basic

К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:

Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:

Обычный макрос, введенный в стандартный модуль выглядит примерно так:

Давайте разберем приведенный выше в качестве примера макрос Zamena:

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

Способ 2. Запись макросов макрорекордером

Чтобы включить запись необходимо:

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .

Запуск и редактирование макросов

Создание кнопки для запуска макросов

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

Кнопка на панели инструментов в Excel 2003 и старше

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

Кнопка на панели быстрого доступа в Excel 2007 и новее

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :

Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:

Кнопка на листе

Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:

Выберите объект Кнопка (Button) :

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

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

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

Источник

Программирование пользовательских форм VBA Excel

Лабораторная работа 5

Программирование пользовательских форм VBA Excel

Цель работы:приобрести навыки формирования и программирования диалоговых форм для создания баз данных и управления ими средствами Excel и VBA.

Чтобы добиться максимальной гибкости использования элементов управления, создают пользовательские формы — настраиваемые диалоговые окна, которые обычно содержат один или несколько элементов ActiveX. Пользовательские формы вызываются из программного кода VBA, который создается в редакторе Visual Basic.

Общая схема работы по созданию пользовательских форм следующая:

1. Вставить пользовательскую форму в проект VBA книги.

2. Создать процедуру для отображения пользовательской

3. Добавить элементы ActiveX.

4. Изменить свойства элементов ActiveX.

5. Создать процедуры обработчика событий для элементов

Самые важные методы форм:

Show() —запуск формы(UserForm1.Show —если формауже была загружена в память, она просто станет видимой, если еще нет — то будет автоматически загружена (произойдет событие Load);

Hide() —спрятать форму(UserForm1.Hide —форма будетубрана с экрана, но останется в памяти. Потом при помощи метода Show() можно будет опять ее вызвать в том же состоянии, в каком она была на момент «прятанья», а можно, например, пока она спрятана, программно изменять ее и расположенные на ней элементы управления. Окончательно форма удалится из памяти при закрытии документа;

Unload —удалить из памяти,если форма больше точно непотребуется (Unload UserForm1).

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

Создание формы с элементами Label и TextBox

Откроем новую рабочую книгу Excel и перейдем в редактор Visual Basic (Alt+F11). Выберем из меню Вставка (Insert) команду UserForm. На экране появится заготовка диалоговой формы. Вызовем панель инструментов ToolBox (меню View или соответствующая кнопка на панели управления Standart).

Поместим в поле формы объект Label (надпись), под надписью поместим текстовое поле (TextBox), а справа добавим две кнопки (CommandButton), взяв все это с панели инструментов ToolBox.Сменим надписи на кнопках на«OK»и«Cancel»,дляэтого выберем в контекстном меню команду Properties и изменим в свойстве Caption имена кнопок на соответствующие. Измените название формы и надпись (рис 5.1).

Нажав на клавишу F5, запустим форму на выполнение.

Так как кнопкам, расположенным на форме, не присвоено никаких процедур, то при щелчке по ним ничего происходить не будет.

Создадим макрос с помощью команды Insert | Procedure. В открывшемся диалоговом окне укажем: имя макроса DisplayDialog,тип макросаПодпрограмма(Sub),область определения

Общая (Public).

Затем в окне редактирования кода модуля запишем код активизации формы:

Public Sub DisplayDialog()

UserForm1.Show

If UserForm1.Tag = vbOK Then

MsgBox «OK clicked»

Else

MsgBox «Cancel clicked»

End If

End Sub

Свойство Tag (англ. ярлык), использованное в макросе, сохраняет дополнительную информацию о каждом элементе управления на UserForm. Синтаксис его применения:

объект.Tag [=строка]

Строка идентифицирует объект (не обязательное). Создадим процедуры отклика на нажатие кнопок диалоговой формы. В процедурах поместим метод сокрытия формы Hide

и установим код завершения выполнения формы в свойстве Tag. Для этого в окошке Project — VBAProject два раза щелкнем по UserForm1.Затем в появившемся окошке UserForm1 два разащелкнем по кнопке OK, тем самым перейдем в окно редактирования кода процедуры отклика кнопки OK на щелчок по ней. В теле процедуры запишем следующее:

Private Sub CommandButton1_Click()

‘Занести в ячейку А1 значение, набранное в текстовом поле

ActiveSheet.Cells(1, 1).Value = TextBox1.Value

Me.Hide

‘Свойству Tag зададим значение vbOK

Me.Tag = vbOK

End Sub

В теле процедуры отклика кнопки Cancel на щелчок по ней запишем следующее:

Private Sub CommandButton 2_ Click ()

‘Удалить из ячейки А1 значение

ActiveSheet.Cells(1, 1).Value =»»

Me.Hide

‘Свойству Tag зададим значение vbCancel

Me.Tag = vbCancel

End Sub

Здесь ключевое слово Me VBA заменяет объект UserForm1, который требуется спрятать (убрать с экрана), но не удалить (метод Hide). Оно обозначает тот объект, в котором находится другой объект, код которого сейчас исполняется (кнопка находится в форме).

Затем организуем запуск макроса на выполнение. Сделать это можно разными способами, например создав на листе Excel кнопку как элемент управления и назначить ей макрос DisplayDialog.Теперь при нажатии на кнопкуЗапуск формыбудет запускаться этот макрос (Рисунок 5.2).

Рисунок 5.2 — Вызов пользовательской формы

Протестируем работу кнопок. Введем в текстовое поле значение «Привет!» и нажмем на кнопку OK. В ячейке А1 появится введенный текст, а на листе — окно с надписью «ОК clicked» (Рисунок 5.3).

При нажатии на кнопку Cancel текст в ячейке А1 исчезает и появляется окно с надписью «Cancel clicked».

Рисунок 5.3 — Тестирование кнопки ОК

If UserForm1.Tag = vbOK Then

Если нажатие на крестик считать аналогом работы кнопки Cancel, то имея в виду числовые значения формы, избежать ошибки можно, заменив эту строку на:

If UserForm1.Tag = «1» Then

Создание формы с RefEdit

Откроем редактор Visual Basic и выберем из меню Вставка команду UserForm. На экране появится заготовка диалоговой формы и панель инструментов ToolBox.

В диалоговой форме поместим элементы управления RefEdit (редактирование ссылок)и TextBox (текстовое поле).

Если на панели ToolBox отсутствует кнопка RefEdit, то ее следует вызвать, выполнив команду Tools | Additional Controls |

RefEdit.Ctrl.

Добавим к размещенным элементам управления надписи (Label) «Выбор диапазона ячеек» и «Значение для заполнения» соответственно. Как и в предыдущем пункте создадим две кнопки OK и Cancel (Рисунок 5.4)и назначим им те же процедуры отклика.

Рисунок 5.4 — Форма UserForm2 с размещенными

на ней элементами управления

Затем в окне редактирования кода модуля запишем код активизации формы:

Public Sub ShowRefEdit()

With UserForm2

.RefEdit1.Text = Selection.Address

Show

End If

End With

End Sub

Первая инструкция передает элементу RefEdit адрес выбранного диапазона на рабочем листе. Затем запускается форма UsesForm2 с помощью метода Show.После того как форма завершит работу, проверяется возвращаемое значение. Если завершение было успешным, то выбранный диапазон заполняется указанным значением, если завершение было неуспешным, то выбранный диапазон не заполняется.

Для запуска макроса на выполнение так же, как и в предыдущем задании, создадим кнопку Работа с RefEdit и протестируем работу макроса (Рисунок 5.5).

В поле Выбор диапазона ячеек задаем массив ячеек на листе, а в поле Значение для заполнения вводим какой либо текст. При нажатии на кнопку ОК выделенный массив ячеек (А1:А10) заполнится текстом, при нажатии на кнопку Cancel заполнения происходить не будет.

Рисунок 5.5 — Тестирование формы UserForm2

Me.Hide

End Sub

Рисунок 5.6 — Форма UserForm3 с размещенными

на ней элементами управления

На рабочем листе в ячейках А1, В1 и С1 напишем заголовки «Компания», «Город» и «Телефон», а ниже введем 9 соответствующих значений (название компании, местонахождение и контактный телефон).

Затем выделив поочередно массивы этих значений и выбрав в контекстном меню команду Присвоить имя … или вызвав Диспетчер имен (Ctrl+F3),зададим массивам имена соответственно «Company», «City» и «Tel» (Рисунок 5.7).

Рисунок 5.7 — Присвоение имен группам ячеек на листе Excel

Свяжем эти ячейки с элементами управления ComboBox1 и ComboBox2 формы UserForm3.Для этого в их Свойствах укажем в поле RowSource имя соответствующего массива ячеек («Company» и «City»).

Теперь создадим процедуру отклика при нажатии на кнопку ОК. Алгоритм предлагается следующий. Пользователь выбирает

в окнах Компания и Адрес соответствующие значения и нажимает на ОК. Если компания находится по этому адресу, то в окне Контактный телефон появляется ее номер телефона,если нет,то появляется информационное сообщение о том, что такой компании не существует.

Для этого два раза щелкнем по кнопке ОК в форме User-Form3 в редактореVBAи запишем в окне редактирования следующий код

Private Sub CommandButton1_Click()

‘Очистить поле ListBox

Me.ListBox1.Clear

flag = 0

‘ Цикл по строкам столбцов Компания и Адрес и проверка того,

‘ соответствуют ли введенные (или выбранные) компании и их адреса

‘ без учета пробелов в именах (функция Trim) тому, что записано в таблице.

‘ Если совпадение, то в ListBox заносится соответствующий номер телефона

For i = 1 To 9

If Trim(ComboBox1.Text) = Cells(i + 1, 1) And _ Trim(ComboBox2.Text) = Cells(i + 1, 2) Then

Me.ListBox1.AddItem Cells(i + 1, 3)

flag = 1

End If

Next i

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

If flag = 0 Then

End If

End Sub

Теперь создадим макрос запуска нашей формы (переименуем ее в Поиск контактного телефона). Для этого в новом модуле «Module3» вставим процедуру:

Public Sub Find()

With UserForm3

Show

End With

End Sub

Затем создадим на листе Excel кнопку Найти и назначим этой кнопке макрос Find.

Протестируем работу макросов (Рисунок 5.8, 5.9).

Рисунок 5.8 — Положительный результат поиска

Вы уже, наверно, обратили внимание, что при создании форм или элементов управления, VBA устанавливает их имена (свойство Name) по умолчанию. Но часто возникает необходимость в их смысловом переименовании (как сейчас, например). Для переименования объектов и переменных в среде Windows существует соглашение об именах.

Его суть: имя начинается с короткого префикса, одинакового для всех объектов одного и того же рода (табл. 5.1). За ним следует собственно имя — идентификатор объекта. Будем в дальнейшем придерживаться этого соглашения при работе с элементами управления.

Таблица 5.1 — Примеры префиксов имен объектов

Поместим на форму четыре элемента управления Рамки (Frame) (Рисунок 5.10). Пронумеруем рамки, задав заголовки 1, 2, 3, 4 в свойстве Caption. Свойству SpecialEffect установим значение 2-fmSpecialEffectSunken (верхняя и левая границы объекта затенены, а правая и нижняя подсвечены; элемент углублен в окружающее окно).

Рисунок 5.10 — Форма TripWizard с размещенными

на ней элементами управления

Разместим в рамках элементы управления. На первой рамке поместим Label и ComboBox (Рисунок 5.10). Дадим полю со списком имя cboTransport.

На второй рамке разместим Label и три элемента управления OptionButton друг под другом (Рисунок 5.10). Дадим имена optAfrica, optAsia, optEuropa соответственно.

На третьей рамке разместим элементы Label и TextBox (Рисунок 5.10). Дадим текстовому полю имя txtNamet.

На четвертой рамке разместим элемент Label и два элемента управления CheckBox (Рисунок 5.10). Дадим флажкам имена chkHotel и chkMeals соответственно.

Наложим по очереди все рамки точно друг на друга.

Если после этого понадобится перейти к нужной рамке (например, для внесения изменений), то необходимо сначала выбрать ее в списке объектов окна свойств формы TripWizard, а затем щелкнуть на контуре рамки правой кнопкой и в появившемся контекстном меню выбрать команду Переместить (Bring Forward).

Справа поместим кнопку с картинкой и назначим ей по клику мышкой переход на сайт отзывов http://www.otzyv.ru Для этого в процедуру обработки щелчка по кнопке надо поместить следующий код:

Private Sub CommandButton1_Click()

ActiveWorkbook.FollowHyperlink Address:=»http://www.otzyv.ru»,

_ NewWindow:=True

End Sub

Рисунок 5.11 — Окончательный вид формы TripWizard

Итак, форма с необходимыми объектами готова (Рисунок 5.11). Создадим в редакторе модуль и поместим туда макрос запуска формы StartWizard:

Public Sub StartWizard()

With TripWizard

Show

End With

End Sub

С помощью элементов управления создадим на листе Excel кнопку Путешествие и назначим кнопке созданный макрос. Но отображение рамок не произошло, кнопки тоже не работают (Рисунок 5.12):

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

Введем переменную CurrentPanel (счетчик рамок (панелей)) и создадим подпрограмму ShowPanel, с помощью которых сможем в нужный момент вызывать требуемую рамку.

Щелкнем два раза по свободному месту на форме TripWizard,тем самым попадем в окно редактирования кода формы.

Сначала объявим переменные и константы, общие для всех макросов:

Dim CurrentPanel As Integer ‘Это счетчик рамок

Const FirstPanel = 1

Const LastPanel = 4

Затем создадим макрос ShowPanel и поместим в него следующий код:

Public Sub ShowPanel()

‘Сначала делаем все рамки невидимыми frame1.Visible = False frame2.Visible = False frame3.Visible = False frame4.Visible = False

‘Выбор рамки в зависимости от значения счетчика

Select Case CurrentPanel

Case 1

frame1.Visible = True ‘если 1, то рамка 1 видима

Case 2

frame2.Visible = True ‘если 2, то рамка 2 видима

Case 3

frame3.Visible = True ‘если 3, то рамка 3 видима

Case 4

frame4.Visible = True ‘если 4, то рамка 4 видима

Case Else

End Select ‘Окончание выбора’Даем название заголовку формы

Me.Caption = «TripWizard: Step » & CurrentPanel & » of » & LastPanel End Sub

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

Private Sub UserForm_Initialize()

‘Задаем начальное значение счетчика

CurrentPanel = 1

‘Запускаем процедуру показа первой рамки

ShowPanel

‘Введем список для ComboBox, находящегося в этой рамке

With cboTransport

.AddItem » Самолет «

.AddItem » Поезд «

.AddItem » Корабль «

End With

End Sub

Два раза щелкнем по кнопке Готово, тем самым мы создадим процедуру отклика кнопки на щелчок по ней. Запишем в появившемся окне редактирования кода следующее:

Private Sub cmdFinish_Click()

Me.Hide

Me.Tag = vbOK

End Sub

Затем, не выходя из окна редактирования кода, создадим еще 3 процедуры отклика.

Процедура отклика кнопки Отмена:

Private Sub cmdCancel_Click()

Me.Tag = vbCancel

End Sub

Процедура отклика кнопки Назад:

Private Sub cmdPrevious_Click()

‘Движение к предыдущей рамке

ShowPanel

cmdNext.Enabled = True

If CurrentPanel = FirstPanel Then

cmdPrevious.Enabled = False

Else

cmdPrevious.Enabled = True

End If

End Sub

Процедура отклика кнопки Вперед:

Private Sub cmdNext_Click()

‘Движение к следующей рамке

CurrentPanel = CurrentPanel + 1

ShowPanel

cmdPrevious.Enabled = True

If CurrentPanel = LastPanel Then

cmdNext.Enabled = False

Else

cmdNext.Enabled = True

End If

End Sub

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

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

Чтобы это исправить, внесем изменения в макрос StartWizard:

Public Sub StartWizard()

Dim msg As String

With TripWizard

Show

‘Последовательно обрабатываем щелчки мыши, ‘выбирая данные с элементов управления и ‘формируя информационную строку msg

msg = msg & » Континент : «

msg = msg & » Африка » & Chr(13)

Else

msg = msg & » Азия » & Chr(13)

Else

msg = msg & » Европа » & Chr(13)

End If

End If

End If

msg = msg & » Оплата включает : «

msg = msg & » Гостиница «

End If

msg = msg & » Питание «

End If

msg = msg & Chr(13)

End With

End Sub

Теперь можно протестировать работу созданного «Мастера путешествий» — от нажатия на кнопку «Путешествие» до нажатия на кнопку «Готово».

Появится результирующий отчет, составленный по данным, полученным на всех шагах «Мастера путешествий» (Рисунок 5.13).

Рисунок 5.13 — Результирующий отчет

Задание 5.5. Создание формы туристического ваучера

Измените макрос таким образом, чтобы в результате работы «Мастера» формировался ваучер на туристическую поездку, где были бы указаны следующие данные: ФИО путешественника, страна и город посещения, название отеля, срок пребывания, тип номера, вид питания, стоимость тура. Выразите сожаление, если клиент вышел, ничего не заполнив.

Для формирования многостраничности использовать элемент управления MultiPage.

ЗаголовокРабочегоЛиста

End Sub

Для формирования заголовков столбцов базы данных создадим процедуру ЗаголовокРабочегоЛиста, которая будет вызываться из процедуры обработки кнопки рабочего листа «Прием платежа». Для этого выполним команду меню «Вставка | Процедура».Присвоим ей имя.

Процедура выполняет следующие действия:

– проверяет, заполнена ли первая строка — строка заголовков столбцов (по значению ячейки А 1); если заполнена, то не выполняет никаких действий и завершает работу, передавая управление в точку вызова;

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

В окне редактирования кода введем текст этой процедуры:

With ActiveSheet

.Range(«A2»).Select

Else

‘Очищаем рабочий лист

‘Записываем названия столбцов

Application.Worksheets(» База «).Range(«A1:I1»).Select

With Selection

.Value= Array(» Фамилия «, » Имя «, » Адрес «, » Текущее показание счетчика «, » Предыдущее показание счетчика «, » Тариф «, » Дата платежа «, » Расход электроэнергии «, » Сумма «)

.Interior.ColorIndex = 8

.Font.Bold = True

End With

.Range(«A1»).AddComment

.Range(«A1»).Comment.Visible = False

.Range(«A1″).Comment.Text Text:= » Фамилия клиента «

.Range(«B1»).AddComment

.Range(«B1»).Comment.Visible = False

.Range(«B1″).Comment.Text Text:= » Имя клиента «

.Range(«C1»).AddComment

.Range(«C1»).Comment.Visible = False

.Range(«C1″).Comment.Text Text:= » Адрес клиента «

.Range(«D1»).AddComment

.Range(«D1»).Comment.Visible = False

.Range(«E1″).Comment.Text Text:= » Предыдущее показание счетчика «

.Range(«H1″).Comment.Text Text:= » Расход электроэнергии «

.Range(«I1″).Comment.Text Text:= » Сумма «

End If

End With

‘Форматирование табличных ячеек

Worksheets(» База «).Range(«A:I»).Select With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = True

.Orientation = 0

.AddIndent = False

.ShrinkToFit = False

End With

‘Вызвать на экран форму

UserForm1.Show

End Sub

Напишем процедуры для кнопок Принять, Отмена и Выход.

Перейдем к форме, щелкнув два раза по UserForm1 в окне Project-VBAProject. В появившейся форме два раза щелкнем покнопке «Принять», перейдя в процедуру обработки события Click.

Запишем следующий код (здесь, как и в предыдущей работе, изменены имена элементов управления на форме:

Private Sub CommandButton1_Click() ‘Декларация переменных Dim fam, nam, adr As String

Dim nomer As Integer

Dim data As Date

‘Вычисление номера первой свободной строки в таблице nomer = Application.CountA(ActiveSheet.Columns(1)) + 1

With UserForm1

‘Проверяем, введена ли фамилия

MsgBox » Вы забыли указать фамилию «, vbExclamation

Exit Sub ‘Выход из процедуры до ее естественного окончания

End If

‘Проверяем, введено ли имя

MsgBox » Вы забыли указать имя «, vbExclamation

Exit Sub

End If

‘Проверяем, введен ли адрес

MsgBox » Вы забыли указать адрес «, vbExclamation

Exit Sub

End If

‘Присваиваем значения переменным в элементах TextBox

‘Проверяем, введено ли текущее показание счетчика

If IsNumeric(.txttekpok.Text) = False Then

MsgBox » Введено неверное показание счетчика «, vbExclamation

Exit Sub

End If

tekpok = CSng(.txttekpok.Text)

‘Проверяем, введено ли предыдущее показание счетчика

If IsNumeric(.txtprpok.Text) = False Then

MsgBox » Введено неверное показание счетчика «, vbExclamation

Exit Sub

End If

prpok = CSng(.txtprpok.Text)

‘Проверяем, введен ли тариф

If IsNumeric(.txttarif.Text) = False Then

MsgBox » Введен неверный тариф «, vbExclamation Exit Sub

End If

tarif = CSng(.txttarif.Text)

If IsDate(.txtdata) = False Then

MsgBox » Дата введена не верно «, vbExclamation Exit Sub

End If

If Val(txtprpok.Text) > Val(txttekpok.Text) Then

MsgBox » Предыдущее показание счетчика больше текущего «, vbExclamation

Exit Sub

End If

End With

summa = rashod * tarif

‘Записываем данные в ячейки рабочего листа

With ActiveSheet

.Cells(nomer, 1).Value = fam

.Cells(nomer, 2).Value = nam

.Cells(nomer, 3).Value = adr

.Cells(nomer, 4).Value = tekpok

.Cells(nomer, 5).Value = prpok

.Cells(nomer, 6).Value = tarif

.Cells(nomer, 7).Value = data

.Cells(nomer, 8).Value = rashod

.Cells(nomer, 9).Value = summa

End With

ClearForm

End Sub

В данном коде функция CSng преобразует выражение в числовой тип данных Single для того, чтобы можно было провести арифметические операции над данными, внесенными в TextBox. Обратное действие совершает функция Str.

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

Функция IsNumeric проверяет, является ли значение данного выражения числом.

Функция IsDate проверяет, является ли данное выражение корректной датой или временем

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

Private Sub ClearForm()

Unload UserForm1

UserForm1.Show

End Sub

Теперь обработаем нажатие на кнопку Отмена.

Так же перейдем в процедуру обработки события Click и запишем следующий код:

Private Sub CommandButton2_Click()

Dim nomer As Integer

‘Вычисляем номер последней строки

nomer = Application.CountA(ActiveSheet.Columns(1))

‘Удаляем содержимое ячеек строки

With ActiveSheet

If nomer > 1 Then

.Cells(nomer, 1).Value = «»

.Cells(nomer, 2).Value = «»

.Cells(nomer, 3).Value = «»

.Cells(nomer, 4).Value = «»

.Cells(nomer, 5).Value = «»

.Cells(nomer, 6).Value = «»

.Cells(nomer, 7).Value = «»

.Cells(nomer, 8).Value = «»

End If

End With

End Sub

Теперь обработаем нажатие на кнопку Выход.

В процедуре обработки события Click запишем следующий

Private Sub CommandButton3_Click()

‘Активизируем рабочий лист с именем Меню

Sheets(» Меню «).Activate

‘Завершаем выполнение программы

End Sub

Перейдем в нашу рабочую книгу. Выберем лист Меню. Щелкнем по кнопке «Прием платежа», появится пустая таблица с заголовками и форма для заполнения. Введем в нее значения (Рисунок 5.15).

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

Рисунок 5.15 — Заполненные форма и таблица

Построение диаграммы

Private Sub CommandButton4_Click()

‘ Активизируем рабочий лист с именем Диаграмма

Sheets(» Диаграмма «).Activate

‘Очищаем лист от всех объектов

I.Delete

Next i

‘ Создаем новую диаграмму

Loop

‘ Определяем источник данных для построения диаграммы:

‘ с листа «База» от ячейки I2 до ячейки IM

SetSourceData

Source:=Sheets(» База «).Range(«I2:I» + Trim(Str(M))), PlotBy:=xlRows

‘ Выбираем подписи к данным из первого столбца таблицы

For i = 2 To M

Next

‘Размещение диаграммы на отдельном листе

.Location Where:=xlLocationAsObject, Name:= » Диаграмма » With ActiveChart

.HasTitle = True

.ChartTitle.Characters.Text = » Сумма оплаты _ за электроэнергию «

.HasLegend = True

Legend.Select

Selection.Position = xlLeft

.HasDataTable = False

.Axes(xlCategory).MajorTickMark = xlNone

.Axes(xlCategory).MinorTickMark = xlNone

.Axes(xlCategory).TickLabelPosition = xlNone

End With

End With

End Sub

Затем нам нужно изменить процедуру кнопки Выход так, чтобы при выходе активным оставался лист с диаграммой. Для этого в окне Project-VBAProject два раза щелкнем по UserForm1,затем,в появившейся форме,два раза щелкнем покнопке Выход. Запишем там следующее:

Private Sub CommandButton3_Click()

‘Активизируем рабочий лист с именем «Меню»

Sheets(» Диаграмма «).Activate

‘Завершаем выполнение программы

End Sub

Затем перейдем в рабочую книгу и протестируем наш макрос. Запустим форму, нажмем на кнопку Диаграмма. На листе Диаграмма появится диаграмма,построенная по табличнымданным (Рисунок 5.16).

Рисунок 5.16 — Лист Excel с диаграммой

Задание 5.8. Создание базы данных

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

Задание по вариантам

Вариант 1. База данных по учету выданных кредитов.Таблица базы данных должна содержать: фамилию, имя, отчество, адрес заемщика, срок кредита, сумму кредита, пол заемщика, сведения о залоге и его сумме (или о поручит

Источник