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

Для вставки элементов управления на лист необходимо отобразить вкладку Разработчик.

• В MS EXCEL 2007 это можно сделать через меню Кнопка офис/ Параметры Excel/ Основные/ Показывать вкладку Разработчик на ленте .
• В MS EXCEL 2010 это можно сделать так: Откройте вкладку Файл ; Нажмите кнопку Параметры ; Нажмите кнопку Настроить ленту ; Выберите команду Настройка ленты и в разделе Основные вкладки установите флажок Разработчик .

Теперь вставить элемент управления можно через меню: .

Обратите внимание, что в этом меню можно вставить Элементы ActiveX, которые расположены ниже интересующих нас Элементов управления формы. У обоих типов есть одни и те же элементы Кнопка, Список, Флажок и т.п. Разница между ними следующая: чтобы использовать Элементы ActiveX необходимо использовать VBA, а Элементы управления формы можно напрямую привязать к ячейке на листе.

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

В этой статье рассмотрим более сложный пример совместного использования элементов управления и .

Пример

Разберем конкретный пример применения сразу нескольких Элементов управления. В файле примера с помощью элементов управления показано как пользователь может отредактировать значения в таблице (диапазон F9:K12 ).

С помощью одного из 3-х элементов управления Поле со списком, Список и Счетчик , пользователь может выбрать столбец таблицы (год). Нужный элемент управления выбирается с помощью группы Переключателей . Название выбранного элемента подсвечивается серым цветом (см. A8:B8 на рис. выше). Выбранный год выделяется в таблице темно серым цветом (см. H9 :H12 на рис. выше). Отображение этого выделения регулируется Флажком (фон флажка - красный). Полосами прокрутки можно редактировать Цену и Количество в выбранном году, но только в определенном диапазоне. Теперь – подробнее.

Переключатели

Сначала вставим на лист элемент . Этим элементом мы будем включать и выключать выделение в таблице столбца выбранного года. Элемент свяжите с ячейкой $G$2 . Если флажок снят, то в этой ячейке будет ЛОЖЬ (этому значению соответствует 0), если установлен, то ИСТИНА (этому значению соответствует 1).

Для настройки выделим диапазон G9:K12 . Так как формула в Условном форматировании будет содержать , то убедимся, что после выделения диапазона активной ячейкой является G9 (т.е. диапазон надо выделять начиная именно с нее. Подсказкой служит поле Имя , находящееся слева от Строки формул . После выделения диапазона оно должно содержать G 9 ).

