2 семестр / vba_2002

объект.свойство

Параметр редактора VBE Auto List Members применяется к к объектам, определенным в модуле класса. Это упрощает определение свойств и методов при написании кода.

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

Property Get FileNameOnly () As String FileNameOnly = ""

For i = Len(FullName) To 1 Step -1 Char = Mid(FullName, i, 1)

If Char = "\" Then Exit Function

Else

FileNameOnly = Char & FileNameOnly End If

Next i

End Property

Несложно заметить, что процедура Property Get работает как функция. Код рассчитывает и возвращает значение свойства, которое соответствует названию процедуры. В приведенном примере в качестве имени процедуры используется FileNameOnly. Процедура возвращает имя файла, которое содержится в значении общедоступной переменной FullName. Например, если переменная FullName содержит значение с: \windows\myfile.txt, то процедура возвращает значение my f i l e . txt. Процедура FileNameOnly вызывается тогда, когда код VBA обращается к свойству объекта.

Для свойств, ориентированных на чтение и запись, необходимо создать две процедуры: Property Get (отвечает за чтение значения свойства) и Property Let (устанавливает ;начение свойства). Значение, определенное свойству, рассматривается в качестве последнего или единственного) аргумента процедуры Property Get.

Ниже приводятся примеры подобных процедур.

Property Get SaveAsExcelFile() As Boolean

SaveAsExcelFile = XLFile End Property

Property Let SaveAsExcelFile(boolVal As Boolean) XLFile = boolVal

End Property

/Если свойство имеет объектный тип данных, вместо Property Let необходимо

/тмметку использовать Property Set.

Переменная, объявленная в модуле класса с областью действия Public, может также применяться в качестве свойства объекта. В представленном выше примере избавимся от процедур Property Get и Property Let, а также заменим их на объявление переменной на уровне модуля:

Public SaveAsExcelFile As Boolean

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

Предыдущие примеры предполагают существование булевой переменной XLFile, которая определена на уровне модуля. Процедура Property Get возвращает значение этой переменной

ЧастьУН.Другиетемы

699

в качестве значения свойства. Если объект называется FileSys, то следующий оператор будет отображать текущее значение свойства SaveAsExcelFile:

MsgBox FileSys.SaveAsExcelFile

С другой стороны, процедура Property Let принимает аргумент и использует значение аргумента для изменения значения свойства. Например, можно создать оператор (приведенный ниже), который будет устанавливать свойство SaveAsExcelFile в значение True:

FileSys.SaveAsExcelFile = True

Вданном случае значение True передается процедуре Property Let, которая и изменяет значение свойства.

Впредыдущих примерах используется переменная уровня модуля — XLFile. Именно эта переменная используется для хранения значения свойства. Такую переменную необходимо создавать для каждого свойства, которое объявляется в модуле класса.

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

Методы программирования

Метод объекта класса программируется подобно другим процедурам. Для этого используются ключевые слова Sub или Function, которые размещаются в модуле класса. Как правило, объект имеет методы, однако может существовать и без них. Для вызова метода в коде используются стандартные соглашения:

объект.метод

Как и любой другой метод VBA, метод объекта класса выполняет определенные действия Следующая процедура представляет метод, который сохраняет рабочую книгу в одном и: двух форматов. Используемый формат определяется значением свойства XLFile. Легко за метить, что в этой процедуре нет ничего необычного.

Sub SavePileO

If XLFile Then

Ac t i veWorkbook.SaveAs F i1eName:= FName, _ FileFormat:=xlWorkbookNormal

Else

ActiveWorkbook.SaveAs FileName:=FName, _ FileFormat:=xlCSV

End If End Sub

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

События модуля класса

Каждый модуль класса поддерживает два события: I n i t i a l i z e и Terminate. Событие I n i t i a l i z e возникает при создании нового экземпляра класса, а событие Terminate — при уничтожении объекта. Событие I n i t i a l i z e можно использовать для установки значений свойств, принятых по умолчанию.

Шаблон для процедур обработки событий выглядит следующим образом:

700	Глава 29. Принципы управления модулями классов

Private Sub Class_InitializeO

'	Здесь располагается код инициализации
End Sub
Private Sub Class_Terminate()
1	Здесь располагается код уничтожения

End Sub

Объект уничтожается (и освобождается выделенная для него память), когда процедура или модуль, в котором он объявлен, завершает свое выполнение. Объект можно уничтожить в любой момент, присвоив ему значение N o t h i n g . Следующий оператор уничтожает объект, который называется MyObject:

Set MyObject = Nothing

Пример: класс CSVFile

