Izmantojot Excel makro, varat paātrināt darbu un ietaupīt daudz laika.
Viens veids, kā iegūt VBA kodu, ir ierakstīt makro un paņemt tā ģenerēto kodu. Tomēr šis makro ierakstītāja kods bieži ir pilns ar kodu, kas patiesībā nav vajadzīgs. Arī makro ierakstītājam ir daži ierobežojumi.
Tāpēc ir vērts iegūt noderīgu VBA makro kodu kolekciju, kas var būt jūsu aizmugurējā kabatā, un izmantot to, kad nepieciešams.
Lai gan Excel VBA makro koda rakstīšana sākotnēji var aizņemt kādu laiku, kad tas ir izdarīts, varat paturēt to pieejamu kā atsauci un izmantot to ikreiz, kad tas ir nepieciešams.
Šajā milzīgajā rakstā es uzskaitīšu dažus noderīgus Excel makro piemērus, kas man bieži nepieciešami, un glabāšu tos savā privātajā glabātuvē.
Es turpināšu atjaunināt šo apmācību ar vairākiem makro piemēriem. Ja jūs domājat, ka kaut kam vajadzētu būt sarakstā, vienkārši atstājiet komentāru.
Šo lapu varat atzīmēt kā grāmatzīmi turpmākai izmantošanai.
Pirms es nokļuvu makro piemērā un sniedzu jums VBA kodu, ļaujiet man vispirms parādīt, kā izmantot šos kodu piemērus.
Izmantojot kodu no Excel makro piemēriem
Tālāk ir norādītas darbības, kas jāveic, lai izmantotu kodu no jebkura piemēra.
- Atveriet darbgrāmatu, kurā vēlaties izmantot makro.
- Turiet taustiņu ALT un nospiediet F11. Tas atver VB redaktoru.
- Ar peles labo pogu noklikšķiniet uz jebkura projekta izpētes objekta.
- Dodieties uz Ievietot -> Modulis.
- Kopējiet un ielīmējiet kodu moduļa koda logā.
Ja piemērā teikts, ka kods ir jāielīmē darblapas koda logā, veiciet dubultklikšķi uz darblapas objekta un nokopējiet kodu koda logā.
Kad esat ievietojis kodu darbgrāmatā, tas jāsaglabā ar paplašinājumu .XLSM vai .XLS.
Kā palaist makro
Kad esat nokopējis kodu VB redaktorā, rīkojieties šādi, lai palaistu makro:
- Dodieties uz cilni Izstrādātājs.
- Noklikšķiniet uz Makro.
- Dialoglodziņā Makro atlasiet makro, kuru vēlaties palaist.
- Noklikšķiniet uz pogas Palaist.
Ja lentē nevarat atrast izstrādātāja cilni, izlasiet šo pamācību, lai uzzinātu, kā to iegūt.
Saistītā apmācība: dažādi makro palaišanas veidi programmā Excel.
Ja kods ir ielīmēts darblapas koda logā, jums nav jāuztraucas par koda palaišanu. Tas tiks automātiski palaists, kad notiks norādītā darbība.
Tagad apskatīsim noderīgus makro piemērus, kas var palīdzēt automatizēt darbu un ietaupīt laiku.
Piezīme. Jūs atradīsit daudzus apostrofa (“) gadījumus, kam seko rindiņa vai divas. Šie ir komentāri, kas tiek ignorēti, izpildot kodu, un tiek ievietoti kā piezīmes pašam/lasītājam.
Ja rakstā vai kodā atrodat kādu kļūdu, lūdzu, esiet satriecošs un informējiet mani.
Excel makro piemēri
Šajā rakstā ir apskatīti makro piemēri:
Vienā reizē parādīt visas darblapas
Ja strādājat darbgrāmatā, kurā ir vairākas slēptās lapas, šīs lapas jāslēpj pa vienai. Tas var aizņemt kādu laiku, ja ir daudz slēptu lapu.
Šeit ir kods, kas parādīs visas darbgrāmatas darblapas.
'Šis kods parādīs visas darbgrāmatas lapas Sub UnhideAllWoksheets () Dim ws kā darblapu katrai ww ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
Iepriekš minētais kods izmanto VBA cilpu (katram), lai izietu cauri visām darbgrāmatas darblapām. Pēc tam darblapas redzamais rekvizīts tiek mainīts uz redzamu.
Šeit ir detalizēta apmācība par to, kā izmantot dažādas metodes, lai parādītu lapas Excel.
Slēpt visas darblapas, izņemot aktīvo lapu
Ja strādājat pie pārskata vai informācijas paneļa un vēlaties paslēpt visu darblapu, izņemot to, kurā ir pārskats/informācijas panelis, varat izmantot šo makro kodu.
"Šis makro paslēps visu darblapu, izņemot aktīvo lapu Sub HideAllExceptActiveSheet () Dim ws kā darblapu katram ws šajā darbagrāmatā.
Darblapu kārtošana alfabētiskā secībā, izmantojot VBA
Ja jums ir darbgrāmata ar daudzām darblapām un vēlaties tās kārtot alfabētiskā secībā, šis makro kods var noderēt. Tas varētu notikt, ja jums ir lapu nosaukumi kā gadi vai darbinieku vārdi vai produktu nosaukumi.
'Šis kods sakārtos darblapas alfabētiskā secībā 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 Ja Izklājlapas (j). Nosaukums <Izklājlapas (i). Nosaukums Pēc tam Izklājlapas (j). Pārvietot pirms: = Izklājlapas (i) Beigt Ja Nākamā j Nākamā i Lietojumprogramma.
Aizsargājiet visas darblapas vienā reizē
Ja darbgrāmatā ir daudz darblapu un vēlaties aizsargāt visas lapas, varat izmantot šo makro kodu.
Tas ļauj kodā norādīt paroli. Šī parole būs nepieciešama, lai noņemtu darblapas aizsardzību.
'Šis kods vienlaikus aizsargās visas lapas. Beigu apakš
Vienā reizē noņemiet visu darblapu aizsardzību
Ja dažas vai visas darblapas ir aizsargātas, varat vienkārši izmantot nelielas izmaiņas koda izmantošanā, lai aizsargātu lapas, lai to aizsargātu.
'Šis kods vienlaikus aizsargās visas lapas Sub ProtectAllSheets () Dim ws kā darblapa Dim parole kā virknes parole = "Test123" "aizstājiet Test123 ar vajadzīgo paroli. Katram ws darblapās ws. Aizsargāt paroli: = parole Nākamā ws Beigu apakš
Ņemiet vērā, ka parolei ir jābūt tādai pašai, kāda tika izmantota, lai bloķētu darblapas. Ja tā nav, jūs redzēsit kļūdu.
Rādīt visas rindas un slejas
Šis makro kods parādīs visas slēptās rindas un kolonnas.
Tas varētu būt patiešām noderīgi, ja saņemat failu no kāda cita un vēlaties būt pārliecināts, ka tajā nav slēptu rindu/kolonnu.
"Šis kods parādīs visas rindas un kolonnas darblapā Sub UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Apvienot visas sapludinātās šūnas
Šūnu apvienošana, lai to izveidotu, ir izplatīta prakse. Kamēr tas darbojas, kad šūnas tiek apvienotas, jūs nevarēsit kārtot datus.
Ja strādājat ar darblapu ar sapludinātām šūnām, izmantojiet tālāk norādīto kodu, lai vienā reizē atvienotu visas sapludinātās šūnas.
"Šis kods apvienos visas sapludinātās šūnas Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Ņemiet vērā, ka apvienošanas un centra vietā es iesaku izmantot opciju Centrālā atlase.
Saglabājiet darbgrāmatu ar tās laika zīmogu
Daudz laika, iespējams, būs jāizveido sava darba versijas. Tie ir diezgan noderīgi garos projektos, kuros laika gaitā strādājat ar failu.
Laba prakse ir saglabāt failu ar laika zīmogiem.
Izmantojot laika zīmogus, varēsit atgriezties pie noteikta faila, lai redzētu, kādas izmaiņas tika veiktas vai kādi dati tika izmantoti.
Šeit ir kods, kas automātiski saglabās darbgrāmatu norādītajā mapē un ikreiz, kad tā tiks saglabāta, pievienos laika zīmogu.
'Šis kods saglabās failu ar tā laika zīmogu Sub SaveWorkbookWithTimeStamp () Dim timestamp As String timestamp = Format (Date, "dd-mm-yyyy") & "_" & Format (Time, "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & timestamp End Sub
Jums jānorāda mapes atrašanās vieta un faila nosaukums.
Iepriekš minētajā kodā “C: UsersUsernameDesktop ir manis izmantotā mapes atrašanās vieta. Jums jānorāda mapes atrašanās vieta, kur vēlaties saglabāt failu. Tāpat kā faila nosaukuma prefiksu esmu izmantojis vispārēju nosaukumu “WorkbookName”. Jūs varat norādīt kaut ko, kas saistīts ar jūsu projektu vai uzņēmumu.
Saglabājiet katru darblapu kā atsevišķu PDF failu
Ja strādājat ar dažādu gadu datiem, nodaļām vai produktiem, iespējams, vajadzēs saglabāt dažādas darblapas kā PDF failus.
Lai gan tas varētu būt laikietilpīgs process, ja tas tiek veikts manuāli, VBA to patiešām var paātrināt.
Šeit ir VBA kods, kas katru darblapu saglabās kā atsevišķu PDF failu.
'Šis kods katru uzdevumu saglabās kā atsevišķu PDF apakšfailu SaveWorkshetAsPDF () Dim ws kā darblapa katram ws darblapās ws.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub
Iepriekš minētajā kodā esmu norādījis mapes atrašanās vietas adresi, kurā vēlos saglabāt PDF failus. Turklāt katram PDF failam būs tāds pats nosaukums kā darblapai. Jums būs jāmaina šīs mapes atrašanās vieta (ja vien jūsu vārds nav Sumit un jūs to saglabājat darbvirsmas testa mapē).
Ņemiet vērā, ka šis kods darbojas tikai darblapām (nevis diagrammu lapām).
Saglabājiet katru darblapu kā atsevišķu PDF failu
Šeit ir kods, kas norādītajā mapē saglabās visu darbgrāmatu kā PDF failu.
'Šis kods visu darbgrāmatu saglabās kā PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Lai izmantotu šo kodu, jums būs jāmaina mapes atrašanās vieta.
Pārvērst visas formulas vērtībās
Izmantojiet šo kodu, ja jums ir darblapa, kurā ir daudz formulu un vēlaties pārvērst šīs formulas par vērtībām.
"Šis kods visas formulas pārvērtīs par vērtībām Sub ConvertToValues () ar ActiveSheet.UsedRange .Value = .Value End With End Sub
Šis kods automātiski identificē izmantotās šūnas un pārvērš tās vērtībās.
Aizsargājiet/bloķējiet šūnas ar formulām
Iespējams, vēlēsities bloķēt šūnas ar formulām, ja jums ir daudz aprēķinu un nevēlaties to nejauši izdzēst vai mainīt.
Šeit ir kods, kas bloķēs visas šūnas, kurām ir formulas, bet visas pārējās šūnas nav bloķētas.
'Šis makro kods bloķēs visas šūnas ar formulām Sub LockCellsWithFormulas () Ar ActiveSheet .Aizsargāt .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True .Protect AllowDelatingRows: = Patiess beigas ar beigu apakšdaļu
Saistītā apmācība: Kā bloķēt šūnas programmā Excel.
Aizsargājiet visas darbgrāmatas darblapas
Izmantojiet zemāk esošo kodu, lai vienā reizē aizsargātu visas darbgrāmatas darblapas.
"Šis kods aizsargās visas lapas darbgrāmatā Sub ProtectAllSheets () Dim ws kā darblapu katram ws darblapā ws. Aizsargāt nākamo ws End Sub
Šis kods pa vienam izies visas darblapas un aizsargās to.
Ja vēlaties atcelt visu darblapu aizsardzību, izmantojiet ws. Neaizsargāt, nevis ws. Aizsargāt kodu.
Ievietojiet rindu pēc katras citas atlases rindas
Izmantojiet šo kodu, ja vēlaties ievietot tukšu rindu pēc katras atlasītā diapazona rindas.
'Šis kods ievietos rindu pēc katras atlases rindas Sub InsertAlternateRows () Dim rng kā diapazons Dim CountRow kā vesels skaitlis Dim i kā vesels skaitlis Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Ievietojiet ActiveCell.Offset (2, 0). Atlasiet Next i End Sub
Tāpat jūs varat modificēt šo kodu, lai ievietotu tukšu kolonnu pēc katras atlasītā diapazona kolonnas.
Automātiski ievietojiet datumu un laika zīmogu blakus esošajā šūnā
Laika zīmogs ir tas, ko izmantojat, kad vēlaties izsekot darbībām.
Piemēram, iespējams, vēlēsities izsekot tādas darbības kā, piemēram, kad radās konkrēti izdevumi, cikos tika izveidots pārdošanas rēķins, kad tika ievadīta šūna, kad pēdējo reizi tika atjaunināts pārskats utt.
Izmantojiet šo kodu, lai ievietotu datumu un laika zīmogu blakus esošajā šūnā, kad tiek veikts ieraksts vai rediģēts esošais saturs.
'Šis kods ievietos laika zīmogu blakus esošajā šūnā Privātā apakšdarblapa_Mainīt (ByVal mērķis kā diapazons) uz kļūdas GoTo apstrādātājs If Target.Column = 1 And Target.Value "" Then Application.EnableEvents = False Target.Offset (0, 1) = Formāts (tagad (), "dd-mm-gggg hh: mm: ss") Application.EnableEvents = Patiesas beigas, ja apstrādātājs: beigas
Ņemiet vērā, ka šis kods ir jāievieto darblapas koda logā (nevis moduļa koda logā, kā mēs līdz šim esam darījuši citos Excel makro piemēros). Lai to izdarītu, VB redaktorā veiciet dubultklikšķi uz lapas nosaukuma, kurai vēlaties izmantot šo funkcionalitāti. Pēc tam nokopējiet un ielīmējiet šo kodu šīs lapas koda logā.
Šis kods darbojas arī tad, ja datu ievadīšana ir veikta A slejā (ņemiet vērā, ka kodam ir rinda Target.Column = 1). Jūs varat to attiecīgi mainīt.
Atlasē iezīmējiet alternatīvās rindas
Alternatīvu rindu izcelšana var ievērojami uzlabot jūsu datu lasāmību. Tas var būt noderīgi, ja jums ir jāizņem izdruka un jāizpēta dati.
Šeit ir kods, kas atlasē uzreiz izceļ alternatīvas rindas.
"Šis kods atlasē atlasītu alternatīvas rindas Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Atlase katrai Myrow In Myrange. Rindas If Myrow.Row Mod 2 = 1 Pēc tam Myrow.Interior.Color = vbCyan End Ja nākamais Myrow End Sub
Ņemiet vērā, ka kodā esmu norādījis krāsu kā vbCyan. Varat norādīt arī citas krāsas (piemēram, vbRed, vbGreen, vbBlue).
Iezīmējiet šūnas ar nepareizi uzrakstītiem vārdiem
Programmai Excel nav pareizrakstības pārbaudes, kā tas ir Word vai PowerPoint. Lai gan pareizrakstības pārbaudi var veikt, nospiežot taustiņu F7, pareizrakstības kļūdas gadījumā nav redzamas norādes.
Izmantojiet šo kodu, lai uzreiz iezīmētu visas šūnas, kurās ir pareizrakstības kļūda.
"Šis kods iezīmēs šūnas, kurās ir kļūdaini uzrakstīti vārdi Sub HighlightMisspelledCells () Dim cl As Range for each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling (word: = cl.Text) Tad cl.Interior.Color = vbRed End If Next cl Beigt apakš
Ņemiet vērā, ka izceltās šūnas ir tās, kurās ir teksts, ko Excel uzskata par pareizrakstības kļūdu. Daudzos gadījumos tas izceltu arī vārdus vai zīmola terminus, kurus tā nesaprot.
Atsvaidziniet visas darbgrāmatas rakurstabulas
Ja darbgrāmatā ir vairākas rakurstabulas, varat izmantot šo kodu, lai atsvaidzinātu visas šīs rakurstabulas vienlaikus.
"Šis kods atsvaidzinās visu rakurstabulu darbgrāmatā Sub RefreshAllPivotTables () Dim PT kā rakurstabulu katrai PT ActiveSheet. PivotTables PT. RefreshTable Next PT End
Vairāk par rakurstabulu atsvaidzināšanu varat lasīt šeit.
Mainiet atlasīto šūnu burtu reģistru uz lielajiem burtiem
Lai gan programmā Excel ir formulas, lai mainītu teksta burtu reģistru, tas liek to darīt citā šūnu kopā.
Izmantojiet šo kodu, lai uzreiz mainītu atlasītā teksta teksta burtu reģistru.
"Šis kods mainīs atlasi uz lieliem burtiem Sub ChangeCase () Dim Rng kā diapazons katrai atlasītajai rindai.
Ņemiet vērā, ka šajā gadījumā es esmu izmantojis UCase, lai padarītu teksta reģistru augšējo. Jūs varat izmantot LCase mazajiem burtiem.
Iezīmējiet visas šūnas ar komentāriem
Izmantojiet zemāk esošo kodu, lai iezīmētu visas šūnas, kurās ir komentāri.
"Šis kods iezīmēs šūnas, kurām ir komentāri" Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub
Šajā gadījumā es izmantoju vbBlue, lai šūnām piešķirtu zilu krāsu. Ja vēlaties, varat to mainīt uz citām krāsām.
Iezīmējiet tukšās šūnas ar VBA
Lai gan jūs varat izcelt tukšu šūnu ar nosacītu formatējumu vai izmantojot dialoglodziņu Pāriet uz īpašo, ja tas jādara diezgan bieži, labāk ir izmantot makro.
Kad esat izveidojis šo makro, varat to atrast ātrās piekļuves rīkjoslā vai saglabāt savā personīgajā makro darbgrāmatā.
Šeit ir VBA makro kods:
"Šis kods iezīmēs visas tukšās šūnas datu kopā Sub HighlightBlankCells () Dim datu kopa kā diapazona kopa Dataset = Atlases datu kopa. SpecialCells (xlCellTypeBlanks). Interior.Color = vbRed End Sub Sub
Šajā kodā esmu norādījis tukšās šūnas, kas jāizceļ sarkanā krāsā. Jūs varat izvēlēties citas krāsas, piemēram, zilu, dzeltenu, ciānu utt.
Kā kārtot datus pēc vienas kolonnas
Lai kārtotu datus pēc norādītās slejas, varat izmantot zemāk esošo kodu.
Sub SortDataHeader () Diapazons ("DataRange"). Kārtošanas atslēga1: = Diapazons ("A1"), secība1: = xlPieaugoši, galvene: = xl Jā Beigu apakšdaļa
Ņemiet vērā, ka esmu izveidojis nosauktu diapazonu ar nosaukumu “DataRange” un izmantojis to šūnu atsauču vietā.
Šeit tiek izmantoti arī trīs galvenie parametri:
- Key1 - Šī ir tā, pēc kuras vēlaties kārtot datu kopu. Iepriekš minētajā koda piemērā dati tiks sakārtoti, pamatojoties uz A slejas vērtībām.
- Pasūtījums- šeit jānorāda, vai vēlaties kārtot datus augošā vai dilstošā secībā.
- Galvene - šeit jānorāda, vai jūsu datiem ir galvenes vai nav.
Lasiet vairāk par to, kā kārtot datus programmā Excel, izmantojot VBA.
Kā kārtot datus pēc vairākām kolonnām
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:
Zemāk ir kods, kas sakārtos datus, pamatojoties uz vairākām kolonnām:
Sub SortMultipleColumns () Ar ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 "). Virsraksts = xlJā .Pielietot beigas ar beigu apakšdaļu
Ņemiet vērā, ka šeit es esmu norādījis vispirms kārtot, pamatojoties uz kolonnu A un pēc tam uz kolonnu B.
Rezultāts būtu tāds, kā parādīts zemāk:
Kā iegūt virknē tikai ciparu daļu programmā Excel
Ja vēlaties no virknes izvilkt tikai skaitlisko daļu vai tikai teksta daļu, varat izveidot pielāgotu funkciju VBA.
Pēc tam darblapā varat izmantot šo VBA funkciju (tāpat kā parastās Excel funkcijas), un tā no virknes iegūs tikai ciparu vai teksta daļu.
Kaut kas, kā parādīts zemāk:
Zemāk ir VBA kods, kas izveidos funkciju, lai no virknes iegūtu skaitlisko daļu:
'Šis VBA kods izveidos funkciju, lai iegūtu virknes skaitlisko daļu ) Tad rezultāts = Rezultāts un vidus (CellRef, i, 1) Nākamais i GetNumeric = Rezultāta beigu funkcija
Jums ir jāievieto kods modulī, un pēc tam darblapā varat izmantot funkciju = GetNumeric.
Šī funkcija aizņems tikai vienu argumentu, kas ir šūnas atsauce, no kuras vēlaties iegūt skaitlisko daļu.
Līdzīgi, zemāk ir funkcija, kas jums parādīs tikai teksta daļu no virknes programmā Excel:
Šis VBA kods izveidos funkciju, lai iegūtu teksta daļu no virknes 1))) Tad rezultāts = Rezultāts un vidus (CellRef, i, 1) Nākamais i GetText = Rezultāta beigu funkcija
Tātad šie ir daži noderīgi Excel makro kodi, kurus varat izmantot savā ikdienas darbā, lai automatizētu uzdevumus un būtu daudz produktīvāki.
