tässä artikkelissa aion näyttää joitakin kaikkein hämmästyttävä VBA Excel-koodit, joita voit käyttää optimoida työsi. VBA on ohjelmointikieli, jolla voidaan laajentaa MS Excelin ja muiden MS Office-sovellusten ominaisuuksia. Se on erittäin hyödyllinen MS Excel-käyttäjille, koska sitä voidaan käyttää automatisoimaan työsi ja merkittävästi parantaa tehokkuutta. Tämä artikkeli esittelee sinulle VBA ja näyttää joitakin kaikkein hyödyllinen, valmis käyttämään VBA koodit siellä. Voit käyttää näitä makroesimerkkejä luoda omia skriptejä, jotka sopivat omiin tarpeisiisi.

et tarvitse ohjelmointikokemusta hyödyntääksesi tämän artikkelin tietoja, mutta sinulla odotetaan olevan Excel-perustiedot. Jos olet aloittelija käyttäjä, suosittelen sinua lukemaan artikkelin 20 Excel kaavoja sinun pitäisi alkaa käyttää nyt oppia lisää Excelin ydintoiminnot.

olen valmistellut sinulle useita käyttövalmiita VBA Excel-Makroesimerkkejä, joilla on suuri toiminnallisuus, jonka avulla voit optimoida työsi. Jotta voit käyttää niitä, sinun täytyy ”asentaa” ne Excel-tiedoston. Seuraava kappale käsittelee Excel-makroasennusta. Ohita tämä osa, Jos olet jo perehtynyt tähän.

makron asentaminen

Exceliin, paina näppäinyhdistelmää alt + F11. Tämä vie sinut VBA editor MS Excel. Napsauta sitten vasemmalla olevaa Microsoft Excel-Objektikansiota hiiren kakkospainikkeella ja valitse Insert => Module. Tämä on paikka, jossa makroja säilytetään. Voit hyödyntää makro, sinun täytyy tallentaa Excel-asiakirjan makro-käytössä. From the tab file => Tallenna nimellä, valitse Tallenna makro-yhteensopivana työkirja (the .xlsm laajennus) nyt, on aika kirjoittaa ensimmäinen makro!

1. Kopioi tiedot tiedostosta toiseen.

erittäin hyödyllinen makro, koska se näyttää, miten kopioidaan erilaisia tietoja vba: n sisältä ja miten luodaan ja nimetään uusi työkirja. Voit helposti päivittää sen omiin tarpeisiisi sopivaksi:

Sub CopyFiletoAnotherWorkbook ()"Kopioi tietolomakkeet ("Esimerkki 1").Alue ("B4: C15").Kopioi ' Luo uusi työkirja työkirjat.Add ' Liitä data ActiveSheet.Liitä ' Sammuta sovellus hälytykset sovellus.DisplayAlerts = False ' Tallenna uusi tiedosto. Muuta kansion nimi. ActiveWorkbook.SaveAs-Tiedostonimi:="C:\Temp\MyNewBook.xlsx "' Käännä sovellus hälytykset takaisin sovelluksen.DisplayAlerts = TrueEnd Sub

2. Näytä piilotetut rivit

joskus suuret Excel-tiedostot sisältävät piilotettuja viivoja paremman selkeyden vuoksi. Tässä makro, joka poistaa kaikki rivit aktiivisesta laskentataulukosta:

Sub ShowHiddenRows() sarakkeet.Koko syksy.Hidden = Väärät Rivit.EntireRow.Hidden = FalseEnd Sub

3. Poista tyhjät rivit ja sarakkeet

tyhjät rivit Excelissä ovat ongelma tietojenkäsittelyssä. Näin pääset niistä eroon:

Sub DeleteEmptyRowsAndColumns () " Declare your variables. Dim MyRange kuin alue Dim iCounter niin kauan " määritellä kohdealueen. Aseta MyRange = ActiveSheet.UsedRange ' Start reverse looping Rivivalikoiman läpi. Sillä iCounter = MyRange.Rivi.Laske 1 askel -1 ' jos koko rivi on tyhjä poista se. Jos Hakemus.CountA (rivit (iCounter).EntireRow) = 0 sitten Rivit (iCounter).Poista "Poista kommentti nähdäksesi, mitkä ovat tyhjiä rivejä "MsgBox" rivi " & iCounter &" on tyhjä "päättyy, jos" lisää laskuri alas seuraavan icounterin " Vaihe 6: Aloita Käänteinen silmukka Sarakealueen läpi. Sillä iCounter = MyRange.Sarake.Laske 1 vaihe -1 ' Vaihe 7: Jos koko sarake on tyhjä, poista se. Jos Hakemus.Määrä (sarakkeet (iCounter).EntireColumn) = 0 sitten sarakkeet (iCounter).Poista Loppu, Jos " Vaihe 8: Kasvata laskuri alas seuraavan ikounterin loppuun Sub

4. Etsi tyhjä solu

Sub FindEmptyCell() ActiveCell.Offset (1, 0).Valitse Do While Not Isempty (ActiveCell) ActiveCell.Offset (1, 0).Valitse LoopEnd Sub

5. Korvaa tyhjät solut arvolla.

kuten aiemmin mainittiin, tyhjät solut häiritsevät tietojenkäsittelyä ja kääntötaulukoiden luomista. Tässä on koodi, joka korvaa kaikki tyhjät solut 0: lla. Tällä makrolla on erittäin suuri sovellus, koska voit käyttää sitä löytämään ja korvaamaan N/A-tuloksia sekä muita merkkejä, kuten pisteitä, pilkkuja tai päällekkäisiä arvoja:

Sub FindAndReplace() 'Declare your variables Dim MyRange As Range Dim MyCell As Range 'Tallenna työkirja ennen solujen muuttamista? Valitse Case MsgBox ("tätä toimintoa ei voi perua. "& _ "Tallenna työkirja ensin?", vbiesnocancel) tapaus on = Vbyes ThisWorkbook.Tallenna asia on = Vbcancel Exit alapää Select ' Määritä kohdealue. Set MyRange = Selection ' Aloita silmukointi alueen läpi. Kunkin MyCell MyRange 'Tarkista nolla pituus sitten lisätään 0. If Len(Suom.Arvo) = 0 sitten MyCell = 0 päättyy, Jos " saada seuraava solu alueella Seuraava mykiön Sub

6. Lajittele numerot

seuraava makro lajittelee nousevaan järjestykseen kaikki numerot aktiivisen solun sarakkeesta. Kaksoisnapsauta mitä tahansa solua sarakkeesta, jonka haluat lajitella.HUOM: sinun täytyy laittaa Koodi arkille 1 eikä moduuliin toimiaksesi:

Private Sub Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel As Boolean) "Declar your Variables Dim LastRow As Long" Find last row LastRow = Cells (Rows.Count, 1).Loppu (xlUp).Rivi " Lajittele nousevaksi kaksoisnapsautetuilla sarakeriveillä ("6:"& LastRow) .Järjestä _ Key1: = Solut (6, ActiveCell.Sarake), _ Tilaus1: = xlAscendingEnd Sub

7. Poista tyhjät välilyönnit

silloin tällöin työkirjan tiedot sisältävät ylimääräisiä välilyöntejä (välilyöntejä), jotka voivat häiritä tietojen analysointia ja turmella kaavoja. Tässä on makro, joka poistaa kaikki välilyönnit ennalta valitusta solualueesta:

Alitrimmiavaruudet() 'ilmoita muuttujasi Dim MyRange as Range Dim MyCell As Range 'Tallenna työkirja ennen solujen muuttamista valitse Case MsgBox("tätä toimintoa ei voi kumota. "& _ "Tallenna työkirja ensin?", vbiesnocancel) tapaus on = Vbyes ThisWorkbook.Tallenna asia on = Vbcancel Exit alapää Select ' Määritä kohdealue. Set MyRange = Selection ' Aloita silmukointi alueen läpi. Kunkin MyCell MyRange 'leikata välilyöntejä. Jos ei ole IsEmpty(MyCell), niin MyCell = Trim (MyCell) päättyy, jos " saa seuraavan solun alueella seuraavan MyCellEnd Sub

8. Korosta dublisoidut arvot

joskus on päällekkäisiä arvoja useissa sarakkeissa, jotka haluaisimme valaista. Tässä makro, joka tekee juuri niin:

Sub Highlightduplikates() 'Declar your variables Dim MyRange As Range Dim MyCell As Range 'Määrittele kohdealue. Set MyRange = Selection ' Aloita silmukointi alueen läpi. Kunkin MyCell MyRange 'varmista solu on tekstin muotoilu. If WorksheetFunction.CountIf (MyRange, MyCell.Arvo) > 1 sitten MyCell.Sisustus.ColorIndex = 36 End If " Get the next cell in the range Next MyCellEnd Sub

9. Korosta kymmenen parasta arvoa

tämä koodi korosta kymmenen parasta arvoa solujen valinnasta:

Sub TopTen () - valinta.Muotoehdot.AddTop10 Valinta.Formaattiehdot (Valinta.Muotoehdot.Lasketa).Aseta Ykkösprioriteetti Valinnalla.Muotoehdot (1).TopBottom = xlTop10Top ' muuta sijoitusta tässä korostaaksesi eri arvoja .Sija = 10 .Prosentti = False End Kanssa Valinnan.Muotoehdot (1).Fontti .Väri = -16752384 .TintAndShade = 0 Päättyy Valintaan.Muotoehdot (1).Sisustus .PatternColorIndex = xlAutomatic .Väri = 13561798 .TintAndShade = 0 Päättyy Valintaan.Muotoehdot (1).StopIfTrue = FalseEnd Sub

voit helposti muokata koodia korostaaksesi eri arvoja.

10. Korosta suurempi kuin arvot

kun suoritat tämän koodin, ikkuna kysyy. Se kysyy, minkä arvon haluat vertailla valitsemiasi soluja.

Sub HighlightGreaterThanValues() Dim I as Integer i = InputBox("Enter Greater Than Value", "Enter Value") valinta.Muotoehdot.Poista ' Vaihda operaattori xlloweriin korostaaksesi alempien arvojen valintaa.Muotoehdot.Lisää Tyyppi: = xlCellValue, operaattori:=xlGreater, formula_1: = I valinta.Formaattiehdot (Valinta.Muotoehdot.Lasketa).Aseta Ykkösprioriteetti Valinnalla.Muotoehdot (1).Fontti.Väri = RGB (0, 0, 0).Sisustus.Color = RGB (31, 218, 154) End WithEnd Sub

voit säätää tätä koodia korostaaksesi myös alempia arvoja.

yksinkertainen makro, joka korostaa kaikkia kommentteja sisältäviä soluja:

Sub HighlightCommentCells () - valinta.SpecialCells (xlCellTypeComments).Valitse Valinta.Style= "Note" End Sub

12. Korosta soluja väärin kirjoitetuilla sanoilla

Tämä on erittäin hyödyllistä, kun työskentelet funktioilla, jotka ottavat merkkijonoja, kuitenkin joku tuli merkkijonoon virheellä ja kaavasi eivät toimi. Ongelman voi korjata näin:

 Sub ColorMispelledCells() jokaiselle cl: lle Activesheetissä.Käytetty Alue, Jos Ei Sovellus.CheckSpelling(Sana:=cl.Teksti) sitten _ cl.Sisustus.ColorIndex = 28 Next clEnd Sub

13. Luo pivot taulukko

tässä on miten luoda pivot taulukko MS Excel (2007 versio). Erityisen hyödyllinen, kun teet mukautetun raportin joka päivä. Voit optimoida pivot-taulukon luomisen seuraavasti:

Sub PivotTableForExcel2007() Dim SourceRange as Range Set SourceRange = Sheets("Sheet1").Range ("A3:N86") ActiveWorkbook.PivotCaches.Luo (_SourceType:=xlDatabase, _ SourceData:=SourceRange, _ Version:=xlPivotTableVersion12).CreatePivotTable _ TableDestination:="", _ TableName:="", _ DefaultVersion:=xlPivotTableVersion12End Sub

14. Liitä aktiivinen työkirja sähköpostiin

suosikkini VBA-koodi. Sen avulla voit liittää ja lähettää tiedoston olet työskennellyt ennalta sähköpostiosoitteen, viestin otsikko, ja viestin runko! Sinun täytyy ensin asettaa viittaus Microsoft Outlook (omassa VBA editior, klikkaa tools => viitteet ja valitse Microsoft Outlook).

Sub SendFIleAsAttachment() 'Declare your variables 'Set reference to Microsoft Outlook Object library Dim OLApp As Outlook.Sovellus Dim OLMail Object ' Open Outlook Aloita uusi sähköpostikohde asettaa OLApp = Uusi Outlook.Sovellussarja OLMail = OLApp.CreateItem (0) OLApp.Istunto.Kirjaudu ' Rakenna sähköpostisi ja lähetä Olmail. To = "[email protected]; [email protected]". CC ="".BCC = "".Subject = "This is the Subject line".Body = "Hei siellä".Liite.Lisää ActiveWorkbook.Koko nimi .Näytä ' muutos kohteeseen .Send to send without reviewing End With " Memory cleanup Set OLMail = Nothing Set OLApp = NothingEnd Sub

15. Lähetä kaikki Excel-kaaviot PowerPoint-esitykseen

erittäin kätevä makro, jonka avulla voit lisätä kaikki Excel-kaaviot Powerpoint-esitykseesi yhdellä napsautuksella:

Sub SendExcelFiguresToPowerPoint() 'Set reference to Microsoft Powerpoint Object Library 'julistaa muuttujasi Dim PP Powerpointiksi.Sovellus Dim PPPres PowerPoint.Esitys Dim PPSlide PowerPoint.Slide Dim i kuin kokonaisluku ' Tarkista kaaviot; poistu, jos taulukoita ei ole("Diadata").Valitse Jos ActiveSheet.ChartObjects.Count < 1 Then MsgBox" no charts existing the active sheet " Exit Sub End If 'Open PowerPoint and create new presentation Set PP = New PowerPoint.Sovellussarja PPPres = PP.Esitys.Lisää PP.Visible = True ' Aloita silmukka kaavion perusteella, jonka mukaan I = 1 on ActiveSheet.ChartObjects.Count ' Kopioi kaavio kuva ActiveSheet.ChartObjects (i).Kaavio.CopyPicture _ Size: = xlScreen, Format:=xlPicture Application.Odota (Nyt + TimeValue ("0: 00:1")) ' Laske liukumäet ja lisää uusi liukumäki seuraavan käytettävissä olevan liukumäärän ppSlideCount = PPPres.Diaesitys.Laskusarja PPSlide = PPPres.Diaesitys.Lisää (SlideCount + 1, ppLayoutBlank) PPSlide.Valitse ' Liitä kuva ja säädä sen sijainti; siirry seuraavaan kaavioon PPSlide.Muoto.Liittää.Valitse PP.ActiveWindow.Valinta.ShapeRange.Kohdista msoAlignCenters, True PP.ActiveWindow.Valinta.ShapeRange.Align msoAlignMiddles, True Next I ' Memory Cleanup Set PPSlide = Nothing Set Pppres = Nothing Set PP = NothingEnd Sub

16. Lähetä Excel-taulukko MS Wordissa

Excel-taulukot laitetaan yleensä tekstidokumenttien sisään. Tässä on automaattinen tapa viedä Excel-taulukkosi MS Wordiin:

 Sub ExcelTableInWord() 'Set reference to Microsoft Word Object library 'julista muuttujasi Dim MyRange Exceliksi.Alue Dim wd sanana.Sovellus Dim wdDoc sanana.Asiakirja Dim WdRange Sanana.Vaihteluväli ' Kopioi määritellyt vaihteluväli-arkit ("tulotaulukko").Alue ("B4: F10").Cop ' avaa kohdesana-asiakirjasarja wd = uusi sana.Sovellussarja wdDoc = wd.Asiakirja.Avaa _ (Tämä Työkirja.Path && " PasteTable.docx") wd.Visible = True ' Set focus on the target bookmark Set wdrange = wdDoc.Kirjanmerkit ("DataTableHere").Alue ' Poista vanha pöytä ja liitä Uusi virhe jatkaa seuraavan WdRange.Taulukot (1).Poista WdRange.Liitä 'liitä taulukossa' Säädä sarakkeen leveydet WdRange.Taulukot (1).Sarake.SetWidth _ (MyRange.Width / MyRange.Sarake.Count), wdAdjustSameWidth 'Aseta Kirjanmerkki wddoc.Kirjanmerkki.Lisää "DataTableHere", Wdrangen " Memory cleanup Set wd = Nothing Set wdDoc = Nothing Set WdRange = NothingEnd Sub

17. Poimia tietyn sanan solusta

Voimme käyttää kaavoja, jos haluamme poimia tietyn määrän symboleja. Mutta entä jos haluamme erottaa solussa olevasta lauseesta tai sanavalikoimasta vain toisen sanan? Voit tehdä tämän, voimme luoda mukautetun Excel-toiminto VBA. Tämä on yksi kaikkein iportant VBA toiminnot, koska sen avulla voit luoda omia toimintoja, jotka ovat olemattomia MS Excel. Jatketaan ja luodaan kaksi toimintoa: findword () ja findwordrev (). Tässä VBA-koodi tälle:

Function FindWord(lähde merkkijonona, sijainti kokonaislukuna) kuten String On Error Resume Next FindWord = Split(WorksheetFunction.Trim(Source),"") (Position - 1) on ERR GoTo 0End FunctionFunction FindWordRev (Source As String, Position As Integer) As String Dim Arr () As String Arr = VBA.Split (WorksheetFunction.Trim(Source), " ") on ERR Resume Next FindWordRev = Arr(UBound (Arr) - Position + 1) on ERR GoTo 0End Function

Very nice, we have created two cstom Excel functions. Yritä nyt käyttää niitä Excelissä. Funktio = FindWordRev (A1,1) ottaa viimeisen sanan solusta A1. Funktio = FindWord (A1, 3) ottaa kolmannen sanan solusta A1 jne.

18. Suojaa työkirjasi

joskus haluamme suojata tiedostossamme olevia tietoja niin, että vain me voimme muuttaa niitä. Näin voit tehdä tämän VBA: n kanssa:

Alisuojataulukot() 'ilmoita muuttujasi Dim ws Laskentataulukoksi 'Aloita silmukointi kaikkien laskentataulukoiden kautta jokaiselle ws: lle Activeworkbookissa.Laskentataulukot ' Suojaa ja silmukka seuraavaan laskentataulukkoon ws.Protect Password:=" 1234 " Next wsEnd Sub

Onneksi olkoon! Koska olet vielä lukea tätä, olet todella innokas oppimaan VBA. Kuten olet jo nähnyt itse, VBA ohjelmointikieli on erittäin hyödyllinen ja voi säästää paljon aikaa. Toivottavasti löysit nämä tiedot hyödyllisiä ja käyttää sitä tulla master MS Excel, VBA ja tietokoneohjelmistot yleensä.