Пример, приведенный в этом разделе, определяет такой класс объектов, как CSVFile - C l a s s . Этот класс имеет два свойства и два метода.

Свойства

•ExportRange — предназначено для чтения и записи. Указывает диапазон ячеек рабочего листа, который будет экспортирован в файл формата CSV.

•ImportRange — предназначено для чтения и записи. Указывает диапазон, в который, будет импортирована информация из файла формата CSV.

Методы

•I m p o r t — используется для импортирования информации из файла в формате CSV, который определяется значением аргумента CSVFileName. Информация импортируется в диапазон, обозначенный свойством ImportRange .

•E x p o r t — используется для экспортирования диапазона, определенного значением свойства ExportRange, в файл формата CSV, который указывается значением аргумента CSVFileName.

Переменные уровня модуля класса

Модуль класса должен поддерживать собственные локальные переменные, которые отражают значения свойств объекта. Модуль класса CSVFileClass содержит две переменные, которые используются для хранения значений свойств. Эти переменные объявляются в начале модуля:

Private RangeToExport As Range

Private ImportToCell As Range

RangeToExport — это объект Range, который представляет экспортируемый диапазон. ImportToCell является объектом Range, представляющим верхнюю левую ячейку того диапазона, в который будут импортироваться данные из файла в формате CSV. Эти переменные получают значения с помощью процедур P r o p e r t y Get и P r o p e r t y Let (см. следующий раздел).

Процедуры свойств

Процедуры свойств модуля класса C S V F i l e C l a s s	показаны в листинге 29.1. Процедуры
P r o p e r t y Get используются для получения значения переменной, а процедуры P r o p e r t y
L e t — для установки значения переменной.
Часть VII. Другие темы	701

Листинг 29.1. Процедуры свойств модуля CSVFileClass

Property Get ExportRange () As Rar.ge

Set ExportRange = RangeToExport

End Property

Property Let ExportRange(rng As Range)

Set Range To Expo r-t = rng

End Property

Property Get ImportRange() As Range

Set ImportRange = ItnportToCell

End Property

Property Let ImportRange(rng As Range)

Set ImportToCell = rng

End Property

Процедуры методов

Модуль класса CSVFileClas s содержит две процедуры, которые представляют методы класса. Этнпроцедурырассматриваются вследующемразделе.

Процедура Export

Процедура Export , приведенная в листинге 29.2, вызывается каждый раз при обращена к методу E x p o r t . Процедура принимает один аргумент: полное имя файла, который будехранить экспортируемый диапазон. Процедура обеспечивает базовый уровень обработю ошибок. Например, проверяется наличие значения у свойства ExportRange . Для этого про веряется значение переменной RangeToExport . Процедура создает обработчик для возни каюших ошибок.

! Листинг 29.2. Экспорт диапазона ячеекрабочего листа с помощью метода модуля класса

Sub Export(CSVFileName)

1Экспортирует диапазон в файл CSV Dim ExpBook As Workbook

If RangeToExport Is Nothing Then

MsgE-ox "Экспортируемый диапазон не определен" Exit Sub

End If

On Error GoTo ErrHandle Application.ScreenUpdating = False

Set ExpBook = Workbooks.Add(xlWorksheet) RangeToExport.Copy Application.DisplayAlerts - False

With ExpBook

.Sheets(1).Paste

.SaveAs FileName:=CSVFileName, FileFormat:=xlCSV

.Close SaveChanges:=False End With

702	Глава 29. Принципыуправлениямодулями классов

Application.CutCopyMode = False

Application.ScreenUpdating = True

Application.DisplayAlerts = True Exit Sub

ErrHandle:

ExpBook.Close SaveChanges:=False

Application.CutCopyMode = False Application.ScreenUpdating = True Application.DisplayAlerts = True

MsgBox "ОШИБКА " & Err & vbCrLf & vbCrLf & Error(Err), _ vbCritical, "Ошибка метода экспортирования"

End Sub

Процедура E x p o r t при выполнении копирует указанный с помощью переменной RangeToExport диапазон во временную рабочую книгу. После этого рабочая книга сохраняется в файле формата CSV, и файл закрывается. Так как обновление экрана не выполняется, пользователь не видит происходящего. Если возникнет ошибка (например, будет указано неверное имя файла), процедура перейдет к метке E r r H a n d l e и отобразит окно сообщения, которое содержит номер ошибки и ее описание.

Процедура import

