Атанас Йонков Блоггер, Веб-разработчик
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 — Примеры префиксов имен объектов
| Имя | Управляющий элемент | Префикс |
| CheckBox | Флажок | chk |
| ComboBox | Поле со списком | cbo |
| CommandButton | Кнопка | cmd |
| Frame | Рамка | fra |
| Image | Изображение | img |
| Label | Надпись | lbl |
| ListBox | Список | lst |
| MultiPage | Набор страниц | mlt |
| OptionButton | Переключатель | opt |
| RefEdit | Редактирование ссылок | ref |
| ScrollBar | Полоса прокрутки | scr |
| SpinButton | Счетчик | spn |
| TextBox | Текстовое поле | txt |
| ToggleButton | Выключатель | tgl |
| UserForm | Форма | ftm |
Поместим на форму четыре элемента управления Рамки (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. | ||
| 2. | ||
| 3. | ||
| 4. | ||
| 5. | ||
| 6. | ||
| 7. | ||
| 8. | ||
| 9. | ||
| 10. |
Вариант 1. База данных по учету выданных кредитов.Таблица базы данных должна содержать: фамилию, имя, отчество, адрес заемщика, срок кредита, сумму кредита, пол заемщика, сведения о залоге и его сумме (или о поручит