Laskentataulukoiden käyttäminen Excel VBA: n avulla (selitetty esimerkeillä)

Solujen ja alueiden lisäksi laskentataulukoiden kanssa työskentely on toinen alue, jonka sinun pitäisi tietää VBA: n tehokkaasta käytöstä Excelissä.

Kuten kaikilla VBA -objekteilla, laskentataulukoilla on erilaisia ​​ominaisuuksia ja menetelmiä, joita voit käyttää automatisoidessasi työtäsi VBA: n kanssa Excelissä.

Tässä opetusohjelmassa käsittelen "laskentataulukoita" yksityiskohtaisesti ja näytän myös joitain käytännön esimerkkejä.

Aloitetaan siis.

Kaikki tässä opetusohjelmassa mainitsemani koodit on sijoitettava VB -editoriin. Siirry VBA -koodin sijoitusosaan saadaksesi tietää, miten se toimii.

Jos olet kiinnostunut VBA: n oppimisesta helpolla tavalla, tutustu minun Online Excel VBA -koulutus.

Ero laskentataulukoiden ja taulukkojen välillä VBA: ssa

VBA: ssa sinulla on kaksi kokoelmaa, jotka voivat olla joskus hämmentäviä.

Työkirjassa voi olla laskentataulukoita ja kaavioarkkeja. Alla olevassa esimerkissä on kolme laskentataulukkoa ja yksi kaavio.

Excel VBA: ssa:

• Työarkit -kokoelma viittaisi työkirjan kaikkien laskentataulukko -objektien kokoelmaan. Yllä olevassa esimerkissä Työarkit -kokoelma koostuisi kolmesta laskentataulukosta.
• Sheets -kokoelma viittaisi kaikkiin työkirjan laskentataulukoihin ja kaavioarkkeihin. Yllä olevassa esimerkissä siinä olisi neljä elementtiä - 3 laskentataulukkoa + 1 kaavioarkki.

Jos sinulla on työkirja, jossa on vain laskentataulukoita eikä kaavioarkkeja, työkirjat ja taulukot -kokoelma ovat samat.

Mutta jos sinulla on yksi tai useampi kaavio, Sheets -kokoelma olisi suurempi kuin Worksheets -kokoelma

Taulukot = laskentataulukot + kaavioarkit

Tällä erolla suosittelen olemaan mahdollisimman täsmällinen VBA -koodia kirjoittaessasi.

Joten jos sinun on viitattava vain laskentataulukoihin, käytä "Työarkit" -kokoelmaa ja jos sinun on viitattava kaikkiin arkeihin (mukaan lukien kaavioarkit), käytä "Taulukot" -kokoelmaa.

Tässä opetusohjelmassa käytän vain "Työarkit" -kokoelmaa.

Viittaus laskentataulukkoon VBA: ssa

VBA: n laskentataulukkoon voi viitata monella eri tavalla.

Laskentataulukoihin viittaaminen auttaa sinua kirjoittamaan parempaa koodia, varsinkin kun käytät silmukoita VBA -koodissasi.

Laskentataulukon nimen käyttäminen

Helpoin tapa viitata laskentataulukkoon on käyttää sen nimeä.

Oletetaan esimerkiksi, että sinulla on työkirja, jossa on kolme laskentataulukkoa - Taulukko 1, Taulukko 2, Taulukko 3.

Ja haluat aktivoida arkin 2.

Voit tehdä sen käyttämällä seuraavaa koodia: Sub ActivateSheet () Työsivut ("Sheet2"). Aktivoi End Sub

Yllä oleva koodi pyytää VBA: ta viittaamaan laskentataulukkoon Sheet2 ja aktivoimaan sen.

Koska käytämme tarkkaa taulukon nimeä, voit käyttää myös Sheets -kokoelmaa täällä. Joten alla oleva koodi tekisi myös saman asian.

Aliaktivoi taulukon () taulukot ("arkki 2")

Indeksinumero