Процедура I m p o r t , приведенная в листинге 29.3, импортирует данные из файла CSV, который указан с помощью аргумента CSVFileName. Содержимое файла копируется в диапаюн, указанный с помощью значения переменной i m p o r t T o C e l l . Эта переменная используется для хранения значения свойства ImportRange . В данном случае обновление экрана не ыполняется— пользователь не должен иметь возможности наблюдать за работой процеду-

ы. Как и E x p o r t , процедура I m p o r t содержит базовые функции обработки ошибок.

Листинг 29.3. Импортирование содержимого файла в указанный диапазон с помощью метода модуля класса

зиЬ Import{CSVFileName)

' Импортирует файл CSV в диапазон Dim CSVFile As Workbook

If ImportToCell Is Nothing Then

MsgBox "Диапазон для импорта не определен"

Exit Sub

End If

If CSVFileName = "" Then

MsgBox "He определено имя импортируемого файла"

Exit Sub

End If

On Error GoTo ErrHandle

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Workbooks.Open CSVFileName

Set CSVFile = ActiveWorkbook

ActiveSheet.UsedRange.Copy Destination:=ImportToCell

CSVFile.Close SaveChanges:=False

Application.ScreenUpdating = True

Application.DisplayAlerts = True

Часть Vil. Другие темы

703

Exit Sub ErrHandle:

CSVFile.Close SaveChanges:=Palse

Application.ScreenUpdating * True

Application.DisplayAlerts = True

