24 полезни примера за макроси в Excel за начинаещи VBA (готови за използване)

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

Един от начините да получите VBA кода е да запишете макрос и да получите кода, който генерира.Въпреки това, кодът на записващото устройство за макроси често е пълен с код, който всъщност не е необходим.Макро рекордерът също има някои ограничения.

Така че си струва да имате полезен набор от VBA макроси, които можете да държите в задния си джоб и да използвате, когато имате нужда от него.

Въпреки че първоначалното кодиране на макрос на Excel VBA може да отнеме известно време, след като сте готови, можете да го използвате като справка и да го използвате следващия път, когато имате нужда от него.

В тази масивна статия ще изброя някои полезни примери за макрос на Excel, от които често се нуждая и които съхранявам в личния си трезор.

Ще продължа да актуализирам този урок с повече примери за макроси.Ако смятате, че нещо трябва да бъде в списъка, моля, оставете коментар.

Можете да маркирате тази страница за бъдещи справки.

Сега, преди да вляза в примерите за макроси и да ви дам VBA кода, нека първо ви покажа как да използвате примерния код.

Използване на кода от примера за макрос на Excel

Ето стъпките, които трябва да следвате, за да използвате кода от някой от примерите:

• Отворете работната книга, в която искате да използвате макроса.
• Задръжте натиснат клавиш ALT и натиснете F11.Това ще се отвориVB редактор.
• Щракнете с десния бутон върху всеки обект в Project Explorer.
• Отидете на Вмъкване -> Модули.
• Копирайте и поставете кода в прозореца с кода на модула.

Ако примерът казва, че трябва да поставите кода в прозореца с код на работния лист, щракнете двукратно върху обекта на работен лист и копирайте и поставете кода в прозореца с кода.

След като поставите кода в работната книга, трябва да го запишете с разширението .XLSM или .XLS.

Как да стартирате макрос

След копиране на кода в редактора на VB, стъпките за стартиране на макроса са както следва:

• Отидете в раздела за програмисти.
• Щракнете върху Macro.

• В диалоговия прозорец Макроси изберете макроса за изпълнение.
• Щракнете върху бутона Run.

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

Ако кодът е поставен в прозореца с кода на работния лист, не е нужно да се притеснявате за стартиране на кода.Той се изпълнява автоматично, когато настъпи определеното действие.

Сега, нека влезем в полезни примери за макроси, които могат да ви помогнат да автоматизирате работата си и да спестите време.

Забележка: Ще намерите много апострофи ('), последвани от ред или два.Това са коментари, които се игнорират при стартиране на кода и се поставят като коментари за себе си/четец.

Ако откриете грешки в статията или кода, моля, уведомете ме.

Пример за макрос на Excel

Тази статия описва следните примери за макроси:

Покажете всички работни листове наведнъж

Ако работите върху работна книга с множество скрити листа, трябва да покажете тези листове един по един.Това може да отнеме известно време, ако има много скрити листове.

Ето кода за разкриване на всички листове в работната книга.

„Този ​​код ще разкрие всички листове в работната книга Sub UnhideAllWoksheets() Dim ws като работен лист за всеки ws в ActiveWorkbook. Worksheets ws. Visible = xlSheetVisible Следващ ws End Sub

Горният код използва VBA цикъл (For Every), за да премине през всеки работен лист в работната книга.След това променя видимото свойство на листа на видимо.

Ето подробен урок за това как да разкриете работен лист в Excel с помощта на различни методи.

Скриване на всички работни листове с изключение на активния работен лист

Можете да използвате този макрокод, ако работите върху отчет или табло за управление и искате да скриете всички листове с изключение на листа, който има отчета/таблото.

„Този ​​макрос ще скрие целия работен лист с изключение на активния лист Sub HideAllExceptActiveSheet() Dim ws като работен лист за всеки ws в тази работна книга. работни листове Ако ws.name <> ActiveSheet.Name Тогава ws.Visible = xlSheetHidden Следващ ws Край под

Сортирайте работния лист по азбучен ред с помощта на VBA

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

„Този ​​код ще сортира работните листове по азбучен ред Sub 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 If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move before:=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" 'заменете Test123 с паролата, която искате За всяка ws В работни листове ws.Protect password:=password Следваща ws Край под

Премахнете защитата на всички работни листове наведнъж

Ако сте защитили някои или всичките си работни листове, можете да ги премахнете само с лека модификация на кода, използван за защита на работните листове.

'Този код ще защити всички листове наведнъж Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'заменете Test123 с паролата, която искате За всеки ws В работни листове ws.Unprotect password:=парола Следваща ws Край под

Имайте предвид, че паролата трябва да е същата парола, използвана за заключване на листа.Ако не, ще видите грешка.

Показване на всички редове и колони

Този макрокод ще покаже всички скрити редове и колони.

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

„Този ​​код ще покаже всички редове и колони в подложката на работния лист UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

Прекратете обединяването на всички обединени клетки

Обединяването на клетки в една е обичайна практика.Докато работи, няма да можете да сортирате данните, когато клетките се обединят.

Ако използвате работен лист със обединени клетки, използвайте кода по-долу, за да премахнете обединяването на всички обединени клетки наведнъж.

„Този ​​код ще отмени всички обединени клетки Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Обърнете внимание, че препоръчвам да използвате опцията „Център през селекция“ вместо опцията „Обединяване и център“.

Запазете работната книга с времеви печат в името

Има много случаи, когато може да се наложи да създадете работеща версия.Те са чудесни за дългосрочни проекти, които се занимават с файлове с течение на времето.

Добра практика е да запазите файла с времеви печат.

Използването на времеви печат ще ви позволи да се върнете към файл, за да видите какви промени са направени или какви данни са използвани.

Ето кода, който автоматично записва работната книга в посочената папка и добавя времева марка при записване.

„Този ​​код ще запази файла с клеймо за време в името му Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & timestamp End Sub

Трябва да посочите местоположението на папката и името на файла.

В кода по-горе „C:UsersUsernameDesktop е местоположението на папката, което използвам.Трябва да посочите местоположението на папката, където искате да запишете файла.Също така използвах общото име "WorkbookName" като префикс на името на файла.Можете да посочите съдържание, свързано с вашия проект или компания.

Запазете всеки работен лист като отделен PDF

Ако използвате данни от различни години или отдели или продукти, може да се наложи да запазите различни работни листове като PDF файлове.

Въпреки че това може да е отнемащ време процес, ако се прави ръчно, VBA ускорява нещата.

Ето VBA код, който записва всеки работен лист като отделен PDF.

'Този код ще запази всеки работен лист като отделен PDF Sub SaveWorkshetAsPDF() Dim ws като работен лист за всеки ws В работни листове ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Следващ ws Край под

В кода по-горе посочих адреса на местоположението на папката, където искам да запиша 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 .Protect With End AllowDeleting:

Свързан урок: Как да заключите клетки в Excel.

Защитете всички листове в работна книга

Използвайте следния код, за да защитите всички листове в работна книга наведнъж.

„Този ​​код ще защити всички листове в работната книга Sub ProtectAllSheets() Dim ws като работен лист за всеки ws В работни листове ws. Protect Next ws End Sub

Този код ще премине през всички листове един по един и ще ги защити.

Ако искате да премахнете защитата на всички листове, използвайте ws.Unprotect вместо ws.Protect във вашия код.

Вмъкнете ред след всеки ред от селекцията

Използвайте този код, когато искате да вмъкнете празен ред след всеки ред в избрания диапазон.

„Този ​​код ще вмъкне ред след всеки ред в селекцията Sub InsertAlternateRow() 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). Изберете Next i End Sub

Отново можете да промените този код, за да вмъкнете празна колона след всяка колона в избрания диапазон.

Автоматично вмъкване на дата и времеви печат в съседни клетки

Отпечатъците за време са това, което използвате, когато искате да проследявате активността.

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

Използвайте този код, за да вмъкнете печат за дата и час в съседни клетки, когато пишете или редактирате съществуващо съдържание.

'Този код ще вмъкне времева марка в съседната клетка Private Sub Worksheet_Change(ByVal Target As Range) При грешка GoTo Handler Ако Target.Column = 1 и Target.Value <> "" Тогава Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss") Application.EnableEvents = True End If Handler: End Sub

Имайте предвид, че трябва да вмъкнете този код в прозореца с кода на работния лист (не в прозореца с кода на модула, както сме правили в други примери за макроси на Excel досега).За да направите това, в редактора на VB щракнете двукратно върху името на листа, за който искате тази функционалност.След това копирайте и поставете този код в кодовия прозорец на този лист.

Също така този код работи, когато въвеждането на данни се извършва в колона A (обърнете внимание, че кодът има Target.Column = 1 ред).Можете да го промените съответно.

Маркирайте алтернативни редове в селекцията

Открояването на редуващи се редове може значително да подобри четливостта на вашите данни.Това може да бъде полезно, когато трябва да го разпечатате и да прегледате данните.

Ето код, който веднага ще маркира алтернативни редове в селекцията.

'Този код ще подчертае алтернативни редове в селекцията Sub HighlightAlternateRows() Dim Myrow As Range Dim Myrow като Range Set Myrange = Избор за всеки Myrow в Myrange.Rows Ако Myrow.Row Mod 2 = 1 Тогава Myrow.Interior.Color = vbCyan End Ако Next Myrow End Sub

Имайте предвид, че посочих цвета като vbCyan в кода.Можете също да посочите други цветове (напр. vbRed, vbGreen, vbBlue).

Маркирайте грешно написаните клетки

Excel няма проверка на правописа в Word или PowerPoint.Въпреки че можете да стартирате проверка на правописа, като натиснете F7, няма визуална подсказка за правописни грешки.

Използвайте този код, за да маркирате незабавно всички грешно написани клетки.

„Този ​​код ще подчертае клетките, които имат грешно изписани думи Sub HighlightMisspelledCells() Dim cl Като диапазон за всеки cl В ActiveSheet.UsedRange Ако не Application.CheckSpelling(word:=cl.Text) Тогава cl.Interior.Color = vbRed End Ако следва cl Край Под

Обърнете внимание, че маркираните клетки са клетките с текст, който Excel смята, че е изписан неправилно.В много случаи той също така подчертава имена или термини на марката, които не разбира.

Опреснете всички централни таблици в работна книга

Ако имате няколко централни таблици в работната книга, можете да използвате този код, за да обновите всички наведнъж.

„Този ​​код ще опресни цялата обобщена таблица в подразделението на работната книга RefreshAllPivotTables() Dim PT като обобщена таблица за всеки PT в ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub

Можете да прочетете повече за опресняването на централните таблици тук.

Променете главните букви на избраните клетки на главни

Докато Excel има формули за промяна на главния и главния шрифт на текстовите букви, той ви позволява да правите това в друг набор от клетки.

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

'Този код ще промени селекцията на Upper Case Sub ChangeCase() Dim Rng като диапазон за всеки Rng в Selection.Cells Ако Rng.HasFormula = False Тогава Rng.Value = UCase(Rng.Value) Край, ако следващото Rng End Sub

Обърнете внимание, че в този случай използвам UCase, за да задам главния регистър на текста.Можете да използвате LCase за малки букви.

Маркирайте всички клетки с коментари

Използвайте следния код, за да маркирате всички клетки с коментари в тях.

„Този ​​код ще маркира клетки, които имат коментари“ Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

В този случай използвам vbBlue, за да дам на клетката син цвят.Можете да го промените в друг цвят, ако е необходимо.

Маркирайте празните клетки с VBA

Въпреки че можете да използвате условно форматиране или да използвате диалоговия прозорец Go To Special, за да маркирате празни клетки, ако трябва да правите това често, най-добре е да използвате макрос.

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

Ето кода на VBA макроса:

„Този ​​код ще маркира всички празни клетки в набора от данни Sub HighlightBlankCells() Dim Dataset като набор от данни за диапазон = Набор от данни за избор.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

В този код посочих празни клетки, подчертани в червено.Можете да изберете други цветове като синьо, жълто, циан и др.

Как да сортирате данни по една колона

Можете да използвате следния код, за да сортирате данни по определена колона.

Sub SortDataHeader() 
Диапазон("DataRange"). Ключ за сортиране1:=Обхват("A1"), Order1:=xlAscending, Header:=xlYes 
End Sub

Имайте предвид, че създадох именуван диапазон, наречен "DataRange" и го използвах вместо препратката към клетката.

Тук също се използват три ключови параметъра:

• Key1 – Това е ключът, по който искате да сортирате набора от данни.В примерния код по-горе данните ще бъдат сортирани въз основа на стойностите в колона A.
• Order1 – Тук трябва да посочите дали искате да сортирате данните във възходящ или низходящ ред.
• Заглавка – Тук трябва да посочите дали вашите данни имат заглавка или не.

Прочетете повече за това как да сортирате данни в Excel с помощта на VBA.

Как да сортирате данни по множество колони

Да предположим, че имате набор от данни, който изглежда така:

Ето кода за сортиране на данни въз основа на множество колони:

Sub SortMultipleColumns() С ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1" :C13") .Header = xlYes .Прилагане 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) ) След това Резултат = Резултат & Mid(CellRef, i, 1) Следващ i GetNumeric = Крайна функция на резултата

Трябва да поставите кода в модул и след това можете да използвате функцията =GetNumeric в работния лист.

Тази функция ще приеме само един параметър, препратката към клетката, от която да получи числовата част.

По същия начин функцията по-долу ще получи само текстовата част от низ в Excel:

„Този ​​VBA код ще създаде функция за получаване на текстовата част от низ. Функция GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength Ако не (IsNumeric(Mid(CellRef, i, 1))) След това Резултат = Резултат и средата (CellRef, i, 1) След това i GetText = Крайна функция на резултата

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