Полезные макросы в excel

Готовые макросы в VBA Excel, Миронов

Готовые макросы в VBA Excel, Миронов.

Код в стандартном модуле
Sub FastChangeNumberFormat()
Dim bar As CommandBar
Dim button As CommandBarButton

Проверка наличия файла по указанному пути_1.
Sub VerifyFileLocation()
Dim strFileName As String
Dim strFileTitle As String
‘Имя и путь искомого файла
strFileTitle = «primer.xls»
strFileName = «C:Документыprimer.xls»
‘Проверка наличия файла (функция Dir возвращает пустую _
строку, если по указанному пути файл обнаружить не удалось)
If Dir(strFileName) <> «» Then
MsgBox «Файл » & strFileTitle & » найден»
Else
MsgBox «Файл » & strFileTitle & » не найден»
End If
End Sub

Ограничение
Глава 1. Макросы 8
Запуск макроса с поиском ячейки 8
Запуск макроса при открытии книги 8
Запуск макроса при вводе в ячейку «2» 8
Запуск макроса при нажатии «Ентер» 9
Добавить в панель свою вкладку «Надстройки» (Формат ячейки) 9
Глава 2. Работа с файлами (т.е.обмен данными с ТХТ, RTF, XLS и т.д.) 11
Проверка наличия файла по указанному пути_1 11
Проверка наличия файла по указанному пути_2 11
Проверка наличия файла по указанному пути_3 11
Поиск нужного файла_1 12
Поиск нужного файла_2 12
Поиск нужного файла_3 13
Поиск нужного файла_4 13
Автоматизация удаления файлов 13
Произвольный текст в строке состояния 14
Восстановление строки состояния 14
Бегущая строка в строке состояния 14
Быстрое изменение заголовка окна 14
Быстрое изменение заголовка окна_2 14
Изменение заголовка окна (со скрытием названия файла) 14
Возврат к первоначальному заголовку 15
Что открыто в данный момент 15
Работа с текстовыми файлами 15
Запись и чтение текстового файла 15
Обработка нескольких текстовых файлов 16
Определение конца строки текстового файла 17
Копирование из текстового файла в эксель 18
Копирование содержимого в текстовый файл_1 18
Копирование содержимого в текстовый файл_2 18
Экспорт данных в txt 18
Экспорт данных в html 20
Импорт данных, для которых нужно более 256 столбцов 22
Создание резервных копий ценных файлов 25
Подсчет количества открытий файла 25
Вывод пути к файлу в активную ячейку 26
Копирование содержимого файла RTF в эксель 26
Копирование данных из закрытой книги 27
Извлечение данных из закрытого файла 27
Поиск слова в файлах 28
Создание текстового файла и ввод текста в файл 29
Создание текстового файла и ввод текста (определение конца файла) 30
Создание документов Word на основе таблицы Excel 30
Команды создания и удаления каталогов 32
Получение текущего каталога 32
Смена каталога 32
Посмотреть все файлы в каталоге_1 32
Посмотреть все файлы в каталоге_2 33
Посмотреть все файлы в каталоге_3 35
Глава 3. Рабочая область Microsoft Excel 37
Рабочая книга 37
Количество имен рабочей книги 37
Защита рабочей книги 37
Запрет печати книги 38
Открытие книги (или текстовых файлов) 38
Открытие книги и добавление в ячейку А1 текста 38
Сколько книг открыто 38
Закрытие всех книг 39
Закрытие рабочей книги только при выполнении условия 39
Сохранение рабочей книги с именем, представляющим собой текущую дату 39
Сохранена ли рабочая книга 39
Создать книгу с одним листом 39
Создать книгу 39
Удаление ненужных имен 40
Быстрое размножение рабочей книги 40
Сортировка листов 40
Поиск максимального значения на всех листах книги 42
Рабочий лист 43
Проверка наличия защиты рабочего листа 43
Список отсортированных листов 43
Создать новый лист_1 45
Создать новый лист_2 45
Удаление листов в зависимости от даты 45
Копирование листа в книге 46
Копирование листа в новую книгу (создается) 46
Перемещение листа в книге 46
Перемещение нескольких листов в новую книгу 46
Заменить существующий файл 46
«Перелистывание» книги 46
Вставка колонтитула с именем книги, листа и текущей датой 47
Существует ли лист 47
Существует ли лист_2 47
Вывод количества листов в активной книге 48
Вывод количества листов в активной книге в виде гиперссылок 48
Вывод имен активных листов по очереди 48
Вывод имени и номеров листов текущей книги 48
Сделать лист невидимым 49
Сколько страниц на всех листах? 49
Ячейка и диапазон (столбцы и строки) 49
Копирование строк на другой лист 49
Копирование столбцов на другой лист 49
Подсчет количества ячеек, содержащих указанные значения_1 50
Подсчет количества ячеек в диапазоне, содержащих указанные значения_2 50
Подсчет количества видимых ячеек в диапазоне 51
Определение количества ячеек в диапазоне и суммы их значений 51
Подсчет количества ячеек 51
Автоматический пересчет данных таблицы при изменении ее значений 51
Ввод данных в ячейки 53
Ввод данных с использованием формул 53
Последовательный ввод данных 53
Ввод текстоввых данных в ячейки 53
Вывод в ячейки названия книги, листа и количества листов 54
Удаление пустых строк_1 54
Удаление пустых строк_2 54
Удаление пустых строк_3 55
Удаление строки по условию 55
Удаление скрытых строк 56
Удаление используемых скрытых строк или строк с нулевой высотой 56
Удаление дубликатов по маске 56
Выделение диапазона над текущей ячейкой 57
Выделение диапазона над текущей ячейкой_2 57
Выделить ячейку и поместить туда число 58
Выделение отрицательных значений 58
Выделение диапазона и использование абсолютных адресов 58
Выделение ячеек через интервал_1 59
Выделение ячеек через интервал_2 59
Выделение нескольких диапазонов 60
Движение по ячейкам 60
Поиск ближайшей пустой ячейки столбца 61
Поиск максимального значения 61
Поиск и замена по шаблону 61
Поиск значения с отображением результата в отдельном окне 62
Поиск с выделением найденных данных_1 62
Поиск с выделением найденных данных_2 62
Поиск по условию в диапазоне 63
Поиск последней непустой ячейки диапазона 64
Поиск последней непустой ячейки столбца 64
Поиск последней непустой ячейки строки 64
Поиск ячейки синего цвета в диапазоне 65
Поиск отрицательного значения в диапазоне и выделения синим цветом 65
Поиск наличия значения в столбце 65
Поиск совпадений в диапазоне 66
Поиск ячейки в диапазоне_1 67
Поиск ячейки в диапазоне_2 67
Поиск приближенного значения в диапазоне 67
Поиск начала и окончания диапазона, содержащего данные 68
Поиск начала данных 68
Автоматическая замена значений 68
Быстрое заполнение диапазона (массив) 69
Заполнение через интервал(массив) 69
Заполнение указанного диапазона(массив) 70
Заполнение диапазона(массив) 70
Расчет суммы первых значений диапазона 71
Размещение в ячейке электронных часов 72
«Будильник» 72
Оформление верхней и нижней границ диапазона 72
Адрес активной ячейки 73
Координаты активной ячейки 73
Формула активной ячейки 73
Получение из ячейки формулы 73
Тип данных ячейки 73
Вывод адреса конца диапазона 74
Получение информации о выделенном диапазоне 74
Взять слово с 13 символа в ячейке 76
Создание изменяемого списка (таблица) 77
Проверка на пустое значение 77
Пересечение ячеек 77
Умножение выделенного диапазона на 2 77
Одновременное умножение всех данных диапазона 78
Деление диапазона на 100 78
Возведение каждой ячейки диапазона в квадрат 78
Суммирование данных только видимых ячеек 78
Сумма ячеек с числовыми значениями 79
При суммировании — курсор внутри диапазона 79
Начисление процентов в зависимости от суммы_1 80
Начисление процентов в зависимости от суммы_2 80
Начисление процентов в зависимости от суммы_3 81
Сводный пример расчета комиссионного вознаграждения 81
Движение по диапазону 83
Сдвиг от выделенной ячейки 83
Перебор ячеек вниз по колонне 83
Создание заливки диапазона 84
Подбор параметра ячейки 84
Разбиение диапазона 84
Объединение данных диапазона 85
Объединение данных диапазона_2 85
Узнать максимальную колонку или строку. 86
Ограничение возможных значений диапазона 86
Тестирование скорости чтения и записи диапазонов 88
Открыть MsgBox при выборе ячейки 89
Скрытие строки 89
Скрытие нескольких строк 89
Скрытие столбца 89
Скрытие нескольких столбцов 89
Скрытие строки по имени ячейки 89
Скрытие нескольких строк по адресам ячеек 89
Скрытие столбца по имени ячейки 89
Скрытие нескольких столбцов по адресам ячеек 90
Мигание ячейки 90
Глава 4. Работа с примечаниями 91
Вывод на экран всех примечаний рабочего листа 91
Функция извлечения комментария 91
Список примечаний защищенных листов 91
Перечень примечаний в отдельном списке_1 92
Перечень примечаний в отдельном списке_2 93
Перечень примечаний в отдельном списке_3 93
Подсчет количества примечаний_1 94
Подсчет количества примечаний_2 95
Подсчет примечаний_3 95
Выделение ячеек с примечаниями 95
Отображение всех примечаний 95
Изменение цвета примечаний 96
Добавление примечаний 96
Добавление примечаний в диапазон по условию 96
Перенос комментария в ячейку и обратно 96
Перенос значений из ячейки в комментарий_1 97
Перенос значений из ячейки в комментарий_2 98
Глава 5 . Пользовательские вкладки на ленте 99
Дополнение панели инструментов 99
Добавление кнопки на панель инструментов 99
Панель с одной кнопкой 99
Панель с двумя кнопками 99
Создание панели справа 100
Вызов предварительного просмотра 100
Создание пользовательского меню (вариант 1) 100
Создание пользовательского меню (вариант 2) 101
Создание пользовательского меню (вариант 3) 102
Создание пользовательского меню (вариант 4) 102
Создание пользовательского меню (вариант 5) 102
Создание пользовательского меню (вариант 6) 106
Создание списка пунктов главного меню Excel 108
Создание списка пунктов контекстных меню 108
Отображение панели инструментов при определенном условии 109
Скрытие и отображение панелей инструментов 111
Создать подсказку к моим кнопкам 112
Создание меню на основе данных рабочего листа 112
Создание контекстного меню 115
Блокировка контекстного меню 117
Добавление команды в меню Сервис 118
Добавление команды в меню Вид 119
Создание панели со списком 120
Мультфильм с помощником в главной роли 122
Дополнение помощника текстом, заголовком, кнопкой и значком 123
Новые параметры помощника 124
Использование помощника для выбора цвета заливки 125
Глава 6. ДИАЛОГОВЫЕ ОКНА 127
Функция INPUTBOX (через ввод значения) 127
Вызов предварительного просмотра 127
Настройка ввода данных в диалоговом окне 127
Открытие диалогового окна (“Открыть файл”)_1 128
Открытие диалогового окна (“Открыть файл”)_2 128
Открытие диалогового окна (“Печать”) 128
Другие диалоговые окна 128
Вызов броузера из Экселя 129
Диалоговое окно ввода данных 129
Диалоговое окно настройки шрифта 129
Значения по умолчанию 129
Глава 7. Форматирование текста. Таблицы. ГРАНИЦЫ И ЗАЛИВКА. 130
Вывод списка доступных шрифтов 130
Выбор из текста всех чисел 130
Прописная буква только в начале текста 131
Подсчет количества повторов искомого текста 131
Выделение из текста произвольного элемента 132
Отображение текста «задом наперед» 133
Англоязычный текст — заглавными буквами 133
Запуск таблицы символов из Excel 134
глава информация о пользователе, компьютере, принтере и т.д. 136
Получить имя пользователя 136
Вывод разрешения монитора 137
Получение информации об используемом принтере 137
Просмотр информации о дисках компьютера 138
Глава 8. ЮЗЕРФОРМЫ 140
Глава 9. ДИАГРАММЫ 142
Построение диаграммы с помощью макроса 142
Сохранение диаграммы в отдельном файле 143
Построение и удаление диаграммы нажатием одной кнопки 144
Вывод списка диаграмм в отдельном окне 145
Применение случайной цветовой палитры 146
Эффект прозрачности диаграммы 146
Построение диаграммы на основе данных нескольких рабочих листов 148
Создание подписей к данным диаграммы 150
Глава 10. РАЗНЫЕ ПРОГРАММЫ. 151
Программа для составления кроссвордов 151
Создать обложку DVD 155
Игра «Минное поле» 156
Игра «Угадай животное» 158
Расчет на основании ячеек определенного цвета 161
Глава 11. ДРУГИЕ ФУНКЦИИ И МАКРОСЫ 175
Вызов функциональных клавиш 175
Расчет среднего арифметического значения 175
Перевод чисел в «деньги» 175
Поиск ближайшего понедельника 176
Подсчет количества полных лет 177
Расчет средневзвешенного значения 177
Преобразование номера месяца в его название 178
Использование относительных ссылок 178
Преобразование таблицы Excel в HTML-формат 179
Генератор случайных чисел 181
Случайные числа — на основании диапазона 182
Применение функции без ввода ее в ячейку 183
Подсчет именованных объектов 183
Включение автофильтра с помощью макроса 183
Создание бегущей строки 183
Создание бегущей картинки 184
Вращающиеся автофигуры 185
Вызов таблицы цветов 187
Создание калькулятора 188
Склонение фамилии, имени и отчества 188
Глава 12. ДАТА И ВРЕМЯ 194
Вывод даты и времени_1 194
Вывод даты и времени_2 194
Получение системной даты 195
Извлечение даты и часов 195
Функция ДатаПолная 195.

Читать еще:  Среднее значение в экселе

Бесплатно скачать электронную книгу в удобном формате, смотреть и читать:
Скачать книгу Готовые макросы в VBA Excel, Миронов — fileskachat.com, быстрое и бесплатное скачивание.

Скачать doc
Ниже можно купить эту книгу по лучшей цене со скидкой с доставкой по всей России. Купить эту книгу

Автоматизация задач с помощью средства записи макросов — Excel

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

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

При записи макроса записываются все действия, описанные в Visual Basic для приложений (VBA) коде. Эти действия могут включать ввод текста или чисел, нажатие ячеек или команд на ленте или в меню, форматирование ячеек, строк или столбцов, а также импорт данных из внешнего источника, например Microsoft Access. Приложение Visual Basic (VBA) — это подмножество мощного языка программирования Visual Basic, которое входит в большинство приложений Office. Несмотря на то, что VBA обеспечивает возможность автоматизации процессов между приложениями Office, вам не нужно знать код VBA или программное программирование, если это нужно.

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

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

Запись макроса

Перед записью макросов полезно знать следующее:

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

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

В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.

Чтобы записать макрос, следуйте инструкциям ниже.

На вкладке Разработчик в группе Код нажмите кнопку Запись макроса.

В поле Имя макроса введите название макроса. Сделайте имя понятным, чтобы можно было быстро найти нужный макрос.

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

Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш с CTRL+SHIFT, так как они будут заменять собой совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос. Например, если назначить сочетание клавиш CTRL+Z (Отменить), вы не сможете использовать его для функции «Отменить» в данном экземпляре Excel.