MsgBox "ОШИБКА " & Err & vbCrLf & vbCrLf & Error(ErrJ, _ vbCritical, "Ошибка метода импортирования"

End Sub

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

Для того чтобы создать экземпляр класса C S V F i l e C l a s s в собственном коде VBA, необходимо создать переменную, которая имеет тип C S V F i l e C l a s s . Ниже приведен пример объявления такой переменной.

Dim CSVFile As New CSVFileClass

Иногда может потребоваться объявить переменную указанного типа, а также создать объект в тот момент, когда он будет запрашиваться в коде. Для этого используются операторы Dim и S e t .

Dim CSVFile As CSVFileClass
'	Здесь	может	находится	другой	-<од
Set CSVFile = New CSVFile

Преимуществом применения операторов Dim и S e t является то, что объект не создается до тех пор, пока в коде не встретится соответствующий оператор S e t . При этом оче видна экономия памяти: если объект не нужен, память под него не выделяется. Например код может следовать алгоритму, который точно определяет, когда необходимо создават объект. Кроме того, использование оператора S e t позволяет создавать несколько экземп ляров одного класса.

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

Средство редактора VBE Auto List Members выполняется для всех объектов. После ввода имени переменной с точкой в конце отобразите список свойств и методов, которые определены для этого объекта.

Следующая процедура демонстрирует сохранение текущего выделенного диапазона ячеек в файл CSV, который называется temp . csv. Этот файл находится а той же папке, что и рабочая книга.

Sub ExportARange()

Dim CSVFile As New CSVFileClass With CSVFile

.ExportRange = ActiveWindow.RangeSelection

.Export CSVFileName:=ThisWorkbook.Path & "\temp.csv" End With

End Sub

Использовать структуру w i t h - E n d With не обязательно. Например, процедура может выглядеть следующим образом:

Sub ExportARange()

	Dim CSVFile As New CSVFileClass
	CSVFile.ExportRange = ActiveWindow.RangeSelection
	CSVFile.Export CSVFileName:=ThisWorkbook.Path & "\temp.csv"
End	Sub
704	Глава 29. Принципы управления модулями классов

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

Sub ImportAFileO

Dim CSVFile As New CSVFileClass With CSVFile

On Error Resume Next

.ImporcRange = ActiveCell

.Import CSVFileKame:=ThisWorkbook.Path & "\temp.csv" End With

If Err <> 0 Then _

MsgBox "Невозможно импортировать " & ThisWorkbook.Path & "\temp.csv" End Sub

VBA может работать с большим количеством экземпляров класса. Следующий код создает массив из трех объектов CSVFileClass:

Sub Export3Files()

Dim CSVFile(1 To 3) As New CSVFileClass

CSVFile(l)-ExportRange = Range{"Al:A20")

CSVFile(2).EKportRange = Range("Bl:B20")

CSVFile(3).ExportRange = Range{"Cl:C20")

For i = 1 To 3

CSVFile(i).Export CSVFileName:="File" & i & ".csv"

Next i<

End Sub

Резюме

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

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

ЧастьVII.Другиетемы

70S

Часто задаваемые вопросы о программировании в Excel

Те пользователи, которые часто "блуждают" в Internet, знают., что такое FAQ (ЧаВо). Это список часто зада-

ваемых вопросов (frequently asked questions} (и ответов на них), касающихся определенной темы. Такие списки вопросов, в основном, публикуются в конференциях, где они регулярно публикуются с целью сократить поток одинаковых вопросов на определенную тему. Но зачастую такие документы не выполняют возложенную на них функцию, гак как одинаковые вопросы продолжают повторяться.

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

•Группы новостей m i c r o s o f t . p u b l i c . e x c e l . *.

•Группы новостей comp. apps . s p r e a d s h e e t .

•База знаний Microsoft.

•Почтовые сообщения, отправленные на мое имя.

Если данная глава не содержит ответа на ваш вопрос.

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

Все задаваемые вопросы были объеденены в восемь категорий. Каждый вопрос относится

кодной из следующих тем.

•Общие вопросы об Excel.

•Вопросы о редакторе Visual Basic.

•Вопросы о процедурах и функциях

•Вопросы об объектах, свойствах, методах и событиях.

•Вопросы об инструкциях VBA.

•Вопросы о пользовательских диалоговых окнах.

•Вопросы о надстройках.

•Вопросы об объектах CommandBar,

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

Кстати, большая часть информации, приведенной в этой главе, подробно рассматривается в других главах настоящей книги.

Общие вопросы об Excel

Почему в Excel используется два языка создания макросов?

Ранние версии Excel использовали макроязык, который назывался XLM. Язык VBA начал использоваться в Excel 5, и он намного превосходит по возможностям XLM. Со временем язык XLM был вытеснен из среды разработки, поэтому в данный момент рекомендуется использовать только язык VBA.

Мне необходимо распространять рабочую книгу среди пользователей Excel 4. Существует ли способ записи действий на макроязыке XLM?

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

Работают ли в Excel 97 и более поздних версиях макросы, созданные

спомощью языка XLM?

Вбольшинстве случае такие макросы выполняются.

Мне необходима утилита, которая сможет преобразовать макросы Excel 4 в язык VBA. Существует ли такая утилита?

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

ЧастьVII.Другиетемы

707

Можно ли вызывать процедуру VBA из макроса XLM для Excel 4?

Да. Воспользуйтесь функцией XLM RUN. Например, следующий макрос применяется для запуска процедуры T e s t , которая находится в модуле Modulel рабочей книги Bookl . x l s : =RUN(Bookl.xls ! M o d u l e l . T e s t )

Существует ли способ автоматического преобразования макросов Lotus 1-2-3 или Quattro Pro в макросы на языке VBA?

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

Где можно найти примеры кода VBA?

В Internet находятся тысячи примеров кодов VBA. Хорошей отправной точкой может служить узел автора книги — h t t p : / /www. j - walk, com/ss/

Существует ли утилита для преобразования приложения Excel в отдельный ЕХЕ-файл?

Нет, не существует.

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

Введите список допустимых значений в один столбец. Если необходимо, столбец можно скрыть. Выберите ячейку или ячейки, которые будут отображать список значений, а затем выполните команду Данные1 ^ Проверка и перейдите на вкладку Параметры. Из выпадающего списка Тип данных выберите Список. В поле Источник укажите адрес диапазона или ссылку на элементы списка, хранящиеся на рабочем листе. Удостоверьтесь, что установлен флажок Список допустимых значений. Если список достаточно небольшой, то значения можно просто ввести, разделив их запятой. Такая техника не требует использования макросов.

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

Да. Необходимо создать имя для этого списка (например, L i s t E n t r i e s ) . Затем в диалоговом окне Проверка вводимых значений введите =ListEntries в поле Источник. Удостоверьтесь, что в поле был введен символ равенства, так как в противном случае метод работать не будет.

Я использую свойство Application.Calculation для установки ручного режима расчетов. Но этот параметр влияет на все рабочие книги, а не только на активную.

Свойство C a l c u l a t i o n принадлежит объекту A p p l i c a t i o n . Таким образом, изменение режима относится ко всем открытым рабочим книгам. Невозможно установить режим расчетов для отдельной рабочей книги. Ехсе] 2000 имеет новое свойство объекта Worksheet, которое называется E n a b l e C a l c u l a t i o n . Когда это свойство установлено в значение F a l s e , рабочий лист не будет пересчитываться, даже если пользователь явно затребует это действие. Установка данного свойства в значение True приводит к пересчету рабочего листа.

Как увеличить количество столбцов в рабочем листе?

Это невозможно. Количество столбцов рабочего листа фиксированно и не может быть изменено, Microsoft продолжает игнорировать тысячи запросов об увеличении количества столбцов на рабочем листе.

708	Глава 30. Часто задаваемые вопросы о программировании в Excel