Open Notes
Обо всём, что мне интересно
Полезные команды VBA
Пополняемый список полезных отрывков кода VBA для выполнения часто востребованных действий в MS Excel.
Служебные команды для ускорения скорости выполнения макроса:
‘Отключение отображения выполняемых действий Application.ScreenUpdating = False ‘Предотвращение появления предупреждающих сообщений Application.DisplayAlerts = False ‘Предотвращение появления предупреждения об обновлении связей данных Application.AskToUpdateLinks = False ‘Очистка буфера обмена Application.CutCopyMode = False
Проверка имени пользователя, запустившего макрос:
Чтобы проверить, какой пользователь открыл книгу Excel можно использовать один из следующих вариантов:
If Application.UserName = «Имя_автора_документа» Then .
If Environ(«username») = «user» Then .
Поиск последней строки таблицы:
Set myWSheet = ThisWorkbook.Sheets(«Имя_листа») With myWSheet ‘Определение индекса последней строки таблицы lastRow = .Cells(Rows.Count, 1).End(xlUp).Row ‘Определение значения в ячейки последней строке столбца A lastARow = .Range(«A» & lastRow).Value End With
Замена формулы на значение:
Добавление нового листа с именем после всех существующих:
Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = «Имя_листа»
Как узнать последний день предыдущего месяца:
LastMonthDay = DateAdd(«d», -1, DateSerial(Year(dtDate), Month(dtDate), 1))
Определение оставшихся дней месяца:
dToEndOfMonth = DateDiff(«d», dFrom, DateAdd(«d», -1, _ DateSerial(Year(dFrom), Month(dFrom) + 1, 1)))
Номер текущего дня в неделе (воскресенье — первый день):
DayOfWeek = DatePart(«w», dToday)
Создание нового файла из текущего:
pathNewBook = «C:Temp» nameNewBook = «Имя_нового_файла.xls» Workbooks.Add ActiveWorkbook.SaveAs Filename:=pathNewBook & nameNewBook ActiveWorkbook.Close True
Сохранить текущий файл в формате CSV
Чтобы при сохранении файла в формате CSV, вместо запятых в качестве разделителя использовалась точка с запятой, следует использовать подобный код:
ActiveWorkbook.SaveAs FileName:=»Name.csv», FileFormat:=xlCSV, _ CreateBackup:=False, Local:=True ActiveWorkbook.Saved = True ActiveWorkbook.Close True
Копирование данных из одного файла в другой:
wbPath = «C:Temp» wbName = «Имя_файла_откуда_копируем.xls» Workbooks.Open (wbPath & wbName) Set WB = Workbooks(wbName) WB.Sheets(«Лист 1»).Range(«A1:С10»).Copy Sheet(«Лист_в_текущем_файле»).Range(«A2»).PasteSpecial xlPasteValues
Чтобы открыть файл только для чтения, следует использовать:
Workbooks.Open (Filename:=wbPath & wbName, ReadOnly:=True)
Предотвращение ошибки при неудачном поиске значения в таблице:
Set DateRowObj = WB.Sheets(«Имя_листа»).Range(«A:A»)._ Find(What:=dtToAsDate, LookIn:=xlFormulas) If (DateRowObj Is Nothing) Then WB.Close False MsgBox «Данные не найдены.» Else DateRow = DateRowObj.Row ‘Номер строки с искомым значением End If
Как получить имя активной книги Excel без его расширения (без .xls либо без .xlsx):
wbName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, «.») — 1)
Проверка существования файла:
fPath = «C:Temp» fName = «Файл.txt» If Dir(fPath & fName) = «» Then MsgBox «Файл не найден:» & Chr(13) & fPath & fName Exit Sub End If
Кнопка, скрывающая/разворачивающая часть таблицы:
Private Sub tbV ).H >
Обновление сводной таблицы:
currPath = ThisWorkbook.Path currWBName = ThisWorkbook.Name ListName.PivotTables(«СводнаяТаблица1»).ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=currPath & «[» & _ currWBName & «]Лист1!R1C1:R10C5»)
Обращение к элементам Frame:
Замена #ДЕЛ/0! в диапазоне:
Selection.Replace What:=»#DIV/0!», Replacement:=»», LookAt:=xlPart,_ SearchOrder:=xlByRows, MatchCase:=False,_ SearchFormat:=False, ReplaceFormat:=False
Количество строк в отфильтрованной таблице:
Быстро убрать лишние пробелы в диапазоне:
Программно снять защиту с листа:
Работа с диапазоном
Умножить диапазон на число:
ThisWorkbook.Sheets(1).Range(«A1:A10») = _ ThisWorkbook.Sheets(1).Evaluate(«A1:A10» & «*80»)
Добавить ко всем значениям диапазона строку:
ThisWorkbook.Range(«A1:A10»).Value = _ Evaluate(«=»»» & addTxt & «»» & » & ThisWorkbook.Range(«A1:A10»).Address)
Сортировка выбранного столбца в сводной таблице
Col = Selection.Column ‘Номер выбранного столбца ColMax = ActiveSheet.PivotTables(«СводнаяТаблица»).PivotColumnAxis. _ PivotLines.Count If Col — 1
Поиск файлов в папке
Dim strDirPath, strMaskSearch, strFileName as String strDirPath = «C:/test/» ‘Папка поиска strMaskSearch = «*.xls*» ‘Маска поиска ‘Получаем первый файл соответствующий шаблону strFileName = Dir(strDirPath & strMaskSearch) Do While strFileName <> «» ‘До тех пор пока файлы «не закончатся» MsgBox strFileName strFileName = Dir ‘Следующий файл Loop
Cutcopymode vba excel
Пополняемый список полезных отрывков кода VBA для выполнения часто востребованных действий в MS Excel.
Служебные команды для ускорения скорости выполнения макроса:
‘Отключение отображения выполняемых действий
‘Предотвращение появления предупреждающих сообщений
‘Предотвращение появления предупреждения об обновлении связей данных
‘Очистка буфера обмена
П роверка имени пользователя, запустившего макрос:
Чтобы проверить, какой пользователь открыл книгу Excel можно использовать один из следующих вариантов:
If Application.UserName = «Имя_автора_документа» Then .
If Environ( «username» ) = «user» Then .
Поиск последней строки таблицы:
Set myWSheet = ThisWorkbook.Sheets( «Имя_листа» )
‘Определение индекса последней строки таблицы
lastRow = .Cells(Rows.Count, 1). End (xlUp).Row
‘Определение значения в ячейки последней строке столбца A
lastARow = .Range( «A» & lastRow).Value
Замена формулы на значение:
Добавление нового листа с именем после всех существующих:
Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = «Имя_листа»
Как узнать последний день предыдущего месяца:
LastMonthDay = DateAdd( «d» , -1, DateSerial(Year(dtDate), Month(dtDate), 1))
Определение оставшихся дней месяца:
dToEndOfMonth = DateDiff( «d» , dFrom, DateAdd( «d» , -1, _
DateSerial(Year(dFrom), Month(dFrom) + 1, 1)))
Номер текущего дня в неделе (воскресенье — первый день):
DayOfWeek = DatePart( «w» , dToday)
Создание нового файла из текущего:
ActiveWorkbook.SaveAs Filename:=pathNewBook & nameNewBook
ActiveWorkbook. Close True
Сохранить текущий файл в формате CSV
Чтобы при сохранении файла в формате CSV, вместо запятых в качестве разделителя использовалась точка с запятой, следует использовать подобный код:
ActiveWorkbook.SaveAs FileName:= «Name.csv» , FileFormat:=xlCSV, _
CreateBackup:= False , Local:= True
ActiveWorkbook. Close True
Копирование данных из одного файла в другой:
Workbooks. Open (wbPath & wbName)
Set WB = Workbooks(wbName)
WB.Sheets( «Лист 1» ).Range( «A1:С10» ).Copy
Sheet( «Лист_в_текущем_файле» ).Range( «A2» ).PasteSpecial xlPasteValues
Чтобы открыть файл только для чтения, следует использовать:
Workbooks. Open (Filename:=wbPath & wbName, ReadOnly:= True )
Предотвращение ошибки при неудачном поиске значения в таблице:
Set DateRowObj = WB.Sheets( «Имя_листа» ).Range( «A:A» )._
If (DateRowObj Is Nothing ) Then
MsgBox «Данные не найдены.»
DateRow = DateRowObj.Row ‘Номер строки с искомым значением
Как получить имя активной книги Excel без его расширения (без .xls либо без .xlsx):
wbName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, «.» ) — 1)
Проверка существования файла:
If Dir(fPath & fName) = «» Then
MsgBox «Файл не найден:» & Chr(13) & fPath & fName
Кнопка, скрывающая/разворачивающая часть таблицы:
Private Sub tbVid_Click()
ActiveSheet.Rows( «2:29» ).H > False
ActiveSheet.Rows( «2:29» ).H > True
Обновление сводной таблицы:
ListName.PivotTables( «СводнаяТаблица1» ).ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=currPath & «[» & _
Обращение к элементам Frame:
Замена #ДЕЛ/0! в диапазоне:
Selection.Replace What:= «#DIV/0!» , Replacement:= «» , LookAt:=xlPart,_
SearchOrder:=xlByRows, MatchCase:= False ,_
SearchFormat:= False , ReplaceFormat:= False
Количество строк в отфильтрованной таблице:
Быстро убрать лишние пробелы в диапазоне:
Программно снять защиту с листа:
Работа с диапазоном
Умножить диапазон на число:
ThisWorkbook.Sheets(1).Range( «A1:A10» ) = _
ThisWorkbook.Sheets(1).Evaluate( «A1:A10» & «*80» )
Добавить ко всем значениям диапазона строку:
ThisWorkbook.Range( «A1:A10» ).Value = _
Evaluate( «=»»» & addTxt & «»» & » & ThisWorkbook.Range( «A1:A10» ).Address)
Сортировка выбранного столбца в сводной таблице
Col = Selection.Column ‘Номер выбранного столбца
ColMax = ActiveSheet.PivotTables( «СводнаяТаблица» ).PivotColumnAxis. _
If Col — 1 And Col 1 Then
ActiveSheet.PivotTables( «СводнаяТаблица» ).PivotFields( «Label» ).AutoSort _
xlDescending, » » , ActiveSheet.PivotTables( «СводнаяТаблица» ). _
PivotColumnAxis.PivotLines(Col — 1), 1
Счетчик времени выполнения процедуры
‘Счётчик, ставится в начале процедуры
‘Сообщение, выводится в конце процедуры
MsgBox «Данные обновлены за » & Fix(1440 * (Now – StartUpdDate)) & » мин. » & 86400 * (Now – StartUpdDate) Mod 60 & » сек.»
Функция транслитерации с русского на английский
Function Translit(Txt As String ) As String
Rus = Array( «ий» , «ый» , «ъе» , «ъя» , «ъю» , _
«ъё» , «ье» , «ья» , «ью» , «ьё» , «а» , «б» , «в» , «г» , _
«д» , «е» , «ё» , «ж» , «з» , «и» , «й» , «к» , «л» , _
«м» , «н» , «о» , «п» , «р» , «с» , «т» , «у» , «ф» , «х» , _
«ц» , «ч» , «ш» , «щ» , «ъ» , «ы» , «ь» , «э» , «ю» , «я» , _
«ИЙ» , «ЫЙ» , «ЪЕ» , «ЪЯ» , «ЪЮ» , _
«ЪЁ» , «ЬЕ» , «ЬЯ» , «ЬЮ» , «ЬЁ» , «А» , «Б» , «В» , «Г» , _
«Д» , «Е» , «Ё» , «Ж» , «З» , «И» , «Й» , «К» , «Л» , _
«М» , «Н» , «О» , «П» , «Р» , «С» , «Т» , «У» , «Ф» , «Х» , _
«Ц» , «Ч» , «Ш» , «Щ» , «Ъ» , «Ы» , «Ь» , «Э» , «Ю» , «Я» , _
«a» , «b» , «c» , «d» , «e» , «f» , «g» , «h» , «i» , «j» , «k» , «l» , «m» , _
«n» , «o» , «p» , «q» , «r» , «s» , «t» , «u» , «v» , «w» , «x» , «y» , «z» , ««» , «»» )
Eng = Array( «y» , «y» , «ye» , «ya» , «yu» , _
«yo» , «ye» , «ya» , «yu» , «yo» , «a» , «b» , «v» , «g» , _
«d» , «e» , «yo» , «zh» , «z» , «i» , «y» , «k» , «l» , «m» , _
«n» , «o» , «p» , «r» , «s» , «t» , «u» , «f» , «h» , «ts» , _
«ch» , «sh» , «sch» , «» , «y» , «» , «eh» , «u» , «ya» , _
«Y» , «Y» , «Ye» , «Ya» , «Yu» , _
«Yo» , «Ye» , «Ya» , «Yu» , «Yo» , «A» , «B» , «V» , «G» , _
«D» , «E» , «Yo» , «Zh» , «Z» , «I» , «Y» , «K» , «L» , «M» , _
«N» , «O» , «P» , «R» , «S» , «T» , «U» , «F» , «H» , «Ts» , _
«Ch» , «Sh» , «Sch» , «» , «Y» , «» , «Eh» , «U» , «Ya» , _
«a» , «b» , «c» , «d» , «e» , «f» , «g» , «h» , «i» , «j» , «k» , «l» , «m» , _
«n» , «o» , «p» , «q» , «r» , «s» , «t» , «u» , «v» , «w» , «x» , «y» , «z» , «» , «» )
For i = 1 To Len(Txt)
For J = 0 To 116
If Rus(J) = с Then
If flag Then outstr = outstr & outchr Else outstr = outstr & с
Поиск файлов в папке
Dim strDirPath, strMaskSearch, strFileName as String
strDirPath = «C:/test/» ‘Папка поиска
strMaskSearch = «*.xls*» ‘Маска поиска
‘Получаем первый файл соответствующий шаблону
strFileName = Dir(strDirPath & strMaskSearch)
Do While strFileName <> «» ‘До тех пор пока файлы «не закончатся»
Макрос на VBA Excel – Формируем документы по шаблону
Очень часто бывает такое, что нужно сформировать документы по определенному шаблону, на основе каких-то данных, например, по каждому сотруднику или по каждому лицевому счету. И делать это вручную бывает достаточно долго, когда этих самых сотрудников или лицевых счетов много, поэтому сегодня мы рассмотрим примеры реализации таких задач в Excel с помощью макроса написанного на VBA Excel.
Немного поясню задачу, допустим, нам необходимо сформировать какие-то специфические документы по шаблону массово, т.е. в итоге их получится очень много, как я уже сказал выше, например, по каждому сотруднику. И это нужно сделать непосредственно в Excel, если было бы можно это сделать в Word, то мы бы это сделали через «Слияние», но нам нужно именно в Excel, поэтому для этой задачи мы будем писать макрос.
Мы с Вами уже выгружали данные по шаблону через клиент Access из базы MSSql 2008 в Word и Excel вот в этой статье — Выгрузка данных из Access в шаблон Word и Excel. Но сейчас допустим, у нас данные располагаются в базе, в клиенте которой нельзя или слишком трудоемко реализовать такую задачу, поэтому мы просто выгрузим необходимые данные в Excel и на основе таких данных по шаблону сформируем наши документы.
В нашем примере мы, конечно, будем использовать простой шаблон, только для того чтобы это было просто наглядно и понятно (только в качестве примера), у Вас в свою очередь шаблон будет, как мне кажется намного сложней.
Напомню, что на данном сайте тема VBA Excel уже затрагивалась, например, в материале – Запрет доступа к листу Excel с помощью пароля
И так приступим!
Пишем макрос на VBA Excel по формированию документов
Реализовывать нашу задачу будем на примере «Электронной карточке сотрудника» (я это просто придумал:), хотя может такие и на самом деле есть), т.е. документ в котором хранится личные данные сотрудника вашего предприятия, в определенном виде, именно в Excel.
Примечание! Программировать будем в Excel 2010.
И для начала приведем исходные данные, т.е. сами данные и шаблон
Данные.
Лист, на котором расположены эти данные так и назовем «Данные»
Шаблон.
Лист, на котором расположен шаблон, тоже так и назовем «Шаблон»
Далее, нам необходимо присвоить имена полей для вставки, так более удобней к ним обращаться чем, например, по номеру ячейки.
Это делается очень просто, выделяете необходимую ячейку или диапазон, и жмете правой кнопкой мыши и выбираете «Присвоить имя», пишите имя ячейки и жмете «ОК»
Свои поля я назвал следующим образом:
- ФИО – fio;
- № — number;
- Должность – dolgn;
- Адрес проживания – addres;
- Тел. № сотрудника – phone;
- Комментарий – comment.
Код макроса на VBA Excel
Для того чтобы написать код макроса, открывайте на ленте вкладку «Разработчик», далее макросы.
Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»
затем, в правой области поставьте галочку напротив пункта «Разработчик»
После вкладка разработчик станет отображаться на ленте.
Далее, когда Вы откроете вкладку разработчик и нажмете кнопку «Макросы» у Вас отобразится окно создания макроса, Вы пишите название макросы и жмете «создать».
После у Вас откроется окно редактора кода, где собственно мы и будем писать свой код VBA. Ниже представлен код, я его как обычно подробно прокомментировал:
Теперь осталось выполнить этот макрос, для этого откройте вкладку разработчик->макросы->выполнить наш макрос:
и после выполнения у Вас в той же папке появится вот такие файлы
Вот с таким содержимым:
Для удобства можете на листе с данными создать кнопку и задать ей событие выполнить наш только что созданный макрос, и после чего простым нажатием выполнять этот макрос. Вот и все! Удачи!
Cutcopymode vba excel
На этом шаге мы рассмотрим перечень окончательных действий по созданию указанного приложения .
Вернемся на рабочий лист ЖурналРегистрацииКУ .
При разработке приложения считалось, что основной метод формирования новой строки с данными в журнале регистрации — копирование строки с имеющейся записью, последующий переход в конец списка, выделение первой пустой строки и вставка скопированного содержимого. Задумайтесь, сколько однотипных действий должен совершить пользователь для выполнения этой, казалось бы очень простой, операции.
Оставим за пользователем только выбор копируемой строки, а все остальное поручите выполнять Excel в автоматическом режиме при нажатии на созданную кнопку Копирование и вставка выделенной строки (рисунок 1), которой назначьте следующий записываемый макрос с именем ВставкаСтроки .
Рис.1. Левая область журнала регистрации командировочных удостоверений на рабочем листе ЖурналРегистрацииКУ
Для записи макроса выполните следующую последовательность действий:
- перед записью макроса выделите выбранную строку, например, строку 9;
- выполните процедуру начала записи макроса, после чего задайте команду копирования, например, нажатием на кнопку Копирование на стандартной панели инструментов. Содержимое строки 9 будет помещено в буфер обмена;
- в связи с тем, что процедура автоматического определения и выделения первой пустой строки списка еще не изучена, выделите самую последнюю строку списка. Согласно ранее определенным договоренностям — это строка 200. Но чтобы добраться до нее, нужно выполнить ряд действий, например, перемещений на несколько экранов монитора вниз. Не затрудняйте себя, а выделите, например, строку 14 и произведите вставку из буфера обмена, например, нажатием на кнопку Вставка на стандартной панели инструментов;
- выделите строки от строки вставки (14) до первой строки после заголовка списка (4) и нажмите на кнопку Сортировка по возрастанию на стандартной панели управления. По умолчанию Excel произведет сортировку по данным столбца А . Список расположится в порядке возрастания порядковых номеров и содержимое скопированной строки 9 будет находиться в конце списка;
- остановите запись макроса.
Назначьте макрос кнопке и выделив одну из заполненных строк списка журнала регистрации, нажатием на созданную кнопку, проверьте правильность выполнения заданной последовательности ваших действий.
Исследование кода VBA при записи макроса ВставкаСтроки
После записи макроса не откладывайте на потом исследование записанного кода VBA , а открыв Редактор Visual Basic просмотрите записанные действия и при необходимости отредактируйте код VBA . Механически сгенерированный код показан на рисунке 2.
Рис.2. Механически сгенерированный код VBA
Между ключевыми словами начала и окончания выполнения подпрограммы находится восемь строк кода. По очереди рассмотрим каждую строку. Первая строка:
Обратите на точку, находящемуся между текстом кода. Она называется операция точка , и предназначена для разделения названия свойства и названия метода в этой строке.
Обратите внимание на три рассмотренные строки в совокупности. Они предназначены для выполнения одной операции — копирования выделенной строки с целью вставки ее содержимого в другую строку. Но ту же операцию может выполнить всего одна строка, в которой в качестве аргумента метода Copy указано место вставки содержимого скопированного диапазона:
При подобном редактировании кода макроса нет необходимости даже соблюдать интервал (пробелы) между операторами Copy и Rows . После окончания редактирования, установка интервалов между операторами при отсутствии ошибок выполняется Редактором Microsoft Visual Basic автоматически.
Следующую строку Excel генерирует также самостоятельно:
Следующая выполняемая операция (сортировка) состоит из двух строк:
В одной строке Редактора может находиться до 1024 символов. Но если все 1024 символа разместить в одной строке, то это затруднит ее чтение. Для переноса кода строки в следующую, используется символ подчеркивания (_), перед которым обязательно должен находиться пробел.
Excel позволяет выполнять операцию сортировки с использованием от одного до трех ключей и для сортировки диапазона с помощью VBA используется метод Sort . В этой команде, выполняющей операцию сортировки, находятся следующие операторы:
- Selection — выделенная область рабочего листа, значения которой будут сортироваться;
- Sort — имя метода (сортировка);
- Key1:=Range(«A14») — произвести сортировку выделенного диапазона по столбцу А , где аргумент Key1 указывает, что это первый ключ сортировки. Как вы знаете, Excel позволяет производить последнюю сортировку выделенного диапазона по трем полям с помощью диалогового окна Сортировка диапазона , которое вызывается с помощью команды Данные | Сортировка ;
- Order1 — указывает на порядок сортировки диапазона по первому ключу. Аргумент xlAscending — произвести сортировку по возрастанию. При сортировке по убыванию присваивается значение xlDescending ;
- Header — аргумент, с помощью которого определяется: сортировать ли первую строку выделенного диапазона. При механической записи макроса этому аргументу присвоено значение xlGuess . В этом случае Excel самостоятельно определяет, является ли первая строка строкой заголовка и нужно ли ее сортировать. Для того чтобы указать, что первая строка является строкой заголовка, и ее сортировать не требуется, присвойте этому аргументу значение xlYes . Если присвоить значение xlNo , то в этом случае первая строка сортируется;
- OrderCustom — целое число, которое указывает порядок сортировки, указанный пользователем на вкладке Списки диалогового окна Параметры , выводимое командой Сервис| Параметры . По умолчанию присваивается значение 1;
- MatchCase — указывает различать (True) или нет (False) при сортировке заглавные и строчные буквы;
- Orientation — указывает как проводить сортировку: по строкам (xlTopToBottom) или столбцам (xlLeftToRight) .
Как видно из записанного кода для присвоения методам значений применяются знаки := (двоеточие и равно).
Полученный в результате редактирования код макроса показан на рисунке 3, а остальной текст кода можно удалить.
Рис.3. Отредактированный код VBA подпрограммы вставки в конец списка содержимого выделенной строки
За счет минимизации текста подпрограммы было увеличено его быстродействие. Ведь для Excel нужно было прочитать ранее 8 строк, по каждой из которых он должен был предпринять какие-то действия, что значительно дольше, чем 3.
Полный текст этого приложения можно взять здесь.
Со следующего шага мы начнем рассматривать автоматизацию рабочего процесса по формированию и учету кассовых документов .