Excel -makrojen käyttö voi nopeuttaa työtä ja säästää paljon aikaa.
Yksi tapa saada VBA -koodi on tallentaa makro ja ottaa sen luoma koodi. Makrotallentimen koodi on kuitenkin usein täynnä koodia, jota ei todellakaan tarvita. Myös makrotallentimella on joitain rajoituksia.
Joten kannattaa hankkia kokoelma hyödyllisiä VBA -makrokoodeja, joita voit pitää takataskussa ja käyttää sitä tarvittaessa.
Vaikka Excel VBA -makrokoodin kirjoittaminen voi aluksi kestää jonkin aikaa, voit pitää sen saatavana viitteenä ja käyttää sitä aina, kun tarvitset sitä seuraavaksi.
Tässä massiivisessa artikkelissa aion luetella joitain hyödyllisiä Excel -makroesimerkkejä, joita tarvitsen usein, ja pidän ne yksityisessä varastossani.
Aion päivittää tätä opetusohjelmaa lisää makroesimerkeillä. Jos luulet, että jotain pitäisi olla luettelossa, jätä kommentti.
Voit lisätä tämän sivun kirjanmerkkeihin tulevaa käyttöä varten.
Ennen kuin pääsen makroesimerkkiin ja annan sinulle VBA -koodin, näytän ensin, miten näitä esimerkkikoodeja käytetään.
Excel -makroesimerkkien koodin käyttäminen
Tässä on vaiheet, joita sinun on noudatettava, jos haluat käyttää minkä tahansa esimerkin koodia:
- Avaa työkirja, jossa haluat käyttää makroa.
- Pidä ALT -näppäintä painettuna ja paina F11. Tämä avaa VB -editorin.
- Napsauta hiiren kakkospainikkeella mitä tahansa projektinhallinnan kohdetta.
- Siirry kohtaan Lisää -> Moduuli.
- Kopioi ja liitä koodi moduulikoodi -ikkunaan.
Jos esimerkki sanoo, että sinun on liitettävä koodi laskentataulukon koodi -ikkunaan, kaksoisnapsauta laskentataulukko -objektia ja kopioi liitä koodi koodi -ikkunaan.
Kun olet lisännyt koodin työkirjaan, sinun on tallennettava se .XLSM- tai .XLS -laajennuksella.
Makron suorittaminen
Kun olet kopioinut koodin VB Editorissa, voit suorittaa makron seuraavasti:
- Siirry Kehittäjä -välilehdelle.
- Napsauta Makrot.
- Valitse Makro -valintaikkunasta makro, jonka haluat suorittaa.
- Napsauta Suorita -painiketta.
Jos et löydä kehittäjä -välilehteä valintanauhasta, lue tämä opetusohjelma saadaksesi sen.
Aiheeseen liittyvä opetusohjelma: Eri tapoja suorittaa makro Excelissä.
Jos koodi on liitetty laskentataulukon koodi -ikkunaan, sinun ei tarvitse huolehtia koodin suorittamisesta. Se suoritetaan automaattisesti, kun määritetty toiminto suoritetaan.
Siirrytään nyt hyödyllisiin makroesimerkkeihin, joiden avulla voit automatisoida työtä ja säästää aikaa.
Huomautus: Löydät monia tapauksia, joissa on heittomerkki (’), jota seuraa rivi tai kaksi. Nämä ovat kommentteja, jotka jätetään huomiotta koodia suoritettaessa ja jotka sijoitetaan muistiinpanoiksi itselle/lukijalle.
Jos löydät virheen artikkelista tai koodista, ole mahtava ja kerro minulle.
Excel -makroesimerkkejä
Tässä artikkelissa käsitellään alla olevia makroesimerkkejä:
Näytä kaikki laskentataulukot kerralla
Jos työskentelet työkirjassa, jossa on useita piilotettuja arkkeja, sinun on näytettävä nämä taulukot yksitellen. Tämä voi viedä jonkin aikaa, jos piilotettuja arkkeja on paljon.
Tässä on koodi, joka näyttää kaikki työkirjan laskentataulukot.
'Tämä koodi näyttää kaikki työkirjan taulukot Sub UnhideAllWoksheets () Dim ws kuin työkirja jokaiselle ws: lle ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
Yllä oleva koodi käyttää VBA -silmukkaa (jokaiselle) käymään läpi työkirjan jokaisen laskentataulukon. Sitten se muuttaa laskentataulukon näkyvän ominaisuuden näkyväksi.
Tässä on yksityiskohtainen opetusohjelma siitä, miten voit käyttää erilaisia menetelmiä taulukkojen paljastamiseen Excelissä.
Piilota kaikki laskentataulukot paitsi aktiivinen taulukko
Jos työskentelet raportin tai koontinäytön parissa ja haluat piilottaa kaikki laskentataulukot paitsi sen, jossa on raportti/koontinäyttö, voit käyttää tätä makrokoodia.
'Tämä makro piilottaa kaikki laskentataulukot paitsi aktiivisen taulukon Sub HideAllExceptActiveSheet () Dim ws kuin laskentataulukko jokaiselle tämän työkirjan ws: lle. Työsivut Jos ws.
Lajittele laskentataulukot aakkosjärjestykseen VBA: n avulla
Jos sinulla on työkirja, jossa on monia laskentataulukoita ja haluat lajitella ne aakkosjärjestyksessä, tämä makrokoodi voi olla todella kätevä. Näin voi olla, jos taulukonimet ovat vuosiluku tai työntekijöiden tai tuotteiden nimet.
'Tämä koodi lajittelee laskentataulukot aakkosjärjestyksessä Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount kokonaislukuna, i kokonaislukuna, j kokonaislukuna ShCount = Sheets.Count For i = 1 To ShCount - 1 j = i + 1 ShCount Jos Sheets (j). Nimi <Sheets (i). Nimi Sitten Sheets (j). Siirrä ennen: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub
Suojaa kaikki laskentataulukot kerralla
Jos työkirjassa on paljon laskentataulukoita ja haluat suojata kaikki taulukot, voit käyttää tätä makrokoodia.
Sen avulla voit määrittää salasanan koodissa. Tarvitset tämän salasanan laskentataulukon suojauksen poistamiseen.
'Tämä koodi suojaa kaikki taulukot kerralla Sub ProtectAllSheets () Dim ws kuin laskentataulukko Himmentää salasanan merkkijonona salasana = "Test123" "korvaa Test123 haluamallasi salasanalla. End Sub
Poista kaikkien laskentataulukoiden suojaus kerralla
Jos osa tai kaikki laskentataulukoista on suojattu, voit poistaa niiden suojauksen vain pienellä muokkauksella arkkien suojaamiseen käytettyä koodia.
'Tämä koodi suojaa kaikki taulukot kerralla Sub ProtectAllSheets () Dim ws kuin laskentataulukko Dim dim password as String password = "Test123" "korvata Test123 haluamallasi salasanalla. End Sub
Huomaa, että salasanan on oltava sama kuin laskentataulukoiden lukitsemisessa. Jos ei, näet virheen.
Näytä kaikki rivit ja sarakkeet
Tämä makrokoodi näyttää kaikki piilotetut rivit ja sarakkeet.
Tästä voi olla todella apua, jos saat tiedoston toiselta henkilöltä ja haluat olla varma, ettei piilotettuja rivejä/sarakkeita ole.
Tämä koodi näyttää kaikki taulukon rivit ja sarakkeet.
Poista kaikki yhdistetyt solut
Yleinen käytäntö on yhdistää solut yhdeksi. Vaikka se toimii, kun solut yhdistetään, et voi lajitella tietoja.
Jos käytät laskentataulukkoa, jossa on yhdistettyjä soluja, poista kaikki yhdistetyt solut yhdellä kertaa alla olevan koodin avulla.
'Tämä koodi yhdistää kaikki yhdistetyt solut Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Huomaa, että yhdistämisen ja keskittämisen sijasta suosittelen käyttämään keskellä valintaa.
Tallenna työkirja nimellä TimeStamp
Paljon aikaa saatat joutua luomaan versioita työstäsi. Nämä ovat varsin hyödyllisiä pitkissä projekteissa, joissa työskentelet tiedoston kanssa ajan mittaan.
Hyvä käytäntö on tallentaa tiedosto aikaleimoilla.
Aikaleimojen avulla voit palata tiettyyn tiedostoon nähdäksesi, mitä muutoksia on tehty tai mitä tietoja on käytetty.
Tässä on koodi, joka tallentaa työkirjan automaattisesti määritettyyn kansioon ja lisää aikaleiman aina, kun se tallennetaan.
'Tämä koodi tallentaa tiedoston, jonka nimessä on aikaleima ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & aikaleima End Sub
Sinun on määritettävä kansion sijainti ja tiedostonimi.
Yllä olevassa koodissa "C: UsersUsernameDesktop on käyttämäni kansion sijainti. Sinun on määritettävä kansion sijainti, johon haluat tallentaa tiedoston. Olen myös käyttänyt yleisnimeä "WorkbookName" tiedostonimen etuliitteenä. Voit määrittää jotain projektiin tai yritykseen liittyvää.
Tallenna jokainen laskentataulukko erillisenä PDF -tiedostona
Jos käsittelet tietoja eri vuosilta tai divisioonista tai tuotteista, saatat joutua tallentamaan eri laskentataulukoita PDF -tiedostoina.
Vaikka se voi olla aikaa vievä prosessi, jos se tehdään manuaalisesti, VBA voi todella nopeuttaa sitä.
Tässä on VBA -koodi, joka tallentaa jokaisen laskentataulukon erillisenä PDF -tiedostona.
'Tämä koodi tallentaa jokaisen tehtävän taulukon erilliseksi PDF -aliosaksi SaveWorkshetAsPDF () Dim ws laskentataulukkona jokaiselle ws -lomakkeelle ws.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ws.Name & ".pdf" Seuraava ws End Sub
Yllä olevassa koodissa olen määrittänyt sen kansion osoitteen, johon haluan tallentaa PDF -tiedostot. Lisäksi jokaiselle PDF -tiedostolle annetaan sama nimi kuin laskentataulukolle. Sinun on muutettava tätä kansion sijaintia (paitsi jos nimesi on myös Sumit ja tallennat sen työpöydän testikansioon).
Huomaa, että tämä koodi toimii vain laskentataulukoille (ei kaavioille).
Tallenna jokainen laskentataulukko erillisenä PDF -tiedostona
Tässä on koodi, joka tallentaa koko työkirjasi PDF -tiedostona määritettyyn kansioon.
'Tämä koodi tallentaa koko työkirjan PDF -tiedostona TallennaWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Sinun on vaihdettava kansion sijainti tämän koodin käyttämiseksi.
Muunna kaikki kaavat arvoiksi
Käytä tätä koodia, kun sinulla on laskentataulukko, joka sisältää paljon kaavoja ja haluat muuntaa nämä kaavat arvoiksi.
'Tämä koodi muuntaa kaikki kaavat arvoiksi Sub ConvertToValues () ActiveSheet.UsedRange .Value = .Arvo End End End
Tämä koodi tunnistaa automaattisesti käytetyt solut ja muuntaa ne arvoiksi.
Suojaa/lukitse solut kaavoilla
Haluat ehkä lukita solut kaavoilla, kun sinulla on paljon laskelmia etkä halua poistaa sitä vahingossa tai muuttaa sitä.
Tässä on koodi, joka lukitsee kaikki solut, joilla on kaavoja, kun taas kaikki muut solut eivät ole lukittuja.
Tämä makrokoodi lukitsee kaikki solut kaavoilla Sub LockCellsWithFormulas () ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True .Protect AllowDelatingRows: = True End With End Sub
Aiheeseen liittyvä opetusohjelma: Solujen lukitseminen Excelissä.
Suojaa kaikki työkirjan laskentataulukot
Käytä alla olevaa koodia kaikkien työkirjan laskentataulukoiden suojaamiseen kerralla.
'Tämä koodi suojaa kaikki työkirjan taulukot Sub ProtectAllSheets () Dim ws laskentataulukkona jokaiselle ws -lomakkeelle ws. Protect next ws End Sub
Tämä koodi käy läpi kaikki laskentataulukot yksitellen ja suojaa sitä.
Jos haluat poistaa kaikkien laskentataulukoiden suojauksen, käytä ws.Unprotect ws.Protect -koodin sijaan.
Lisää rivi jokaisen valitun rivin jälkeen
Käytä tätä koodia, kun haluat lisätä tyhjän rivin valitun alueen jokaisen rivin jälkeen.
'Tämä koodi lisää rivin jokaisen valitun rivin jälkeen Sub InsertAlternateRows () Dim rng As Range Dim CountRow kuten Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Lisää ActiveCell.Offset (2, 0) Valitse Next i End Sub
Samoin voit muokata tätä koodia lisätäksesi tyhjän sarakkeen valitun alueen jokaisen sarakkeen jälkeen.
Lisää päivämäärä ja aikaleima automaattisesti viereiseen soluun
Aikaleima on jotain, jota käytät, kun haluat seurata toimintoja.
Voit esimerkiksi seurata toimintoja, kuten milloin tietyt kulut syntyivät, mihin aikaan myyntilasku luotiin, milloin tieto syötettiin soluun, milloin raportti päivitettiin viimeksi jne.
Käytä tätä koodia päivämäärän ja aikaleiman lisäämiseen viereiseen soluun, kun syöte tehdään tai olemassa olevaa sisältöä muokataan.
'Tämä koodi lisää aikaleiman viereiseen soluun Private Sub Worksheet_Change (ByVal Target as Range) On Error GoTo Handler If Target.Column = 1 And Target.Value "" Then Application.EnableEvents = False Target.Offset (0, 1) = Muoto (Nyt (), "pp-kk-vvvv hh: mm: ss") Application.EnableEvents = Todellinen loppu, jos käsittelijä: Loppuosa
Huomaa, että sinun on lisättävä tämä koodi laskentataulukon koodi -ikkunaan (ei moduulikoodin ikkunaan, kuten olemme tehneet muissa Excel -makroesimerkeissä tähän mennessä). Voit tehdä tämän kaksoisnapsauttamalla VB -editorissa sen arkin nimeä, jolle haluat tämän toiminnon. Kopioi ja liitä tämä koodi kyseisen taulukon koodi -ikkunaan.
Tämä koodi toimii myös silloin, kun tiedot on syötetty sarakkeeseen A (huomaa, että koodissa on rivi Target.Column = 1). Voit muuttaa tätä vastaavasti.
Korosta vaihtoehtoiset rivit valinnassa
Vaihtoehtoisten rivien korostaminen voi parantaa tietojen luettavuutta valtavasti. Tästä voi olla hyötyä, kun haluat tulostaa ja käydä läpi tiedot.
Tässä on koodi, joka korostaa heti valinnan vaihtoehtoiset rivit.
'Tämä koodi korostaisi vaihtoehtoisia rivejä valinnassa Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow as Range Set Myrange = Valinta jokaiselle Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 then Myrow.Interior.Color = vbCyan End Jos Seuraava Myrow End Sub
Huomaa, että olen määrittänyt koodin värin vbCyaniksi. Voit määrittää myös muita värejä (kuten vbRed, vbGreen, vbBlue).
Korosta solut väärin kirjoitetuilla sanoilla
Excelissä ei ole oikeinkirjoituksen tarkistusta, kuten Wordissa tai PowerPointissa. Vaikka voit suorittaa oikeinkirjoituksen tarkistuksen painamalla F7 -näppäintä, visuaalista vihjettä ei ole, jos kirjoitusvirhe on tapahtunut.
Käytä tätä koodia korostaaksesi välittömästi kaikki solut, joissa on kirjoitusvirhe.
'Tämä koodi korostaa solut, joissa on väärin kirjoitettuja sanoja Sub HighlightMisspelledCells () Dim cl As Range for each cl ActiveSheet.UsedRange If Not Application.CheckSpelling (word: = cl.Text) Sitten cl.Interior.Color = vbPunainen loppu jos seuraava cl End Sub
Huomaa, että korostetut solut sisältävät tekstiä, jota Excel pitää kirjoitusvirheenä. Monissa tapauksissa se korostaisi myös nimiä tai tuotemerkkejä, joita se ei ymmärrä.
Päivitä työkirjan kaikki pivot -taulukot
Jos työkirjassa on useita pivot -taulukoita, voit käyttää tätä koodia päivittääksesi kaikki nämä pivot -taulukot kerralla.
'Tämä koodi päivittää työkirjan kaikki pivot -taulukot Sub RefreshAllPivotTables () Dim PT kuten pivot -taulukko jokaiselle ActiveSheet -sivun PT -pivot -taulukolle.
Voit lukea lisää pivot -taulukoiden päivittämisestä täältä.
Vaihda valittujen solujen kirjainkoko isoiksi kirjaimiksi
Vaikka Excelillä on kaavat tekstin kirjainkoon vaihtamiseksi, se saa sinut tekemään sen toisessa solusarjassa.
Tällä koodilla voit vaihtaa valitun tekstin tekstin kirjainkoon välittömästi.
'' Tämä koodi muuttaa valinnan isoiksi kirjaimiksi Sub ChangeCase () Dim Rng as Range for each Rng in Selection.Cells if Rng.HasFormula = False then Rng.Value = UCase (Rng.Value) End If Next Rng End Sub
Huomaa, että tässä tapauksessa olen käyttänyt UCasea tehdäksesi tekstikokoelmasta ylemmän. Voit käyttää LCasea pienillä kirjaimilla.
Korosta kaikki solut kommenteilla
Käytä alla olevaa koodia korostamaan kaikki solut, joissa on kommentteja.
"Tämä koodi korostaa soluja, joissa on kommentteja" Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub
Tässä tapauksessa olen käyttänyt vbBluea antamaan soluille sinisen värin. Voit halutessasi vaihtaa tämän muihin väreihin.
Korosta tyhjät solut VBA: n avulla
Vaikka voit korostaa tyhjän solun ehdollisella muotoilulla tai käyttämällä Siirry erikoisvalintaikkunaan, jos sinun on tehtävä se melko usein, on parempi käyttää makroa.
Kun olet luonut tämän makron, voit käyttää sitä pikakäytön työkalupalkissa tai tallentaa sen henkilökohtaiseen makrotyökirjaasi.
Tässä on VBA -makrokoodi:
Tämä koodi korostaa kaikki tietojoukon tyhjät solut Sub HighlightBlankCells () Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks) .Interior.Color = vb
Tässä koodissa olen määrittänyt tyhjät solut korostettavaksi punaisella värillä. Voit valita muita värejä, kuten sinisen, keltaisen, syaanin jne.
Tietojen lajitteleminen yhden sarakkeen mukaan
Voit lajitella tiedot määritetyn sarakkeen mukaan alla olevan koodin avulla.
Sub SortDataHeader () Range ("DataRange").
Huomaa, että olen luonut nimellisen alueen nimellä ”DataRange” ja käyttänyt sitä soluviittausten sijasta.
Lisäksi tässä käytetään kolmea keskeistä parametria:
- Avain1 - Tämä on tieto, jonka haluat lajitella. Yllä olevassa esimerkkikoodissa tiedot lajitellaan sarakkeen A arvojen perusteella.
- Järjestys- Tässä sinun on määritettävä, haluatko lajitella tiedot nousevaan vai laskevaan järjestykseen.
- Otsikko - Tässä sinun on määritettävä, onko tiedoissasi otsikoita vai ei.
Lue lisää tietojen lajittelusta Excelissä VBA: n avulla.
Tietojen lajittelu usean sarakkeen mukaan
Oletetaan, että sinulla on alla näkyvä tietojoukko:
Alla on koodi, joka lajittelee tiedot useiden sarakkeiden perusteella:
Sub SortMultipleColumns () ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 "). Otsikko = xlKyllä. Käytä lopuksi lopussa
Huomaa, että tässä olen määrittänyt lajittelun ensin sarakkeen A ja sitten sarakkeen B perusteella.
Tulos olisi jotain alla esitetystä:
Kuinka saada vain numeerinen osa merkkijonosta Excelissä
Jos haluat poimia merkkijonosta vain numeerisen osan tai vain tekstiosan, voit luoda mukautetun funktion VBA: ssa.
Voit sitten käyttää tätä VBA -funktiota laskentataulukossa (aivan kuten tavalliset Excel -toiminnot), ja se poimii merkkijonosta vain numeerisen tai tekstiosan.
Jotain alla olevan kuvan mukaisesti:
Alla on VBA -koodi, joka luo toiminnon numeerisen osan poimimiseksi merkkijonosta:
Tämä VBA -koodi luo funktion, joka saa numeerisen osan merkkijonosta. ) Sitten tulos = Tulos ja puoliväli (CellRef, i, 1) Seuraava i GetNumeric = Tuloksen lopetusfunktio
Sinun on sijoitettava koodi moduuliin ja voit käyttää laskentataulukon funktiota = GetNumeric.
Tämä toiminto käyttää vain yhtä argumenttia, joka on sen solun viite, josta haluat saada numeerisen osan.
Samoin alla on toiminto, joka saa sinulle vain tekstiosan Excel -merkkijonosta:
'Tämä VBA -koodi luo toiminnon, joka saa tekstiosan merkkijonosta Funktio GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 to StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Sitten Tulos = Tulos ja puoliväli (CellRef, i, 1) Seuraava i GetText = Tuloksen lopetusfunktio
Joten nämä ovat joitain hyödyllisiä Excel-makrokoodeja, joita voit käyttää päivittäisessä työssäsi tehtävien automatisoimiseen ja tuottavuuden parantamiseen.