• вызовите инструмент (Главная/ Стили/ Условное форматирование/ Создать правило );
• выберите Использовать формулу для определения форматируемых ячеек;
• в поле «Форматировать значения, для которых следующая формула является истинной » введите =И(СТОЛБЕЦ(G9)=СмещГода+6;$G$2) Формула примет значение ИСТИНА, когда выполнится одновременно 2 условия:
• значение выражения (СмещГода (изменяется от 1 до 5 (т.е. от 2009 до 2013 года) + 6) совпадет с номером текущего столбца (7, т.е. 2009 год);
• Флажок Условное форматирование установлен.
• выберите требуемый формат, например, серый цвет заливки;
• нажмите ОК.

Тестируем

• убедимся, что флажок Условное форматирование установлен;
• выберем переключатель Список ;
• в элементе управления Список выберем 2010;
• убедимся, что столбец 2010 выделен серым;
• Полосой прокрутки изменим количество в столбце 2010.

Результат показан на рисунке.

К сожалению, у элементов управления формы Флажок, Поле со списком и Список нет возможности отформатировать отображаемый шрифт. Зато это можно сделать у элементов ActiveX (Разработчик/ Элементы управления/ Вставить ). Правда, для работы с этими элементами требуется писать программу на VBA.

Фризен Ирина Григорьевна – кандидат педагогических наук, преподаватель информационных технологий, автор многочисленных работ в сфере информационных технологий.

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

Офисное программирование

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

Учебное пособие ставит перед собой цель помочь студентам изучить данную дисциплину в полном объеме, отвечающем требованиям государственного стандарта по дисциплине.

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

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

Книга:

Разделы на этой странице:

Использование объектов Range и Selection

В Excel наиболее важным является объект Application. Объект Application (приложение) является главным в иерархии объектов Excel и представляет само приложение Excel. Он имеет более 120 свойств и 40 методов. Эти свойства и методы предназначены для установки общих параметров приложения Excel. В иерархии Excel объект Workbook (рабочая книга) идет сразу после объекта Application и представляет файл рабочей книги. Рабочая книга хранится либо в файлах формата XLS (стандартная рабочая книга), либо XLA (полностью откомпилированное приложение). Свойства и методы рабочей книги позволяют работать с файлами. Однако наиболее «употребляемым» на практике является объект Range, который наилучшим образом отображает возможности использования VBA в Excel (о свойствах объекта Range см. табл. 19, о методах – табл. 20).

В иерархии Excel объект Range (диапазон) идет сразу после объекта worksheet. Объект Range является одним из ключевых объектов VBA. Объект selection (выбор) возникает в VBA двояко – либо как результат работы метода Select, либо при вызове свойства selection. Тип получаемого объекта зависит от типа выделенного объекта. Чаще всего объект Selection принадлежит классу Range, и при работе с ним можно использовать свойства и методы объекта Range. Интересной особенностью объектов Range и Selection является то, что они не являются элементами никакого семейства объектов.

При работе с объектом Range необходимо помнить, как в Excel ссылаются на ячейку рабочего листа.

Задание групп строк и столбцов с помощью объекта Range

Если в диапазоне указываются только имена столбцов или строк, то объект Range задает диапазон, состоящий из указанных столбцов или строк. Например, Range («а: с») задает диапазон, состоящий из столбцов а, в и с, а Range(«2:2») – из второй строки. Другим способом работы со строками и столбцами являются методы Rows (строки) и columns (столбцы), возвращающие коллекции строк и столбцов. Например, столбцом а является columns (1), а второй строкой – Rows (2).

Связь объекта Range и свойства Cells

Так как ячейка является частным случаем диапазона, состоящим только из единственной ячейки, объект Range также позволяет работать с ней. Объект Cells (ячейки) – это альтернативный способ работы с ячейкой. Например, ячейка А2 как объект описывается Range («A2») или Cells (l, 2). В свою очередь, объект cells, вкладываясь в Range, также позволяет записывать диапазон в альтернативном виде, который иногда удобен для работы, а именно Range(«А2:СЗ») и Range(Cells(1,2), Cells(3,3)) определяют один и тот же диапазон.

Таблица 19

Свойства объекта Range

Методы объекта Range

Методы объекта Range, использующие команды Excel

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

Метод AutoFill

Метод AutoFill (автозаполнение) автоматически заполняет ячейки диапазона элементами последовательности. Метод AutoFill отличается от метода DataSeries тем, что явно указывается диапазон, в котором будет располагаться прогрессия. Вручную этот метод эквивалентен расположению указателя мыши на маркере заполнения выделенного диапазона (в который введены значения, порождающие создаваемую последовательность) и протаскиванию маркера заполнения вдоль диапазона, в котором будет располагаться создаваемая последовательность.

Синтаксис:

объект. AutoFill(диапазон, тип)

Аргументы:

Диапазон Диапазон, с которого начинается заполнение тип Допустимые значения: xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues,xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. По умолчанию xlFillDefault

Метод AutoFilter

Метод AutoFilter (автофильтр) представляет собой простой способ запроса и фильтрации данных на рабочем листе. Если AutoFilter активизирован, то каждый заголовок поля выделенного диапазона данных превращается в поле с раскрывающимся списком. Выбирая запрос на вывод данных в поле с раскрывающимся списком, осуществляется вывод только тех записей, которые удовлетворяют указанным условиям. Поле с раскрывающимся списком содержит следующие типы условий: Все (All), Первые десять (Тор 10), Условие (Custom), конкретный элемент данных, Пустые (Blanks) и Непустые (NonBlanks). Вручную метод запускается посредством выбора команды Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter).

