Вставка в текст макроса excel

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

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

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

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

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

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:

Skip to content

Sub DobavitTekst()
'Шаг 1: Объявляем переменные
Dim MyRange As Range
Dim MyCell As Range
'Шаг 2: Сохранить книгу прежде,чем изменить ячейки?
Select Case MsgBox("Перед изменением ячеек. " & _
"Сохранить книгу?", vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
'Шаг 3: Определение целевого диапазона
Set MyRange = Selection
'Шаг 4: Запускаем цикл по диапазону
For Each MyCell In MyRange
'Шаг 5: Убедитесь, что ячейка имеет формат текста
If Not IsEmpty(MyCell) Then
MyCell = "(972) " & MyCell
End If
'Шаг 6: Получите следующую ячейку в диапазоне
Next MyCell
End Sub

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

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

Как записать макрос

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

• вручную;
• автоматически.

Для того чтобы создать макрос автоматически, достаточно записать определенный порядок действий через Excel. При этом их необходимо выполнять в данный момент. Когда запись будет окончена, ее можно будет применить к другим таблицам, нажав на воспроизведение. Главное преимущество данного способа – нет необходимости учить код, чтобы применять его на практике. Однако такой алгоритм не будет обладать какой-либо гибкостью, его можно будет применять только в определенных ситуациях. Процесс автоматической записи макросов:

1. Изначально необходимой зайти “Центр управления безопасностью” через главное меню программы.
2. Зайти во вкладку “Параметры макросов”.
3. Активировать команду “Включить все макросы”.

1. Перейти на вкладку “Разработчик”, которая находится на основной панели инструментов.
2. Нажать на кнопку “Запись макроса” (она находится в панели инструментов под названием “Код”).

Далее откроется окно с настройками алгоритма, где нужно придумать имя макроса, установить комбинацию клавиш для его запуска. После завершения настройки необходимо подтвердить заданные параметры кнопкой “ОК”.

Важно! Для открытия панели с готовыми макросами нужно нажать комбинацию клавиш Alt+F8. Она вызывает окно с уже созданными алгоритмами, из которых необходимо выбрать желаемый вариант.

Как добавить готовый макрос в рабочую книгу

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

1. Изначально необходимо найти желаемую команду в интернете. После этого выделить полученный код.
2. Запустить рабочую книгу, активировать комбинацию клавиш Alt+F
3. Перед пользователем должно открыться окно VBA.

1. По левую сторону необходимо найти окно “Project — VBA Project”. Из появившегося списка нужно выбрать рабочую книгу.
2. Открыть меню “Insert”, активировать функцию “Module”.
3. После этого в левом окне появится новая папка под названием “Moduels”.
4. Открыть новый объект внутри созданной паки, в свободное окно вставить скопированный код по комбинации клавиш “Ctrl+V”.

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

Важно! Чтобы сохранить готовую таблицу с добавленным макросом в Excel версий 2007, 2010, 2013 годов, нужно выбрать из списка тип файла строку “Книга Excel с поддержкой макросов”.

Как запустить макрос

Существует несколько проверенных способов запуска добавленных или созданных алгоритмов действий в Excel. Самый простой метод – запуск макроса через VBA:

1. Зайти во вкладку “Разработчик” на главной панели с инструментами.
2. Перейти в панель с инструментами “Код”, нажать на кнопку “Макросы”.
3. Из открывшегося окна нужно выбрать требуемый алгоритм с помощью ЛКМ, нажать на кнопку “Выполнить”.

После этого алгоритм будет выполняться. Активация макроса через комбинацию клавиш:

1. Необходимо зайти в окно с готовыми или добавленными алгоритмами действий точно так же, как было описано в способе выше.
2. Перейти во вкладку “Параметры” которая расположена в правой части появившегося окна.
3. Откроется еще одно окно, в котором можно настроить алгоритм.

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

Совет! В окне с параметрами есть большое свободное поле снизу, которое имеет название “Описание”. В ней рекомендуется кратко написать, какие действия выполняет данный алгоритм. Это поможет не путаться в созданных макросах при их использовании.

Запуска алгоритма через панель быстрого доступа:

1. На панели быстрого доступа найти значок стрелки, направленной вниз.

1. Из открывшегося списка выбрать функцию “Другие команды”. Должно появиться окно с параметрами Excel.
2. Из списка в левой стороне зайти во вкладку “Выбрать команду”. Нажать на функцию “Макросы”.
3. Далее необходимо выбрать желаемый алгоритм действий.

На панели быстрого доступа перед стрелкой вниз должен появиться произвольный значок выбранного макроса. Его можно изменить через настройки. Чтобы сохранить установленные параметры, необходимо нажать кнопку “ОК”.

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

Заключение

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

1

Макрос для вставки строк с текстом

22.07.2015, 18:58. Показов 7521. Ответов 3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Sub Insert_Rows()
    Dim lLastRow As Long, li As Long, i As Range ' переменные
    Application.ScreenUpdating = 0 'заморозим экран от изменений
    lLastRow = Cells(Rows.Count, 1).End(xlUp).Row 'переменной присваиваетс¤ последн¤¤ строка
    For li = lLastRow To 1 Step -1 'ѕ≈–≈Ѕ»–ј≈ћ — последней до первой строки с шагом -1
    Sheets("Ћист2").Select
    ActiveCell.Rows("1:2").EntireRow.Select
    Selection.Copy
    Sheets("Ћист1").Select
    ActiveCell.Rows().EntireRow.Select
    Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Next li
    Application.ScreenUpdating = 1 'разморозили экран и он обновилс¤
End Sub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

' это вставка двух строк при нахождении фразы,но в выделенной ¤чейке
Sub StrokaAfterSumm()
Attribute StrokaAfterSumm.VB_ProcData.VB_Invoke_Func = "fn14"
Dim i As Range
Application.ScreenUpdating = 0
  For Each i In Selection
    If i = "3311св" Then i.Offset(-1, 0).EntireRow.Resize(2).Insert xlDown
  Next
  Application.ScreenUpdating = 1
End Sub
' Ёто вставка 2 строк до
Sub Insert_Rows()
Attribute Insert_Rows.VB_ProcData.VB_Invoke_Func = "фn14"
    Dim lLastRow As Long, li As Long, i As Range ' переменные
    Application.ScreenUpdating = 0 'заморозим экран от изменений
    lLastRow = Cells(Rows.Count, 1).End(xlUp).Row 'переменной присваиваетс¤ последн¤¤ строка
    For li = lLastRow To 1 Step -1 'ѕ≈–≈Ѕ»–ј≈ћ — последней до первой строки с шагом -1
'поиск и добавление строк, в for не работает -->
    Cells.Find(What:="3311св", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
  '  Cells.FindNext(After:=ActiveCell).Activate
'   Cells.Find(What:="3311св", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(-1, 0).EntireRow.Resize(2).Insert xlDown
'<--
     '  Rows(li).Resize(2).Insert 'добавл¤ем 2 строки до нужной нам
' если заменить Resize(2) на Resize(1) то будет вставл¤тьс¤ только одна строка
    Next li
    Application.ScreenUpdating = 1 'разморозили экран и он обновилс¤
End Sub
' это вставка двух строк при нахождении фразы,но в выделенной ¤чейке
Sub StrokaAfterSumm2()
Dim i As Range
Application.ScreenUpdating = 0
 ' For Each i In ActiveWorkbook.Worksheets
  Range("A:A").Find("3311св").Offset(-1, 0).EntireRow.Resize(2).Insert xlDown
   'If i = "3311св" Then i.Offset(-1, 0).EntireRow.Resize(2).Insert xlDown
  'Next i
  Application.ScreenUpdating = 1
End Sub

0