Excel works!

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

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

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

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

Sub Makros1()
 Application.ScreenUpdating = 0
    'Ваш код
  Application.ScreenUpdating = 1
End Sub

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

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

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

Sub Makros1()
'Ваш код
Application.CutCopyMode = 0
End Sub

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

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

Sub makros1()
Dim mLastRow As Long
Dim nLastCol As Long
 
 mLastRow = Cells(Rows.Count, 1).End(xlUp).Row 'Находит номер последней строки заполненного диапазона
 nLastCol= Cells(1, Columns.Count).End(xlToLeft).Column 'Находит номер последнего заполненного столбца диапазона
End Sub

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

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

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

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

Sub makros1()
Application.ScreenUpdating = 0

Dim mLastRow As Long
Dim Kol As Long 'Переменная количества
Dim i As Long 'Переменная цикла
 
 mLastRow = Cells(Rows.Count, 1).End(xlUp).Row
 Kol = 0
 
 For i = 1 To mLastRow
    If Cells(i, 1).Value = "" Then
     Kol = Kol + 1
    End If
 Next i
 
 MsgBox Kol
 
Application.ScreenUpdating = 1
End Sub

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

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

Sub makros1()
TimeStart = Now

TimeFinish = Now
MsgBox "Time: " & Format(TimeFinish - TimeStart, "h:mm:ss")
End Sub

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

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

 

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