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 kartojate užduotis, makrokomandos yra geriausias jūsų sąjungininkas, padedantis nebegaišti laiko. Su jomis galite automatizuoti visas veiksmų sekas (sužinokite daugiau apie VBA programavimas), nuo formatavimo iki duomenų importavimo ar skaičiavimų atlikimo, ir suaktyvinkite juos bet kada spustelėdami arba sparčiuoju klavišu.

Kuriant makrokomandą, „Excel“ įrašo jūsų paspaudimus ir klavišų paspaudimus ir juos tiksliai vykdo. Tada galite pakoreguoti kodą, kad tiksliai suderintumėte vykdymą ir peržiūrėtumėte „Excel“ ištekliaiĮprasta pradėti nuo įrašymo įrenginio, o tada koreguoti naudojant VBA, gaunant daugiau kontrolės, lankstumo ir nulinės klaidos dėl neatsargumo.

Kas tiksliai yra „Excel“ makrokomanda?

Makrokomanda yra instrukcijų, kurias „Excel“ vykdo nustatyta tvarka, sąrašasTai gali būti taip paprasta, kaip procentinio stiliaus pritaikymas, arba taip sudėtinga, kaip parengti ataskaitą su suvestinėmis lentelėmis, formatuoti, kurti skaičiuokles ir siųsti el. laišką programoje „Outlook“. Makrokomandos išsaugomos jūsų darbaknygėje ir jas galite paleisti bet kada, kai prireikia.

Norint pradėti, nebūtina mokėti programuoti: įrašymo programa jūsų veiksmus paverčia VBA kodu. Tačiau šiek tiek VBA žinių leis jums Kurkite pasirinktines funkcijas, formas ir net programėles programoje „Excel“.

Kodėl naudoti makrokomandas? Tikri privalumai

Pagrindinis makrokomandos tikslas yra vengti pasikartojančio darbo ir sumažinti klaidų skaičiųJei kiekvieną mėnesį rengiate tą pačią ataskaitą, makrokomanda sutaupo jums mechaninių veiksmų ir leidžia sutelkti dėmesį į tai, kas svarbu.

Tarp privalumų yra laiko taupymas, nuoseklus vykdymas (nepamiršant) ir galimybė kurti spartieji klavišai ir mygtukai procesams pagreitintiJei „Excel“ naudojate 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 kūrėjo juostelė yra paslėpta. „Windows“ sistemoje dešiniuoju pelės mygtuku spustelėkite juostelę ir pasirinkite „Tinkinti juostelę“. Pasirinkite „Kūrėjas“ ir patvirtinkite. Po to Šalia „Vista“ 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 įrankių juostos. įrašymo įrenginys, prieiga prie VBA ir valdikliųJei tik pradedate, peržiūrėkite pagrindinis „Excel“ tvarkymas kad padėtų jums naudotis juostele ir parinktimis.

Kaip kurti makrokomandas naudojant įrašymo įrenginį

Puiki pradžia – įrašytuvas. Dalyje „Programuotojas“ > „Įrašyti makrokomandą“ (arba „Windows“ sistemoje – „Alt+T+M+R“) 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“ ją sukurs / išsaugos. Asmeninis.xlsb, kuris atidaromas paslėptas programoje „Excel“.

