Makrokomandų programavimas programoje „Excel“: išsamus žingsnis po žingsnio vadovas

Informatec Digital » programavimas » Makrokomandų programavimas programoje „Excel“: išsamus žingsnis po žingsnio vadovas

Jei „Excel“ programoje nuolat atliekate tas pačias užduotis, makrokomandos yra geriausias jūsų draugas, taupantis laiką. Su jomis galite automatizuoti ištisas veiksmų sekas (sužinokite daugiau apie VBA programavimą ), nuo formatavimo iki duomenų importavimo ar skaičiavimų atlikimo, ir suaktyvinti jas bet kada spustelėdami arba sparčiuoju klavišu.

Kuriant makrokomandą, „Excel“ įrašo jūsų paspaudimus ir klavišų paspaudimus ir tiksliai juos pakartotinai vykdo. Tada galite pakoreguoti kodą, kad tiksliai suderintumėte vykdymą, ir peržiūrėti „Excel“ išteklius . Įprasta pradėti nuo makrokomandų įrašymo įrankio, o tada jį patobulinti naudojant VBA, taip pasiekiant didesnę kontrolę, lankstumą ir išvengiant klaidų dėl neatsargumo.

Kas tiksliai yra „Excel“ makrokomanda?

Makrokomanda – tai instrukcijų, kurias „Excel“ vykdo nustatyta tvarka, sąrašas . Tai gali būti paprasta, pavyzdžiui, procentinio stiliaus taikymas, arba sudėtinga, pavyzdžiui, ataskaitos su suvestinėmis lentelėmis parengimas , jos formatavimas, darbalapių kūrimas ir el. laiško siuntimas programoje „Outlook“. Makrokomandos išsaugomos jūsų darbaknygėje ir gali būti vykdomos bet kada.

Jums nereikia žinoti, kaip programuoti, kad pradėtumėte: įrašymo programa konvertuoja jūsų veiksmus į VBA kodą. Nepaisant to, tam tikros VBA žinios leis jums kurti pasirinktines funkcijas, formas ir net mini programas programoje „Excel“.

Kodėl naudoti makrokomandas? Tikri privalumai

Pagrindinis makrokomandos tikslas – išvengti pasikartojančių užduočių ir sumažinti klaidų skaičių . Jei kiekvieną mėnesį rengsite tą pačią ataskaitą, makrokomanda apsaugos jus nuo mechaninių veiksmų ir leis sutelkti dėmesį į tai, kas svarbu.

Privalumai apima laiko taupymą, nuoseklų vykdymą (be praleistų veiksmų) ir galimybę kurti sparčiuosius klavišus bei mygtukus, kad pagreitintų procesus . Jei „Excel“ naudojate tik retkarčiais, tai gali būti neverta; bet jei naudosite kasdien, skirtumą pastebėsite nuo pirmos dienos.

Įjunkite skirtuką „Kūrėjas“ („Windows“ ir „Mac“)

Pagal numatytuosius nustatymus skirtukas „Kūrėjas“ yra paslėptas. „Windows“ sistemoje dešiniuoju pelės mygtuku spustelėkite juostelę ir pasirinkite „Tinkinti juostelę“. Pažymėkite „Kūrėjas“ ir spustelėkite „Gerai“. Po to šalia „Rodymas“ matysite skirtuką „Kūrėjas“.

„Mac“ kompiuteryje eikite į „Excel“ > „Nuostatos“ > „Įrankių juostos ir juostelė“. Skiltyje „Pagrindiniai skirtukai“ pasirinkite „Kūrėjas“ ir išsaugokite. Tai suteiks jums prieigą prie įrašymo įrankio, VBA ir valdiklių . Jei esate naujokas „Excel“, peržiūrėkite pagrindinį „Excel“ vadovą , kad susipažintumėte su juostele ir jos parinktimis.

Kaip kurti makrokomandas naudojant įrašymo įrenginį