В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

Как правило, макрос сохраняется в указанном расположении книги , но если вы хотите, чтобы макрос был доступен при использовании Excel, выберите пункт Личная книга макросов . Если выбрать команду Личная книга макросов, Excel создаст скрытую личную книгу макросов (личное. xlsb), если она еще не существует, и сохранит макрос в этой книге.

В поле Описание при необходимости введите краткое описание действий макроса.

Хотя поле «Описание» является необязательным, рекомендуется его заполнить. Кроме того, желательно ввести понятное описание, которое будет полезно вам и всем, кто запускает макрос. Если у вас много макросов, описания помогут быстро определить, для чего они нужны.

Чтобы начать запись макроса, нажмите кнопку ОК.

Выполните действия, которые нужно записать.

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

Работа с макросами, записанными в Excel

На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. Кроме того, можно нажать клавиши ALT+F8. При этом откроется диалоговое окно Макрос.

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

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Сведения о параметрах безопасности макросов и их значении.

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

С помощью редактора Visual Basic можно изменять макросы, присоединенные к книге.

Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.

Назначение макроса объекту, фигуре или графическому элементу

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

В поле Назначить макроса выберите макрос, который вы хотите назначить.

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

Вы можете назначать макросы формам и элементам ActiveX на листе.

Узнайте, как включать и отключать макросы в файлах Office.