Pasirinktinai pridėkite spartųjį klavišą. Norint išvengti konfliktų (pvz., prarasti Ctrl+Z), rekomenduojama naudoti derinius su Ctrl+Shift. Taip pat galite įvesti spartųjį klavišą. trumpas ir aiškus aprašymas (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 Diktofonas įrašo beveik viską.

Kai baigsite, sustabdykite įrašymą. Norėdami paleisti, eikite į Programavimo programa > Makrokomandos (arba paspauskite Alt + F8), pasirinkite makrokomandą ir paspauskite Vykdyti. Pamatysite, kaip „Excel“ pakartokite veiksmus tiksliai ta pačia tvarka.

Makrokomandų vykdymas, redagavimas ir valdymas

Programuotojas > Makrokomandos (Alt+F8) galite paleisti, redaguoti arba ištrinti makrokomandas. Pasirinkus Redaguoti, atsidarys „Visual Basic“ redaktorius su įrašytu kodu, kurį galėsite redaguoti. išvalyti ir optimizuoti (paprastai apima pasikartojančius veiksmus).

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ą, bus įvykdytas nedelsiant.

Kad visada turėtumėte jį po ranka greitosios prieigos įrankių juostoje, atidarykite juostos meniu (rodyklė žemyn) > Daugiau komandų > skiltyje „Komandos, prieinamos“ pasirinkite „Makrokomandos“, pridėkite savo ir, jei norite, pakeiskite piktogramą. Dabar turėsite nuolatinis mygtukas net kai nesate „Programmer“ programoje.

Jei norite pakartotinai panaudoti makrokomandą kitoje darbaknygėje, galite nukopijuoti modulį iš „Visual Basic“ redaktoriaus. Atidarykite VBE (Alt+F11), nuvilkite modulį į kitą atidarytą darbaknygę arba naudokite 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 savo pirmąją subrutinę. Pagrindinis šablonas yra Pavadinimas () … Pabaiga poskyryje.

Pavyzdžiui, norėdami parodyti pranešimą ir įrašyti reikšmę į A1 langelį, 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 mažai eilučių:

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

Dar vienas greitas pavyzdys, kaip pritaikyti procentinį stilių dabartiniam pasirinkimui. Ši makrokomanda pakartokite tai, ką darėte su diktofonu, bet parašyta 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 makrokomanda. tvirtas ir daugkartinio naudojimo.

Absoliučios ir santykinės nuorodos

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

Pasirinkus „Naudoti santykines nuorodas“, makrokomanda veikia remdamasi aktyviu langeliu, kai pradeda veikti. Pavyzdžiui, jei pradėjote įrašyti iš B3 ir įvedėte „Vardas“, „Pavardė“ ir pan., judėdami į dešinę, paleidus iš C4, ji tai įrašys iš C4, D4, E4… santykinai.

Tipiškas pavyzdys: lentelės pavadinimas, kuris kartais prasideda B3, o kartais C5. Absoliučiuoju režimu visada rašytumėte B3. Santykiniu režimu jis prisitaikys prie pasirinktas pradinis langelis.

Dažniausios klaidos registruojant procesus

Dažna klaida – įrašyti formulės tempimą, tarkime, trims eilutėms ir manyti, kad tai veiks ilgesnėms lentelėms. Įrašymo įrenginys įrašys kažką panašaus į „užpildykite tris langelius žemyn", o ne "iki stalo galo".

Sprendimas – pakeisti požiūrį: naudoti dinaminiai diapazonai (pvz., aptikti paskutinę eilutę), ciklus arba „CurrentRegion“ metodą, arba pakoreguoti makrokomandą VBA po jos įrašymo taip, kad pritaikomas bet kokiam dydžiui.

Gera praktika įrašant

Prieš įrašydami, kelis kartus atlikite rankinį procesą, kad jį nupoliruotumėte. trumpas ir konkretus vietoj vieno didžiulio; tai leidžia juos lengviau prižiūrėti ir sujungti.

Venkite nereikalingų spustelėjimų, nereikalingo slinkimo ir nereikšmingų pasirinkimų. Makrokomandos pavadinimas ir aprašymas turi būti aiškūs, kad jūs ar komandos nariai galėtų juos suprasti. iš karto atpažįstate jo paskirtį.

Makrokomandų vykdymas įvairiais būdais

Be makrokomandų lauko (Alt+F8), makrokomandas galite paleisti naudodami figūrą, mygtuką greitosios prieigos įrankių juostoje arba atidarę darbaknygę. Galite netgi nustatyti, kad „Excel“ paleistų makrokomandą iš kitų „Office“ programų, taip leisdami jums automatizuoti užduotis kelioms programoms (pavyzdžiui, lentelės atnaujinimas ir el. laiško siuntimas naudojant „Outlook“).

Gerai pagalvokite apie sparčiuosius klavišus: jei priskirsite makrokomandą Ctrl+Z, prarasite galimybę anuliuoti, kol darbaknygė bus atidaryta. Todėl siūloma naudoti derinius su Ctrl + Shift išvengti konfliktų.

Darbaknygių išsaugojimas su makrokomandomis

Kai faile yra VBA kodo, išsaugokite jį kaip .xlsm, kad kodas būtų išsaugotas ir jį būtų galima vykdyti. Jei išsaugosite jį kaip .xlsx, prarasite VBA projektą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 galėtumėte jas įjungti pasirinktinai ir niekada neaktyvuoti makrokomandų iš nežinomų šaltinių. Žinojimas apie savo saugos lygį jums padės. apsaugos ir produktyvumo balansavimas.

Makrokomandų priskyrimas mygtukams, figūroms ir valdikliams

Kad vartotojas galėtų vykdyti makrokomandą negalvodamas, priskirkite ją mygtukui, formai arba valdikliui. Dešiniuoju pelės mygtuku spustelėkite objektą > Priskirti makrokomandą > pasirinkti. Taip pat galite įdėkite piktogramą ant juostelės arba greitosios prieigos juostos ir susiekite jį su makrokomanda.

Jei naudojate formas arba „ActiveX“ valdiklius, galite jiems priskirti makrokomandas arba įvykių procedūras (pavyzdžiui, spustelėjus). Tai leidžia patogesnės vartotojo sąsajos ir valdomi procesai.

Naudingi makrokomandų pavyzdžiai (VBA)

Šie pavyzdžiai iliustruoja įprastas užduotis. Galite juos įklijuoti į modulį (Alt+F11 > Įterpti > Modulis) ir juos pritaikyti. Nepamirškite pasirašyti knygų arba pakoreguoti apsaugos, jei to reikalauja jūsų organizacija. galėti juos paleisti be įspėjimų.

Nukopijuokite pasirinktą turinį į kitą vietą tame pačiame lape: Pasirinkimas.Kopijavimo paskirties vieta

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

Diapazono formatavimas (pritaikykite diapazoną pagal savo poreikius): Su diapazonu (…)

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į: Diapazonas(…).Rasti

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: paleiskite įrašą, pavadinkite jį, pasirinkite, kur jį išsaugoti, ir pasirinktinai priskirkite nuorodą bei aprašymą. Pasirinkite langelius ir meniu Pradėti pritaikykite parinktį „Procentas“. Sustabdykite įrašymą ir paleiskite jį paspausdami Alt+F8. Jei norite leisti minimalistiniu kodu, naudokite Selection.Style = „Procentai“ VBA.

Patarimas: Įrašymo metu nekeiskite langelių be priežasties. Šie atvykimai ir išvykimai yra įrašomi ir gali būti sulėtinkite ir sutepkite makrokomandą.

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

Jei turite procesą, sudarytą iš daugelio žingsnių, apsvarstykite galimybę jį suskaidyti į kelias mažesnes makrokomandas ir jas iškviesti grandinėje. Tai palengvina priežiūrą, leidžia pakartotinai naudoti blokus ir supaprastina derinimą.

Pavyzdžiui, viena makrokomanda duomenims paruošti, kita – formatuoti, dar kita – spausdinimo lapui generuoti, o dar kita – eksportuoti į PDF arba siųsti el.paštuGalite jiems skambinti po vieną arba visiems 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 („Select/Selection“) ir pakeičia juos tiesioginėmis nuorodomis (pvz., Diapazonas("A1").Reikšmė = …) ir grupuoja formatus į blokus „Su“ / „Pabaiga su“.

Taip pat pridedami kintamieji ir pagrindinis klaidų apdorojimas. Keletas „If“ sakinių ir paprasti patikrinimai padeda išvengti klaidų, kai diapazonas yra tuščias arba pasirinkimas ne toks, kokio tikėjotės.

Dažniausiai užduodami klausimai

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

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Tada priskirkite procedūras valdiklių įvykiams (pvz., mygtuko paspaudimas), kad vykdykite jums reikalingą logiką.

Kaip apsaugoti / pašalinti lapo apsaugą naudojant makrokomandas? Naudokite šias procedūras, kad pakeistumėte slaptažodį pagal savo skonį. Jos leidžia jums aktyvuoti arba pašalinti 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 į Programuotojas > Makrokomandos, pasirinkite makrokomandą ir paspauskite Delete. Jei norite ištrinti visą modulio kodą, atidarykite VBE (Alt+F11), suraskite modulį projekte, spustelėkite dešiniuoju pelės mygtuku ir pasirinkite Delete. Pašalinti modulį (jei norite išsaugoti, galite jį pirmiausia eksportuoti).

Paskutinė praktinė pastaba: jei formatuojate mėnesinę ataskaitą, kurioje klientai, turintys skolų, turėtų būti paryškinti raudonai, užrašykite seką vieną kartą, pakoreguokite VBA kodą, kad jis būtų pritaikytas teisingam diapazonui (naudodami paskutinės eilutės aptikimą arba filtrus), ir išsaugokite makrokomandą asmeninėje makrokomandų darbaknygėje. Kitą kartą pakaks nuorodos arba mygtuko, kad formatavimas būtų pritaikytas akimirksniu. nulis gedimų ir per kelias sekundes.