What is a VBA Module?

In this Article

This tutorial will teach you about Class Modules in VBA. You’ll learn what they are and how to use them.

Return to VBA Code Examples

Returns the absolute value of a number.

Simple Abs Examples

Sub Abs_Example()
MsgBox Abs(-12.5)
End Sub

This code will return 12.5

In the VBA Editor, you can type  “Abs(” to see the syntax for the Abs Function:

The Abs function contains an argument:

Number: A numeric value.

Examples of Excel VBA Abs Function

you can reference a cell containing a date:

Sub Abs_Example1()
Dim cell As Range

For Each cell In Range(“A2:A4”)
cell.Offset(0, 1) = Abs(cell.Value)
Next cell
End Sub

Sub Abs_Example2()
Dim Numbers
Dim item
Dim closestValue As Double
Dim diff As Double
Dim minDiff As Double
minDiff = 100

Numbers = Array(1.5, 3.1, 2.1, 2.2, 1.8)

For Each item In Numbers
diff = Abs(item – 2)
If diff < minDiff Then
minDiff = diff
closestValue = item
End If
Next item

MsgBox “The closest value: ” & closestValue
End Sub

VBA Coding Made Easy

When you insert modules into the Visual Basic Editor (VBE) in order to enter your code, you may have noticed that you can also insert what is called a ‘Class Module’.

Class Modules vs. Modules

The class modules work in a very different way to the ordinary modules in that they facilitate creating a Component Object Model (COM) which can then be used within your normal VBA code

Effectively, you create an object which works in the same way as a built in Excel object such as ‘Worksheets’.  In the Worksheets Object, you have a number of properties and methods which allow you to get the number of worksheets within a workbook or each individual name of a worksheet, or numerous other information

You can also use it where you have placed Active X controls onto a worksheet, such as a command button or a drop down. These controls all use VBA, and your new object can easily be incorporated into the event code for these controls.

Finally, underneath all of this, you have the data services layer which holds all the data that you have entered into the worksheets and cells.  Excel accesses this using the Excel Object model.

Creating a Class Module allows you to extend the Excel Object Module with your own custom objects and members

This article explains to you how you to create a simple hierarchy of objects using Class Modules.

Advantages of Using Class Modules

Любой код VBA должен где-то храниться. Для хранения кодов в VBA используются модули, которые хранятся в книге. Книга может содержать сколько угодно модулей. Каждый модуль в свою очередь может содержать множество процедур(макросов).
Все имеющиеся в книге модули можно посмотреть через редактор VBA (Alt+F11). Если горячие клавиши не срабатывают, то в редактор Visual Basic можно перейти из вкладки Разработчик(Developer) -Visual Basic. Имеющиеся модули отображены в левой части редактора в проводнeике объектов(Project Explorer).

рис.1
Сам проводник объектов может быть не отображен по умолчанию и тогда его необходимо отобразить: нажать Ctrl+R либо в меню редактора VBA -View -Project Explorer

Модули делятся на пять основных типов:

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

на рис.1 Module1
Самый распространенный тип модулей, который используется в большинстве случаев. Именно в них макрорекордер создает записываемые макросы. Все коды и процедуры в таких модулях пишутся вручную, либо копируются из других источников(другого модуля, с этого сайта и т.п.). В основном именно в стандартных модулях содержится большая часть кодов. Они предназначены для хранения основных процедур и Public переменных, которые могут быть доступны впоследствии из любого модуля. Как создать стандартный модуль: в окне проводника объектов щелкаем правой кнопкой мыши –

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

Лист1 или Sheet1 –

на рис.1: Лист1(Лист1), Лист2(Лист2), Лист3(Лист3)

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

Исходный текст(View Code)в зависимости от версии Excel этот пункт на русском может называться так же: Просмотреть код или Исходный код

Можно и более трудным путем пойти – через редактор VBA: Alt+F11 и в окне Проводника объектов(Project Explorer) дважды щелкнуть по объекту с именем листа или правая кнопка мыши на модуле листа -View code.
Размещая код в модуле листа следует помнить, что при копировании или переносе данного листа в другую книгу код так же будет скопирован, т.к. является частью листа. Это и плюс и минус одновременно. Плюс в том, что разместив код в модуле листа можно использовать этот лист в качестве шаблона для распространения со своими кнопками вызова этих кодов(в том числе создания книг кодом) и весь функционал будет доступен. Минус же заключается в некоторых нюансах обращения к ячейкам(подробнее можно ознакомиться в этой статье: Как обратиться к диапазону из VBA) и необходимости размещения ВСЕХ используемых процедур в этом листе, иначе при переносе в другие книги коды могут работать с ошибками.

В модуле листа содержатся встроенные событийные процедуры, каждая из которых отвечает за обработку определенного события на этом листе. Посмотреть их можно так: выбираете объект(на рисунке ниже список в левой части) Worksheet, а в правом списке выбираете событие(в этом списке все процедуры, доступные для выбранного листа):

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

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

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

Worksheet_Change( Target Range)
MsgBox “Адрес измененной ячейки: ” & Target.Address & _
“; Адрес активной ячейки: ” & Selection.Address, vbInformation,

После этого запишите в ячейку A1 значение 5 и нажмите Enter. Событие Change сработает в момент завершения редактирования – т.е. в момент нажатия Enter. При этом будет произведен переход на ячейку A2(в большинстве случаев, если настройками не задано иное) и появится сообщение, которое покажет, что изменили ячейку A1, а выделена сейчас A2. Т.е. Target – это всегда ссылка именно на измененную ячейку независимо от того, что сейчас выделено. Данное событие(Worksheet_Change) не будет срабатывать при изменении значений ячеек с формулами. Только ручной ввод.

Примечание: для всех кодов, приведенных на сайте, достаточно просто открыть необходимый модуль(книги или листа) и вставить предложенный код. Корректировка может понадобиться только в случаях, когда в модуле Листа или Книги вашего файла уже имеется код в необходимой событийной процедуре.

ВАЖНО: могут быть обработаны только те событийные процедуры, которые можно выбрать из списка справа. Если простым языком – каждое событие из этого списка VBE регистрирует “внутри себя”, что позволяет в дальнейшем его отслеживать. Если просто написать какое-либо событие “от себя” – то оно никогда не запустится, потому что VBE про него ничего не знает. Например, нам необходимо отследить смену имени листа. Мы создаем событие вроде Worksheet_ChangeName(). Но оно никогда не запустится, т.к. про такое событие VBE ничего не знает, нигде его не регистрирует и не отслеживает.

ЭтаКнига или ThisWorkbook:

В модуль книги можно попасть только через проводник объектов(Project Explorer) редактора VBA – двойной щелчок по ЭтаКнига (ThisWorkbook)

или правая кнопка мыши на модуле –

. В модуле книги так же содержатся “встроенные” событийные процедуры. Так же как и для листа выбираем в списке объектов(вверху слева)

. В правом окне выбора процедур, так же как и с модулем листа, будут все процедуры, доступные для объекта

. Пример использования событийных процедур книги можно посмотреть в статье Как отследить событие(например выделение ячеек) в любой книге?
Но там применяются все те же правила – главное не забывать про аргументы, доступные из этих процедур и передаваемые им самим Excel. Например, для события

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

. Вот пример подобного кода:

Workbook_BeforeClose(Cancel )
Me.Sheets().Range().Value =
MsgBox “Необходимо заполнить ячейку A1 на листе ‘Отчет'”, vbCritical,
Cancel = ‘отменяем закрытие книги

Из кода видно, что на листе “Отчет” должна быть не пустой ячейка A1(лист “Отчет” тоже должен существовать в этой книге). Но есть и еще одна вещь – какое-то Me. Это краткое обращение к объекту модуля класса, в данном случае это равнозначно обращению ThisWorkbook.
А ниже пример кода для модуля ЭтаКнига, который запрещает сохранять исходную книгу, разрешая сохранить её только через пункт Сохранить как(SaveAs):

Workbook_BeforeSave( SaveAsUI , Cancel )
SaveAsUI = ‘используется простое сохранить
MsgBox “Эта книга является шаблоном. Сохранять её можно только через Сохранить как”, vbCritical,
Cancel = ‘отменяем сохранение книги

Такое может потребоваться, если книга является шаблоном с полями для заполнения и необходимо предотвратить случайное сохранение исходного документа. Хотя это можно так же сделать без макросов – книгу можно сохранить с правами только на чтение.
И еще один пример – отслеживание активации листов. Может пригодиться, чтобы при активации конкретного листа сделать какое-либо конкретное действие. В коде ниже при активации листа “Лист4” будет показываться сообщение, но действие может быть любым(обновление данных, снятие защиты, установка защиты и т.п.):

Workbook_SheetActivate( Sh )
Sh.Name =
MsgBox “Вы перешли на Лист4”, vbInformation,

Обращаю внимание, что здесь, как и раньше, мы используем передаваемые процедуре аргументы. В данном случае это объект Sh – это именно тот лист, который мы активировали.

ClassModule – на рис.1 Class1.
В большинстве случаев создается специально для отслеживания событий различных объектов. Вряд ли понадобиться начинающим изучение VBA, хотя все зависит от поставленной задачи. Но обычно начинающим изучать это кажется слишком сложным. В любом случае, перед работой с модулями классов лучше научиться хоть чуть-чуть работать с обычными модулями и самостоятельно писать процедуры. Как добавить такой модуль: в окне проводника объектов щелкаем правой кнопкой мыши-Insert-Class Module. Подробнее про модули классов и работу с ними можно почитать в этой статье: Работа с модулями классов. Там описаны все основные принципы и приложен файл примера.

Иногда нужно модуль из одной книги переместить в другую. Сделать это можно несколькими способами. Самый простой – открыть обе книги, перейти в проводник проектов -найти нужный модуль -захватить его левой кнопкой мыши и не отпуская кнопку перетащить на проект другой книги:

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

(откуда хотим копировать) -копируем весь код -переходим в модуль ЭтаКнига второй книги и вставляем скопированное:

Экспорт модуля(сохранение в отдельный файл)

Если же надо сохранить стандартный модуль, модуль класса или модуль формы и не переносить сразу же в другую книгу, то можно экспортировать модуль. Для чего это может быть нужно? Как правило, чтобы перенести коды из дома на работу, переслать кому-то на другой ПК(пересылка файла с макросами может быть запрещена политикой безопасности компании) и т.п. Делается это просто: щелкаем на модуле правой кнопки мыши -Export file.
У экспортируемых модулей есть разные расширения, в зависимости от типа модуля. Для стандартных модулей это

(Module1.bas), для модулей класса –

(Class1.cls). А вот для модулей форм будет создано целых два файла:

. Их важно хранить вместе – один без другого не может быть импортирован в дальнейшем в файл. В файле

хранится информация об визуальном отображении формы и её элементах, если можно так сказать. В файле

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

Импорт модуля(перенос экспортированного ранее в новую книгу)

Для переноса экспортированного модуля в другую книгу надо просто в проводнике объектов выделить нужный проект правой кнопкой мыши -Import module -выбрать в диалоговом окне нужный модуль.
Экспортировать можно любой модуль, а вот импортировать – нет. Модули листов и книг хоть и экспортируются в отдельные файлы(кстати, с расширением .cls), но импортировать их не получится в том виде, как это предполагается. Они будут импортированы как новые модули класса и только. Поэтому для переноса кодов из модулей листов и книг придется использовать все равно копирование и вставку непосредственно кодов.
И в довершение – можно переносить модули автоматически, кодами VBA: Как добавить код процедуры программно, скопировать модуль

Также см.:
Копирование модулей и форм из одной книги в другую
Что такое макрос и где его искать?
Как удалить макросы в книге?

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Turning Your Object into an Add-In

Select the file type as Add-In (.xlam) from the file type drop down and click OK.  The file will be saved to the Add-In folder by default, but you can change the location.

You can then incorporate the add-in file into your Excel applications, giving you the flexibility to make use of your new object

To include your new Add-In into Excel, click on File on the Excel ribbon, and then click on Options at the bottom of the left-hand pane

Click on ‘Add-Ins’ in the left-hand pane in the pop-up window that appears.  At the bottom of the window is a button marked ‘Go’

Click on this and an ‘Add-In’ pop-up window will appear. Click on ‘Browse’ and then locate your Add-In file. You will then be able to refer to your object in your code.

Exporting a module or form from the Project Explorer

Right-click on the module or form you wish to export to show the right click short cut menu.

Select the module/form you wish to Export, and click on the File menu.  Then select Export File.

Select the location you wish to save the module to, and then click Save.

NOTE: when you Export a form or a module, it saves it as an individual file on your PC, but it DOES NOT remove it from your VBA Project.

What is a VBA Module?

A VBA module is used to store any VBA code that you have written in the VBE (Visual Basic Editor).

The modules are contained within a VBA Project and when the file is saved – be it an Excel workbook, Word document or Access database, the module or modules are saved within that file – that file is essentially the parent application of the module.

Modules can also be exported out of the parent file and saved as their own individual files.  This is useful when you want to re-use code in a different file, and therefore perhaps import that module into a new file.

Using a Class Module to Create a Variable Repository

When you write VBA code you use variables all over the place, all with different scopes.  Some may only be defined for a particular procedure, some for a particular module, and some may be global variables that can be used all over the application

The added advantage is that when you refer to your variable object, you will see a list of all the variable names held in the object sorted into ascending order.

Change the name to ‘MyVariables’ using the same methodology as previously discussed in this article.

Private mV As Variant
Public Property Get Variable1() As Variant
Variable1 = mV
End Property

Public Property Let Variable1(ByVal vNewValue As Variant)
mV = vNewValue
End Property

Public Property Get Variable2() As Variant
Variable1 = mV
End Property

Public Property Let Variable2(ByVal vNewValue As Variant)
mV = vNewValue
End Property

This code sets up ‘Let’ and ‘Get’ properties for two variables (‘Variable1’ and ‘Variable2’).  The Let and Get properties are required for each of your variables so that they are read / write

You can use your own names for the variables instead of the sample ones in this code, and you can add further variables, making sure that each new variable has a ‘Let’ and ‘Get’ statement.

The private declaration of the variable ‘mV’ is to create a working variable that is only used within the class module to transfer values.

Global VarRepo As New MyVariables
Sub TestVariableRepository()
MsgBox VarRepo.Variable1
VarRepo.Variable1 = 10
MsgBox VarRepo.Variable1
End Sub

This code creates a global instance of your ‘MyVariables’ object that you created.  You only need to do this declaration once from anywhere within your code.

The code first displays the value of ‘Variable1’ to show that it is empty.

A value of 10 is assigned to ‘Variable1’ and the new value within the object is then displayed to show that this property now holds this value.

Because the instance of the ‘MyVariables’ object has been defined globally, you can refer to any of the defined variables within the object from anywhere within your code.

This has a huge advantage in that if you want to use your variables anywhere in your code, you only need to define one global variable, and from that instance, all the variables can be freely accessed and modified throughout your code.

Creating an Object Item

This example will create a top-level object called ‘MyItems’ with a member object below it called ‘MyItem’ which will hold the individual data for each item.  Once created it will work in the same way as a built in Excel Object. For example, there is an object called ‘Worksheets’ which is a collection of each worksheet within your workbook. There is also an object called ‘Sheet’ which represents each individual worksheet within your workbook, and holds all the properties and methods for each worksheet.  This object relates to the ‘Worksheets’ collection object.

You can iterate through the ‘Worksheets’ collection, viewing each ‘Sheet’ in turn.  In the same way you will be able to iterate through the ‘MyItems’ collection viewing the properties that you created in the ‘Myitem’ member.

The first thing to do is to create the sub object for the member level which will hold the actual items within the collection of the top-level object.  This is the equivalent of the members (e.g. name, visible, count) within the ‘Sheet’ object in Excel.  This code is entered into the class module called ‘MyItem’

Class modules have Properties and Methods.  Properties are effectively like variables, in that they hold values of data like variables, and Methods are like sub routines or functions.

In the sub object we are going to create two properties for the object – Item and Detail

Initially two string variables need to be declared to hold the values for the properties:

Private mItem As String
Private mDetail As String

These need to be declared in the Declarations section at the top of the code for the class module so that they can be used in all sub routines throughout the module

They need to be given unique names to make them different from the properties that we are going to create, so an ‘m’ (for member) has been put in front of each name.

The variables are declared as Private so they cannot be seen by anyone using the object.  They are working variables for use within the object code and are not there as part of the final object.

The next step is to set up code to give access to the two properties. You do this by means of a Property Let and a Property Get statement for each property.  These must be Public otherwise the top-level object will not have any visible properties

Public Property Let Item(vdata As String)
mItem = vdata
End Property

Public Property Get Item () As String
Item = mItem
End Property

Public Property Let Detail (vdata As String)
mDetail = vdata
End Property

Public Property Get Detail () As String
Detail = mDetail
End Property

This code creates the means to read and write values to the two properties (Item and Detail) using the two private variables that were defined in the declarations section of the module.

The ‘vdata’ parameter is used to pass data to the property concerned.

It is important that each property has a ‘Let’ and ‘Get’ statement and that the property name is the same in each case. You could end up with two different properties if miss-spelt  – one that you can read from and one that you can write to!

This will display a pop-up window where you type the property name in and select ‘Property’ on the radio buttons:

Click ‘OK’ and the skeleton code will be added into the class module:

Public Property Get MyProperty() As Variant

End Property

Public Property Let MyProperty(ByVal vNewValue As Variant)

End Property

This prevents any mistakes over names of properties. You simply add your code in between the ‘Public Property’ and ‘End Property’ statements.

You now have an object called ‘MyItem’ which will hold all the data for this exercise.

Entering Code into a VBA Module

Once you have created your module, you will want to start typing your procedure. Most procedures are Sub Procedures.

Sub gridlines()
ActiveWindow.DisplayGridlines = False
End Sub

Creating a new procedure – Alternative Method

This dialog box is a good way to learn about the options for Procedures.

When you have filled in all the relevant details, click on OK.

You then type your code between the Sub and End Sub statements.

Summary of Creating an Object Using a Class Module

As you have seen, creating a hierarchy of class modules to use as an object is quite a complicated business, even for a structure as simple as the example provided here.  The scope for making mistakes is enormous!

In this example of how to create an object to hold data, it would be a normal approach to create a multi-dimensional array to hold the multi-column spreadsheet data, and you would write a line of code to update or read each element in the array. This would probably end up being quite messy, and mistakes could easily be made in addressing the various elements.

With your new object, you can simply refer to it and the members that you have created below it to hold the data.

Also, if the data changes in the spreadsheet (or in a linked database if you have used this as a data source within your class module) whenever you use the ‘Dim’ statement the initialize routine will be called and the data will be instantly updated. No need to write code to re-populate your array.

Inserting a module or form into your code

To insert a new module into your code, click on the Insert option on the menu bar, and click Module.

Or, click on the Insert Module button which you will find on the standard ribbon.

You can also insert a Class Module

A class module is used to insert objects into your VBA project.

Removing a module or form from the Project Explorer

Right-click on the module or form you wish to remove to show the right click short cut menu.

A warning box will appear asking if you want to Export the form or module before you remove it.  Exporting the form or module enables you to save it as an individual file for use in a different Excel project at some other time.

More often than not when you remove a module or form it is because you do not need it, so click No.

Creating a Collection

The next stage is to create a top-level object as a Collection object to give access to the properties that you have set up in the ‘MyItem’ object

Again, you need to define a working object to act as the collection object in the same way that you defined the two string variables in the ‘MyItem’ object.

Private mItems As Collection

Again, this this has to have a unique name which is why there is an ‘m’ (member object) in front of the name, and it is also declared as ‘Private’ so that it does not appear when the new object is being used

Next, you need to populate the Class_Initialize code. This runs when you first use the object within your code, and it determines what values will be loaded into the object

You can access this sub routine by selecting ‘Class’ in the first drop down and ‘Initialize’ in the second drop down of the module window

Private Sub Class_Initialize()
Dim objItem As MyItem
Set mItems = New Collection
For n = 1 To 3
Set objItem = New MyItem
objItem.Item = Worksheets(“Sheet1”).Range(“a” & n).Value
objItem.Detail = Worksheets(“Sheet1”).Range(“b” & n).Value
mItems.Add objItem
Next n
End Sub

The code sets up an object called ‘objItem’ using the definition of ‘MyItem’ which we built as a class module earlier on.

It then creates a new Collection based on the ‘mItems’ object defined earlier

It iterates through values held on Sheet1 of the workbook and puts them into the properties that we created for the ‘MyItem’ object.  Note that when you use ‘objitem’, a drop down appears showing the two properties, exactly as if you were using a built-in Excel object.

The item object is then added into the collection object which now holds all the data in the property values.

The input data does not have to be taken from a worksheet.  It could be static values, or it could come from a connection to a database such as Microsoft Access or SQL Server, or it could come from another worksheet.

You then need to add a public function called ‘Item’

Public Function Item(index As Integer) As MyItem
Set Item = mItems.Item(index)
End Function

This allows you to refer to individual objects within the collection object by their index number.  This function provides a ‘mirror’ of what is going on in the ‘mMyItems’ collection in the background.

You will also need to add a property called ‘Count’ so that your code can establish how many ‘MyItem’ objects are in the ‘MyItems’ collection, should you wish to iterate through it.

Public Property Get Count() As Long
Count = mItems.Count
End Property

In this case you only need a ‘Get’ property because it is read-only.  It uses the mItems collection because this already has a count property built into it.

You now have an object (MyItems) with a full hierarchy defined by the object ‘MyItem’

To make the whole thing work, you now need to populate a worksheet (Sheet1) with data so that the Class Initialize routine can collect this into the object

Your spreadsheet should look like this:

Importing a VBA module or form into your VBA Project

Right click on your VBA Project and then click Import File.

Select the file you wish to import, and click Open.

The module or form you have imported will now appear in your Project Explorer.

Sub vs Function Procedures

You may have noticed that there are 2 types of procedures you can create – a SUB PROCEDURE or a FUNCTION PROCEDURE.

If you have recorded a macro in Excel or Word, your macro will be put into a Standard module and will be put into a sub procedure. The macro recorder can only record sub procedures.  A Sub procedure does things.  They perform actions such as formatting a table, creating a pivot table, or  changing the view settings of your active window.  The majority of procedures written are Sub procedures.  All macros are Sub procedures.

A Function procedure returns a value.  This value may be a single value, an array, a range of cells or an object.  Functions usually perform some type of calculation.   Functions in Excel (UDFs) can be used with the Function Wizard.

Inserting a Class Module

You change the name in the Properties window where the arrow is pointing.  You simply type in your new name, and this will change in the Class Modules collection

Call your new class module ‘MyItem’ and double click the name in the tree-view in the Project Explorer to display the code window for it.

Using Your New Object

Sub test_object()
Dim MyClass As New MyItems, n As Integer
MsgBox MyClass.Count
For n = 1 To MyClass.Count
MsgBox MyClass.Item(n).Item
MsgBox MyClass.Item(n).Detail
Next n
End Sub

This code creates an object called ‘MyClass’ based on the collection object that you created called ‘MyItems’. This fire off the ‘Initialize’ routine that extracts all the data from the worksheet into the object.

It displays the number of items in the collection and then iterates through the collection showing the ‘Item’ text and the ‘Detail’ text. You will notice that when you refer to the ‘MyClass’ object in your code, you will see a list of the two member properties which helps in adding the correct property.

If you change the value of a cell in the input data on the spreadsheet, this will automatically be updated in the collection when you run the above code again, since when you dimension the object, the initialize routine runs and picks up all the new data

If you use the word ‘Static’ instead of ‘Dim’ the initialise routine does not run and the old values are kept, so long as the code is continuously running.  If the data on the spreadsheet changes this will not be reflected in the object

Sub Test_Static()
Static Myclass As New MyItems, n As Integer
For n = 1 To Myclass.Count
MsgBox Myclass.Item(n).Item
MsgBox Myclass.Item(n).Detail
Next n
End Sub

Type of modules

The modules are organised into 3 different types.

When your write your VBA code, you will usually use more than one module.  It is good coding practice to ‘group’ your code into relevant modules – for example put all the global variables in one module, all public functions in another module etc.

Работа с модулями классов

Многие наверняка слышали про модули классов, но не все их используют. На самом деле довольно многие программирующие на VBA за все время программирования прекрасно обходятся без применения модулей классов. Т.к. VBA не является языком объектно-ориентированного программирования(ООП) в строгом смысле слова, то пользовательские классы здесь не обязательны и как следствие не так уж и часто используются при разработке. Это не значит, что VBA не содержит модулей классов: модули книги, листов, пользовательские формы – все это модули классов. Многие, кстати, используют их даже не зная того, что используют именно модули классов. Т.к. модуль листа, книги и формы – это модуль класса, то почти каждый, кто работал с формой работал с модулем класса. В чем их большая польза – с их помощью можно отслеживать различные события объектов. Для форм это события самой формы или любого её элемента – например CommandButton_Click или TextBox_Change. Но мы сейчас рассмотрим лишь тот тип модулей, который в VBA обычно называют модулем класса – Class Module.

Модуль класса(Class Module) – это модуль, содержащий программные коды, которые реализуют работу пользовательских классов. В подавляющем большинстве случаев создается специально для отслеживания событий различных объектов. Создается так же, как и любой другой объект проекта: в окне проводника объектов щелкаем правой кнопкой мыши на нужном проекте-Insert-Class Module

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

TextBox1_Change()
MsgBox “Изменено значение TextBox1”

TextBox2_Change()
MsgBox “Изменено значение TextBox2”

TextBox3_Change()
MsgBox “Изменено значение TextBox3”

С одной стороны – все верно. А с другой: что если таких текстбоксов у нас не 3, а 43? Не очень удобно для каждого событие прописывать. Да и читабельность такой “портянки” кода тоже значительно падает.
Или другая ситуация – необходимо “на ходу” создать ТекстБоксы на форме и в дальнейшем отслеживать их события. Как тут быть? Ведь раз ТексБоксов еще нет – то и события в форме для них не создать. Создание для них кодов обработки событий заранее ничего не даст – они не будут связаны с самими объектами, поэтому и пытаться даже не стоит. Почему так – при создании элемента вручную VBE делает за нас всю грязную работу – он сам ассоциирует созданный объект с событиями, предназначенные для него заранее. Если же создать объект программно – то часть грязной работы придется делать самим. И создание модуля класса, с описанием в нем объекта ТекстБокс и его событий, как раз очень даже подойдет.
Рассмотрим сразу оба случая. Что нам для этого потребуется:

Tips_Macro_UseClassModules.xls (63,5 KiB, 6 042 скачиваний)

Для начала создадим на нашей форме frmTest 4 ТекстБокса, не меняя их имена(по умолчанию они будут TextBox1, TextBox2, TextBox3, TextBox4). Это для того, чтобы понять как применить модули класса к уже созданным ранее на форме элементам.
Далее в стандартный модуль mMain поместим следующий код:

aoTxtBxes(1 8) clsmTxtBxes
Show_Form()
frmTest.Show

aoTxtBxes – массив, который будет содержать до 8 ТекстБоксов. Объявляется как Public (чтобы был доступен из любого модуля проекта. Подробнее в статье: Что такое переменная и как правильно её объявить?). Обращаю внимание, что данный массив объявлен как созданный нами модуль класса – As clsmTxtBxes. Это обязательное условие. Если у вас модуль класса называется ClassModule1, то и объявлять aoTxtBxes следует соответственно:

aoTxtBxes(1 8) ClassModule1

но я не приветствую подобный подход, т.к. имя ClassModule1 ни о чем нам не говорит, в то время как clsmTxtBxes сразу дает понять, что там мы обрабатываем ТекстБоксы. Хотя это дело вкуса. Если в одном модуле класса собраны различные событийные процедуры для разных типов(TextBox, ComboBox, ListBox и т.д.) – то конечно, имя лучше дать более общее.
Теперь в созданный модуль класса clsmTxtBxes запишем создание объекта и код, который хотим применить для всех наших ТекстБоксов:

oTxtBx MSForms.TextBox
‘событие изменения текста в TextBox-ах
oTxtBx_Change()
MsgBox “Вы изменили значение ” & oTxtBx.Name, vbInformation,

Public WithEvents oTxtBx As MSForms.TextBox – создаем объект типа ТекстБокс с отслеживанием его событий. Идентификатором объекта с отслеживанием событий служит оператор WithEvents (может применяться только в модулях классов).
Если необходимо отследить изменения не TextBox, а ComboBox, то соответственно объявляем объект нужного типа:
Public WithEvents oCmbBx As MSForms.ComboBox
Сами события для контролов не берутся из головы и не пишутся вручную – они уже есть и следует использовать именно те, которые доступны. Чтобы для конкретного элемента создать событие, необходимо перейти в модуль класса, вверху в левой части выбрать из списка нужный объект(в нашем случае это oTxtBx) и после этого в правом списке выбрать событие(в этом списке перечисляются все процедуры, доступные для выбранного объекта):

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

Завершающий этап – создаем код в модуле формы frmTest, который создаст недостающие ТекстБоксы и свяжет их и ранее созданные с модулем класса:

Кратко описать, что делает эта процедура, можно так:

Если необходимо больше ТекстБоксов обработать – увеличиваем верхнюю границу массива aoTxtBxes(если хотим вместить 20 текстбоксов – Public aoTxtBxes(1 To 20) As New clsmTxtBxes). Если заранее неизвестно количество – либо задаем с запасом, либо объявляем aoTxtBxes как динамический массив(Public aoTxtBxes() As New clsmTxtBxes), а границы определяем в процессе(посредством ReDim Preserve). Но это уже совершенно другая тема.

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

Me.Controls.Add(, & i)

и соответственно изменить/добавить тип переменной в модуле класса:

oCmbBx MSForms.ComboBox

Всего для создания доступно 11 встроенных типов контролов:
ComboBox – MSForms.ComboBox
CheckBox – MSForms.CheckBox
CommandButton – MSForms.CommandButton
Frame – MSForms.Frame
Image – MSForms.Image
Label – MSForms.Label
ListBox – MSForms.ListBox
MultiPage – MSForms.MultiPage
SpinButton – MSForms.SpinButton
TabStrip – MSForms.TabStrip
ToggleButton – MSForms.ToggleButton

Также см.:
Что такое модуль? Какие бывают модули?
Что такое переменная и как правильно её объявить?
Variable not defined или что такое Option Explicit и зачем оно нужно?