Excel код в модуле книги

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

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Пример		14 Кб	2795

Рассмотрим два примера:

Ищем и используем команду
Ищем и используем функцию

1. Ищем и используем команду

Например, нам необходима команда, которая бы вставляла в выделенные ячейки цифру «1». Запускаем поисковик, набираем поисковую фразу, получаем результаты, начинаем просматривать, находим код примерно в таком виде:

Sub Вставка1()
    Dim q As Object
    On Error Resume Next
    Set q = Cells
    For Each q In Selection
        q = 1
    Next q
End Sub

Выделяем данный код (без нумерации строк, начиная со слова Sub) и нажимаем Ctrl+C. Переходим в свою рабочую книгу MS Excel и нажимаем сочетание клавиш Alt+F11, у вас откроется окно редактора VBA:

В левом окне «Project — VBA Project» выбираем (щелкаем мышкой) нашу рабочую книгу, в которую необходимо вставить макрос, например, «VBAProject (Книга2)»:

Я не вижу окна «Project — VBA Project».

В пункте меню «Insert» выбираем «Module»:

В левом окне «Project — VBA Project» у вас должна появиться новая папка «Modules» и в ней новый объект «Module1»:

Переходим курсором в правое «Большое» поле для ввода и нажимаем Ctrl+V, скопированный макрос вставиться в модуль:

У меня в коде «????????«

После чего закрываем редактор VBA и возвращаемся в рабочую книгу.

Если вы работаете в MS Excel 2007, 2010 или 2013, вам необходимо будет сохранить вашу рабочую книгу как «Книга Excel с поддержкой макросов (.xlsm)»:

В MS Excel 2003 достаточно будет просто сохранить файл.

Чтобы выполнить скопированный в книгу макрос выделяем ячейки, в которые необходимо вставить «1», нажимаем в меню «Вид» кнопку «Макросы» и в выпавшем списке выбираем пункт «Макросы» или нажимаем сочетание клавиш Alt+F8:

Откроется диалоговое окно «Макрос», в списке макросов выбираем свой и нажимаем кнопку «Выполнить»:

Макрос выполнится — в выделенные ячейки вставиться «1»:

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

Макросы в MS Excel можно вставлять в следующие места:

• В Модуль, обычно вставляют код макросов, запуск которых будет производится по нажатию кнопки пользователем (как, например, в нашем случае) или код функций (формул);
• В Рабочий лист, обычно вставляют код макросов, запуск которых должен происходить автоматически в зависимости от действий пользователя или изменения данных в листе (поменялись данный, макрос выполнился);
• В Рабочую книгу, обычно вставляют код макросов, запуск которых должен происходить автоматически в зависимости от действий, производимых над книгой (файлом). Например, макрос, который запускается при открытии или закрытии книги, или при ее сохранении;
• Так же макросы могут быть частью пользовательской формы.

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

Чтобы вставить код в Рабочий лист, в левом окне редактора VBA выберите соответствующий лист, щелкните по нему два раза левой кнопкой мышки, переместите курсор в правое поле ввода и вставьте код.

Для вставки кода в Книгу, выберите «ЭтаКнига»:

Давайте потренируемся. Вставьте код опубликованный ниже в «Лист1».

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo a
    If Target = 2 Then
        VBA.MsgBox ("Ячейка " & Target.Address & " = 2")
    End If
a:
    Exit Sub
End Sub

Данный макрос выводит информационное сообщение если вы введете в любую ячейку листа «2».

Вернитесь в рабочую книгу, перейдите в «Лист1» и введите в ячейку «А1» цифру «2» и нажмите Enter, после чего у вас должно появиться следующее сообщение:

Если вы видите это сообщение, то вы все сделали правильно. Если нет, то вы вставили код куда-то не туда, повторите попытку.

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

2. Ищем и используем функцию

Находим в интернете VBA код функции, которая подсчитывает, например, количество слов в ячейке:

Public Function КолСловВЯчейке(Ячейка As Range)
    Dim q As Variant
    Application.Volatile
    q = VBA.Split(Application.WorksheetFunction.Trim(Ячейка.Value), " ")
    КолСловВЯчейке = UBound(q) + 1