Открытие редактора Visual Basic

Нажмите клавиши ALT+F11.

Узнайте, как найти справку по элементам Visual Basic.

Работа с записанным кодом в редакторе Visual Basic (VBE)

С помощью редактора Visual Basic (VBE) вы можете добавлять в записанный код собственные переменные, управляющие структуры и другие элементы, которые не поддерживает средство записи макросов. Так как средство записи макросов фиксирует почти каждый шаг, выполняемый во время записи, может также потребоваться удалить ненужный код. Просмотр записанного кода — отличный способ научиться программировать на VBA или отточить свои навыки.

Пример изменения записанного кода можно найти в статье Начало работы с VBA в Excel.

Запись макроса

Перед записью макросов полезно знать следующее:

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

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

В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.

Читать еще:  Посчитать процент в excel

Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее.

Перейдите в раздел настройки _гт_ Excel. панель инструментов _Гт_ ленты _амп_.

В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.

Чтобы записать макрос, следуйте инструкциям ниже.

На вкладке Разработчик нажмите кнопку Запись макроса.

В поле Имя макроса введите название макроса. Сделайте имя понятным, чтобы можно было быстро найти нужный макрос.

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

В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

Как правило, макрос сохраняется в указанном расположении книги , но если вы хотите, чтобы макрос был доступен при использовании Excel, выберите пункт Личная книга макросов. Если выбрать команду Личная книга макросов, в Excel будет создана скрытая личная книга макросов (личное. XLSB), если он еще не существует, и сохранение макроса в этой книге. Книги в этой папке открываются автоматически при запуске Excel, а код, хранящийся в личной книге макросов, будет указан в диалоговом окне Макрос, которое описано в следующем разделе.

Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш, которые еще не назначены другим командам, так как они будут переопределять совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос.