Diktofonas puikiai tinka pradžiai. Eikite į „Kūrėjas“ > „Įrašyti makrokomandą“ (arba „Windows“ sistemoje – „Alt“ + „T“ + „M“ + „R“) ir priskirkite aprašomąjį pavadinimą. Atminkite: pavadinimas turi prasidėti raide, be tarpų ar simbolių.

Pasirinkite, kur išsaugoti: daugeliu atvejų veiks „Ši darbaknygė“. Jei norite, kad ji visada būtų pasiekiama, pasirinkite „Asmeninė makrokomandų darbaknygė“ ir „Excel“ sukurs / išsaugos ją faile Personal.xlsb , kuris atidaromas paslėptai naudojant „Excel“.

Pasirinktinai galite pridėti spartųjį klavišą. Norint išvengti konfliktų (pvz., prarasti Ctrl+Z), rekomenduojama naudoti sparčiuosius klavišus su Ctrl+Shift. Taip pat galite parašyti trumpą , aiškų aprašymą (labai naudinga, kai turite daug makrokomandų).

Spustelėkite „Gerai“ ir atlikite veiksmus, kuriuos norite įrašyti. Pavyzdžiui: pasirinkite A1 ir pritaikykite formatą „Procentai“. Venkite nereikalingų paspaudimų ar pasirinkimo pakeitimų, kurie nėra numatyto proceso dalis, nes įrašymo įrenginys įrašo beveik viską.

Baigę sustabdykite įrašymą. Norėdami paleisti, eikite į Kūrėjas > Makrokomandos (arba paspauskite Alt + F8), pasirinkite makrokomandą ir spustelėkite Vykdyti. Pamatysite, kaip „Excel“ pakartos veiksmus ta pačia tvarka.

Makrokomandų vykdymas, redagavimas ir valdymas

Makrokomandas galite vykdyti, redaguoti arba naikinti pasirinkę Kūrėjas > Makrokomandos (Alt+F8). Pasirinkus Redaguoti, atsidarys „Visual Basic“ rengyklė su įrašytu kodu, kurį galite išvalyti ir optimizuoti (jame dažnai būna pasikartojančių veiksmų).

Makrokomandos priskyrimas mygtukui ar formai yra labai patogus: įterpkite formą, spustelėkite dešiniuoju pelės mygtuku > Priskirti makrokomandą, pasirinkite makrokomandą ir viskas. Spustelėjus formą, ji bus paleista akimirksniu.

Kad jis visada būtų lengvai pasiekiamas greitosios prieigos įrankių juostoje, atidarykite įrankių juostos meniu (rodyklė žemyn) > Daugiau komandų > skiltyje „Komandos, pasiekiamos“ pasirinkite „Makrokomandos“, pridėkite savo ir, jei norite, pakeiskite piktogramą. Dabar turėsite nuolatinį mygtuką net ir tada, kai nesate kūrėjo režime.

Jei reikia pakartotinai panaudoti makrokomandą kitoje darbaknygėje, galite nukopijuoti modulį iš „Visual Basic“ redaktoriaus. Atidarykite VBE (Alt+F11), nuvilkite modulį į kitą atidarytą darbaknygę arba naudokite funkciją „Eksportuoti/importuoti .bas failą“.

Makrokomandų programavimas programoje „Excel“ naudojant „Visual Basic“ (VBA) redaktorių

Norėdami pereiti už įrašymo įrenginio ribų, atidarykite redaktorių paspausdami Alt+F11. Įterpkite modulį (Įterpti > Modulis) ir sukurkite pirmąją subrutinę. Pagrindinis šablonas yra Sub Name() … End Sub.

Pavyzdžiui, norėdami rodyti pranešimą ir įrašyti reikšmę į langelį A1, galite pradėti nuo kažko paprasto. Taip suprasite struktūrą ir kaip iškviesti makrokomandą iš „Excel“ (Alt+F8) arba priskirti ją mygtukui, naudodami labai kelias kodo eilutes :

Sub Primera_Macro()
    Range("A1").Value = "Esto de las macros mola."
    MsgBox "Hola, querido amigo."
End Sub

Dar vienas greitas procentinio stiliaus taikymo dabartiniam pasirinkimui pavyzdys. Ši makrokomanda atkartoja tai, ką darėte su įrašymo įrankiu , bet įvedėte tiesiogiai:

Sub FormatoPorcentaje()
    Selection.Style = "Percent"
End Sub

Idealus derinys yra toks: įrašymas, siekiant pagreitinti procesą, tada VBE atidarymas, norint išvalyti nereikalingus veiksmus, pridėti kintamuosius, sąlygas ar ciklus. Tokiu būdu „pažodinį“ įrašą paverčiate patikima ir daugkartinio naudojimo makrokomanda.

Absoliučios ir santykinės nuorodos

Pagal numatytuosius nustatymus įrašymo programa kuria makrokomandas absoliučiu režimu: ji visada veikia tas pačias ląsteles (pvz., B3), nepriklausomai nuo to, kur esate vykdymo metu. Jei duomenys pakeičia poziciją jūsų procese, jums gali būti ne tai, ko reikia.

Pasirinkus „Naudoti santykines nuorodas“, makrokomanda veikia aktyvaus langelio atžvilgiu, kai pradedama vykdyti. Pavyzdžiui, jei pradėjote įrašinėti langelyje B3 ir įvedėte „Vardas“, „Pavardė“ ir pan., judėdami į dešinę, paleidus iš langelio C4, ta pati informacija bus įvesta langelių C4, D4, E4 ir pan. atžvilgiu .

Tipiškas pavyzdys: pavadinimų pridėjimas prie lentelės, kuri kartais prasideda langelyje B3, o kartais – langelyje C5. Absoliučiuoju režimu visada būtų rašoma langelyje B3. Santykiniu režimu jis prisitaikys prie pasirinkto pradinio langelio.

Dažniausios klaidos registruojant procesus

Dažna klaida – įrašyti formulę „nutempiant“, tarkime, tris eilutes ir manyti, kad tai veiks ilgesnėms lentelėms. Įrašymo įrenginys užregistruos kažką atitinkančio „ užpildyti tris langelius žemyn “, o ne „iki lentelės pabaigos“.

Sprendimas – pakeisti savo metodą: naudoti dinaminius diapazonus (pavyzdžiui, aptikti paskutinę eilutę), ciklus arba metodą „CurrentRegion“ arba pakoreguoti makrokomandą VBA po jos įrašymo, kad ji prisitaikytų prie bet kokio dydžio.

Gera praktika įrašant

Prieš įrašydami, kelis kartus rankiniu būdu atlikite procesą, kad jį patobulintumėte. Kurkite trumpus, konkrečius įrašus , o ne vieną didelį; taip juos bus lengviau prižiūrėti ir sujungti.

Venkite nereikalingų spustelėjimų, slinkimo per juostelę ir pasirinkimų, kurie niekuo nepapildo. Makrokomandos pavadinimą ir aprašymą pateikite aiškiai, kad jūs ar bet kuris kolega galėtumėte iš karto suprasti jos paskirtį.

Makrokomandų vykdymas įvairiais būdais

Be dialogo lango „Makrokomandos“ (Alt+F8), makrokomandas galite paleisti naudodami figūrą, mygtuką greitosios prieigos įrankių juostoje arba atidarydami darbaknygę. Galite netgi leisti „Excel“ programai paleisti makrokomandą iš kitų „Office“ programų, o tai leis automatizuoti užduotis keliose programose (pvz., lentelės atnaujinimą ir el. laiško siuntimą naudojant „Outlook“).

Gerai pagalvokite apie sparčiuosius klavišus: jei priskirsite makrokomandą Ctrl+Z, prarasite galimybę anuliuoti, kai darbaknygė atidaryta. Todėl rekomenduojama naudoti Ctrl+Shift derinius , kad būtų išvengta konfliktų.

