Използването на Excel Macros може да ускори работата и да ви спести много време.
Един от начините за получаване на VBA кода е да запишете макроса и да вземете генерирания от него код. Този код от макрорекордера обаче често е пълен с код, който всъщност не е необходим. Също така макрорекордерът има някои ограничения.
Така че си струва да имате колекция от полезни VBA макро кодове, които да имате в задния си джоб и да ги използвате, когато е необходимо.
Докато писането на макро код на Excel VBA първоначално може да отнеме известно време, след като приключи, можете да го запазите като референтен и да го използвате винаги, когато имате нужда от него след това.
В тази обширна статия ще изброя някои полезни примери за макроси в Excel, които ми трябват често и ще бъдат скрити в личния ми сейф.
Ще продължавам да актуализирам този урок с още примери за макроси. Ако смятате, че нещо трябва да е в списъка, просто оставете коментар.
Можете да маркирате тази страница за бъдещи справки.
Сега, преди да вляза в примера за макрос и да ви дам VBA кода, нека първо ви покажа как да използвате тези примерни кодове.
Използване на кода от примери за макроси на Excel
Ето стъпките, които трябва да следвате, за да използвате кода от всеки от примерите:
- Отворете работната книга, в която искате да използвате макроса.
- Задръжте клавиша ALT и натиснете F11. Това отваря VB Editor.
- Щракнете с десния бутон върху някой от обектите в изследователя на проекта.
- Отидете на Вмъкване -> Модул.
- Копирайте и поставете кода в прозореца за код на модула.
В случай, че примерът казва, че трябва да поставите кода в прозореца с код на работния лист, щракнете двукратно върху обекта на работния лист и копирайте поставете кода в кодовия прозорец.
След като сте вмъкнали кода в работна книга, трябва да го запишете с разширение .XLSM или .XLS.
Как да стартирате макроса
След като сте копирали кода във VB Editor, ето стъпките за стартиране на макроса:
- Отидете в раздела Разработчик.
- Щракнете върху Макроси.
- В диалоговия прозорец Макрос изберете макроса, който искате да изпълните.
- Щракнете върху бутона Run.
В случай, че не можете да намерите раздела за програмисти в лентата, прочетете този урок, за да научите как да го получите.
Свързан урок: Различни начини за стартиране на макрос в Excel.
В случай, че кодът е поставен в прозореца с код на работния лист, не е нужно да се притеснявате за стартирането на кода. Той ще се стартира автоматично, когато се случи посоченото действие.
Сега нека да разгледаме полезните примери за макроси, които могат да ви помогнат да автоматизирате работата и да спестите време.
Забележка: Ще намерите много случаи на апостроф (‘), последван от ред или два. Това са коментари, които се игнорират по време на изпълнението на кода и се поставят като бележки за себе си/четец.
В случай, че откриете някаква грешка в статията или кода, моля бъдете страхотни и ме уведомете.
Примери за макроси на Excel
По -долу в тази статия са описани макропримери:
Покажете всички работни листове с едно движение
Ако работите в работна книга, която има няколко скрити листа, трябва да разкриете тези листове един по един. Това може да отнеме известно време, в случай че има много скрити листове.
Ето кода, който ще покаже всички работни листове в работната книга.
„Този код ще покаже всички листове в работната книга Sub UnhideAllWoksheets () Dim ws като работен лист за всеки ws в ActiveWorkbook. Работни листове ws.Visible = xlSheetVisible Следващ ws End Sub
Горният код използва цикъл VBA (за всеки), за да премине през всеки работен лист в работната книга. След това променя видимото свойство на работния лист на видимо.
Ето подробен урок за това как да използвате различни методи за разкриване на листове в Excel.
Скриване на всички работни листове с изключение на активния лист
Ако работите върху отчет или табло за управление и искате да скриете целия работен лист, с изключение на този, който има отчета/таблото за управление, можете да използвате този макро код.
„Този макрос ще скрие целия работен лист, с изключение на активния лист Sub HideAllExceptActiveSheet () Dim ws като работен лист за всеки ws в ThisWorkbook.Worksheets Ако 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 Ако 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 "'заменете Test123 с паролата, която искате За всеки ws В работни листове ws.Парола за защита: = парола Следваща ws End Sub
Премахнете защитата на всички работни листове наведнъж
Ако имате защитени някои или всички работни листове, можете просто да използвате лека модификация на кода, използван за защита на листове, за да го премахнете.
„Този код ще защити всички листове наведнъж Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password =" Test123 "'заменете Test123 с паролата, която искате За всеки ws В работни листове ws.Unprotect парола: = парола Следваща ws End Sub
Имайте предвид, че паролата трябва да е същата, която е била използвана за заключване на работните листове. Ако не е, ще видите грешка.
Показване на всички редове и колони
Този макро код ще покаже всички скрити редове и колони.
Това би могло да бъде наистина полезно, ако получите файл от някой друг и искате да сте сигурни, че няма скрити редове/колони.
„Този код ще покаже всички редове и колони в под -листа UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Премахване на всички обединени клетки
Обичайна практика е да се обединяват клетки, за да се направят една. Въпреки че върши работата, когато клетките се обединят, няма да можете да сортирате данните.
В случай, че работите с работен лист със слети клетки, използвайте кода по -долу, за да обедините всички обединени клетки наведнъж.
„Този код ще обедини всички обединени клетки Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Обърнете внимание, че вместо Обединяване и Център, препоръчвам да използвате опцията Център чрез избор.
Запазете работната книга с TimeStamp в нейното име
Много време може да се наложи да създадете версии на работата си. Те са доста полезни при дълги проекти, където работите с файл с течение на времето.
Добра практика е да запишете файла с времеви марки.
Използването на часови марки ще ви позволи да се върнете към определен файл, за да видите какви промени са направени или какви данни са използвани.
Ето кода, който автоматично ще запише работната книга в посочената папка и ще добави времева отметка, когато тя бъде запазена.
„Този код ще запише файла с времева отметка в името си Sub SaveWorkbookWithTimeStamp () Затъмнете времевата марка като String timestamp = Format (Date," dd-mm-yyyy ") &" _ "& Format (Time," hh-ss ") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & timetamp 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 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 () Затъмняване на w като работен лист за всеки ws в работни листове 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). Изберете Next i End Sub
По същия начин можете да промените този код, за да вмъкнете празна колона след всяка колона в избрания диапазон.
Автоматично вмъкване на дата и времева отметка в съседната клетка
Времевата марка е нещо, което използвате, когато искате да проследявате дейности.
Например, може да искате да проследявате дейности, като например кога е бил направен определен разход, през колко време е била създадена фактурата за продажба, кога е било въведено въвеждането на данни в клетка, кога отчетът е актуализиран за последно и т.н.
Използвайте този код, за да вмъкнете дата и час в съседната клетка, когато се прави запис или се редактира съществуващото съдържание.
„Този код ще вмъкне времева отметка в съседната клетка Private Sub Worksheet_Change (ByVal Target As Range) On Error 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 If Handler: End Sub
Имайте предвид, че трябва да вмъкнете този код в прозореца с код на работния лист (а не в прозореца с код на модула, както направихме в други примери за макроси на Excel досега). За да направите това, в VB Editor щракнете двукратно върху името на листа, на който искате тази функционалност. След това копирайте и поставете този код в кодовия прозорец на този лист.
Също така, този код работи, когато въвеждането на данни се извършва в колона А (имайте предвид, че кодът има реда Target.Column = 1). Можете съответно да промените това.
Маркирайте алтернативни редове в селекцията
Открояването на алтернативни редове може значително да увеличи четливостта на вашите данни. Това може да бъде полезно, когато трябва да отпечатате и прегледате данните.
Ето код, който незабавно ще подчертае алтернативни редове в селекцията.
"Този код ще подчертае алтернативни редове в селекцията Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Избор за всеки Myrow в Myrange.Rows Ако Myrow.Row Mod 2 = 1 Тогава Myrow.Interior.Color = vbCyan Край If Next Myrow End Sub
Обърнете внимание, че съм посочил цвета като vbCyan в кода. Можете да посочите и други цветове (като vbRed, vbGreen, vbBlue).
Маркирайте клетки с грешно написани думи
Excel няма проверка на правописа, както в Word или PowerPoint. Докато можете да стартирате проверката на правописа, като натиснете клавиша F7, няма визуален знак, когато има правописна грешка.
Използвайте този код, за да подчертаете незабавно всички клетки, които имат правописна грешка в него.
„Този код ще подчертае клетките, които имат грешно написани думи Sub HighlightMisspelledCells () Dim cl As Range За всеки cl В ActiveSheet.UsedRange Ако не е Application.CheckSpelling (word: = cl.Text) Тогава cl.Interior.Color = vbRed End Ако Next cl End Sub
Имайте предвид, че маркираните клетки са тези, които имат текст, който Excel счита за правописна грешка. В много случаи той също така подчертава имена или термини на марката, които не разбира.
Опреснете всички обобщени таблици в работната книга
Ако имате повече от една обобщена таблица в работната книга, можете да използвате този код, за да опресните всички тези обобщени таблици наведнъж.
„Този код ще опресни цялата обобщена таблица в подречника на работната книга RefreshAllPivotTables () Затъмняване на PT като обобщена таблица за всеки PT в ActiveSheet. PivoTables PT.
Можете да прочетете повече за опресняването на обобщените таблици тук.
Променете регистъра на буквите на избраните клетки на големи букви
Докато Excel има формулите за промяна на буквите в текста, той ви кара да направите това в друг набор от клетки.
Използвайте този код, за да промените незабавно буквите на текста в избрания текст.
"Този код ще промени селекцията на Горна буква Sub ChangeCase () Dim Rng като диапазон за всеки Rng в селекцията. Клетки Ако Rng.HasFormula = False Тогава Rng.Value = UCase (Rng.Value) Край Ако следващата Rng End Sub
Обърнете внимание, че в този случай съм използвал UCase, за да направя текстовия регистър горен. Можете да използвате LCase за малки букви.
Маркирайте всички клетки с коментари
Използвайте кода по -долу, за да маркирате всички клетки, в които има коментари.
„Този код ще подчертае клетки с коментари“ Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments).
В този случай използвах vbBlue, за да дам син цвят на клетките. Можете да промените това в други цветове, ако желаете.
Маркирайте празни клетки с VBA
Въпреки че можете да маркирате празна клетка с условно форматиране или да използвате диалоговия прозорец Отиди на Специално, ако трябва да го правите доста често, по -добре е да използвате макрос.
След като бъде създаден, можете да имате този макрос в лентата с инструменти за бърз достъп или да го запишете в личната си работна книга за макроси.
Ето кода на макроса VBA:
„Този код ще подчертае всички празни клетки в набора от данни Sub HighlightBlankCells () Dim Dataset като Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks) .Interior.Color = vbRed End Sub
В този код съм посочил празните клетки, които да бъдат подчертани в червен цвят. Можете да изберете други цветове като синьо, жълто, циан и т.н.
Как да сортирате данни по една колона
Можете да използвате кода по -долу, за да сортирате данните по посочената колона.
Sub SortDataHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlY End End Sub
Обърнете внимание, че създадох именован диапазон с името „DataRange“ и го използвах вместо препратките към клетки.
Тук също се използват три ключови параметъра:
- Key1 - Това е мястото, по което искате да сортирате набора от данни. В горния примерен код данните ще бъдат сортирани въз основа на стойностите в колона А.
- Ред- Тук трябва да посочите дали искате да сортирате данните във възходящ или низходящ ред.
- Заглавка - Тук трябва да посочите дали вашите данни имат заглавки или не.
Прочетете повече за това как да сортирате данни в Excel с помощта на VBA.
Как да сортирате данни по няколко колони
Да предположим, че имате набор от данни, както е показано по -долу:
По -долу е кодът, който ще сортира данните въз основа на множество колони:
Sub SortMultipleColumns () С ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("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) ) Тогава Result = Result & 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 If Not (IsNumeric (Mid (CellRef, i, 1))) Тогава Result = Result & Mid (CellRef, i, 1) Next i GetText = Функция за край на резултата
Така че това са някои от полезните макро кодове на Excel, които можете да използвате в ежедневната си работа, за да автоматизирате задачите и да бъдете много по-продуктивни.