В поле Описание при необходимости введите краткое описание действий макроса.

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

Чтобы начать запись макроса, нажмите кнопку ОК.

Выполните действия, которые нужно записать.

На вкладке Разработчик щелкните Остановить запись.

Работа с макросами, записанными в Excel

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

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

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Excel works!

Excel работает за вас

Excel works!

Thanks for Visiting

Полезные макросы Excel. Простые возможности VBA, которые я часто использую

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

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

Как убрать отображение выполнения действий макроса?

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

Не забудьте включить функцию в конце макроса

Как убрать выделение копирования после выполнения макроса?

Если вы в своем макросе все такие выделяете диапазон для копирования и вставки. Не забудьте в конце макроса поставить отключение выделения

Полезные макросы. Как найти последнюю строку или столбец диапазона

Эта конструкция поможет легко найти номер последней строки или столбца заполненного диапазона. Особенно удобно применять в циклах, вам не нужно задавать 1000 строк цикла с запасом, Excel сам найдет где конец диапазона при помощи такой конструкции:

Причем, я сразу объявляю переменную как Long (длина 2 147 483 647), чтобы не попасть в ту ситуация когда популярного Integer может не хватить (32 767) для больших таблиц.

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

Цикл For и проверка условия в цикле

Если вы нашли последние номера строк и столбцов диапазонов, вы можете использовать их в циклах. Например, надо пробежать по первому столбцу и посчитать кол-во пустых ячеек.

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

Подсчет времени выполнения макроса

MsgBox выдает такой результат:

Очень удобно засекать время выполнения макросов для последующей оптимизации.

Вот такие полезные макросы, я использую очень часто. Постепенно буду расширять этот список. Всем эффективной автоматизации!

Готовые макросы в VBA Excel, Миронов

Готовые макросы в VBA Excel, Миронов.

Код в стандартном модуле
Sub FastChangeNumberFormat()
Dim bar As CommandBar
Dim button As CommandBarButton

Проверка наличия файла по указанному пути_1.
Sub VerifyFileLocation()
Dim strFileName As String
Dim strFileTitle As String
‘Имя и путь искомого файла
strFileTitle = «primer.xls»
strFileName = «C:Документыprimer.xls»
‘Проверка наличия файла (функция Dir возвращает пустую _
строку, если по указанному пути файл обнаружить не удалось)
If Dir(strFileName) <> «» Then
MsgBox «Файл » & strFileTitle & » найден»
Else
MsgBox «Файл » & strFileTitle & » не найден»
End If
End Sub