Darbaknygių išsaugojimas su makrokomandomis

Kai faile yra VBA kodo, įrašykite jį kaip .xlsm failą, kad kodas būtų išsaugotas ir jį būtų galima vykdyti. Įrašant kaip .xlsx failą, VBA projektas bus sunaikintas . Jei norite visuotinių makrokomandų, naudokite asmeninę makrokomandų darbaknygę (Personal.xlsb).

Saugumas ir makrokomandų įgalinimas

Makrokomandos gali būti blokuojamos atsižvelgiant į jūsų saugos nustatymus. Patikrinkite patikimumo centrą, kad įgalintumėte jas pasirinktinai, ir niekada neleiskite makrokomandų iš nežinomų šaltinių. Suprasdami savo saugos lygį, galite subalansuoti apsaugą ir produktyvumą.

Makrokomandų priskyrimas mygtukams, figūroms ir valdikliams

Norėdami įgalinti vartotojus automatiškai vykdyti makrokomandas, priskirkite makrokomandą mygtukui, figūrai arba valdikliui. Dešiniuoju pelės mygtuku spustelėkite objektą > Priskirti makrokomandą > pasirinkite. Taip pat galite įdėti piktogramą į juostelę arba greitosios prieigos įrankių juostą ir susieti ją su makrokomanda.

Jei naudojate formas arba „ActiveX“ valdiklius, galite jiems priskirti makrokomandas arba įvykių procedūras (pvz., spustelėjus). Tai leidžia sukurti patogesnes sąsajas ir valdomus procesus.

Naudingi makrokomandų pavyzdžiai (VBA)

Šie pavyzdžiai iliustruoja įprastas užduotis. Galite juos įklijuoti į modulį (Alt+F11 > Įterpti > Modulis) ir pritaikyti. Nepamirškite pasirašyti darbaknygių arba pakoreguoti saugos nustatymų, jei to reikalauja jūsų organizacija, kad galėtumėte jas vykdyti be įspėjimų.

Nukopijuokite pažymėtą turinį į kitą vietą tame pačiame lape: Selection.Copy Destination

Sub CopiarSeleccion()
    If TypeName(Selection) = "Range" Then
        Selection.Copy Destination:=Selection.Offset(0, 2)
    Else
        MsgBox "Selecciona un rango de celdas primero."
    End If
End Sub

Spausdinti aktyvų lapą: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Įrašykite failą kaip makrokomandas palaikančią darbaknygę (.xlsm): ActiveWorkbook.SaveAs

Sub GuardarComoXlsm()
    Dim ruta As String
    ruta = ThisWorkbook.Path & Application.PathSeparator & "LibroConMacros.xlsm"
    ActiveWorkbook.SaveAs Filename:=ruta, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Taikyti formatavimą diapazonui (koreguoti diapazoną pagal savo poreikius): Su Range(…)

Sub FormatoRango()
    With Range("A1:D10")
        .Font.Bold = True
        .Interior.Color = RGB(230, 230, 250)
        .Borders.LineStyle = xlContinuous
    End With
End Sub

Raskite reikšmę diapazone ir parodykite pirmąjį atitikmenį: Range(…).Find

Sub BuscarValor()
    Dim celda As Range
    Dim valor As String
    valor = InputBox("¿Qué valor quieres buscar?")
    If valor = "" Then Exit Sub
    Set celda = Range("A1:D1000").Find(What:=valor, LookIn:=xlValues, LookAt:=xlPart)
    If Not celda Is Nothing Then
        MsgBox "Encontrado en: " & celda.Address
        celda.Select
    Else
        MsgBox "No se encontró el valor especificado."
    End If
End Sub

Praktinis atvejis: formatavimas procentais

Įrašymas: pradėkite įrašymą, pavadinkite jį, pasirinkite, kur jį išsaugoti, ir pasirinktinai priskirkite nuorodą bei aprašymą. Pasirinkite langelius ir iš skirtuko „Pagrindinis“ pritaikykite parinktį „Procentai“. Sustabdykite įrašymą ir paleiskite jį paspausdami Alt+F8. Jei norite atkurti minimalistiniu kodu, VBA kalboje naudokite Selection.Style = „Procentai“ .

Patarimas: Įrašymo metu venkite be reikalo perjungti langelius. Šie judesiai pirmyn ir atgal yra įrašomi ir gali sulėtinti bei sugadinti makrokomandą.

Automatizuokite ilgas užduotis: mažos makrokomandos yra geresnės

Jei turite procesą, kurį sudaro daug žingsnių, apsvarstykite galimybę jį padalinti į kelias mažesnes makrokomandas ir jas iškviesti nuosekliai. Tai palengvina priežiūrą, leidžia pakartotinai naudoti blokus ir supaprastina derinimą.

Pavyzdžiui, viena makrokomanda skirta duomenims paruošti, kita – formatuoti, trečia – spaudiniui generuoti, o trečia – eksportuoti į PDF arba siųsti el. paštu . Galite jas iškviesti po vieną arba visas iš karto.

Redaguoti ir išvalyti įrašytą kodą

Įrašymo programa fiksuoja „viską“: pasirinkimus, spustelėjimus ir tabuliacijos keitimus. Atidarius VBE, ji pašalina nereikalingus pasirinkimus (Pasirinkti/Pasirinkimas), pakeisdama juos tiesioginėmis nuorodomis (pvz., Diapazonas("A1").Reikšmė = ... ) ir sugrupuoja formatus į blokus Su/Pabaiga su.

Be to, jame yra kintamųjų ir pagrindinių klaidų apdorojimo funkcijų. Keletas „if“ sakinių ir paprastų patikrinimų padeda išvengti klaidų, kai diapazonas tuščias arba pasirinkimas neatitinka lūkesčių.

Dažniausiai užduodami klausimai

Kaip sukurti vartotojo formą programoje „Excel“? Atidarykite „Visual Basic“ redaktorių (Alt + F11), eikite į Įterpti > Vartotojo forma ir pridėkite valdiklius (teksto laukus, mygtukus ir kt.). Galite ją rodyti naudodami makrokomandą.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Tada priskirkite procedūras valdiklių įvykiams (pvz., mygtuko paspaudimui), kad būtų vykdoma reikiama logika.

Kaip apsaugoti / pašalinti lapo apsaugą naudojant makrokomandas? Naudokite šias procedūras pakeisdami slaptažodį pagal poreikį. Jos leidžia įjungti arba išjungti apsaugą vienu spustelėjimu.

Sub ProtegerHoja()
    ActiveSheet.Protect Password:="segura", AllowFiltering:=True
End Sub

Sub DesprotegerHoja()
    ActiveSheet.Unprotect Password:="segura"
End Sub

Kaip ištrinti makrokomandą? Eikite į Programų kūrėjas > Makrokomandos, pasirinkite makrokomandą ir paspauskite Delete. Jei norite ištrinti visą kodą iš modulio, atidarykite VBE (Alt+F11), raskite modulį projekte, spustelėkite dešiniuoju pelės mygtuku ir pasirinkite Remove Module (pirmiausia galite jį eksportuoti, jei norite išsaugoti).

Paskutinis praktinis patarimas: jei formatuojate mėnesinę ataskaitą taip, kad klientai su neapmokėtais likučiais turėtų būti paryškinti raudonai ir paryškintu šriftu, užrašykite seką vieną kartą, pakoreguokite VBA kodą, kad jis būtų taikomas teisingam diapazonui (naudodami paskutinės eilutės aptikimą arba filtrus), ir išsaugokite makrokomandą asmeninėje makrokomandų darbaknygėje. Kitą kartą užteks paprasto sparčiojo klavišo arba mygtuko, kad formatavimas būtų pritaikytas akimirksniu, nepriekaištingai, per kelias sekundes.