End Function

Копируем код, нажимаем сочетание клавиш Alt+F11, откроется редактор VBA:

Добавляем новый модуль в свою книгу и в этот модуль вставляем скопированный код:

Закрываем редактор VBA и переходим в свою рабочую книгу. Создаем новый лист (необязательно) в ячейку A1 вводим текст «мама мыла раму». Встаем в ячейку, в которой хотим получить результат (количество слов), в меню «Формулы» нажимаем кнопку «Вставить функцию»:

В открывшемся окне «Вставка функции» в поле «Категория» выбираем «Определенные пользователем»

В списке доступных функций выбираем «КолСловВЯчейке», нажимаем «ОК»:

Вводим необходимые аргументы и нажимаем «ОК»:

Получаем результат:

Важно:

Если вы не сохраните книгу, в которую вставили макрос как «Книгу с поддержкой макросов», все модули с макросами удаляться и вам придется, потом, повторно проделывать всю эту работу.

Если при вставке макроса в модуль у вас вместо некоторого текста стоят красные знаки «????????«

Это значит, что у вас проблема с кодировкой, которая наблюдается с копированием Кириллического текста из некоторых браузеров. Чтобы победить эту проблему, попробуйте вставить скопированный код в пустой лист MS Excel, как «Текст в кодировке Unicode». Для этого перейдите в книгу MS Excel, выберите или создайте пустой лист, встаньте в ячейку «A1» и нажмите сочетания клавиш Ctrl+Alt+V. Должно будет появиться меню «Специальной вставки», выберите пункт «Текст в кодировке Unicode» и нажмите «OK».

Код должен будет вставиться в рабочий лист без знаков вопроса:

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

Если вы не видите в редакторе VBA окна «Project — VBA Project», перейдите во вкладку меню «View» и в списке выберите пункт «Project Explorer» или нажмите сочетание клавиш Ctrl+R:

Хитрости »

3 Август 2013              198837 просмотров

Как добавить код процедуры программно, скопировать модуль

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

Для изменения кодов программно необходимо, чтобы было проставлено доверие к объектной модели проекта VBA и изменяемый проект не должен быть защищен. Подробнее читайте в статье: Что необходимо для внесения изменений в проект VBA(макросы) программно
Без этого будет невозможно программное вмешательство в проект VBA.

В данной статье я покажу как программно выполнить следующее:

• Как копировать модуль из одной книги в другую;
• Как создать новый модуль;
• Как создать событийную процедуру (изменение данных на листе, открытие книги и т.п.).

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

Теперь перейдем непосредственно к сути. Сначала рассмотрим добавление в проект стандартного модуля.
Для добавления стандартного модуля и кода в нем можно воспользоваться двумя методами:

1. Экспорт имеющегося модуля(с процедурами) из книги с кодом в новую книгу. Чаще всего применяется когда кодов для записи в новую книгу довольно много и создавать их все, прописывая в коде, весьма неудобно и громоздко;
2. Создание нового модуля и запись необходимых кодов в него. Применяется, если необходимо создать относительно короткие процедуры в модуле.

Sub Copy_Module()
    Dim objVBProjFrom As Object, objVBProjTo As Object, objVBComp As Object
    Dim sModuleName As String, sFullName As String
    'расширение стандартного модуля
    Const sExt As String = ".bas"
 
    'имя модуля для копирования
    sModuleName = "Module1"
    On Error Resume Next
    'проект книги, из которой копируем модуль
    Set objVBProjFrom = ThisWorkbook.VBProject
    'необходимый компонент
    Set objVBComp = objVBProjFrom.VBComponents(sModuleName)
    'если указанного модуля не существует
    If objVBComp Is Nothing Then
        MsgBox "Модуль с именем '" & sModuleName & "' отсутствует в книге.", vbCritical, "Error"
        Exit Sub
    End If
    'проект книги для добавления модуля
    Set objVBProjTo = ActiveWorkbook.VBProject
    'полный путь для экспорта/импорта модуля. К папке должен быть доступ на запись/чтение
    sFullName = "C:" & sModuleName & sExt
    objVBComp.Export Filename:=sFullName
    objVBProjTo.VBComponents.Import Filename:=sFullName
    'удаляем временный файл для импорта
    Kill sFullName
End Sub

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

Sub Create_NewModule()
    Dim objVBProj As Object, objVBComp As Object, objCodeMod As Object
    Dim sModuleName As String, sFullName As String
    Dim sProcLines As String
    Dim lLineNum As Long
 
    'добавляем новый стандартный модуль в активную книгу
    Set objVBComp = ActiveWorkbook.VBProject.VBComponents.Add(1)
    'получаем ссылку на коды модуля
    Set objCodeMod = objVBComp.CodeModule
    'узнаем количество строк в модуле
    '(т.к. VBA в зависимости от настроек может добавлять строки деклараций)
    lLineNum = objCodeMod.CountOfLines + 1
    'текст всставляемой процедуры
    sProcLines = "Sub Test()" & vbCrLf & _
        "    MsgBox ""Hello, World""" & vbCrLf & _
        "End Sub"
    'вставляем текст процедуры в тело нового модуля
    objCodeMod.InsertLines lLineNum, sProcLines
End Sub

Данная процедура добавит в активную книгу новый модуль и запишет в него процедуру:

Sub Test()
    MsgBox "Hello, World"
End Sub

• в Лист1 на изменении данных ячейки в новой книге
• в ЭтаКнига(ThisWorkbook) на событие открытия книги.

Sub CreateEventProcedure_WorkSheetChange()
    Dim objVBProj As Object, objVBComp As Object, objCodeMod As Object
    Dim lLineNum As Long
    'добавляем новую книгу
    Workbooks.Add
    'получаем ссылку на проект и модуль листа
    Set objVBProj = ActiveWorkbook.VBProject
    Set objVBComp = objVBProj.VBComponents("Лист1")
    Set objCodeMod = objVBComp.CodeModule
    'вставляем код
    With objCodeMod
        lLineNum = .CreateEventProc("Change", "Worksheet")
        lLineNum = lLineNum + 1
        .InsertLines lLineNum, "    MsgBox ""Hello World"""
    End With
End Sub

Важно: для русской версии используется ссылка на Лист1. Для английской как правило Sheet1

Set objVBComp = objVBProj.VBComponents("Sheet1")

 
CОЗДАНИЕ СОБЫТИЙНОЙ ПРОЦЕДУРЫ Workbook_Open

Sub CreateEventProcedure()
    Dim objVBProj As Object, objVBComp As Object, objCodeMod As Object
    Dim lLineNum As Long
    'добавляем новую книгу
    Workbooks.Add
    'получаем ссылку на проект и модуль книги
    Set objVBProj = ActiveWorkbook.VBProject
    Set objVBComp = objVBProj.VBComponents("ЭтаКнига")
    Set objCodeMod = objVBComp.CodeModule
    'вставляем код
    With objCodeMod
        lLineNum = .CreateEventProc("Open", "Workbook")
        lLineNum = lLineNum + 1
        .InsertLines lLineNum, "    MsgBox ""Hello World"""
    End With
End Sub

Важно: для русской версии используется ссылка на ЭтаКнига. Для английской ThisWorkbook

Set objVBComp = objVBProj.VBComponents("ThisWorkbook")

 
Функция CopyVBComponent
ПОЗВОЛЯЕТ КОПИРОВАТЬ ЛЮБОЙ КОМПОНЕНТ ИЗ ОДНОЙ КНИГИ В ДРУГУЮ