При применении метода AutoFilter допустимы два синтаксиса.

Синтаксис 1:

Объект. AutoFilter

В этом случае метод AutoFilter выбирает или отменяет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter), примененную к диапазону, заданному в аргументе объект.

Синтаксис 2:

Объект. AutoFilter (field, criteria1, operator, criteria2)

В этом случае метод AutoFilter выполняет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter) по критериям, указанным в аргументе.

Аргументы:

field Целое, указывающее поле, в котором производится фильтрация данных

Criteria1 Задают два возможных условия фильтрации и criteria2 поля. Допускается использование строковой постоянной, например 101, и знаков отношений >, <,>=, <=, =, <>

operator Допустимые значения: X1And (логическое объединение первого и второго критериев); X1or (логическое сложение первого и второго критериев)

При работе с фильтрами полезны метод showAllData и свойства FilterMode и AutoFilterMode.

Метод ShowAllData Показывает все отфильтрованные и неотфильтрованные строки рабочего листа

свойство FilterMode Допустимые значения: True (если на рабочем листе имеются отфильтрованные данные со скрытыми строками), False (в противном случае)

Свойство AutoFilterMode Допустимые значения: True (если на рабочем листе выведены раскрывающиеся списки метода AutoFilter), False (в противном случае)

Метод GoalSeek

Метод GoalSeek (подбор параметра) подбирает значение параметра (неизвестной величины), являющееся решением уравнения с одной переменной. Предполагается, что уравнение приведено к виду: правая часть является постоянной, не зависящей от параметра, который входит только в левую часть уравнения. Вручную метод GoalSeek выполняется с помощью команды Сервис, Подбор параметра (Tools, Goal Seek). Метод GoalSeek вычисляет корень, используя метод последовательных приближений, результат выполнения которого, вообще говоря, зависит от начального приближения. Поэтому для корректности нахождения корня надо позаботиться о корректном указании этого начального приближения.

Синтаксис:

Объект. GoalSeek(Goal, ChangingCell)

Аргументы:

Объект Ячейка, в которую введена формула, являющаяся правой частью решаемого уравнения. В этой формуле роль параметра (неизвестной величины) играет ссылка на ячейку, указанную в аргументе ChangingCell

Goal Значение левой части решаемого уравнения, не содержащей параметра

Точность, с которой находится корень и предельно допустимое число итераций, используемых для нахождения корня, устанавливается свойствами Maxchange и Maxiterations объекта Application. Например, определение корня с точностью до 0,0001 максимум за 1000 итераций устанавливается инструкцией:

With Application

Maxiterations = 1000

MaxChange = 0.0001

Вручную эти величины устанавливаются на вкладке Вычисления (Calculation) диалогового окна Параметры (Options), вызываемого командой Сервис, Параметры (Tools, Options).

Метод Sort

Сортировка позволяет выстраивать данные в лексикографическом порядке по возрастанию или убыванию. Метод sort осуществляет сортировку строк списков и баз данных, а также столбцов рабочих листов с учетом до трех критериев, по которым производится сортировка. Сортировка данных вручную совершается с использованием команды Данные, Сортировка (Data, Sort).

Синтаксис:

Объект. Sort(key1, order1, key2, order2, key3, order3, header, orderCustom, matchCase, orientaticn)

Аргументы:

Объект Диапазон, который будет сортироваться

Order1

order2 Задает порядок упорядочивания. Допустимые значения: xlAscending (возрастающий порядок); xlDescending (убывающий порядок)

header Допустимые значения: xlYes (первая строка диапазона содержит заголовок, который не сортируется); xlNo (первая строка диапазона не содержит заголовка, по умолчанию считается данное значение); xlGuess (Excel решает, имеется ли заголовок)

orderCustom Пользовательский порядок сортировки. По умолчанию используется Normal