Vaikka taulukon nimen käyttäminen on helppo tapa viitata laskentataulukkoon, joskus et ehkä tiedä laskentataulukon tarkkaa nimeä.

Jos esimerkiksi käytät VBA -koodia uuden laskentataulukon lisäämiseen työkirjaan etkä tiedä, kuinka monta laskentataulukkoa on jo olemassa, et tiedä uuden laskentataulukon nimeä.

Tässä tapauksessa voit käyttää laskentataulukoiden indeksinumeroa.

Oletetaan, että sinulla on seuraavat taulukot työkirjassa:

Alla oleva koodi aktivoi Sheet2:

Sub ActivateSheet () Laskentataulukot (2) Aktivoi End Sub

Huomaa, että olemme käyttäneet indeksinumeroa 2 tuumaa Laskentataulukot (2). Tämä viittaisi laskentataulukoiden kokoelman toiseen kohteeseen.

Mitä tapahtuu, kun käytät indeksinumerona 3?

Se valitsee Sheet3.

Jos mietit, miksi se valitsi arkin 3, koska se on selvästi neljäs kohde.

Tämä johtuu siitä, että kaavioarkki ei ole osa laskentataulukoiden kokoelmaa.

Joten kun käytämme laskentataulukoiden kokoelman hakemistonumeroita, se viittaa vain työkirjan laskentataulukoihin (ja jättää huomiotta kaavioarkit).

Päinvastoin, jos käytät Sheetsia, Sheets (1) viittaa Sheets1, Sheets (2) viittaavat Sheet2, Sheets (3) viittaavat Chart1 ja Sheets (4) viittaavat Sheet3.

Tämä indeksinumeron käyttötekniikka on hyödyllinen, kun haluat selata kaikkia työkirjan laskentataulukoita. Voit laskea laskentataulukoiden määrän ja selata niitä sitten käyttämällä tätä lukua (näemme kuinka tämä tehdään myöhemmin tässä opetusohjelmassa).

Huomautus: Indeksinumero siirtyy vasemmalta oikealle. Joten jos siirrät taulukkoa2 taulukon 1 vasemmalle puolelle, laskentataulukot (1) viittaavat taulukkoon2.

Laskentataulukon koodinimen käyttäminen

Yksi arkin nimen käytön haitoista (kuten näimme yllä olevassa osassa) on, että käyttäjä voi muuttaa sitä.

Ja jos taulukon nimi on muutettu, koodi ei toimi, ennen kuin muutat laskentataulukon nimen myös VBA -koodissa.

Voit ratkaista tämän ongelman käyttämällä laskentataulukon koodinimeä (tavallisen nimen sijaan, jota olemme käyttäneet tähän mennessä). Koodinimi voidaan määrittää VB Editorissa, eikä se muutu, kun muutat taulukon nimen laskentataulukkoalueelta.

Anna laskentataulukollesi koodinimi seuraavasti:

1. Napsauta Kehittäjä -välilehteä.
2. Napsauta Visual Basic -painiketta. Tämä avaa VB -editorin.
3. Napsauta valikosta Näytä -vaihtoehtoa ja napsauta Projekti -ikkuna. Ominaisuudet -ruutu tulee näkyviin. Jos Ominaisuudet -ruutu on jo näkyvissä, ohita tämä vaihe.
4. Napsauta sen projektinhallinnan taulukon nimeä, jonka haluat nimetä uudelleen.
5. Muuta Ominaisuudet -ruudussa nimi (Nimi) -kentän edessä. Huomaa, että nimessä ei voi olla välilyöntejä.

Yllä olevat vaiheet muuttavat laskentataulukon nimen VBA -taustaohjelmassa. Excel -laskentataulukkonäkymässä voit nimetä laskentataulukon haluamallasi tavalla, mutta taustalla se vastaa sekä nimiin - arkin ja koodin nimen.

Yllä olevassa kuvassa taulukon nimi on "SheetName" ja koodinimi on "CodeName". Vaikka muutat laskentataulukon arkin nimeä, koodinimi pysyy samana.

Voit nyt viitata laskentataulukkoon joko laskentataulukoiden kokoelman avulla tai käyttää koodinimeä.

Esimerkiksi molemmat rivit aktivoivat laskentataulukon.

Laskentataulukot ("Sheetname"). Aktivoi CodeName.Activate

Ero näiden kahden välillä on, että jos muutat laskentataulukon nimen, ensimmäinen ei toimi. Mutta toinen rivi toimisi edelleen, vaikka nimi muuttuisi. Toinen rivi (käyttäen koodinimeä) on myös lyhyempi ja helpompi käyttää.

Viittaus eri työkirjan laskentataulukkoon

Jos haluat viitata toisen työkirjan laskentataulukkoon, sen on oltava auki koodin ajaessa ja sinun on määritettävä työkirjan nimi ja laskentataulukko, johon haluat viitata.

Jos sinulla on esimerkiksi työkirja, jonka nimi on Esimerkit, ja haluat aktivoida taulukon 1 esimerkkityökirjassa, sinun on käytettävä alla olevaa koodia:

Sub SheetActivate () Työkirjat ("Examples.xlsx"). Laskentataulukot ("Sheet1"). Aktivoi End Sub

Huomaa, että jos työkirja on tallennettu, sinun on käytettävä tiedostonimeä laajennuksen kanssa. Jos et ole varma, mitä nimeä käytät, pyydä apua Project Explorerilta.

Jos työkirjaa ei ole tallennettu, sinun ei tarvitse käyttää tiedostopäätettä.

Laskentataulukon lisääminen

Alla oleva koodi lisäisi laskentataulukon (ensimmäisenä laskentataulukkona - eli arkki -välilehden vasemmanpuoleisimpana taulukkona).

Sub AddSheet () -arkit. Lisää End Sub

Se käyttää oletusnimeä Sheet2 (tai mitä tahansa muuta numeroa sen mukaan, kuinka monta arkkia on jo olemassa).

Jos haluat lisätä laskentataulukon ennen tiettyä laskentataulukkoa (esimerkiksi Sheet2), voit käyttää alla olevaa koodia.

Sub AddSheet () Worksheets.Add Before: = Laskentataulukot ("Sheet2") End Sub

Yllä oleva koodi kehottaa VBA: ta lisäämään taulukon ja määrittelee sitten "Ennen" -lausekkeen avulla laskentataulukon, jonka eteen uusi laskentataulukko lisätään.

Voit myös lisätä taulukon laskentataulukon (esimerkiksi Sheet2) jälkeen käyttämällä alla olevaa koodia:

Sub AddSheet () Worksheets.Add After: = Laskentataulukot ("Sheet2") End Sub

Jos haluat lisätä uuden arkin arkkien loppuun, sinun on ensin tiedettävä, kuinka monta arkkia on. Seuraava koodi laskee ensin arkkien lukumäärän ja lisää uuden arkin viimeisen arkin jälkeen (johon viitataan indeksinumeron avulla).

Sub AddSheet () Dim SheetCount As Integer SheetCount = Työarkit. Laske laskentataulukot. Lisää jälkeen: = Laskentataulukot (SheetCount) Loppuosa

Laskentataulukon poistaminen

Alla oleva koodi poistaisi aktiivisen taulukon työkirjasta.

Sub DeleteSheet () ActiveSheet.Delete End Sub

Yllä oleva koodi näyttää varoituksen ennen laskentataulukon poistamista.

Jos et halua nähdä varoitusta, käytä alla olevaa koodia:

Sub DeleteSheet () Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = Todellinen loppuosa

Kun Application.DisplayAlerts on asetettu arvoon False, se ei näytä varoitusta. Jos käytät sitä, muista asettaa se takaisin arvoon Tosi koodin lopussa.

Muista, että et voi kumota tätä poistoa, joten käytä yllä olevaa koodia, kun olet täysin varma.

Jos haluat poistaa tietyn taulukon, voit tehdä sen käyttämällä seuraavaa koodia:

