Використання макросів Excel може прискорити роботу та заощадити багато часу.
Один із способів отримання коду VBA - записати макрос і взяти код, який він генерує. Однак цей код записувача макросів часто заповнений кодом, який насправді не потрібен. Також макрореєстратор має деякі обмеження.
Тому варто мати колекцію корисних макрокодів VBA, які можна мати у своїй задній кишені і використовувати їх у разі потреби.
Хоча написання коду макросу Excel VBA спочатку може зайняти деякий час, а після того, як це буде зроблено, ви можете залишити його доступним як довідковий матеріал і використовувати його, коли вам це буде потрібно.
У цій масштабній статті я збираюся перерахувати кілька корисних прикладів макросів Excel, які мені часто потрібні, і зберігати їх у своєму приватному сховищі.
Я буду продовжувати оновлювати цей підручник з більшою кількістю прикладів макросів. Якщо ви вважаєте, що щось має бути у списку, просто залиште коментар.
Ви можете додати цю сторінку в закладки для подальшого використання.
Тепер, перш ніж я перейду до прикладу макросів і дам вам код VBA, дозвольте мені спочатку показати вам, як використовувати ці приклади кодів.
Використання коду з прикладів макросів Excel
Нижче наведено кроки, які потрібно виконати, щоб використати код з будь -якого з прикладів:
- Відкрийте книгу, у якій потрібно використовувати макрос.
- Утримуйте клавішу ALT і натисніть F11. Відкриється редактор VB.
- Клацніть правою кнопкою миші будь-який об’єкт у провіднику проектів.
- Перейдіть до Insert -> Module.
- Скопіюйте та вставте код у вікно коду модуля.
Якщо у прикладі сказано, що вам потрібно вставити код у вікно коду робочого аркуша, двічі клацніть на об’єкті аркуша та скопіюйте вставте код у вікно коду.
Після того, як ви вставили код у книгу, вам потрібно зберегти його з розширенням .XLSM або .XLS.
Як запустити макрос
Після того, як ви скопіювали код у редакторі VB, виконайте наступні кроки для запуску макросу:
- Перейдіть на вкладку Розробник.
- Натисніть на Макроси.
- У діалоговому вікні Макрос виберіть макрос, який потрібно запустити.
- Натисніть кнопку Виконати.
Якщо ви не можете знайти вкладку розробника на стрічці, прочитайте цей підручник, щоб дізнатися, як її отримати.
Пов’язаний посібник: Різні способи запуску макросу в Excel.
Якщо код вставлено у вікно коду робочого аркуша, вам не потрібно турбуватися про запуск коду. Він автоматично запуститься, коли відбудеться зазначена дія.
Тепер перейдемо до корисних прикладів макросів, які допоможуть вам автоматизувати роботу та заощадити час.
Примітка: Ви знайдете багато прикладів апострофа (‘), за яким слідує один -два рядки. Це коментарі, які ігноруються під час запуску коду та розміщуються як примітки для себе/читача.
Якщо у статті чи коді ви виявите помилку, будь ласка, будьте чудові та повідомте мене.
Приклади макросів Excel
Нижче наведено приклади макросів у цій статті:
Показати всі робочі аркуші за один раз
Якщо ви працюєте в робочій книзі, яка має кілька прихованих аркушів, вам потрібно показати ці аркуші по одному. Це може зайняти деякий час, якщо є багато прихованих аркушів.
Ось код, який покаже всі робочі аркуші у книзі.
'Цей код покаже всі аркуші у книзі Sub UnhideAllWoksheets () Затемнення w як робочого аркуша для кожного ws в ActiveWorkbook.
У наведеному вище коді використовується цикл VBA (для кожного) для перегляду кожного аркуша у книзі. Потім він змінює видиму властивість аркуша на видимий.
Ось детальний підручник про те, як використовувати різні методи для відображення аркушів у Excel.
Приховати всі аркуші, крім активного аркуша
Якщо ви працюєте над звітом або інформаційною панеллю, і хочете приховати весь аркуш, крім того, у якому є звіт/інформаційна панель, ви можете скористатися цим кодом макросу.
'Цей макрос приховає весь робочий аркуш, крім активного аркуша Sub HideAllExceptActiveSheet () Затемнення w як робочого аркуша для кожного ws у ThisWorkbook.Worksheets Якщо ws.Name ActiveSheet.Name Тоді ws.Visible = xlSheetHidden Наступний ws Завершити Sub
Сортування робочих листів за алфавітом за допомогою VBA
Якщо у вас є робоча книга з багатьма аркушами і ви хочете відсортувати їх за алфавітом, цей код макросу може стати в нагоді. Це може статися, якщо у вас є назви аркушів у роках, назви працівників чи назви продуктів.
'Цей код буде сортувати аркуші в алфавітному порядку SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount Якщо Sheets (j) .Name <Sheets (i) .Name then Sheets (j) .Перейти до: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub
Захистіть усі аркуші одним рухом
Якщо у вас багато робочих аркушів у книзі і ви хочете захистити всі аркуші, ви можете скористатися цим кодом макросу.
Він дозволяє вказати пароль у коді. Цей пароль вам знадобиться, щоб зняти захист аркуша.
'Цей код буде захищати всі аркуші одноразово Sub ProtectAllSheets () Dim ws As Worksheet Dim password as String password = "Test123"' ' End Sub
Зняти захист усіх аркушів за один раз
Якщо у вас захищені деякі або всі робочі аркуші, ви можете просто використати невелику зміну коду, який використовується для захисту аркушів, щоб зняти його захист.
'Цей код буде захищати всі аркуші одноразово Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123"' ' End Sub
Зауважте, що пароль має бути таким самим, який використовувався для блокування робочих аркушів. Якщо це не так, ви побачите помилку.
Показати всі рядки та стовпці
Цей код макросу покаже всі приховані рядки та стовпці.
Це може бути дійсно корисним, якщо ви отримаєте файл від когось іншого і хочете переконатися, що немає прихованих рядків/стовпців.
'Цей код буде відображати всі рядки та стовпці у підряднику UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Вилучити всі об’єднані клітинки
Поширеною практикою є об’єднання клітин, щоб вони стали єдиними. Поки це працює, при об’єднанні клітин ви не зможете сортувати дані.
Якщо ви працюєте з робочим аркушем із об’єднаними клітинками, скористайтеся наведеним нижче кодом, щоб за один раз видалити всі об’єднані клітинки.
'Цей код видалить усі об’єднані клітинки Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Зауважте, що замість Злиття та Центру я рекомендую використовувати опцію Центр через вибір.
Збережіть книгу з позначкою часу на її ім’я
Багато часу може знадобитися для створення версій вашої роботи. Вони дуже допомагають у довгих проектах, де з часом ви працюєте з файлом.
Хороша практика - зберегти файл із мітками часу.
Використання позначок часу дозволить вам повернутися до певного файлу, щоб побачити, які зміни були внесені або які дані були використані.
Ось код, який автоматично збереже книгу у зазначеній папці та додасть позначку часу, коли вона буде збережена.
'Цей код збереже файл з позначкою часу на ім'я Sub SaveWorkbookWithTimeStamp () Затемнення мітки часу як мітка часу рядка = Формат (дата, "dd-mm-yyyy") & "_" & Формат (час, "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & мітка часу End Sub
Вам потрібно вказати розташування папки та назву файлу.
У наведеному вище коді “C: UsersUsernameDesktop - це розташування папки, яке я використовував. Потрібно вказати розташування папки, де потрібно зберегти файл. Крім того, я використав загальну назву “WorkbookName” як префікс до назви файлу. Ви можете вказати щось, що стосується вашого проекту чи компанії.
Збережіть кожен аркуш як окремий PDF
Якщо ви працюєте з даними за різні роки чи підрозділи чи продукти, можливо, вам доведеться зберегти різні аркуші як PDF -файли.
Хоча це може зайняти багато часу, якщо це зробити вручну, VBA може дійсно прискорити його.
Ось код VBA, який збереже кожен аркуш як окремий PDF.
'Цей код збереже кожен поклонник як окремий PDF Sub SaveWorkshetAsPDF () Dim ws as Worksheet for each ws in Worksheets ws.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub
У наведеному вище коді я вказав адресу розташування папки, у якій я хочу зберігати файли PDF. Крім того, кожен PDF -файл отримає таку ж назву, що і ім'я робочого аркуша. Вам доведеться змінити це розташування папки (якщо ваше ім’я також не є Sumit, і ви зберігаєте його у тестовій папці на робочому столі).
Зауважте, що цей код працює лише для робочих аркушів (а не для аркушів діаграм).
Збережіть кожен аркуш як окремий PDF
Ось код, який збереже всю вашу книгу як PDF у зазначеній папці.
'Цей код збереже всю книгу як PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Для використання цього коду вам доведеться змінити розташування папки.
Перетворіть усі формули на значення
Використовуйте цей код, якщо у вас є робочий аркуш, який містить багато формул, і ви хочете перетворити ці формули на значення.
'Цей код перетворить усі формули на значення Sub ConvertToValues () з ActiveSheet.UsedRange .Value = .Value End With End Sub
Цей код автоматично визначає використовувані клітинки та перетворює його у значення.
Захист/блокування клітин за допомогою формул
Можливо, вам захочеться заблокувати клітинки за допомогою формул, коли у вас багато обчислень, і ви не хочете випадково видалити його або змінити.
Ось код, який заблокує всі комірки з формулами, тоді як усі інші клітинки не заблоковані.
'Цей код макросу заблокує всі клітинки за допомогою формул Sub LockCellsWithFormulas () з ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True. Захистити AllowDeletingRows: = True End With End Sub
Пов’язаний посібник: Як заблокувати комірки в Excel.
Захистіть усі робочі аркуші в робочому зошиті
Використовуйте наведений нижче код, щоб одночасно захистити всі аркуші у книзі.
'Цей код захищатиме всі аркуші у книзі Sub ProtectAllSheets () Dim ws as Worksheet for each ws in Worksheets ws.Protect Next ws End Sub
Цей код буде проходити по всіх робочих аркушах один за одним і захищатиме його.
Якщо ви хочете зняти захист усіх аркушів, використовуйте ws.Unprotect замість ws.Protect у коді.
Вставте рядок після кожного іншого рядка у виділеному рядку
Використовуйте цей код, якщо потрібно вставити порожній рядок після кожного рядка у вибраному діапазоні.
'Цей код буде вставляти рядок після кожного рядка у виділенні Sub InsertAlternateRows () Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Вставте зміну ActiveCell.Offset (2, 0). Виберіть Далі i Закінчити суб
Так само ви можете змінити цей код, щоб вставити порожній стовпець після кожного стовпця у вибраному діапазоні.
Автоматично вставляти дату та позначку часу в сусідню клітинку
Позначка часу - це те, що ви використовуєте, коли хочете відстежувати дії.
Наприклад, ви можете відстежувати такі дії, як, наприклад, коли були здійснені певні витрати, коли було створено рахунок -фактуру про продаж, коли було здійснено введення даних у клітинку, коли останній раз оновлено звіт тощо.
Використовуйте цей код, щоб вставити дату та час у сусідню клітинку під час внесення запису або редагування наявного вмісту.
'Цей код буде вставляти мітку часу в сусідню клітинку Private Sub Worksheet_Change (ByVal Target As Range) При помилці GoTo Handler If Target.Column = 1 And Target.Value "" Тоді Application.EnableEvents = False Target.Offset (0, 1) = Format (Now (), "dd-mm-yyyy hh: mm: ss") Application.EnableEvents = True End Якщо обробник: End Sub
Зауважте, що вам потрібно вставити цей код у вікно коду робочого аркуша (а не у вікно коду модуля, як ми робили в інших прикладах макросів Excel досі). Для цього в редакторі VB двічі клацніть на назві аркуша, на якому ви хочете цю функціональність. Потім скопіюйте та вставте цей код у вікно коду аркуша.
Крім того, цей код працює, коли введення даних здійснюється у стовпці А (зверніть увагу, що код має рядок Target.Column = 1). Ви можете змінити це відповідно.
Виділіть альтернативні рядки у виділенні
Виділення альтернативних рядків може значно збільшити читабельність ваших даних. Це може бути корисним, коли вам потрібно роздрукувати та переглянути дані.
Ось код, який миттєво виділить альтернативні рядки у виділенні.
'Цей код буде виділяти альтернативні рядки у виділенні Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection for each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Тоді Myrow.Interior.Color = vbCyan Кінець Якщо далі Myrow End Sub
Зауважте, що я вказав колір як vbCyan у коді. Ви також можете вказати інші кольори (наприклад, vbRed, vbGreen, vbBlue).
Виділити клітинки з помилками слів
Excel не має перевірки правопису, як у Word чи PowerPoint. Хоча ви можете запустити перевірку правопису, натиснувши клавішу F7, візуальної підказки при орфографічній помилці немає.
Використовуйте цей код, щоб миттєво виділити всі клітинки, у яких є орфографічна помилка.
'Цей код виділятиме клітинки, які мають помилково написані слова Sub HighlightMisspeppedCells () Dim cl As Range Для кожного cl в ActiveSheet.UsedRange If Not Application.CheckSpelling (word: = cl.Text) Тоді cl.Interior.Color = vbRed End Якщо далі cl Закінчити суб
Зверніть увагу, що виділені клітинки - це ті, які мають текст, який Excel вважає орфографічною помилкою. У багатьох випадках він також виділяє назви або терміни бренду, які йому незрозумілі.
Оновіть усі зведені таблиці у книзі
Якщо у вашій книзі є більше однієї зведеної таблиці, ви можете використовувати цей код для оновлення всіх цих зведених таблиць одночасно.
'Цей код оновить всю зведену таблицю у підручнику підточки RefreshAllPivotTables () Затемнення PT як зведеної таблиці для кожного PT в ActiveSheet.PivotTables PT.
Детальніше про оновлення зведених таблиць можна прочитати тут.
Змініть регістр літер вибраних клітинок на верхній регістр
Хоча Excel має формули для зміни регістру букви тексту, він змушує це робити в іншому наборі клітинок.
Використовуйте цей код, щоб миттєво змінити регістр літер у виділеному тексті.
'Цей код змінить вибір на верхній регістр Sub ChangeCase () Dim Rng як діапазон для кожного Rng у вибірці. Клітинки Якщо Rng.HasFormula = False Тоді Rng.Value = UCase (Rng.Value) End Якщо наступний Rng End Sub
Зауважте, що в цьому випадку я використав UCase, щоб зробити верхній регістр тексту. Ви можете використовувати LCase для нижнього регістру.
Виділіть усі комірки коментарями
Використовуйте код нижче, щоб виділити всі клітинки, у яких є коментарі.
"Цей код виділятиме клітинки з коментарями" Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments).
У цьому випадку я використав vbBlue, щоб надати клітинкам синій колір. Ви можете змінити цей колір на інші кольори, якщо хочете.
Виділіть порожні клітинки за допомогою VBA
Хоча ви можете виділити порожню клітинку з умовним форматуванням або за допомогою діалогового вікна Перейти до спеціального, якщо вам доводиться це робити досить часто, краще скористатися макросом.
Після створення ви можете мати цей макрос на панелі швидкого доступу або зберегти його у власній книзі макросів.
Ось код макросу VBA:
'Цей код виділить усі порожні клітинки в наборі даних Sub HighlightBlankCells () Затемнення набору даних як набору даних діапазону = набору даних вибору.SpecialCells (xlCellTypeBlanks) .Interior.Color = vbRed End Sub
У цьому коді я вказав порожні клітинки для виділення червоним кольором. Ви можете вибрати інші кольори, такі як синій, жовтий, блакитний тощо.
Як сортувати дані по одній колонці
Ви можете скористатися наведеним нижче кодом для сортування даних за вказаним стовпцем.
Sub SortDataHeader () Діапазон ("DataRange"). Ключ сортування1: = Діапазон ("A1"), Порядок1: = xlAscending, Заголовок: = xlТак
Зауважте, що я створив іменований діапазон з назвою "DataRange" і використав його замість посилань на клітинки.
Також тут використовуються три ключові параметри:
- Ключ 1 - це те, за яким потрібно сортувати набір даних. У наведеному вище прикладі коду дані будуть відсортовані на основі значень у стовпці А.
- Порядок- тут потрібно вказати, чи потрібно сортувати дані за зростанням чи спаданням.
- Заголовок - тут потрібно вказати, чи мають ваші дані заголовки чи ні.
Докладніше про те, як сортувати дані в Excel за допомогою VBA.
Як сортувати дані за кількома стовпцями
Припустимо, у вас є набір даних, як показано нижче:
Нижче наведено код, який буде сортувати дані за кількома стовпцями:
Sub SortMultipleColumns () З ActiveSheet.Sort .SortFields.Add Key: = Діапазон ("A1"), Порядок: = xlAscending .SortFields.Add Key: = Діапазон ("B1"), Порядок: = xlAscending .SetRange Діапазон ("A1 : C13 "). Голова = xlТак. Застосуйте End with End Sub
Зауважте, що тут я визначив спочатку сортування на основі стовпця А, а потім на основі стовпця В.
Вихід буде таким, як показано нижче:
Як отримати лише числову частину з рядка в Excel
Якщо ви хочете витягти з рядка лише числову частину або лише текстову частину, ви можете створити власну функцію у VBA.
Потім ви можете використовувати цю функцію VBA на робочому аркуші (так само, як звичайні функції Excel), і вона буде витягувати лише числову або текстову частину з рядка.
Щось, як показано нижче:
Нижче наведено код VBA, який створить функцію для вилучення числової частини з рядка:
'Цей код VBA створить функцію для отримання числової частини з рядка Функція GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1) ) Тоді Результат = Результат & Середина (CellRef, i, 1) Далі i GetNumeric = Функція завершення результату
Вам потрібно розмістити код у модулі, а потім ви можете використовувати функцію = GetNumeric на робочому аркуші.
Ця функція буде приймати лише один аргумент, який є посиланням на клітинку комірки, з якої потрібно отримати числову частину.
Аналогічно, нижче наведена функція, яка отримає лише текстову частину з рядка в Excel:
'Цей код VBA створить функцію для отримання текстової частини зі рядка Функція GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Тоді Результат = Результат і Середина (CellRef, i, 1) Далі i GetText = Функція завершення результату
Отже, це деякі з корисних кодів макросів Excel, які можна використовувати у повсякденній роботі для автоматизації завдань та підвищення продуктивності.