'---------------------------------------------------------------------------------------
' Procedure : CopyVBComponent
' DateTime  : 02.08.2013 23:10
' Author    : The_Prist(Щербаков Дмитрий)
'             http://www.excel-vba.ru
' Purpose   : Функция копирует компонент из одной книги в другую.
'             Возвращает True, если копирование прошло удачно
'             False - если компонент не удалось скопировать
'
' wbFromFrom             Книга, компонент из VBA-проекта которой необходимо копировать
'
' wbFromTo               Книга, в VBA-проект которой необходимо копировать компонент
'
' sModuleName            Имя модуля, который необходимо копировать.
'
' sModuleToName          Имя модуля, в который необходимо копировать.
'
' bOverwriteExistModule  Если True или 1, то при наличии в конечной книге
'                        компонента с именем sModuleName - он будет удален,
'                        а вместо него импортирован копируемый.
'                        Если False, то при наличии в конечной книге
'                        компонента с именем sModuleName функция вернет False,
'                        а сам компонент не будет скопирован.
'---------------------------------------------------------------------------------------
Function CopyVBComponent(sModuleName As String, ByVal sModuleToName As String, _
    wbFromFrom As Workbook, wbFromTo As Workbook, _
    bOverwriteExistModule As Boolean) As Boolean
 
    Dim objVBProjFrom As Object, objVBProjTo As Object
    Dim objVBComp As Object, objTmpVBComp As Object
    Dim sTmpFolderPath As String, sVBCompName As String, sModuleCode As String
 
    'Проверяем корректность указанных параметров
    On Error Resume Next
    Set objVBProjFrom = wbFromFrom.VBProject
    Set objVBProjTo = wbFromTo.VBProject
 
    'если в книге, из которой копируем нет проекта VBA
    If objVBProjFrom Is Nothing Then
        CopyVBComponent = False: Exit Function
    End If
    'если в книге, из которой копируем для проекта VBA установлен пароль
    If objVBProjFrom.Protection = 1 Then
        CopyVBComponent = False: Exit Function
    End If
 
    'если в книге, в которую копируем нет проекта VBA
    If objVBProjTo Is Nothing Then
        CopyVBComponent = False: Exit Function
    End If
    'если в книге, в которую копируем для проекта VBA установлен пароль
    If objVBProjTo.Protection = 1 Then
        CopyVBComponent = False: Exit Function
    End If
 
    'если не задано имя копируемого модуля
    If Trim(sModuleName) = "" Then
        CopyVBComponent = False: Exit Function
    End If
    'если не задано имя модуля для вставки кода - используем имя копируемого
    If Trim(sModuleName) = "" Then
        sModuleToName = sModuleName
    End If
 
    'проверяем, существует ли в книге из которой копируем заданный модуль
    Set objVBComp = objVBProjFrom.VBComponents(sModuleName)
    'модуля нет - выходим из функции
    If objVBComp Is Nothing Then
        CopyVBComponent = False: Exit Function
    End If
 
    '====================================================
    'полный путь для экспорта/импорта модуля. К папке должен быть доступ на запись/чтение
    sTmpFolderPath = Environ("Temp") & "" & sModuleToName & ".bas" '"
    If bOverwriteExistModule = True Then
        ' Если bOverwriteExistModule = True
        ' удаляем из временной папки и из конечного проекта
        ' модуль с указанным именем
        If Dir(sTmpFolderPath, 6) <> "" Then
            Err.Clear
            Kill sTmpFolderPath
            'удалить не удалось, модуль не сохранен. Выход из функции
            If Err.Number <> 0 Then
                CopyVBComponent = False: Exit Function
            End If
        End If
        'удаляем модуль из конечной книги
        With objVBProjTo.VBComponents
            Set objVBComp = Nothing
            Set objVBComp = .Item(sModuleToName)
            'только если это не модуль листа или книги(их можно только очистить, но не удалять)
            If objVBComp.Type <> 100 Then
                .Remove .Item(sModuleToName)
            End If
        End With
    Else
        Err.Clear
        Set objVBComp = objVBProjTo.VBComponents(sModuleToName)
        If Err.Number <> 0 Then
            'Err.Number 9 - отсутствие указанного компонента, что нам не мешает.
            'Если ошибка другая - выход из функции
            If Err.Number <> 9 Then
                CopyVBComponent = False: Exit Function
            End If
        End If
    End If
 
    '====================================================
    'Экспорт/Импорт компонента во временную директорию
    objVBProjFrom.VBComponents(sModuleName).Export sTmpFolderPath
    '====================================================
    'копируем
    Set objVBComp = Nothing
    Set objVBComp = objVBProjTo.VBComponents(sModuleToName)
    If objVBComp Is Nothing Then
        objVBProjTo.VBComponents.Import sTmpFolderPath
    Else
        'Если компонент - модуль листа или книги -
        'его нельзя удалить. Поэтому удаляем из него весь код
        'и добавляем код из копируемого компонента
        If objVBComp.Type = 100 Then
            'для простоты обращения в коде - делаем ссылку на копируемый модуль
            Set objTmpVBComp = objVBProjFrom.VBComponents(sModuleName)
            'копируем из него код
            With objVBComp.CodeModule
                .DeleteLines 1, .CountOfLines
                sModuleCode = objTmpVBComp.CodeModule.Lines(1, objTmpVBComp.CodeModule.CountOfLines)
                .InsertLines 1, sModuleCode
            End With
        End If
    End If
    'удаляем временный файл компонента
    Kill sTmpFolderPath
    CopyVBComponent = True
End Function

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

Sub CopyComponent()
    Workbooks.Add
    If CopyVBComponent("Module1", "", ThisWorkbook, ActiveWorkbook, True) Then
        MsgBox "Указанный компонент успешно скопирован в новую книгу", vbInformation
    Else
        MsgBox "Компонент не был скопирован", vbInformation
    End If
End Sub

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

Sub CopyComponent()
    Workbooks.Add
    If CopyVBComponent("Module1", ActiveWorkbook.CodeName, ThisWorkbook, ActiveWorkbook, True) Then
        MsgBox "Указанный компонент успешно скопирован в новую книгу", vbInformation
    Else
        MsgBox "Компонент не был скопирован", vbInformation
    End If
End Sub

Приведенная функция так же может копировать коды и внутри одной книги: например, перенести код из стандартного модуля в модуль ЭтаКнига или модуль листа:

Sub CopyComponent()
    Workbooks.Add
    If CopyVBComponent("Module1", ThisWorkbook.CodeName, ThisWorkbook, ThisWorkbook, True) Then
        MsgBox "Указанный компонент успешно скопирован в новую книгу", vbInformation
    Else
        MsgBox "Компонент не был скопирован", vbInformation
    End If
End Sub

Думаю теперь у вас не должно возникнуть трудностей с переносом кодов из одной книги в другую.

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

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

    Видеоуроки

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



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

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

Чтобы использовать пример ниже, откройте новую книгу.

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

1. Откройте вкладку Разработчик и щелкните элемент Visual Basic.

   

2. В обозревателе VBA Project слева разверните папку VBA Project книги, а затем дважды щелкните модуль ThisWorkbook. Если вы не видите обозреватель Project, перейдите в > Project проводникаили нажмите CTRL+R.

   

3. В окне модуля, которое открывается справа, вставьте следующий код:

   Private Sub Workbook_Open()
   
   ' Put your code here
   
   End Sub

4. В конце и подмножите записи в процедуру Sub.

   Закройте редактор Visual Basic (вам не нужно ничего сохранять).

5. Сохраните книгу как книгу Excel Macro-Enabled (XLSM)и закроем ее.

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

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

1. В меню выберите пункт Параметры Excel >… >ленты & панели инструментов.

2. В категории Настройка ленты в списке Основные вкладки выберите вариант Разработчик.

3. Нажмите кнопку Сохранить.

Чтобы использовать пример ниже, откройте новую книгу.

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

1. Откройте вкладку Разработчик и щелкните элемент Visual Basic.

2. В обозревателе VBA Project слева разверните папку VBA Project книги, а затем дважды щелкните модуль ThisWorkbook.

3. В окне модуля, которое открывается справа, вставьте следующий код:

   Private Sub Workbook_Open()
   
   ' Put your code here
   
   End Sub

4. В конце и подмножите записи в процедуру Sub.

   Закройте редактор Visual Basic (вам не нужно ничего сохранять).

5. Сохраните книгу как книгу Excel Macro-Enabled (XLSM)и закроем ее.

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