Ограничение
Глава 1. Макросы 8
Запуск макроса с поиском ячейки 8
Запуск макроса при открытии книги 8
Запуск макроса при вводе в ячейку «2» 8
Запуск макроса при нажатии «Ентер» 9
Добавить в панель свою вкладку «Надстройки» (Формат ячейки) 9
Глава 2. Работа с файлами (т.е.обмен данными с ТХТ, RTF, XLS и т.д.) 11
Проверка наличия файла по указанному пути_1 11
Проверка наличия файла по указанному пути_2 11
Проверка наличия файла по указанному пути_3 11
Поиск нужного файла_1 12
Поиск нужного файла_2 12
Поиск нужного файла_3 13
Поиск нужного файла_4 13
Автоматизация удаления файлов 13
Произвольный текст в строке состояния 14
Восстановление строки состояния 14
Бегущая строка в строке состояния 14
Быстрое изменение заголовка окна 14
Быстрое изменение заголовка окна_2 14
Изменение заголовка окна (со скрытием названия файла) 14
Возврат к первоначальному заголовку 15
Что открыто в данный момент 15
Работа с текстовыми файлами 15
Запись и чтение текстового файла 15
Обработка нескольких текстовых файлов 16
Определение конца строки текстового файла 17
Копирование из текстового файла в эксель 18
Копирование содержимого в текстовый файл_1 18
Копирование содержимого в текстовый файл_2 18
Экспорт данных в txt 18
Экспорт данных в html 20
Импорт данных, для которых нужно более 256 столбцов 22
Создание резервных копий ценных файлов 25
Подсчет количества открытий файла 25
Вывод пути к файлу в активную ячейку 26
Копирование содержимого файла RTF в эксель 26
Копирование данных из закрытой книги 27
Извлечение данных из закрытого файла 27
Поиск слова в файлах 28
Создание текстового файла и ввод текста в файл 29
Создание текстового файла и ввод текста (определение конца файла) 30
Создание документов Word на основе таблицы Excel 30
Команды создания и удаления каталогов 32
Получение текущего каталога 32
Смена каталога 32
Посмотреть все файлы в каталоге_1 32
Посмотреть все файлы в каталоге_2 33
Посмотреть все файлы в каталоге_3 35
Глава 3. Рабочая область Microsoft Excel 37
Рабочая книга 37
Количество имен рабочей книги 37
Защита рабочей книги 37
Запрет печати книги 38
Открытие книги (или текстовых файлов) 38
Открытие книги и добавление в ячейку А1 текста 38
Сколько книг открыто 38
Закрытие всех книг 39
Закрытие рабочей книги только при выполнении условия 39
Сохранение рабочей книги с именем, представляющим собой текущую дату 39
Сохранена ли рабочая книга 39
Создать книгу с одним листом 39
Создать книгу 39
Удаление ненужных имен 40
Быстрое размножение рабочей книги 40
Сортировка листов 40
Поиск максимального значения на всех листах книги 42
Рабочий лист 43
Проверка наличия защиты рабочего листа 43
Список отсортированных листов 43
Создать новый лист_1 45
Создать новый лист_2 45
Удаление листов в зависимости от даты 45
Копирование листа в книге 46
Копирование листа в новую книгу (создается) 46
Перемещение листа в книге 46
Перемещение нескольких листов в новую книгу 46
Заменить существующий файл 46
«Перелистывание» книги 46
Вставка колонтитула с именем книги, листа и текущей датой 47
Существует ли лист 47
Существует ли лист_2 47
Вывод количества листов в активной книге 48
Вывод количества листов в активной книге в виде гиперссылок 48
Вывод имен активных листов по очереди 48
Вывод имени и номеров листов текущей книги 48
Сделать лист невидимым 49
Сколько страниц на всех листах? 49
Ячейка и диапазон (столбцы и строки) 49
Копирование строк на другой лист 49
Копирование столбцов на другой лист 49
Подсчет количества ячеек, содержащих указанные значения_1 50
Подсчет количества ячеек в диапазоне, содержащих указанные значения_2 50
Подсчет количества видимых ячеек в диапазоне 51
Определение количества ячеек в диапазоне и суммы их значений 51
Подсчет количества ячеек 51
Автоматический пересчет данных таблицы при изменении ее значений 51
Ввод данных в ячейки 53
Ввод данных с использованием формул 53
Последовательный ввод данных 53
Ввод текстоввых данных в ячейки 53
Вывод в ячейки названия книги, листа и количества листов 54
Удаление пустых строк_1 54
Удаление пустых строк_2 54
Удаление пустых строк_3 55
Удаление строки по условию 55
Удаление скрытых строк 56
Удаление используемых скрытых строк или строк с нулевой высотой 56
Удаление дубликатов по маске 56
Выделение диапазона над текущей ячейкой 57
Выделение диапазона над текущей ячейкой_2 57
Выделить ячейку и поместить туда число 58
Выделение отрицательных значений 58
Выделение диапазона и использование абсолютных адресов 58
Выделение ячеек через интервал_1 59
Выделение ячеек через интервал_2 59
Выделение нескольких диапазонов 60
Движение по ячейкам 60
Поиск ближайшей пустой ячейки столбца 61
Поиск максимального значения 61
Поиск и замена по шаблону 61
Поиск значения с отображением результата в отдельном окне 62
Поиск с выделением найденных данных_1 62
Поиск с выделением найденных данных_2 62
Поиск по условию в диапазоне 63
Поиск последней непустой ячейки диапазона 64
Поиск последней непустой ячейки столбца 64
Поиск последней непустой ячейки строки 64
Поиск ячейки синего цвета в диапазоне 65
Поиск отрицательного значения в диапазоне и выделения синим цветом 65
Поиск наличия значения в столбце 65
Поиск совпадений в диапазоне 66
Поиск ячейки в диапазоне_1 67
Поиск ячейки в диапазоне_2 67
Поиск приближенного значения в диапазоне 67
Поиск начала и окончания диапазона, содержащего данные 68
Поиск начала данных 68
Автоматическая замена значений 68
Быстрое заполнение диапазона (массив) 69
Заполнение через интервал(массив) 69
Заполнение указанного диапазона(массив) 70
Заполнение диапазона(массив) 70
Расчет суммы первых значений диапазона 71
Размещение в ячейке электронных часов 72
«Будильник» 72
Оформление верхней и нижней границ диапазона 72
Адрес активной ячейки 73
Координаты активной ячейки 73
Формула активной ячейки 73
Получение из ячейки формулы 73
Тип данных ячейки 73
Вывод адреса конца диапазона 74
Получение информации о выделенном диапазоне 74
Взять слово с 13 символа в ячейке 76
Создание изменяемого списка (таблица) 77
Проверка на пустое значение 77
Пересечение ячеек 77
Умножение выделенного диапазона на 2 77
Одновременное умножение всех данных диапазона 78
Деление диапазона на 100 78
Возведение каждой ячейки диапазона в квадрат 78
Суммирование данных только видимых ячеек 78
Сумма ячеек с числовыми значениями 79
При суммировании — курсор внутри диапазона 79
Начисление процентов в зависимости от суммы_1 80
Начисление процентов в зависимости от суммы_2 80
Начисление процентов в зависимости от суммы_3 81
Сводный пример расчета комиссионного вознаграждения 81
Движение по диапазону 83
Сдвиг от выделенной ячейки 83
Перебор ячеек вниз по колонне 83
Создание заливки диапазона 84
Подбор параметра ячейки 84
Разбиение диапазона 84
Объединение данных диапазона 85
Объединение данных диапазона_2 85
Узнать максимальную колонку или строку. 86
Ограничение возможных значений диапазона 86
Тестирование скорости чтения и записи диапазонов 88
Открыть MsgBox при выборе ячейки 89
Скрытие строки 89
Скрытие нескольких строк 89
Скрытие столбца 89
Скрытие нескольких столбцов 89
Скрытие строки по имени ячейки 89
Скрытие нескольких строк по адресам ячеек 89
Скрытие столбца по имени ячейки 89
Скрытие нескольких столбцов по адресам ячеек 90
Мигание ячейки 90
Глава 4. Работа с примечаниями 91
Вывод на экран всех примечаний рабочего листа 91
Функция извлечения комментария 91
Список примечаний защищенных листов 91
Перечень примечаний в отдельном списке_1 92
Перечень примечаний в отдельном списке_2 93
Перечень примечаний в отдельном списке_3 93
Подсчет количества примечаний_1 94
Подсчет количества примечаний_2 95
Подсчет примечаний_3 95
Выделение ячеек с примечаниями 95
Отображение всех примечаний 95
Изменение цвета примечаний 96
Добавление примечаний 96
Добавление примечаний в диапазон по условию 96
Перенос комментария в ячейку и обратно 96
Перенос значений из ячейки в комментарий_1 97
Перенос значений из ячейки в комментарий_2 98
Глава 5 . Пользовательские вкладки на ленте 99
Дополнение панели инструментов 99
Добавление кнопки на панель инструментов 99
Панель с одной кнопкой 99
Панель с двумя кнопками 99
Создание панели справа 100
Вызов предварительного просмотра 100
Создание пользовательского меню (вариант 1) 100
Создание пользовательского меню (вариант 2) 101
Создание пользовательского меню (вариант 3) 102
Создание пользовательского меню (вариант 4) 102
Создание пользовательского меню (вариант 5) 102
Создание пользовательского меню (вариант 6) 106
Создание списка пунктов главного меню Excel 108
Создание списка пунктов контекстных меню 108
Отображение панели инструментов при определенном условии 109
Скрытие и отображение панелей инструментов 111
Создать подсказку к моим кнопкам 112
Создание меню на основе данных рабочего листа 112
Создание контекстного меню 115
Блокировка контекстного меню 117
Добавление команды в меню Сервис 118
Добавление команды в меню Вид 119
Создание панели со списком 120
Мультфильм с помощником в главной роли 122
Дополнение помощника текстом, заголовком, кнопкой и значком 123
Новые параметры помощника 124
Использование помощника для выбора цвета заливки 125
Глава 6. ДИАЛОГОВЫЕ ОКНА 127
Функция INPUTBOX (через ввод значения) 127
Вызов предварительного просмотра 127
Настройка ввода данных в диалоговом окне 127
Открытие диалогового окна (“Открыть файл”)_1 128
Открытие диалогового окна (“Открыть файл”)_2 128
Открытие диалогового окна (“Печать”) 128
Другие диалоговые окна 128
Вызов броузера из Экселя 129
Диалоговое окно ввода данных 129
Диалоговое окно настройки шрифта 129
Значения по умолчанию 129
Глава 7. Форматирование текста. Таблицы. ГРАНИЦЫ И ЗАЛИВКА. 130
Вывод списка доступных шрифтов 130
Выбор из текста всех чисел 130
Прописная буква только в начале текста 131
Подсчет количества повторов искомого текста 131
Выделение из текста произвольного элемента 132
Отображение текста «задом наперед» 133
Англоязычный текст — заглавными буквами 133
Запуск таблицы символов из Excel 134
глава информация о пользователе, компьютере, принтере и т.д. 136
Получить имя пользователя 136
Вывод разрешения монитора 137
Получение информации об используемом принтере 137
Просмотр информации о дисках компьютера 138
Глава 8. ЮЗЕРФОРМЫ 140
Глава 9. ДИАГРАММЫ 142
Построение диаграммы с помощью макроса 142
Сохранение диаграммы в отдельном файле 143
Построение и удаление диаграммы нажатием одной кнопки 144
Вывод списка диаграмм в отдельном окне 145
Применение случайной цветовой палитры 146
Эффект прозрачности диаграммы 146
Построение диаграммы на основе данных нескольких рабочих листов 148
Создание подписей к данным диаграммы 150
Глава 10. РАЗНЫЕ ПРОГРАММЫ. 151
Программа для составления кроссвордов 151
Создать обложку DVD 155
Игра «Минное поле» 156
Игра «Угадай животное» 158
Расчет на основании ячеек определенного цвета 161
Глава 11. ДРУГИЕ ФУНКЦИИ И МАКРОСЫ 175
Вызов функциональных клавиш 175
Расчет среднего арифметического значения 175
Перевод чисел в «деньги» 175
Поиск ближайшего понедельника 176
Подсчет количества полных лет 177
Расчет средневзвешенного значения 177
Преобразование номера месяца в его название 178
Использование относительных ссылок 178
Преобразование таблицы Excel в HTML-формат 179
Генератор случайных чисел 181
Случайные числа — на основании диапазона 182
Применение функции без ввода ее в ячейку 183
Подсчет именованных объектов 183
Включение автофильтра с помощью макроса 183
Создание бегущей строки 183
Создание бегущей картинки 184
Вращающиеся автофигуры 185
Вызов таблицы цветов 187
Создание калькулятора 188
Склонение фамилии, имени и отчества 188
Глава 12. ДАТА И ВРЕМЯ 194
Вывод даты и времени_1 194
Вывод даты и времени_2 194
Получение системной даты 195
Извлечение даты и часов 195
Функция ДатаПолная 195.

Читать еще:  Макрос из excel в excel

Бесплатно скачать электронную книгу в удобном формате, смотреть и читать:
Скачать книгу Готовые макросы в VBA Excel, Миронов — fileskachat.com, быстрое и бесплатное скачивание.

Скачать doc
Ниже можно купить эту книгу по лучшей цене со скидкой с доставкой по всей России. Купить эту книгу

Трюки с макросами

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

Трюк №94. Распространение макросов

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

Трюк №93. Выполнение процедур на защищенных рабочих листах Excel

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

Трюк №92. Как обойти ограничение Excel 2003 на три критерия условного форматирования

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

Трюк №91. Получение имени и пути рабочей книги Excel

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

Трюк №90. Включение и снятие защиты паролем для всех рабочих листов Excel одновременно

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

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector