Top-office11.ru

IT и мир ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Команды vba excel

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

VBA-Урок 1. Что такое VBA. Основные понятия.

В данном разделе мы рассмотрим что такое VBA, посколько именно на нем и пишутся макросы.

Что такое VBA?

VBA — язык (расшифровывается как Visual Basic for Application) был разработан компанией Microsoft. Данный язык не является самостоятельным, а предназначен для автоматизации процессов в пакете MS Office. VBA широко используется в Excel, а также в Access, Word и других программах пакета.

VBA — простой язык программирования, которому может научиться любой желающий. Изучив его, вы сможет предоставлять команды Excel, что делать с колонками, строками, значениями в ячейках, перемещать/добавлять/сортировать листы, выводить заранее запрограммированные сообщения, писать свои формулы и функции и т.д. Суть языка заключается в оперировании объектами (что относит его к объектно-ориентированному программированию).

Читать еще:  Как найти внешние ссылки в excel

Чтобы работать с VBA кодом, нам нужен редактор, который уже установлен по умолчанию. Вы можете открыть его, нажав комбинацию клавиш » ALT + F11 «.

Объекты (Objects)

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

Главный объект это Application, что соответствует самой программе Excel. Далее следует Workbooks (книга), Worksheets (лист), Range (диапазон, или отдельная ячейка). Например, чтобы обратиться к ячейке «A1» на листе нам нужно будет прописать следующий путь с учетом иерархии:

Application.Workbooks(«Архив»).Worksheets(«Аркуш1»).Range(«A1»).

Таким образом, мы научились обращаться до наименьшего объекта в Excel — ячейки.

Коллекции (Collections)

В свою очередь объекты имеют «коллекции». Коллекция — это группа объектов одинакового класса. Отдельные элеметы коллекции являются также объектами. Так, объекты Worksheets являются элементами коллекции объекта Worksheet, который содержит также и другие коллекции и объекты:

  • ChartObjects (элемент коллекции объекта ChartObject)
  • Range
  • PageSetup
  • PivotTables (элемент коллекции объекта PivotTable).

Свойства (Properties)

Каждый объект имеет свойства . Например, объект Range имеет свойство Value или Formula.

Worksheets(“Sheet1”).Range(“A1”).Value або Worksheets(“Sheet1”).Range(“A1”).Formula

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

Также, через свойство Formula можно не только получить формулу, но и записать ее:

MsgBox Range(“A1”).Formula — получим сообщение с формулой в ячейке «А1«;

Range(“B12”).Formula = “=2+6*100” — вписываем формулу =2+6*100 в ячейку B12.

Методи (Methods)

Теперь давайте рассмотрим, каким образом мы можем управлять содержимым диапазона или ячейки. Для этого в VBA существуют, так-называемые методы (команды «что сделать»). При написании кода методы отделяются от объекта точкой, например:

Range(«A1»).Select или Cells(1, 1).Select

Данный метод указывает выбрать (Select) ячейку «A1».
Далее, давайте, удалим значение в данной ячейке. Для этого напишем следующий код:

Selection.ClearContents

Здесь программа «берет» то, что мы выделили (Selection) и удаляет его содержимое (ClearContents ).

Команды 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 <> «» ‘До тех пор пока файлы «не закончатся»

Атанас Йонков Блоггер, Веб-разработчик
yonkov.atanas@gmail.com

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

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

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

Table of Contents

Как включить макросы в Excel

В Excel нажмите комбинацию клавиш alt + F11. Это приведет вас к редактору VBA в MS Excel. Затем щелкните правой кнопкой мыши папку Microsoft Excel Objects слева и выберите Insert => Module. Это место, где сохраняются макросы. Чтобы использовать макрос, вам нужно сохранить документ Excel как макрос. Из табуляции File => Save as, выберите Save as macro-enabled Workbok (расширение .xlsm) Теперь пришло время написать свой первый макрос!

1. Копирование данных из одного файла в другой.

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

2. Отображение скрытых строк

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

3. Удаление пустых строк и столбов

Пустые строки в Excel — может быть проблемой для обработки данных. Вот как избавиться от них:

4. Нахождение пустых ячеек

13. Создание сводной таблицы

14. Отправка активного файла по электронной почте

Мой любимый код VBA. Он позволяет вам прикреплять и отправлять файл, с которым вы работаете, с предопределенным адресом электронной почты, заголовком сообщения и телом сообщения! Сначала Вам нужно сделать референцию в Excel на Microsoft Outlook (в редакторе Excel VBA, нажмите tools => references и выберите Microsoft Outlook).

15. Вставка всех графиков Excel в презентацию PowerPoint

Очень удобный макрос, который позволяет вам добавлять все ваши графики Excel в презентацию Powerpoint одним щелчком мыши:

16. Вставка таблицы Excel в MS Word

Таблицы Excel обычно помещаются внутри текстовых документов. Вот один автоматический способ экспорта таблицы Excel в MS Word:

17. Извлечение слов из текста

Мы можем использовать формулы, если хотим извлечь определенное количество символов. Но что, если мы хотим извлечь только одно слово из предложения или диапазон слов в ячейке? Для этого мы можем сами создать функцию Excel с помощью VBA. Это одна из самых удобных функций VBA, поскольку она позволяет создавать собственные формулы, которые отсутствуют в MS Excel. Давайте продолжим и создадим две функции: findword() и findwordrev():

Отлично, мы уже создали две новые функции в Excel! Теперь попробуйте использовать их в Excel. Функция = FindWordRev (A1,1) берет последнее слово из ячейки A1. Функция = FindWord (A1,3) берет третье слово из ячейки A1 и т. Д.

18. Защита данных в MS Excel

Иногда мы хотим защитить данных нашего файла, чтобы только мы могли его изменять. Вот как это сделать с VBA:

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

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