matchCase Допустимые значения: True (учитываются регистры) и False (регистры не учитываются)

orientation Допустимые значения: xlTopToBottom (сортировка осуществляется сверху вниз, т. е. по строкам); xlLeftToRight (слева направо, т. е. по столбцам)

Например, диапазон А1:С20 рабочего листа лист1 сортируется следующей командой в порядке возрастания так, что первоначальная сортировка происходит по первому столбцу этого диапазона, а второстепенная – по второму:

Worksheets(«Лист»).Range(«A1: C20»).Sort _

key1:=Worksheets(«Sheet1»).Range(«A1»), _

key2:=Worksheets («Sheet1»).Range («B1»)

Округление чисел

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

1 способ

Функция Round

X= round(2.505, 2)

Значение х будет 2,5, а не 2,51.

Поэтому часто не используется.

2 способ

Функция Format

sngОкругление=Format(SngНеокругленное, “#, 0.00”)

3 способ

Функция FormatNumber

SngОкругление= FormatNumber(sbgНеокругленное, 2)

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

Примечание. Переменная, в которую помещается округленное значение, должна иметь тип string, single, double, decimal, currency или variant, но не тип integer или long.

Приведение данных

Для приведения введенных данных к нужному типу в VBA включен обширный набор функций, одна из которых – CDBL. Синтаксис:

CDbl(выражение)

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

А = Cdbl(textBoxN.text)

После чего с данной переменной можно работать.

Для выведения значений непосредственно в ячейки книги Excel удобно использовать объект Range:

range(«A5»).value = a

Функцией, обратной по действию к CDbl, является функция CStr – она переводит числа в строки и удобна для вывода результата либо в ячейку на лист, либо в то или иное текстовое окно.

TextBoxN.text = CStr(.Range(«A8»).value)

– считывание значения с ячейки и вывод его в текстовое окно.

Функция Trim (строка) возвращает копию строки, из которой удалены пробелы, находящиеся в начале и конце строки.

Создание VBA-программ

Использование метода GoalSeek

Пример 41. Разработать программу, которая по введенным числовым значениям некоторого уравнения решает данное уравнение и находит неизвестную переменную х. Результат вычисления выводится в текстовое окно на форме и на лист Excel.

Рис. 92. Разработанная форма примера 41 в рабочем состоянии

Технология выполнения

1. Запустите приложение Excel, сохраните документ.

2. Перейдите в редактор VBA.

3. Создайте форму согласно приведенному рис. 92.

4. На листе Excel расположите необходимый текст (оформление), предусмотрев соответствующие ячейки вывода информации (рис. 93).

Рис. 93. Вывод результатов на лист excel после запуска формы примера 41

5. Обработайте кнопки.

Кнопка Вычислить

Private Sub CommandButton1_Click()

Dim a, b, c As Double

a = CDbl(TextBox1.Text)

b = CDbl(TextBox2.Text)

c = CDbl(TextBox3.Text)

With ActiveSheet

Range(«b3»).Value = a

Range(«b4»).Value = b

Range(«b5»).Value = c

Range(«b6»).FormulaLocal = «=b3*b7^3+b4*sin(b7)»

Range(«b6»).GoalSeek Goal:=c, changingCell:=Range(«b7»)

TextBox4.Text = CStr(.Range(«b7»).Value)

TextBox4.Text = FormatNumber(TextBox4.Text, 2)

Кнопка Закрыть

Private Sub CommandButton2_Click()

Процедура инициализации формы

Private Sub UserForm_initialize()

Worksheets(1).Visible = False

Использование методов AutoFill при заполнении таблиц

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

Программирование в Visual Basic реализуется на основе объектно-ори­ен­­ти­ро­ванного программирования (ООП) и его применения в Excel. Для того что­бы ис­пользовать элементы программирования Visual Basic в Excel не­об­ходимо оп­ре­делить понятие: объект, свойства объекта, методы, объек­тов и их ис­поль­зо­ва­ние в программе.

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

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

ООП максимально использует принцип модульности. Программный объект в ООП называют «контейнером». Контейнер включает в себя данные и код, ко­то­рый знает, как манипулировать этими данными.

Объект – совокупность данных вместе с программным кодом, предназначенным для их обработки.

Другими словами, программный объект (контейнер) – это блок, состоящий из кода и данных.

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

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

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

Общими примерами объектов Visual Basic и Excel служат таблицы, области ячеек, командные кнопки, текстовые окна, рабочие папки, диаграммы и модули. Программный объект обладает определенными свойствами и методами.

Свойства - это видимые характеристики объекта. Свойства объекта определяют его внешний вид и поведение.

Методы - это операции преобразования этих данных .

Видимые характеристики – это данные, которые могут быть доступны вне объекта. Свойствами считаются данные, которыми объект манипулирует или ко­то­рые позволяют контролировать, как выглядит объект или как он себя ведет. Например, свойство Value текстового окна – это текст, который вводится в окно.

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

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

Имя объекта. Метод,

а для изменения некоторого свойства

Имя объекта. Свойство=значение .

Пусть имя объекта Power, который имеет свойство Value, тогда возможно изменение этого свойства с помощью инструкции:

Power. Value = 3 ‘Свойство Value –значение – будет равно 3

либо запоминание значения характеристики объекта в переменной

X = Power. Value ‘В переменную x помещается значение свойства value.

Существуют десятки и даже сотни разнообразных объектов в VBA. Однако некоторые из них будут встречаться практически на каждом шагу. Некоторые из них приведены в табл. 4.1.

Таблица 4.1

Объекты VBA Excel

Класс объектов	Описание объекта
Application (приложение)	Этот объект представляет собой само приложение Excel, в целом включает в себя все встроенные функции MS Excel.
Workbook (рабочая книга)	Определяет состояние рабочей книги, например, является ли она открытой для чтения, или какой из методов является активным в настоящий момент. Этому же классу принадлежит объект ActiveWorkBook – представляет активную в настоящий момент книгу.
Worksheets (рабочий лист)	Объект используется при копировании или удалении рабочих листов, их скрытии или показе, проведение вычислений для формул рабочего листа. Этому же классу принадлежит объект ActiveWorkSheet - это объект, который представляет собой активный в настоящее время рабочий лист.
Window (окно)	Объект этого класса используется при свертке или развертке окна, разбиении его на части и фиксировании подокон. ActiveWindow – представляет активное окно
Range (интервал)	Объект этого класс позволяет изменять свойства интервала ячеек, например, используемый шрифт, проверять или изменять содержимое ячеек, вырезать или копировать интервал и т. д. Это наиболее часто используемый класс объектов. К этому же классу относятся объекты: ActiveСell - активная ячейка. Отдельно взятая ячейка представляет собой частный случай Range.

Как уже было сказано, каждый объект имеет целый ряд присущих ему характеристик или свойств. Некоторые из них приведены в табл. 4.2

Таблица 4.2

Свойства некоторых объектов VBA

Объект	Свойство	Описание
Application	ActiveWindow ActiveWorkBook ScreenUpdating StandardFont	Активное окно Активная рабочая книга Имя стандартного шрифта для новых рабочих листов
WorkBook	ActiveSheet FullName Name Saved	Активный рабочий лист Полное имя рабочей книги, включая путь. Имя рабочей книги. Признак того, что состояние рабочей книги сохранено на диске (имеет значение False, если в книге сделаны изменения)
Worksheet	Name Previons ProtectContents Visible	Имя рабочего листа Предыдущий рабочий лист Режим защиты содержимого ячеек рабочего листа. Режим видимости рабочего листа (скрыт или показан).
Window	ActiveCell DisplayGridlines Selection Visible WindowState	Активная ячейка Режим отображения линий сетки Текущий выделенный объект. Режим видимости окна. Режим отображения окна (свернуть окно, полноэкранный режим, нормальный размер)
Range	Column Font Formula Name Row Value Worksheet	Первый столбец интервала Используемый в интервале шрифт Формула интервала Имя интервала Первая строка интервала Значение ячейки Рабочий лист

Приведем несколько примеров, в которых показано как определяются (устанавливаются свойства объектов):

1) установить в активной ячейке шрифт размером в 14 пт.

ActiveCell.Font.Size=14

где Size – свойство объекта Font ;

2) установление в активной ячейке шрифта с названием Courier New Cyr

ActiveCell.Font.Name = «Courier New Cyr»

где Name – свойство объекта Font ;

3) установление в активной ячейке для шрифта начертание – курсив

ActiveCell.Font.Italic=True.

Кроме того, каждый объект имеет присущие ему методы (Метод – описывает действие, которое можно совершить над объектом). Некоторые методы объектов представлены в табл. 4.3.

Таблица 4.3

Таблица некоторых методов объектов VBA

Объект	Метод	Описание
Application	Quit Undo	Завершение MSExsel Отменяет последнее выполнение действия
Workbook	Activate Close Save SaveAs	Активизирует рабочую книгу. Закрывает рабочую книгу. Сохраняет рабочую книгу. Сохраняет рабочую книгу под другим именем.
Worksheet	Activate Calculate Delete Protect Unprotect	Активизирует рабочий лист. Заново вычисляет значение рабочего листа. Удаляет рабочий лист. Защищает рабочий лист. Отменяет защиту рабочего листа.
Window	Activate Close	Активизирует окно. Закрывает окно.
Range	Clear ClearContents ClearFormats Offset Select	Полностью очищает интервал с форматированием. Очищает содержимое ячейки. Очищает форматирование ячейки. Возвращает интервал, с указанным смещением относительно первоначального интервала. Выделяет интервал

Рассмотрим решение нескольких задач.

Задача 4.1

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

Решение

Sub Info()

"Подсчет количества листов и результат помещается в B1

Worksheets("Лист1").Range("B1").Value =Worksheets.Count

"Определяет имя активной книги и помещается в B2

Worksheets("Лист1").Range("B2").Value=AktiveWorkBook.FullName

"Определяет имя третьего листа книги и помещает в B3.

Worksheets("Лист1").Range("B3").Value = Worksheets(3).Name

Задание 4.1

Написать код, используя объекты и методы, который пересчитывает количество листов в текущей книге, прибавляет ещё один лист и последний лист называет «Привет», на новый лист выводит размер шрифта, имя шрифта.

Указание

Метод добавления: add.

Задание 4.2

Написать код, удаляющий последний лист из книги.

Информация

Одними из основных объектов Microsoft Excel являются рабочая книга и рабочий лист.

Создавая, открывая или сохраняя файл в Microsoft Excel, фактически создается, открывается и сохраняется рабочая книга. Для работы с рабочей книгой в Visual Basic используются методы объекта WorkBook или набора WorkBooks.

Инструкция With

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

With < объект>

<инструкции>

End With

где With ,End With – ключевые слова,

< объект> – любой объект Excel,

<инструкции> – инструкции VBA, использующие свойства и методы < объекта>. Каждая инструкция должна начинаться с точки.

Следующий фрагмент программы устанавливает для диапазона А1:С8 полужирный шрифт красного цвета с высотой символов 20пт и не использует инструкцию With.

Range(“ А1:С8”).Font.Bold=True

Range(“ А1:С8”).Font.ColorIndex=3

Range(“ А1:С8”).Font.Size=20

Следующий пример демонстрирует использование инструкции With для присвоения значений нескольким свойствам одного объекта.

With Range(“А1:С8”).Font

.Bold=True

.ColorIndex=3

.Size=20

End With

Формы как объект

VBA позволяет организовать удобный и интуитивно понятный интерфейс пользователя с данными при помощи форм.

Формы – это объекты, которые обладают свойствами, определяющими их внешний вид, методами, определяющими их поведение, и событиями, которые определяют их взаимодействие с пользователем. Установкой свойств формы и разработкой кода VBA для отклика формы на события создается объект, удовлетворяющий требованиям определенного приложения.

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

Microsoft Excel (также иногда называется Microsoft Office Excel) - программа для работы с электронными таблицами, созданная корпорацией Microsoft для Microsoft Windows, Windows NT и Mac OS. Она предоставляет возможности экономико-статистических расчетов, графические инструменты и, за исключением Excel 2008 под Mac OS X, язык макропрограммирования VBA (Visual Basic для приложений). Microsoft Excel входит в состав Microsoft Office и на сегодняшний день Excel есть одним из наиболее популярных программ в мире.

Ценной возможностью Excel есть возможность писать код на основе Visual Basic для приложений (VBA). Этот код пишется с использованием отдельного от таблиц редактора. Управление электронной таблицей осуществляется с помощью объектно-ориентированной модели кода и данных. С помощью этого кода данные входных таблиц будут мгновенно обделываться и отображаться в таблицах и диаграммах (графиках). Таблица становится интерфейсом кода, разрешая легко работать, изменять его и руководить расчетами.

С помощью Excel можно анализировать большие массивы данных. В Excel можно использовать больше 400 математических, статистических, финансовых и других специализированных функций, связывать разные таблицы между собой, выбирать произвольные форматы представления данных, создавать иерархические структуры. Воистину безграничные методы графического представления данных: кроме нескольких десятков встроенных типов диаграмм, можно создавать свои, что настраиваются типы, помогают наглядно отобразить тематику диаграммы. Те, кто только осваивает работу по Excel, по достоинству оценят помощь "мастеров" - вспомогательных программ, которые помогают при создании диаграмм. Они, как добрые волшебники, задавая наводящие вопросы о предвиденных дальнейших шагах и показывая, в зависимости от планированного ответа, результат, проведут пользователя "за руку" за всеми этапами построения диаграммы кратчайшим путем.

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

В Microsoft Excel есть два основных типа объектов: книга и письмо.

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

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

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

В Microsoft Excel очень много разнообразных функций, например:

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

2. Функции даты и времени – большинство функций этой категории ведает преобразованиями даты и времени в разные форматы. Две специальные функции СЕГОДНЯ и ТДАТА вставляют в каморку текущую дату (первая) и дату и время (вторая), обновляя их при каждом вызове файла или при внесение любых изменений в таблицу.

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

6. Текст – В этой группе десятка два команд. С их помощью можно сосчитать количество символов в воротничке, включая пробелы (ДЛСТР), узнать код символа (КОДСИМВ), узнать, какой символ стоит первым (ЛЕВСИМВ) и последним (ПРАВСИМВ) в строке текста, поместить в активную каморку некоторое количество символов из другой воротнички (ПСТР), поместить в активную каморку весь текст из другого каморки большими (ПРОПИСН) или сточными буквами (СТРОЧН), проверить, или совпадают две текстовые каморки (СОВПАД), найти некоторый текст (ПОИСК, НАЙТИ) и заменить его другим (ЗАМЕНИТЬ).

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

8. Работа с базой данных – здесь можно найти команды статистического учета (БДДИСП - дисперсия по выборке из базы, БДДИСПП - дисперсия по генеральной совокупности, ДСТАНДОТКЛ - стандартное отклонение по выборке), операции со столбцами и строками базы, количество непустых (БСЧЕТА) или (БСЧЕТ) ячеек и т.д.

9. Мастер диаграмм – встроенная программа EXCEL, что упрощает работу с основными возможностями программы.

Назначение MS Excel.

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

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

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

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

У Excel есть еще масса преимуществ. Это очень гибкая система "растет" вместе с потребностями пользователя, меняет свой вид и подстраивается под Вас. Основу Excel составляет поле клеток и меню в верхней части экрана. Кроме этого на экране могут быть расположены до 10 панелей инструментов с кнопками и другими элементами управления. Есть возможность не только использовать стандартные панели инструментов, но и создавать свои собственные.

Заключение.

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

©2015-2019 сайт
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2016-08-08

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

Файл рабочей книги Excel представлен в объекте Workbook, который имеет большое количество свойств и методов. Справочная информация по ним присутствует как в электронной справке по VBA, так и в . Мы не будем углубляться в чисто справочную информацию и во вводной части рассмотрим только те сведения, с которыми далее встретимся в приведенных примерах.

Так, свойство Worksheets объекта Workbook представляет семейство всех рабочих листов книги. И для обращения к конкретному листу книги с помощью этого свойства следует просто указать в качестве параметра номер листа, что выглядит так: Worksheets(номер листа) . В качестве номера листа выступает просто его порядковый номер в книге Microsoft Excel. Другой вариант заключается в указании в качестве параметра названия листа: Worksheets ("Название листа") . Одним из наиболее часто программируемых событий, связанных с книгой в целом, является событие Open , которое происходит при открытии рабочей книги. Так, если мы хотим, чтобы при открытии книги выполнялись определенные действия, следует расположить необходимый программный фрагмент внутри процедуры Workbook_Open . Программирование этого события будет рассматриваться в части приводимых далее примеров.

Следующим объектом в порядке иерархии после Workbook является объект Worksheet , представляющий рабочий лист. Из многообразия методов этого объекта широко используется Activate , который существует и для семейства листов Worksheets, о котором мы уже сказали выше. Например, если при работе на первом листе книги Microsoft Excel требуется активизировать второй лист, то синтаксис программной строки в процедуре (выполняться она может, например, по щелчку на кнопке) должен выглядеть следующим образом: Worksheets(2).Activate . Если же требуется активизировать лист, называемый Информация о фирмах , то следует написать в процедуре такую конструкцию: Worksheets("Информация о фирмах").Activate .

Пользователи Microsoft Excel знают, что это приложение предлагает сервис, связанный с защитой рабочих книг и составляющих их листов. Так, метод Protect (семейства Worksheets) обеспечивает защиту рабочего листа от внесения в него изменений. Для программной установки защиты с паролем (пароль указывается в параметре Password этого метода) листа с названием Сотрудники можно поступить следующим образом:

1 2	Worksheets("Сотрудники" ).Protect Password:="zv2345" , _ DrawingObjects:=True , Contents:=True , Scenarios:=True

Worksheets("Сотрудники").Protect Password:="zv2345", _ DrawingObjects:=True, Contents:=True, Scenarios:=True

Существует и соответствующий метод Unprotect , позволяющий снять защиту с листа. Для только что установленной защиты метод ее снятия: Worksheets("Сотрудники").Unprotect Password:="zv2345" . Любая практическая работа в Microsoft Excel, так или иначе, касается информации в ячейках. Для работы с ячейками в VBA существует объект Range (в переводе - диапазон ячеек). Использование этого объекта требует задания параметра диапазона ячеек, которые нас интересуют. Это может быть одна ячейка или группа ячеек. Так, если мы напишем Worksheets(1).Range ("С5").Value = 7 , то это означает, что в ячейку С5 первого листа мы программно записываем число 7. Здесь используется основное свойство объекта Range - Value . Буквально оно означает значение или содержимое ячейки (группы ячеек). В следующей конструкции в совокупность ячеек программно вводится одинаковый набор букв АБСДЕ: Worksheets(1).Range("A1:А3").Value = "АБСДЕ" .

Другой способ работы с ячейками реализуется с помощью объекта Cells , и синтаксис его использования выглядит следующим образом: Cells (номер строки, номер столбца) . Фактически с точки зрения их использования рассматриваемые объекты похожи. Например, получить в переменной Z значение ячейки D5 можно двумя разными способами: Z = Range("D5").Value или Z = Cells(5,4).Value . В качестве примера программной конструкции на данную тему можно привести следующее присвоение:

1 2	Worksheets(2).Range("C5" ).Value = _ Worksheets(3).Cells(5, 1).Value.

Worksheets(2).Range("C5").Value = _ Worksheets(3).Cells(5, 1).Value.