24 Nuttige Excel-makro-voorbeelde vir VBA-beginners (gereed om te gebruik)

Die gebruik van Excel-makro's kan jou werk bespoedig en baie tyd bespaar.

Een manier om die VBA-kode te kry, is om 'n makro op te neem en die kode te kry wat dit genereer.Die makro-opnemer se kode is egter dikwels vol kode wat nie regtig nodig is nie.Die makro-opnemer het ook 'n paar beperkings.

Dit is dus die moeite werd om 'n nuttige stel VBA-makro's te hê wat jy in jou agtersak kan hou en gebruik wanneer jy dit nodig het.

Alhoewel dit 'n geruime tyd kan neem om 'n Excel VBA-makro aanvanklik te kodeer, kan jy dit as 'n verwysing sodra jy klaar is, gebruik en gebruik die volgende keer wat jy dit nodig het.

In hierdie groot artikel sal ek 'n paar nuttige Excel-makro-voorbeelde lys wat ek dikwels nodig het en in my private kluis hou.

Ek sal voortgaan om hierdie tutoriaal op te dateer met meer makro-voorbeelde.Los asseblief 'n opmerking as jy dink iets moet op die lys wees.

Jy kan hierdie bladsy boekmerk vir toekomstige verwysing.

Nou, voordat ek by die makro-voorbeelde ingaan en vir jou die VBA-kode gee, laat ek jou eers wys hoe om die voorbeeldkode te gebruik.

Gebruik die kode uit die Excel-makrovoorbeeld

Hier is die stappe om te volg om die kode uit enige van die voorbeelde te gebruik:

• Maak die werkboek oop waarin jy die makro wil gebruik.
• Hou die ALT-sleutel in en druk F11.Dit sal oopmaakVB redakteur.
• Regskliek op enige voorwerp in die Project Explorer.
• Gaan na Voeg in -> Modules.
• Kopieer en plak die kode in die modulekodevenster.

As die voorbeeld sê dat jy die kode in die werkbladkodevenster moet plak, dubbelklik op die werkbladvoorwerp en kopieer-plak die kode in die kodevenster.

Nadat u die kode in die werkboek ingevoeg het, moet u dit stoor met die .XLSM- of .XLS-uitbreiding.

Hoe om 'n makro te laat loop

Nadat die kode in die VB-redigeerder gekopieer is, is die stappe om die makro te laat loop soos volg:

• Gaan na die ontwikkelaaroortjie.
• Klik Makro.

• In die Makro-dialoogkassie, kies die makro om te hardloop.
• Klik op die Run-knoppie.

As jy nie die ontwikkelaaroortjie in die lint kan vind nie, lees hierdie tutoriaal om te leer hoe om dit te kry.

As die kode in die werkblad-kodevenster geplak is, hoef jy nie bekommerd te wees oor die gebruik van die kode nie.Dit loop outomaties wanneer die gespesifiseerde aksie plaasvind.

Kom ons kyk nou na nuttige makro-voorbeelde wat jou kan help om jou werk te outomatiseer en tyd te bespaar.

Let wel: Jy sal baie apostrofe (') vind, gevolg deur 'n reël of twee.Dit is opmerkings wat geïgnoreer word wanneer die kode uitgevoer word en as self-/leser-kommentaar geplaas word.

As jy enige foute in die artikel of kode vind, laat weet my asseblief.

Excel Makro Voorbeeld

Hierdie artikel beskryf die volgende makro-voorbeelde:

Versteek alle werkblaaie gelyktydig

As jy aan 'n werkboek met veelvuldige versteekte velle werk, moet jy hierdie velle een vir een ontsteek.Dit kan 'n rukkie neem as daar baie versteekte velle is.

Hier is die kode om alle velle in die werkboek te vertoon.

'Hierdie kode sal alle velle in die werkboek ontsteek Sub UnhideAllWoksheets() Dim ws As Worksheet For Every ws In ActiveWorkbook. Worksheets ws. Visible = xlSheetVisible Next ws End Sub

Die bogenoemde kode gebruik 'n VBA-lus (vir elkeen) om deur elke werkblad in die werkboek te herhaal.Dan verander dit die sigbare eienskap van die blad na sigbaar.

Hier is 'n gedetailleerde tutoriaal oor hoe om 'n werkblad in Excel te vertoon met behulp van verskeie metodes.

Versteek alle werkkaarte behalwe die aktiewe werkblad

Hierdie makro-kode kan gebruik word as jy aan 'n verslag of dashboard werk en alle velle wil versteek behalwe die blad wat die verslag/dashboard het.

'Hierdie makro sal al die werkblad versteek behalwe die aktiewe blad Sub HideAllExceptActiveSheet() Dim ws As Worksheet For Every ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden Volgende ws End Sub

Sorteer werkblad alfabeties met VBA

Hierdie makro-kode kan handig te pas kom as jy 'n werkboek met baie velle het en jy daardie velle alfabeties wil sorteer.Dit kan gebeur as jy die bladnaam as die jaar of werknemernaam of produknaam het.

'Hierdie kode sal die werkblaaie alfabeties sorteer Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Heelgetal, i As Heelgetal, j As Heelgetal 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

Beskerm alle werkkaarte gelyktydig

As jy baie velle in jou werkboek het en jy wil alle velle beskerm, kan jy hierdie makro-kode gebruik.

Dit laat jou toe om 'n wagwoord in kode te spesifiseer.Jy sal hierdie wagwoord nodig hê om die vel te ontskerm.

'Hierdie kode sal al die velle op een slag beskerm Sub ProtectAllSheets() Dim ws As Worksheet Dim wagwoord As String password = "Test123" 'vervang Toets123 met die wagwoord wat jy wil hê Vir Elke ws In Worksheets ws.Protect wagwoord:=wagwoord Volgende ws Einde Sub

Ontbeskerm alle werkblaaie gelyktydig

As jy sommige of al jou werkblaaie beskerm het, kan jy hulle ontskerm met slegs 'n effense wysiging aan die kode wat gebruik word om die werkblaaie te beskerm.

'Hierdie kode sal al die velle op een slag beskerm Sub ProtectAllSheets() Dim ws As Worksheet Dim wagwoord As String password = "Test123" 'vervang Toets123 met die wagwoord wat jy wil hê Vir Elke ws In Worksheets ws.Unprotect password:=password Next ws Einde Sub

Let daarop dat die wagwoord dieselfde wagwoord moet wees wat gebruik word om die blad te sluit.Indien nie, sal jy 'n fout sien.

Ontsteek alle rye en kolomme

Hierdie makro-kode sal alle versteekte rye en kolomme ontsteek.

Dit kan nuttig wees as jy die lêer van iemand anders af neem en wil seker maak dat daar geen versteekte rye/kolomme is nie.

'Hierdie kode sal al die rye en kolomme in die Werkblad Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub ontsteek

Ontbind alle saamgevoegde selle

Om selle in een saam te voeg is 'n algemene praktyk.Terwyl dit werk, sal jy nie die data kan sorteer wanneer die selle saamgevoeg word nie.

As jy 'n werkblad met saamgevoegde selle gebruik, gebruik die kode hieronder om al die saamgevoegde selle gelyktydig te ontbind.

'Hierdie kode sal al die saamgevoegde selle ontbind Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Let daarop dat ek aanbeveel dat u die opsie "Sentraal oor seleksie" gebruik in plaas van die opsie "Saamvoeg en sentreer".

Stoor werkboek met tydstempel in naam

Daar is baie tye wanneer jy dalk 'n werkende weergawe moet skep.Dit is ideaal vir langtermynprojekte wat met verloop van tyd met lêers handel.

'n Goeie praktyk is om die lêer met 'n tydstempel te stoor.

Die gebruik van 'n tydstempel sal jou toelaat om terug te gaan na 'n lêer om te sien watter veranderinge gemaak is of watter data gebruik is.

Hier is die kode wat die werkboek outomaties in die gespesifiseerde vouer stoor en 'n tydstempel byvoeg wanneer dit gestoor word.

'Hierdie kode sal die lêer stoor met 'n tydstempel in sy naam Sub SaveWorkbookWithTimeStamp() Dim tydstempel As String timestamp = Format(Datum, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & tydstempel End Sub

Jy moet die gidsligging en lêernaam spesifiseer.

In die kode hierbo, "C: UsersUsernameDesktop is die vouerligging wat ek gebruik.Jy moet die gidsligging spesifiseer waar jy die lêer wil stoor.Ek het ook die generiese naam "WorkbookName" as die lêernaamvoorvoegsel gebruik.Jy kan inhoud spesifiseer wat verband hou met jou projek of maatskappy.

Stoor elke werkblad as 'n aparte PDF

As jy data van verskillende jare of departemente of produkte gebruik, sal jy dalk verskillende werkblaaie as PDF-lêers moet stoor.

Alhoewel dit 'n tydrowende proses kan wees as dit met die hand gedoen word, bespoedig VBA dinge.

Hier is 'n VBA-kode wat elke werkblad as 'n aparte PDF stoor.

'Hierdie kode sal elke worsheet stoor as 'n aparte PDF Sub SaveWorkshetAsPDF() Dim ws As Worksheet For Every ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Volgende ws End Sub

In die kode hierbo het ek die adres gespesifiseer van die lêergids waar ek die PDF wil stoor.Elke PDF kry ook dieselfde naam as die blad.Jy sal hierdie gidsligging moet verander (tensy jou naam ook Sumit is en jy dit in 'n toetslêergids op jou lessenaar gestoor het).

Let daarop dat hierdie kode slegs vir werkkaarte werk (nie vir grafiekblaaie nie).

Stoor elke werkblad as 'n aparte PDF

Hier is die kode om die hele werkboek as 'n PDF in die gespesifiseerde vouer te stoor.

'Hierdie kode sal die hele werkboek stoor as PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

Jy moet die vouerligging verander om hierdie kode te gebruik.

Skakel alle formules om na waardes

Gebruik hierdie kode wanneer jy 'n werkblad met baie formules het en jy daardie formules na waardes wil omskakel.

'Hierdie kode sal alle formules omskep in waardes Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub

Hierdie kode herken outomaties die sel wat gebruik word en skakel dit om na 'n waarde.

Beskerm/sluit selle met formules

Wanneer jy baie berekeninge het en dit nie per ongeluk wil uitvee of verander nie, wil jy dalk selle met formules sluit.

Hier is die kode wat alle selle met formules sal sluit en alle ander selle ontsluit sal laat.

'Hierdie makrokode sal al die selle met formules sluit Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect Laat DeletingRows toe:=True

Verwante handleiding: Hoe om selle in Excel te sluit.

Beskerm alle velle in 'n werkboek

Gebruik die volgende kode om alle velle in 'n werkboek gelyktydig te beskerm.

'Hierdie kode sal alle velle in die werkboek beskerm Sub ProtectAllSheets() Dim ws As Worksheet For Every ws In Worksheets ws. Protect Next ws End Sub

Hierdie kode sal een vir een deur alle velle gaan en hulle beskerm.

As jy alle velle wil ontskerm, gebruik ws.Unprotect in plaas van ws.Protect in jou kode.

Voeg 'n ry na elke ry van die seleksie in

Gebruik hierdie kode wanneer jy 'n leë ry na elke ry in die geselekteerde reeks wil invoeg.

'Hierdie kode sal 'n ry na elke ry in die seleksie invoeg Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Heelgetal Dim i As Heelgetal Stel rng = Seleksie CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Voeg ActiveCell.Offset(2, 0) in. Kies Volgende i End Sub

Weereens, jy kan hierdie kode verander om 'n leë kolom na elke kolom in die geselekteerde reeks in te voeg.

Voeg outomaties datum en tydstempel in aangrensende selle in

Tydstempels is wat jy gebruik wanneer jy aktiwiteit wil dop.

Byvoorbeeld, jy wil dalk aktiwiteite naspoor soos wanneer 'n spesifieke uitgawe aangegaan is, wanneer 'n verkoopsfaktuur geskep is, wanneer data in 'n sel ingevoer is, wanneer 'n verslag laas opgedateer is, ensovoorts.

Gebruik hierdie kode om 'n datum- en tydstempel in aangrensende selle in te voeg wanneer jy bestaande inhoud tik of wysig.

'Hierdie kode sal 'n tydstempel in die aangrensende sel Private Sub Worksheet_Change(ByVal Target As Range) invoeg by fout Gaan na hanteerder As Target.Column = 1 And Target.Value <> "" Then Application.EnableEvents = False Target.Offset(0, 1) = Formaat(Nou(), "dd-mm-jjjj uu:mm:ss") Application.EnableEvents = True End If Hanterer: End Sub

Let daarop dat jy hierdie kode in die werkbladkodevenster moet invoeg (nie in die modulekodevenster soos ons tot dusver in ander Excel-makro-voorbeelde gedoen het nie).Om dit te doen, dubbelklik in die VB-redigeerder op die bladnaam waarvoor jy hierdie funksionaliteit wil hê.Kopieer en plak dan hierdie kode in die kodevenster van daardie blad.

Hierdie kode werk ook wanneer data-invoer in kolom A gedoen word (let daarop dat die kode 'n Target.Column = 1 reël het).Jy kan dit dienooreenkomstig verander.

Merk alternatiewe rye in seleksie

Deur afwisselende reëls uit te lig kan die leesbaarheid van jou data aansienlik verbeter.Dit kan nuttig wees wanneer jy dit moet uitdruk en deur die data moet blaai.

Hier is 'n kode wat onmiddellik alternatiewe rye in die keuse sal uitlig.

'Hierdie kode sal alternatiewe rye in die seleksie uitlig Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Stel Myrange = Selection For Every Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End As Volgende Myrow End Sub

Let daarop dat ek die kleur as vbCyan in die kode gespesifiseer het.Jy kan ook ander kleure spesifiseer (bv. vbRed, vbGreen, vbBlue).

Merk verkeerd gespelde selle

Excel het nie die speltoets in Word of PowerPoint nie.Terwyl jy speltoets kan uitvoer deur F7 te druk, is daar geen visuele aanduiding vir spelfoute nie.

Gebruik hierdie kode om alle verkeerd gespelde selle onmiddellik uit te lig.

'Hierdie kode sal die selle uitlig wat woorde verkeerd gespel het Sub HighlightMisspelledCells() Dim cl As Range Vir Elke cl In ActiveSheet.UsedRange Indien Nie Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl Einde Sub

Let daarop dat die gemerkte selle die selle is met teks wat Excel dink verkeerd gespel is.In baie gevalle beklemtoon dit ook name of handelsmerkterme wat dit nie verstaan ​​nie.

Verfris alle spilpunttabelle in 'n werkboek

As jy veelvuldige spilpunttabelle in die werkboek het, kan jy hierdie kode gebruik om almal gelyktydig te verfris.

'Hierdie kode sal al die Spiltabel in die Werkboek verfris Sub RefreshAllPivotTables() Dim PT As PivotTable Vir Elke PT In ActiveSheet.PivotTables PT.RefreshTable Volgende PT End Sub

Jy kan meer lees oor die verfrissing van spiltabelle hier.

Verander die letterkas van geselekteerde selle na hoofletters

Terwyl Excel formules het om die hoofletters van teksletters te verander, laat dit jou toe om dit in 'n ander stel selle te doen.

Gebruik hierdie kode om die letterkas van teks in die geselekteerde teks onmiddellik te verander.

'Hierdie kode sal die seleksie verander na hoofletter sub ChangeCase() Dim Rng As Range Vir Elke Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub

Let daarop dat ek in hierdie geval UCase gebruik om die tekskas op hoofletters te stel.Jy kan LCase vir kleinletters gebruik.

Merk alle selle met opmerkings

Gebruik die volgende kode om alle selle met opmerkings in te merk.

'Hierdie kode sal selle uitlig wat opmerkings het' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

In hierdie geval gebruik ek vbBlue om die sel 'n blou kleur te gee.Jy kan dit na 'n ander kleur verander indien nodig.

Merk leë selle met VBA

Alhoewel jy voorwaardelike formatering kan gebruik of die Gaan na Spesiale dialoog kan gebruik om leë selle uit te lig, as jy dit gereeld moet doen, is dit die beste om 'n makro te gebruik.

Sodra dit geskep is, kan jy hierdie makro in die Vinnige Toegang Toolbar hê of dit in jou persoonlike makro-werkboek stoor.

Hier is die VBA makro-kode:

'Hierdie kode sal al die leë selle in die datastel uitlig Sub HighlightBlankCells() Dim Dataset as Reeksstel Dataset = Seleksie Dataset.SpecialCells(xlCellTypeBlanks).Binnenland.Color = vbRed End Sub

In hierdie kode het ek leë selle gespesifiseer wat in rooi gemerk is.Jy kan ander kleure kies soos blou, geel, siaan, ens.

Hoe om data volgens 'n enkele kolom te sorteer

Jy kan die volgende kode gebruik om data volgens 'n gespesifiseerde kolom te sorteer.

Sub SortDataHeader() 
Range("DataRange"). Sorteer Sleutel1:=Reeks("A1"), Orde1:=xlStygend, Kopskrif:=xlJa 
End Sub

Let daarop dat ek 'n genoemde reeks genaamd "DataRange" geskep het en dit in die plek van die selverwysing gebruik het.

Daar is ook drie sleutelparameters wat hier gebruik word:

• Sleutel1 – Dit is die sleutel waarmee u die datastel wil sorteer.In die voorbeeldkode hierbo sal die data gesorteer word op grond van die waardes in kolom A.
• Orde1 – Hier moet jy spesifiseer of jy die data in stygende of dalende volgorde wil sorteer.
• Kopskrif – Hier moet jy spesifiseer of jou data 'n kopskrif het of nie.

Lees meer oor hoe om data in Excel met VBA te sorteer.

Hoe om data volgens verskeie kolomme te sorteer

Gestel jy het 'n datastel wat soos volg lyk:

Hier is die kode om data te sorteer gebaseer op verskeie kolomme:

Sub SortMultipleColumns() Met ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1) :C13") .Header = xlYes .Pas Einde met End Sub toe

Let daarop dat ek hier gespesifiseer het om eers op kolom A en dan op kolom B te sorteer.

Die uitset sal soos volg lyk:

Hoe om slegs die numeriese deel van 'n string in Excel te kry

As jy net die numeriese deel of die teksdeel uit die string wil onttrek, kan jy 'n pasgemaakte funksie in VBA skep.

Jy kan dan hierdie VBA-funksie in jou werkblad gebruik (net soos 'n gewone Excel-funksie) en dit sal slegs die nommer of teksgedeelte uit die string onttrek.

Soos hieronder getoon:

Hier is die VBA-kode wat 'n funksie sal skep wat die numeriese deel uit 'n string onttrek:

'Hierdie VBA-kode sal 'n funksie skep om die numeriese deel van 'n string af te kry Funksie GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) Dan Resultaat = Resultaat & Mid(CellRef, i, 1) Volgende i GetNumeric = Resultaat Eindfunksie

Jy moet die kode in 'n module plaas en dan kan jy die funksie =GetNumeric in die werkblad gebruik.

Hierdie funksie sal slegs een parameter neem, die selverwysing van die sel waaruit die numeriese deel verkry moet word.

Net so sal die funksie hieronder slegs die teksgedeelte van 'n string in Excel kry:

'Hierdie VBA-kode sal 'n funksie skep om die teksgedeelte van 'n string af te kry Funksie GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 to StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Dan Resultaat = Resultaat & Mid(CellRef, i, 1) Volgende i GetText = Resultaat Eindfunksie

Dit is dus 'n paar nuttige Excel-makrokodes wat u in u daaglikse werk kan gebruik om take te outomatiseer en u produktiwiteit te verhoog.