Vba excel пользовательский тип
MyVar = MyVar / 2
Sub VariantDemo 2()
MyVar = MyVar + MyVar
Функция определения типа данных
Для определения типа данных переменной используется функция VBA TypeName .
MyVar = MyVar / 2
Тестирование явного объявления типов данных
Чтобы оценить важность определения типа данных, рассмотрим следующую процедуру, в которой выполняются циклические вычисления, а затем отображается общее время выполнения процедуры.
Dim x As Integer, у As Integer
Dim A As Integer, В As Integer, С As Integer
Dim i As Integer, j As Integer
Dim StartTime As Date, EndTime As Date
' Сохранение времени начала вычислений
For i = 1 То 5000
For j = 1 То 1000
' Получение времени окончания вычислений
' Отображение общего времени
MsgBox Format(EndTime – StartTime)
Сравните время выполнения этой программы и время выполнения этой же программы при превращении операторов Dim в комментарии.
Область действия переменных
Область действия переменной определяет, в каких модулях и процедурах она может использоваться. Существуют следующие типы областей действия переменных.
Способ объявления переменной
В процедуру включается оператор Dim или static
Перед первой процедурой в модуле вводится оператор Dim или private
Перед первой процедурой в модуле вводится оператор Public
- Локальная переменная — это переменная, объявленная в процедуре.
- Локальные переменные могут использоваться только в процедуре, в которой они объявлены.
- После выполнения процедуры переменная становится невостребованной, поэтому Excel освобождает соответствующую область памяти.
- Если требуется сохранить значение переменной, объявите ее как static .
- Чтобы объявить локальную переменную — вставьте оператор Dim между операторами Sub и End Sub .
- Dim – сокращение от Dimension (Размерность). В старых версиях BASIC этот оператор использовался исключительно для объявления размерности массива.
- Другой способ указания типа данных для переменной : язык VBA позволяет присоединить символ к названию, чтобы указать ее тип данных.
Пример, можно объявить переменную MyVar как целое число, добавив к ее названию символ %: Dim MyVar % - Символы объявления типов данных представлены для большинства типов данных VBA (отсутствующие в таблице типы данных не имеют собственного символа объявления типа).
Символ объявления типа
• Локальные переменные позволяют экономно использовать память, так как VBA освобождает память, которую они используют, после окончания выполнения процедуры.
Переменные уровня модуля
Иногда необходимо, чтобы переменная была доступна во всех процедурах модуля. В таком случае объявите переменную перед первой процедурой модуля (за пределами процедур или функций).
В приведенном ниже примере оператор Dim — первая инструкция в модуле. Обе процедуры MySub и YourSub имеют доступ к переменной CurrentValue .
Dim CurrentValue As Integer
' -[Здесь вводится текст процедуры] -
'-[Здесь вводится текст процедуры] -
Значение переменной уровня модуля не изменяется при окончании выполнения процедуры.
Чтобы сделать переменную доступной во всех процедурах всех модулей VBA в проекте, необходимо объявить переменную на уровне модуля с помощью ключевого слова Public перед первой процедурой модуля, например, так:
Public CurrentRate as Long
Код объявления переменных Public должен вводиться в стандартном модуле VB А, а не в коде модуля листа или формы.
Переменные Static — особый случай. Они объявляются на уровне процедуры и сохраняют свое значение после окончания процедуры.
Static Counter As Integer
'-[Здесь вводится текст процедуры] –
Работа с константами
Константа – именованное значение или строка, которая не меняется при выполнении программы.
Константы объявляются с помощью оператора Const .
Const NumQuarters as Integer = 4
Const Rate = .0725, Period = 12
Const ModName as String = "Budget Macros"
Public Const AppName as String = "Budget Application"
Во втором примере тип данных не объявлен. Следовательно, указанные две константы имеют тип Variant .
Константы имеют область действия как и переменные.
Способ объявления константы
В процедуре или функции
Перед первой процедурой в модуле
Перед первой процедурой в модуле с ключевым словом Public
При попытке изменить значение константы в процедуре VBA вы получите ошибку.
Использование предопределенных констант
В Excel и VBA существует целый ряд предопределенных констант, которые можно использовать без объявления.
В следующей процедуре для изменения ориентации страницы активного листа на альбомную применена встроенная константа ( xlLandscape ):
Константу xlLandscape можно обнаружить путем записи макроса.
Описание констант можно найти в справочной системе.
Если включен параметр AutoList Members , то можно получить помощь непосредственно при вводе кода. Во многих случаях VBA автоматически перечисляет все константы, присваиваемые определенному свойству.
В VBA представлено два типа строк.
- Строки фиксированной длины объявляются с определенным количеством символов. Максимальная длина строки составляет 65535 символов.
- Строки переменной длины теоретически могут вмещать до 2 млрд. символов.
Память для строки отводится из расчета 1 байт на каждый символ и для хранения заголовка строки.
В следующем примере переменная MyString объявляется как строка с максимальной длиной 50 символов. YourString тоже объявлена как строка, но она имеет переменную
Dim MyString As String * 50
Dim YourString As String
Работа с датами
Переменная, определенная как Date , занимает 8 байт памяти и может содержать даты в диапазоне от 1 января 100 года до 31 декабря 9999 года.
Ошибка дат в Excel
В Excel используется неправильное предположение, что 1900 год – високосный.
В ячейке с формулой =ДАТА(1900;2;29) появится значение 29 февраля 1900 года.
Функция DateSerial (190 Q ,2,29) в VBA возвратит 1 марта 1900 года (!).
Оператор присвоения — это инструкция VBA , выполняющая математическое вычисление и присваивающая результат переменной или объекту.
Выражение определяется как комбинация ключевых слов, операторов, переменных и констант. Эта комбинация возвращает в результате строку, число или объект. Выражение может осуществлять вычисление, обрабатывать символы или тестировать данные.
В VBA оператором присвоения выступает знак равенства (=).
Примеры использования операторов присвоения (выражения приводятся справа от знака равенства):
х = ( у * 2) ! ( г * 2) FileOpen = True FileOpen = Not FileOpen Range С "The Year").Value = 2001
Выражения могут быть очень сложными. Чтобы сделать длинные выражения более удобными для восприятия, используйте символ продолжения строки (пробел с подчеркиванием).
Зачастую в выражениях применяются функции. Это могут быть встроенные функции VBA , функции рабочих листов Excel или специальные функции, разработанные в VBA .
Булевы операторы VBA
Таблица. Булевы операторы VBA
Логическое отрицание выражения
Логическая конъюнкция двух выражений
Логическая дизъюнкция двух выражений
Логическое отрицание двух выражений
Логическая эквивалентность двух выражений
Логическая импликация двух выражений
- Свойство DisplayGridLines принимает значение True или False . Следовательно, применение оператора Not изменяет True на False , a False — на True :
ActiveWindow . DisplayGridLines = _
Not ActiveWindow . DisplayGridLines - Представленное далее выражение осуществляет логическую операцию And . Оператор MsgBox отображает True , только если Лисг1 — активный лист и активная ячейка находится в строке 1. Если одно или оба этих условия неверны, оператор MsgBox отображает False :
MsgBox ActiveSheet . Name = "Лист1" And ActiveCell . Row = 1 - Следующее выражение осуществляет логическую операцию Or . Оператор MsgBox отображает True , если активен лист Лист1 или Лист2:
MsgBox ActiveSheet.Name = " Лист 1" Or ActiveSheet.Name = " Лист 2"
Массив — это именованная группа проиндексированных элементов одного типа. На конкретный элемент массива ссылаются, используя имя массива и индекс. Например, массив MonthNames из 12-ти строк (каждая переменная соответствует названию месяца). Можно обратиться к первому элементу массива как
MonthNames(0), ко второму — как MonthNames(1) и т.д., до MonthNames(11).
Массив объявляется с помощью операторов Dim или Public . Можно определить количество элементов в массиве: введите первый индексный номер, ключевое слово То и последний индексный номер — вся конструкция будет заключена в скобки. Например, так можно объявить массив, содержащий ровно 100 целых чисел:
Dim MyArray(1 To 100) As Integer
Массивы VBA могут иметь до 60-ти измерений. Показанный ниже оператор объявляет двухмерный 100-элементный массив целых чисел:
Dim MyArray(1 To 10, 1 То 10) As Integer
Так присваивается значение элементу предыдущего массива
МуАггау(1, 4) = 125
Трехмерный массив
Dim MyArray(1 To 5, 1 То 6, 1 То 7) As Integer
состоит из 420 чисел типа Double.
Динамический массив не имеет предопределенного количества элементов. Динамический массив объявляется с незаполненными значениями в скобках:
Dim MyArray () As Integer
Перед использованием динамического массива необходимо обратиться к оператору
ReDim , указывающему VBA , сколько элементов находится в массиве
или
ReDim Preserve , если решено сохранить текущую длину массива.
Оператор ReDim можно использовать сколько угодно раз, изменяя, если требуется, размер массива.
Переменная объекта — это переменная, представляющая целый объект, например, диапазон или рабочий лист. Переменные объектов имеют особое значение по двум причинам:
¦ значительно упрощают программу;
¦ ускоряют выполнение программы.
Переменные объектов, как и обычные переменные, объявляются с помощью оператора Dim или Public . Например, в следующем операторе переменная inputArea объявляется как объект Range : Public InputArea As Range
Чтобы узнать, каким образом переменные объектов упрощают программу, проанализируем процедуру, написанную без их использования:
Worksheets(" Лист 1").Range("Al").Font.Bold = True
Worksheets(" Лист 1").Range("Al").Font.Italic = True
Эта процедура вводит значение в ячейку А1 листа Лист1 активной рабочей книги, а затем делает шрифт содержимого ячейки полужирным и курсивным. В примере введено довольно много кода для такой простой операции. Чтобы пощадить себя, сведите процедуру к использованию объектной переменной:
Dim MyCell As Range
Sec MyCell = Worksheets(" Лист 1").Range("Al")
После объявления переменной MyCell как объекта Range оператор Set присваивает ей сам объект. В результате в следующих операторах используется упрощенная ссылка MyCell вместо длинной Worksheets (" Лист1") . Range(" Al ").
Пользовательские типы данных
VBA позволяет создавать специальные, или пользовательские, типы данных . Определенный пользователем тип данных может облегчить управление некоторыми типами данных. Например, если приложение обрабатывает сведения о клиенте, то можно создать пользовательский тип данных с названием
Customerlnfo:
Type Customerlnfo
Company As String * 25
Contact As String * 15
RegionCode As Integer
Sales As Long
End Type
Пользовательские типы данных определяются вверху модуля перед началом
Если пользовательский тип данных уже создан для объявления переменной этого типа примените оператор Dim . Обычно пользовательский тип данных определяется для массивов:
Dim Customers ( 1 To 100) As Customerlnfo
Все 100 элементов этого массива состоят из четырех компонентов (как указано в пользовательском типе данных— Customerlnfo ). Вы можете сослаться на конкретный компонент элемента :
Customers(1).Contact = "Tim Robertson"
Чтобы скопировать информацию из Customers (1) в Customers ( 2 ), используется следующая инструкция:
В статье "Типы данных в Visual Basic (VBA)" мы познакомились с основными простейшими типами в VBA. Но средства VBA нам позволяют создавать свои составные (пользовательские) типы данных, основанные на простейших типах. Для чего это нужно? Например, нам необходимо описать некоторые объекты по следующим параметрам: Длина, Ширина, Глубина, Цвет, Материал и т.д. Это можно сделать двумя способами:
1 - На каждый параметр создать переменную определенного типа
2 - Создать свой пользовательский (составной) тип, содержащий все эти параметры и описать переменную этого типа.
Пока не совсем понятно, что я имел ввиду. Сейчас на примере разберем.
Что нам дает описание собственного типа?
Удобство! В одной переменной вы можете передать множество параметров разного типа. Такой подход значительно упростит код и сделает его легко-читаемым, да и с объектами легче работать.
Как описать свой тип данных в VBA?
В VBA для описания своего типа предусмотрена конструкция:
[Private | Public] Type НазваниеТипа
Элемент1 As Тип
Элемент2 As Тип
.
End Type
где:
Prvate/Public - определяет область видимости нашего типа (private - виден только в текущем модуле, public - виден во всех модулях)
Type - ключевое слово, указывающее на описание нового типа "НазваниеТипа"
Элемент1,2. n - собственно поля простых типов
End Type - конец описания типа.
На примере рассмотрим описание вышеупомянутого составного типа, который содержит несколько элементов описывающих объект.
Описать тип можно в любом модуле, но если таких составных типов в проекте будет не один 10-ок, то для простоты рекомендую выносить их описание в отдельный модуль. Итак, создадим наш тип MyObject с элементами:
Длина, Ширина, Глубина - типа Single
Цвет, Материал - типа String.
Option Explicit
Public Type MyObject
dl as single
sh as single
gl as single
cvet as string
mat as string
End Type
Sub TestMyType ()
dim obj as MyObject
obj.dl = 35.5
obj.sh = 15
obj.gl=3
obj.cvet = "Черный"
obj.mat = "Резина"
' для более удобного обращения к полям объекта obj
'воспользуемся конструкцией with . end with
with obj
msgbox "Параметры объекта:" & chr(13) & "Длина - "& .dl & chr(13) _
& "Ширина - "& .sh & chr(13) & "Глубина - " & .gl & chr(13) _
& "Цвет - " & .cvet & chr(13) & "Материал - " & .mat
end with
end sub
Обратите внимание что когда Вы ставите точку после названия переменной obj у вас выпадает список всех доступных полей.
Вы можете так же составлять новые типы из созданных уже ранее типов т.е.
Type MyObject
dl as single
sh as single
gl as single
cvet as string
mat as string
End Type
Public Type MyType
ID as long
Myobj as MyObject
' .
End Type
В VBA можно создать собственные или пользовательские типы данных. Собственные типы данных целесообразно создавать тогда, когда в программе используется группа связанных элементов (переменных), относящихся к различным встроенным типам данных VBA. Пользовательские типы данных являются структурными типами данных. Пользовательский тип данных может объединить несколько связанных по смыслу переменных с различными базовыми типами данных в единую структуру. Для создания пользовательского типа данных можно применить оператор Type.
Для объявления собственного типа данных используется следующая конструкция:
Туре ИмяПользовательскогоТипа
ИмяЭлемента1 As Type1 ' первый элемент встроенного типа 1
ИмяЭлемента1 As Type2 ' второй элемент встроенного типа 2
ИмяЭлемента1 As TypeN ' N-ый элемент встроенного типа N
End Туре
В качестве примера рассмотрим пользовательский тип данных Student. Атрибуты сущности Student - это группа связанных элементов: фамилия, имя, отчество, пол, дата рождения, название группы.
Туре Student
ФамилияSt As String
ИмяSt As String
ОтчествоSt As String
ПолSt As String
ДатаРождSt As Data
НазвГруппыSt As String
End Туре
Обычно инструкция Туре предназначена для использования в области видимости всего проекта, поэтому целесообразно ее разместить в коде отдельного стандартного модуля. Кроме того, там же можно объявить глобальную переменную пользовательского типа, например strСтудент. Скриншот образца стандартного модуля, представлен на рис. 1.
Рис. 1.
Далее созданный пользовательский тип данных Student и глобальная переменная strСтудент могут быть применены в других модулях. Например, в модулях форм или стандартных модулях можно объявлять переменную Студент1 типа Student: Dim Студент1 As Student, где Student - это пользовательский тип данных. Таким образом, имя пользовательского типа данных используется при объявлении переменной так же, как и имена встроенных типов данных. Кроме того, в модулях можно применять непосредственно глобальную переменную для обращения к элементам, входящим в состав пользовательского типа данных, например strСтудент.ИмяST = "Петр".
Пользовательский тип данных и глобальную переменную можно объявить и непосредственно в исполняемых модулях. На скриншоте рис.2 представлен модуль, в котором объявлены пользовательский тип данных и глобальная переменная. В процедуре Sub Progress() применена переменная strСтудент.
Рис. 2.
Вводим переменную пользовательского типа strСтудент и ставим точку, открывается всплывающее меню со списком имен структурных элементов. Выбираем требуемый элемент и дважды на нем щелкаем. Таким образом, осуществляется обращение к элементам, входящим в состав пользовательского типа данных.
Прочие типы данных
Логический тип Boolean - логические значения (принимает одно из двух логических значений: True - False, 2 байта).
Data - информации о дате и времени (от 1 января 100 года до 31 декабря 9999 года, 8 байт).
Object - ссылка на объект (ссылка на объект, 4 байта).
Если для переменной, которая используется в процедуре VBA , не объявлен тип данных, то, по умолчанию, будет задан тип данных Variant.
Типы Variant
§ Variant (числа) - числовое значение (любое числовое значение, 16 байт);
§ Variant (символы) - строковое значение (любое символьное значение 22 байта + длина строки).
Далее рассмотрим более подробно область переменных в программах VBA.
Выражения в VBA
Выражения
Для выполнения вычислений и других манипуляций в VBA применяются выражения. Выражение - это любая комбинация операндов или элементов данных (чисел, констант, переменных, и других операндов) и знаков операций. Знаки операций - это символы, которые используются для соединения операндов в выражении. Операнды (operands) в программировании - это объекты, над которыми выполняются определенные действия (операции).
Операции в VBA - это действие, которые производятся над операндами. Знаки операций в выражении имеют свои приоритеты. Выражения могут состоять из одного операнда, а могут состоять из сложной комбинации операндов и знаков операций. Результатом вычисления выражения является значение, которое должно иметь допустимый в VBA тип данных. Необходимо отметить, что в одном выражении нельзя использовать несовместимые типы данных.
Операции
К операциям, которые используются для записи выражений в VBA, относятся: операции присваивания; математические операции; логические операции; операции сравнения (отношений); операции для работы со строками (строковые операции).
Оператор - это наименьшая исполняемая единица программного кода VBA. Различают операторы выражения, операторы объявления, операторы присваивания, условный оператор, цикла и т.д. Операторы выражений - это процедуры или строка в тексте исполняемого кода программы. Операторы выражений выполняют какие-либо действия в программе.
Оператор присваивания - это связанное знаком равенства выражение, в котором значение выражения, указанное справа, присваивается переменной, находящейся слева от знака равенства. Например, в операторе присваивания strСтудент.ИмяST = "Петр", имя "Петр" присваивается структурному элементу "ИмяST" переменной пользовательского типа данных "strСтудент".
Арифметические операторы представляют собой инструкции, в которых применяются арифметические операции. В VBA используются арифметические операции: сложение (+); вычитание (-); умножение (*); деление (/); деление без остатка (\); остаток от деления по модулю (mod); возведение в степень (^). Например, result=(7+12)*5^3
Логические операторы - это инструкции, в которых применяются логические операции в логических выражениях. Логические выражения имеют значения типа Boolean, которое принимает одно из двух логических значений: True (1) или False (0). При выполнении логических операций логическим переменным присваивается результат вычислений, указанный в правой части операторов. К логическим операциям относятся: and, or, nod, xor, eqv, imp (логическое и, или, нет, логическое исключающее или, логическая эквивалентность, логическая импликация). Например,(9>8) Or (2>5) ' Результат является True (истина), так как одно из условий является True.
Операторы сравнения или отношений. Результатом операций сравнения является значение Boolean, которое принимает одно из двух логических значений: True (истина) или False (ложь). В операторах могут применяться с определенным синтаксисом следующие знаки операций сравнения: равенство (=); больше чем (>) и меньше чем (<); не равно (<>); больше или равно (>=) и меньше или равно (<=); сcылка на объект (Is); подобие (Like). Например, оператор равенства A1=A2 ' если A1 равно A2, то True (истина), если A1 неравно A2, то False (ложь).
Строковые операторы. В строковом операторе существует знак операции & (конкатенация), который предназначен для объединения нескольких строк в одну строку. Например, strСтудент = "Петров" & "Петр"В переменную strСтудент будет помещена строка Петров Петр.
В языке VBA есть универсальные типы данных, т.е. способные хранить как число, так и строку, дату и любой другой тип информации. Например, ячейка в таблице может содержать что угодно и изначально, программа не знает какой в ней тип данных хранится. Кроме того, в самой программе может использоваться тип данных Variant, который так же может содержать любое значение любого типа.
Чтобы определить какой тип данных в ячейке или в переменной типа Variant, можно воспользоваться несколькими способами.
Способ 1. Использовать функцию TypeName для определения типа данных
Эта функция возвращает строку с названием типа данных на английском. В качестве аргумента принимает переменную, значение ячейки.
Обратите внимание: Функция определяет только стандартные типы данных и не может определить пользовательский тип (определенный с помощью Type).
Возможные возвращаемые функцией значения:
| Byte | Число типа Byte |
| Integer | Целое число |
| Long | Длинное целое число |
| Single | Число одиночной точности с плавающей запятой |
| Double | Число двойной точности с плавающей запятой |
| Currency | Валюта |
| Decimal | Число с плавающей запятой |
| Date | Дата |
| String | Строка |
| Boolean | Логическое |
| Error | Ошибка |
| Empty | Не проинициализировано (т.е. переменная не была объявлена) |
| Null | Неверные данные (в переменной нет корректных данных) |
| Object | Объект (класс) |
| Unknown | Тип данных не известен |
| Nothing | Объект, никуда не ссылающийся |
Приведу несколько примеров по использованию TypeName.
Пример 1. Определение типа переменной.
Обратите внимание: если вы используете результат TypeName в условии, т.е. проверяете, соответствует ли тип данных определенному, например, Integer, то регистр символов возвращаемого типа имеет значение. Т.е. нужно писать Integer с заглавной буквы, либо использовать приведение всех символов к одному регистру.
Пример 2. Использование TypeName в условии.
Пример 3. Определение типа данных в ячейке.
Если функции была передана переменная массив, она вернет тип данных в массиве с добавлением скобок.
Пример 4. Определение типа массива.
Способ 2. Проверка на возможность преобразования строки к нужному типу.
Бывает ситуация, когда значение, например, число или дата, содержится в строке. В этом случае TypeName вернет String, а не Integer или Date. Чтобы узнать, что содержится в строке, можно воспользоваться одной из функций IsNumeric, IsDate, IsObject, IsArray, IsNull, IsError.
| IsNumeric | Проверяет может ли выражение быть преобразовано в число |
| IsDate | Проверяет может ли выражение быть преобразовано в дату |
| IsObject | Проверяет, является ли переменная объектом |
| IsArray | Проверяет, является ли переменная массивом |
| IsNull | Проверка на пустое значение |
| IsError | Проверка выражения на ошибку |
Пример 4. Определение может ли переменная быть преобразована в число.
К сожалению, как видим из примера, нет возможности проверить, содержится ли в строке число с плавающей точкой.
Пример 5. Определение содержит ли переменная дату (может быть преобразована в дату).
Проверка, содержится ли число или дата в ячейке листа делается аналогично, как и с переменными.
Помимо этих способов можно конечно еще придумать и другие, например, проверку строки с данными регулярным выражением или пройти по каждому символу в цикле и проверить цифра это или нет и тому подобное. Но на мой взгляд, описанных мной способов вполне достаточно для решения повседневных задач.
Читайте также: