Excel makro ierakstītājam ir daudz jaudas, taču tam ir ierobežojumi. Kā aprakstīts citā rakstā, makro ierakstītājs bieži reģistrē nevajadzīgu kodu un nevar ierakstīt tādas lietas kā loģika vai mijiedarbība ar citām programmām. To var būt grūti izmantot arī garākiem makro - iespējams, iepriekš saskatīsit savu darbību scenāriju, lai izvairītos no dārgām kļūdām.
Šī raksta mērķis ir palīdzēt jums sākt makro kodēšanu VBA no nulles. Jūs uzzināsit, kur tiek glabāti makro, uzrakstīsit pamata makro un apgūsit programmēšanas pamatus VBA, izmantojot mainīgos, loģiku un cilpas.
Darba sākšana
VBA un Visual Basic redaktors
VBA jeb Visual Basic for Applications ir valoda, kurā tiek rakstīti makro. Visi makro tiek saglabāti kā VBA kods neatkarīgi no tā, vai tie ir kodēti ar rokām vai izveidoti ar makro ierakstītāju.
Jūs varat piekļūt visam darbgrāmatas VBA kodam, izmantojot Visual Basic Editor. Šis ir īpašs teksta redaktors un atkļūdotājs, kas ir iebūvēts visās biroja lietotnēs, ieskaitot Excel. Parasti jūs atverat šo redaktoru, izmantojot ALT+F11 īsinājumtaustiņu programmā Excel, taču varat tam piekļūt arī no programmas Excel Izstrādātājs cilni, ja tā ir iespējota.
Projekta pētnieks
The Projektu pētnieks ir logs VB redaktorā, kas parāda visus vienumus, kuros var būt VBA kods. Ja neredzat šo logu, nospiediet F5 lai tas tiktu parādīts vai atlasīts Projektu pētnieks no Skatīt izvēlne.
Veicot dubultklikšķi uz vienuma Project Explorer, tiks parādīts šī vienuma kods. Project Explorer var parādīties vairāku veidu vienumi:
- Darba burtnīcas
- Darba lapas
- UserForms
- Klases moduļi
- Moduļi (šajos vienumos tiek saglabāti makro)
Lai gan visos šajos vienumu veidos var iekļaut VBA kodu, labākā prakse ir kodēt makro moduļos.
Pirmā makro izveidošana
Makro sarakstu izmantošana
Makro sarakstā ir parādīti visi makro darbgrāmatā. Šajā sarakstā varat rediģēt esošu makro vai izveidot jaunu.
Lai izveidotu jaunu makro, izmantojot makro sarakstu:
- Atlasiet cilni Izstrādātājs un noklikšķiniet uz Makro (vai nospiediet ALT+F8)
- Ierakstiet savu makro nosaukumu, pēc tam noklikšķiniet uz “Izveidot”
Pēc noklikšķināšanas uz “Izveidot” parādīsies VB redaktors, kurā parādīts jaunizveidotais makro. Ja nepieciešams, programma Excel izveidos jaunu makro moduli.
Manuāli VB redaktorā
Jūs varat manuāli pievienot jaunu makro bez makro saraksta. Šī ir labākā iespēja, ja vēlaties norādīt moduli, kurā makro ir saglabāts.
Lai manuāli pievienotu makro, veiciet tālāk norādītās darbības.
- Atveriet VB redaktoru (ALT+F11)
- Arī:
- Pievienojiet jaunu moduli, noklikšķinot Ievietot> Modulis izvēlnē (modulis tiks automātiski atvērts)
-
- VAI divreiz noklikšķiniet uz esoša moduļa Project Explorer, lai to atvērtu
- Modulī ierakstiet jaunā makro kodu
Sub MyMacro () Beigu apakš
Šīs divas rindiņas norāda makro nosaukumu ar nosaukumu “MyMacro” (ņemiet vērā iekavas, kas ir obligātas). Tas tiks parādīts Excel dialoglodziņā “Skatīt makro”, un to var piešķirt pogai (lai gan tas vēl neko nedara).
Pievienojiet makro kodam
Tagad pievienosim kodu starp rindām “Sub” un “End Sub”, lai šis makro faktiski kaut ko darītu:
Apakš MyMacro () diapazons (“A1”). Vērtība = “Sveika pasaule!” Beigu apakš
Pamata koda struktūras
Diapazona objekts
Excel VBA izmanto diapazona objektu, lai attēlotu šūnas darblapā. Iepriekš minētajā piemērā ar kodu tiek izveidots diapazona objekts Diapazons (“A1”) lai piekļūtu šūnas A1 vērtībai.
Diapazona objekti galvenokārt tiek izmantoti šūnu vērtību iestatīšanai:
Diapazons (“A1”). Vērtība = 1
Diapazons (“A1”). Vērtība = "Pirmā šūna"
Ņemiet vērā, ka, definējot šūnu vērtības kā skaitļus, jūs vienkārši ievadāt skaitli, bet, ievadot tekstu, teksts jāapņem ar pēdiņām.
Diapazonus var izmantot arī, lai piekļūtu daudziem šūnu rekvizītiem, piemēram, to fontam, apmalēm, formulām un citam.
Piemēram, šūnas fontu varat iestatīt kā treknrakstu šādi:
Diapazons (“A1”). Fonts. Bold = patiesa
Varat arī iestatīt šūnas formulu:
Diapazons (“A1”). Formula = “= Summa (A2: A10)”
Programmā Excel ar kursoru varat atlasīt šūnu bloku (teiksim, no A1 līdz D10) un iestatīt tās visas treknrakstā. Diapazona objekti var piekļūt šūnu blokiem, piemēram:
Diapazons (“A1: D10”). Fonts. Bold = patiesa
Varat arī atsaukties uz vairākām šūnām/blokiem vienlaikus:
Diapazons (“A1: D10, A12: D12, G1”). Fonts. Bold = patiesa
Šim formātam ir tāds pats formāts, kādu izmantotu, atlasot šūnas SUM () formulai programmā Excel. Katrs bloks ir atdalīts ar komatu, un blokus apzīmē ar augšējo kreiso un apakšējo labo šūnu, kas atdalīti ar kolu.
Visbeidzot, diapazona objektos ir iebūvētas metodes, lai darblapā veiktu parastās darbības. Piemēram, iespējams, vēlēsities kopēt dažus datus no vienas vietas uz citu. Šeit ir piemērs:
Diapazons (“A1: D10”). Kopēt diapazonu (“F1”). PasteSpecial xlPasteValues diapazons (“F1”). PasteSpecial xlPasteFormats
Tas kopē šūnas A1: D10 starpliktuvē un pēc tam veic PasteSpecial (), sākot ar šūnu C1 - tāpat kā manuāli programmā Excel. Ņemiet vērā, ka šajā piemērā ir parādīts, kā izmantot PasteSpecial (), lai ielīmētu tikai vērtības un formātus - visām opcijām ir parametri, kurus redzēsit dialoglodziņā Īpašā ielīmēšana.
Šeit ir piemērs, kā ielīmēt “Visi” citā darblapā:
Diapazons (“A1: D10”). Kopēt lapas (“Sheet2”). Diapazons (“A1”). PasteSpecial xlPasteAll
Ja paziņojumi
Ar Ja paziņojums, jūs varat likt koda sadaļai darboties tikai “ja” noteikts apgalvojums ir patiess.
Piemēram, varat padarīt šūnu treknrakstu un iekrāsot to sarkanā krāsā, bet tikai “ja” vērtība šūnā ir mazāka par 100.
Ja diapazons (“A4”). Vērtība <100 Tad diapazons (“A4”). Fonts. Bold = patiesais diapazons (“A4”). Interjers. Krāsa = vbSarkanais beigas Ja
Pareiza If paziņojuma struktūra ir šāda (kvadrātiekavas norāda izvēles komponentus):
Ja tad
[Citādi Ja tad]
[Citādi]
Beigas Ja
Jūs varat iekļaut tik daudz Citādi blokus, kā vēlaties pārbaudīt vairākus nosacījumus. Varat arī pievienot Citādi bloks, kas darbojas tikai tad, ja nav izpildīts neviens no citiem paziņojuma If nosacījumiem.
Šeit ir vēl viens piemērs, kura pamatā ir iepriekšējais, kur šūna tiek formatēta vairākos dažādos veidos atkarībā no vērtības:
Ja diapazons ("A4"). Vērtība <100 Tad diapazons ("A4"). Fonts. Bold = patiesais diapazons ("A4"). Interior.Color = vbSarkanais cits diapazons ("A4"). Vērtība <200 Tad diapazons ( "A4"). Fonts. Trekns = nepatiess diapazons ("A4"). Interjers. Krāsa = vb Dzeltenā diapazona diapazons ("A4"). Fonts. Bold = nepatiess diapazons ("A4"). Interjers. Krāsa = vbZalā beigas Ja
Iepriekš minētajā piemērā šūna ir treknrakstā blokos ElseIf, kur vērtība nav zemāka par 100. Varat ligzda Ja paziņojumi, lai izvairītos no koda dublēšanās, rīkojieties šādi:
Ja diapazons ("A4"). Vērtība <100 Tad diapazons ("A4"). Fonts. Bold = patiesais diapazons ("A4"). Interior.Color = vbRed Else Range ("A4"). Font.Bold = False ' fontu atlocot tikai vienu reizi Ja diapazons ("A4"). Vērtība <200 Tad diapazons ("A4"). Interjers.Krāsa = vb Dzeltens cits diapazons ("A4").
Mainīgie
A Mainīgs ir atmiņas daļa, ko izmanto pagaidu informācijas glabāšanai, kamēr darbojas makro. Tos bieži izmanto cilpās kā atkārtotājus vai, lai saglabātu darbības rezultātu, kuru makro vēlaties izmantot vairākas reizes.
Šeit ir mainīgā piemērs un kā to izmantot:
Sub ExtractSerialNumber () Dim strSerial As String 'šī ir mainīgā deklarācija' 'As String' nozīmē, ka šis mainīgais ir paredzēts, lai turētu tekstu ', iestatot izlikto sērijas numuru: Diapazons ("A4"). Vērtība = "sērijas# 804567-88 '' Parsējiet sērijas numuru no šūnas A4 un piešķiriet to mainīgajam strSerial = Mid (Diapazons ("A4"). Vērtība, 9) 'tagad izmantojiet mainīgo divreiz, tā vietā, lai sērijas numurs būtu jāpārbauda divreiz Range (" B4 ”). Vērtība = strSerial MsgBox strSerial End Sub
Šajā pamata piemērā mainīgais “strSerial” tiek izmantots, lai izvilktu sērijas numuru no šūnas A4, izmantojot funkciju Mid (), un pēc tam tiek izmantots divās citās vietās.
Standarta veids, kā deklarēt mainīgais ir šāds:
Izmērs kāds vārds [Kā tipa]
- kāds vārds ir nosaukums, kuru jūs nolemjat piešķirt savam mainīgajam
- tipa ir mainīgā datu tips
Tika parādīts “[Kā tipa] ”Daļu var izlaist - ja tā, mainīgais tiek deklarēts kā Variant tips, kurā var būt jebkāda veida dati. Lai gan varianti ir pilnīgi derīgi, no tiem jāizvairās, jo tie var novest pie negaidītiem rezultātiem, ja neesat piesardzīgs.
Tur ir noteikumiem mainīgo nosaukumiem. Tiem jāsākas ar burtu vai pasvītras rakstzīmi, nedrīkst būt atstarpes, punkti, komati, pēdiņas vai rakstzīmes “! @ & $ #”.
Šeit ir daži mainīgo deklarāciju piemēri:
Dim strFilename As String "laba vārda stils - aprakstošs un izmanto prefiksu Dim i As Long" slikta nosaukuma stils - pieņemams tikai dažiem iteratoriem Dim Sale - nav pārāk aprakstošs, izmanto prefiksu, nav datu veida
Visos šajos piemēros ir izmantotas nedaudz atšķirīgas nosaukumu shēmas, taču visas ir derīgas. Nav slikta ideja, ja mainīgā nosaukumam tiek pievienots īss tā tipa tips (saskaņā ar dažiem no šiem piemēriem), jo tas padara jūsu kodu vieglāk lasāmu.
VBA ietver daudz pamata datu veidi. Pie populārākajiem pieder:
- Stīga (izmanto teksta datu glabāšanai)
- Ilgi (izmanto veselu skaitļu glabāšanai, t.i., bez komata)
- Dubultā (izmanto, lai turētu peldošā komata skaitļus, t.i., aiz komata)
Pilnu VBA raksturīgo datu veidu sarakstu var atrast šeit: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Diapazona objektu mainīgie
Ir iespējams izveidot arī mainīgos, kas atsaucas uz diapazona objektiem. Tas ir noderīgi, ja vēlaties atsaukties uz noteiktu koda diapazonu vairākās vietās - tādā veidā, ja jums ir jāmaina diapazons, tas jāmaina tikai vienā vietā.
Veidojot diapazona objekta mainīgo, tas ir “jāiestata” diapazona instancē. Piemēram:
Dim rMyRange kā diapazona kopa rMyRange = Diapazons (“A1: A10; D1: J10”)
Atstājot paziņojumu “Iestatīt”, piešķirot diapazona mainīgo, radīsies kļūda.
Cilpas
Cilpas ir bloki, kas noteiktu skaitu reižu atkārto tajos esošo kodu. Tie ir noderīgi, lai samazinātu rakstāmā koda daudzumu, un ļauj uzrakstīt vienu koda gabalu, kas veic tādas pašas darbības daudziem dažādiem saistītiem vienumiem.
Par-Nākamais
A Par-Nākamais bloks ir cilpa, kas atkārtojas noteiktu skaitu reižu. Tas izmanto mainīgo kā iterators lai saskaitītu, cik reizes tas ir palaists, un šo iteratoru mainīgo var izmantot cilpas iekšpusē. Tas padara For-Next cilpas ļoti noderīgas, lai atkārtotu šūnas vai masīvus.
Šeit ir piemērs, kas pārvietojas pa šūnām 1. līdz 100. rindā, 1. slejā un nosaka to vērtības iteratoru mainīgā vērtībai:
Dim i tik ilgi, cik i = 1 līdz 100 šūnas (i, 1). Vērtība = i Tālāk i
Rinda “For i = 1 to 100” nozīmē, ka cilpa sākas no 1 un beidzas pēc 100. Jūs varat iestatīt jebkuru sākuma un beigu numuru, kas jums patīk; šiem skaitļiem varat izmantot arī mainīgos.
Pēc noklusējuma cilpas For-Next tiek skaitītas par 1. Ja vēlaties skaitīt ar citu skaitli, varat rakstīt ciklu ar skaidru Solis klauzula:
Ja i = 5 līdz 100 5. darbība
Šī cilpa sāksies pie 5, pēc tam ikreiz, kad cikls atkārtojas, pievienojiet 5 pie i (tātad otrajā atkārtojumā “i” būs 10, trešajā - 15 un tā tālāk).
Izmantojot Solis, varat arī aprēķināt cilpas atpakaļ:
Attiecībā uz i = 100 līdz 1 Solis -1
Jūs varat arī ligzda For-Next cilpas. Katram blokam ir nepieciešams savs mainīgais, ar ko rēķināties, taču jūs varat izmantot šos mainīgos jebkurā vietā. Šeit ir piemērs tam, kā tas ir noderīgi programmā Excel VBA:
Dim i tik ilgi, j tik ilgi, cik i = 1 līdz 100, ja j = 1 līdz 100 šūnas (i, j). Vērtība = i * j Nākamā j Nākamā i
Tas ļauj pārvietoties pa rindām un kolonnām.
BRĪDINĀJUMS: lai gan tas ir atļauts, NEKAD nemainiet iteratoru mainīgo For-Next blokā, jo tas izmanto šo iteratoru, lai izsekotu cilpu. Iteratoru modificēšana var izraisīt bezgalīgu cilpu un pakārt makro. Piemēram:
Attiecībā uz i = 1 līdz 100 i = 1 Nākamais i
Šajā ciklā “es” nekad nepārsniegs 2, pirms tiks atiestatīts uz 1, un cilpa atkārtosies mūžīgi.
Katram
Katram bloki ir ļoti līdzīgi blokiem Nākamie, izņemot gadījumus, kad tie neizmanto skaitītāju, lai norādītu, cik reizes tie tiek cilpoti. Tā vietā bloks “Par katru” ņem objektu “kolekciju” (piemēram, šūnu diapazonu) un darbojas tik reižu, cik šajā kolekcijā ir objektu.
Šeit ir piemērs:
Dim r Kā diapazons katram r diapazonā ("A15: J54")
Ievērojiet objekta Range mainīgā “r” izmantošanu. Šis ir iterator mainīgais, ko izmanto cilpā For -Every - katru reizi, izmantojot cilpu, “r” iegūst atsauci uz nākamo diapazona šūnu.
For-Every cilpu izmantošanas priekšrocība programmā Excel VBA ir tā, ka varat cilpot cauri visām diapazona šūnām bez ligzdošanas cilpām. Tas var būt parocīgi, ja jums ir jāapmeklē visas šūnas tādā sarežģītā diapazonā kā Diapazons (“A1: D12, J13, M1: Y12”).
Viens For-Every cilpu trūkums ir tāds, ka jūs nevarat kontrolēt šūnu apstrādes secību. Lai gan praksē Excel sakārtos šūnas, teorētiski tas varētu apstrādāt šūnas pilnīgi nejaušā secībā. Ja šūnas jāapstrādā noteiktā secībā, tā vietā izmantojiet cilpas For-Next.
Veikt cilpu
Kamēr For-Next bloki izmanto skaitītājus, lai uzzinātu, kad apstāties, Veikt cilpu bloki darbojas, līdz tiek izpildīts nosacījums. Lai to izdarītu, izmantojiet an Līdz klauzulu bloka sākumā vai beigās, kas pārbauda stāvokli un izraisa cilpas apstāšanos, kad šis nosacījums ir izpildīts.
Piemērs:
Dim str As String str = "Buffalo" Darīt līdz str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" cilpu diapazons ("A1"). Vērtība = str
Šajā ciklā “Buffalo” katru reizi caur cilpu tiek savienots ar “str”, līdz tas atbilst paredzētajam teikumam. Šajā gadījumā pārbaude tiek veikta cilpas sākumā - ja 'str' jau bija gaidītais teikums (kas nav, jo mēs to tā nesākām, bet ja), tad cikls pat nedarbotos .
Jūs varat likt cilpai darboties vismaz vienu reizi, pārvietojot klauzulu līdz beigām līdz šim:
Vai str = str & "" & "Buffalo" cilpa līdz str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
Makro varat izmantot to versiju, kurai ir jēga.
BRĪDINĀJUMS: jūs varat izraisīt bezgalīgu cilpu ar Do-Loop bloku, ja nosacījums līdz brīdim nekad nav izpildīts. Vienmēr rakstiet savu kodu, lai, lietojot šāda veida cilpas, nosacījums Līdz noteikti tiktu izpildīts.
Ko tālāk?
Kad esat apguvis pamatus, kāpēc nemēģināt apgūt dažas progresīvākas metodes? Mūsu apmācība vietnē https://easyexcel.net/excel/learn-vba-tutorial/ balstīsies uz visu, ko šeit esat iemācījies, un paplašinās savas prasmes, izmantojot notikumus, lietotāja formas, koda optimizāciju un daudz ko citu!