Sub DeleteSheet () -työarkit ("Sheet2"). Poista End Sub

Voit myös käyttää taulukon koodinimeä sen poistamiseen.

Sub DeleteSheet () Sheet 5.Delete End Sub

Laskentataulukoiden nimeäminen uudelleen

Voit muuttaa laskentataulukon nimen ominaisuutta muuttaaksesi sen nimen.

Seuraava koodi muuttaa taulukon 1 nimen yhteenvetoksi.

Sub RenameSheet () -työarkit ("Sheet1"). Name = "Summary" End Sub

Voit yhdistää tämän arkin lisäämismenetelmän kanssa, jotta saat joukon arkeja tietyillä nimillä.

Jos esimerkiksi haluat lisätä neljä arkkia, joiden nimi on 2021-2022 Q1, 2021-2022 Q2, 2021-2022 Q3 ja 2021-2022 Q4, voit käyttää alla olevaa koodia.

Sub RenameSheet () Dim Laskentataulukot kokonaislukulaskentataulukoina = Laskentataulukot End Sub

Yllä olevassa koodissa laskemme ensin arkkien lukumäärän ja lisäämme sitten uudet taulukot loppuun käyttämällä Seuraava -silmukkaa. Kun taulukko lisätään, koodi myös nimeää sen uudelleen.

Laskentataulukko -objektin määrittäminen muuttujaan

Kun käytät laskentataulukoita, voit määrittää laskentataulukon objektimuuttujalle ja käyttää muuttujaa laskentataulukon viitteiden sijaan.

Jos esimerkiksi haluat lisätä vuoden etuliitteen kaikkiin laskentataulukoihin, voit käyttää objektimuuttujaa sen sijaan, että laskisit taulukot ja suoritat silmukan monta kertaa.

Tässä on koodi, joka lisää 2021-2022 etuliitteenä kaikkien laskentataulukoiden nimiin.

Sub RenameSheet () Dim Ws kuin laskentataulukko jokaiselle Ws -lomakkeelle Ws.Name = "2018 -" & Ws.Name Next Ws End Sub

Yllä oleva koodi ilmoittaa muuttujan Ws laskentataulukon tyypiksi (käyttämällä riviä "Dim Ws kuin laskentataulukko").

Nyt meidän ei tarvitse laskea arkkien lukumäärää kiertääksesi nämä. Sen sijaan voimme käyttää "Jokaiselle työkirjan W: lle" -silmukkaa. Tämän avulla voimme käydä läpi kaikki laskentataulukkolomakkeen arkit. Ei ole väliä onko 2 arkkia vai 20 arkkia.

Vaikka yllä olevan koodin avulla voimme käydä läpi kaikki taulukot, voit myös määrittää tietyn taulukon muuttujalle.

Alla olevassa koodissa määritämme muuttujan Ws Sheet2: lle ja käytämme sitä pääsemään kaikkiin Sheet2: n ominaisuuksiin.

Sub RenameSheet () Dim Ws as Workheet Set Ws = Worksheets ("Sheet2") Ws.Name = "Summary" Ws.Protect End Sub

Kun olet asettanut laskentataulukon viittauksen objektimuuttujalle (käyttämällä SET -käskyä), kyseistä objektia voidaan käyttää laskentataulukon viitteen sijaan. Tästä voi olla apua, jos sinulla on pitkä monimutkainen koodi ja haluat muuttaa viitettä. Sen sijaan, että teet muutoksen kaikkialla, voit tehdä sen vain SET -lausekkeessa.

Huomaa, että koodi ilmoittaa Ws -objektin laskentataulukon tyyppimuuttujaksi (käyttämällä Dim Ws -riviä laskentataulukkona).

Piilota laskentataulukot VBA: n avulla (piilotettu + erittäin piilotettu)

Laskentataulukoiden piilottaminen ja näyttäminen Excelissä on yksinkertainen tehtävä.

Voit piilottaa laskentataulukon, eikä käyttäjä näe sitä, kun hän avaa työkirjan. He voivat kuitenkin näyttää laskentataulukon helposti napsauttamalla mitä tahansa välilehteä hiiren kakkospainikkeella.

Mutta entä jos et halua heidän pystyvän paljastamaan laskentataulukkoa.

Voit tehdä tämän käyttämällä VBA: ta.

Alla oleva koodi piilottaa kaikki työkirjan laskentataulukot (paitsi aktiivisen taulukon), joten et voi poistaa sen piilottamista napsauttamalla hiiren kakkospainikkeella arkin nimeä.

Sub HideAllExcetActiveSheet () Dim Ws laskentataulukkona kullekin tämän työkirjan Ws: lle.

Yllä olevassa koodissa Ws.Visible -ominaisuus muutetaan muotoon xlSheetVeryHidden.

• Kun Visible -ominaisuudeksi on asetettu xlSheetVisible, taulukko näkyy laskentataulukkoalueella (laskentataulukon välilehdinä).
• Kun Visible-ominaisuudeksi on asetettu xlSheetHidden, taulukko on piilotettu, mutta käyttäjä voi poistaa sen piilottamisen napsauttamalla hiiren kakkospainikkeella mitä tahansa välilehteä.
• Kun Näkyvä -ominaisuudeksi on asetettu xlSheetVeryHidden, taulukko on piilotettu, eikä sitä voi poistaa laskentataulukkoalueelta. Sinun täytyy käyttää VBA -koodia tai ominaisuusikkunaa näyttääksesi sen.

Jos haluat piilottaa arkit, jotka voidaan avata helposti, käytä alla olevaa koodia:

Sub HideAllExceptActiveSheet () Dim Ws kuin laskentataulukko jokaiselle tämän työkirjan Ws: lle.

Alla oleva koodi paljastaa kaikki laskentataulukot (sekä piilotetut että hyvin piilotetut).

Sub UnhideAllWoksheets () Dim Ws kuin laskentataulukko tämän työkirjan jokaiselle Ws: lle Ws.Visible = xlSheetVisible Next Ws End Sub

Aiheeseen liittyvä artikkeli: Näytä kaikki taulukot Excelissä (kerralla)

Piilota taulukot sen sisältämän tekstin perusteella

Oletetaan, että sinulla on useita arkkeja, joissa on eri osastojen tai vuosien nimet, ja haluat piilottaa kaikki taulukot paitsi ne, joissa on vuosi 2021-2022.

Voit tehdä tämän käyttämällä VBA INSTR -toimintoa.

Alla oleva koodi piilottaa kaikki taulukot paitsi ne, joissa on teksti 2021-2022.

Sub HideWithMatchingText () Dim Ws laskentataulukkona jokaiselle laskentataulukon Ws: lle Jos InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Sitten Ws.Visible = xlSheetPiilotettu loppu, jos seuraava Ws End Sub

Yllä olevassa koodissa INSTR -funktio palauttaa merkin paikan, josta se löytää vastaavan merkkijonon. Jos se ei löydä vastaavaa merkkijonoa, se palauttaa 0.

Yllä oleva koodi tarkistaa, onko nimessä teksti 2021-2022. Jos näin on, mitään ei tapahdu, muuten laskentataulukko on piilotettu.

Voit viedä tämän askeleen pidemmälle ottamalla tekstin soluun ja käyttämällä kyseistä solua koodissa. Näin voit saada arvon soluun ja sitten, kun suoritat makron, kaikki taulukot, lukuun ottamatta sitä, jossa on vastaava teksti, jäävät näkyviin (sekä taulukot, joihin kirjoitat arvon solu).

Laskentataulukot aakkosjärjestyksessä

VBA: n avulla voit lajitella laskentataulukot nopeasti niiden nimien perusteella.

Jos sinulla on esimerkiksi työkirja, jossa on taulukkoja eri osastoille tai vuosille, voit alla olevan koodin avulla lajitella nämä taulukot nopeasti nousevaan järjestykseen.

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 If Sheets (j). Sheets (i). Nimi Sitten Sheets (j). Siirrä ennen: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub

Huomaa, että tämä koodi toimii hyvin tekstinimien kanssa ja useimmissa tapauksissa myös vuosien ja numeroiden kanssa. Mutta se voi antaa sinulle vääriä tuloksia, jos arkin nimet ovat 1,2,11. Se lajittelee ja antaa sinulle järjestyksen 1, 11, 2. Tämä johtuu siitä, että se vertaa tekstinä ja pitää kahta suurempaa kuin 11.

Suojaa/poista kaikkien arkkien suojaus kerralla

Jos työkirjassa on paljon laskentataulukoita ja haluat suojata kaikki taulukot, voit käyttää alla olevaa VBA -koodia.

Sen avulla voit määrittää salasanan koodissa. Tarvitset tämän salasanan laskentataulukon suojauksen poistamiseen.

Sub ProtectAllSheets () Dim ws laskentataulukkona Dim password as String password = "Test123" 'korvata Test123 haluamallasi salasanalla.

Seuraava koodi poistaisi kaikkien arkkien suojauksen kerralla.

Sub ProtectAllSheets () Dim ws laskentataulukkona Dim password as String password = "Test123" 'korvata Test123 salasanalla, jota käytit samalla kun suojaat jokaiselle ws: lle laskentataulukoille ws.Suojaa salasana: = salasana Next ws End Sub

Kaikkien laskentataulukoiden sisällysluettelon luominen (hyperlinkit)

Jos työkirjassa on joukko laskentataulukoita ja haluat lisätä nopeasti yhteenvedon, jossa on linkit kaikkiin arkeihin, voit käyttää alla olevaa koodia.

Sub AddIndexSheet () Worksheets.Add ActiveSheet.Name = "Index" For i = 2 To Sheets.Count ActiveSheet.Hyperlinks.Add Anchor: = Solut (i - 1, 1), _ Osoite: = "", SubAddress: = Tehtävät (i) .Nimi & "! A1", _ TextToDisplay: = Laskentataulukot (i) .Nimi Seuraava i Loppuosa

Yllä oleva koodi lisää uuden laskentataulukon ja nimeää sen Indeksiksi.

Sitten se kiertää kaikki laskentataulukot ja luo hyperlinkin kaikille hakemistoarkin laskentataulukoille.

Minne laittaa VBA -koodi

Mietitkö, mihin VBA -koodi menee Excel -työkirjassa?

Excelissä on VBA -taustaohjelma, jota kutsutaan VBA -editoriksi. Sinun on kopioitava ja liitettävä koodi VB Editor -moduulin koodi -ikkunaan.

Tässä on seuraavat vaiheet:

1. Siirry Kehittäjä -välilehdelle.
2. Napsauta Visual Basic -vaihtoehtoa. Tämä avaa VB -editorin taustaohjelmaan.
3. Napsauta hiiren kakkospainikkeella mitä tahansa työkirjan objektia VB Editorin Project Explorer -ruudussa, johon haluat lisätä koodin. Jos et näe Project Exploreria, siirry Näytä -välilehteen ja napsauta Project Explorer.
4. Siirry Lisää -kohtaan ja napsauta Moduuli. Tämä lisää moduuliobjektin työkirjaasi.
5. Kopioi ja liitä koodi moduuli -ikkunaan.

Saatat pitää myös seuraavista Excel VBA -opetusohjelmista:

• Työkirjojen käyttäminen VBA: n avulla.
• IF IF: n muiden lausuntojen käyttäminen VBA: ssa.
• Seuraava silmukka VBA: ssa.
• Käyttäjän määrittämän toiminnon luominen Excelissä.
• Makron tallentaminen Exceliin.
• Makron suorittaminen Excelissä.
• Excel VBA -tapahtumat - helppo (ja täydellinen) opas.
• Kuinka luoda apuohjelma Excelissä.
• Makron tallentaminen ja uudelleenkäyttö Excelin henkilökohtaisen makron työkirjan avulla.