Ukuhlela ama-Macros ku-Excel: Umhlahlandlela ophelele wesinyathelo ngesinyathelo

Informatec Digital » Ukuhlela » Ukuhlela ama-Macros ku-Excel: Umhlahlandlela ophelele wesinyathelo ngesinyathelo

Uma uzithola wenza imisebenzi efanayo ku-Excel ngokuphindaphindiwe, ama-macro angumngane wakho omkhulu wokonga isikhathi. Ngawo, ungenza ngokuzenzakalelayo ukulandelana kwezenzo eziphelele (funda kabanzi mayelana nohlelo lwe-VBA ), kusukela ekufometheni kuya ekungeniseni idatha noma ekwenzeni izibalo, futhi uziqalise noma nini lapho ufuna ngokuchofoza noma ngesinqamuleli sekhibhodi.

Uma udala i-macro, i-Excel iqopha ukuchofoza kwakho kanye nokucindezela izinkinobho bese ikuphinda ikusebenzise ngokwethembeka. Ungabe usulungisa ikhodi ukuze ulungise ukusebenza futhi uhlole izinsiza ze-Excel . Kuvamile ukuqala nge-macro recorder bese uyilungisa nge-VBA, uthole ukulawula okukhulu, ukuguquguquka, kanye namaphutha angekho ngenxa yokunganaki.

Iyini ngempela i-macro ku-Excel?

I-macro iwuhlu lwemiyalelo eyenziwa yi-Excel ngokulandelana okuchaziwe . Kungaba lula njengokusebenzisa isitayela sephesenti noma kube nzima njengokulungiselela umbiko ngamathebula e-pivot , ukuwufometha, ukudala amaphepha okusebenzela, nokuthumela i-imeyili ku-Outlook. Ama-macro agcinwa encwadini yakho yokusebenzela futhi angasebenza noma nini lapho kudingeka.

Akudingeki wazi ukuthi ungahlela kanjani ukuze uqalise: irekhoda ihumusha izenzo zakho zibe yikhodi ye-VBA. Noma kunjalo, ulwazi oluthile lwe-VBA luzokuvumela ukuthi udale imisebenzi yangokwezifiso, amafomu, ngisho nezinhlelo zokusebenza ezincane ngaphakathi kwe-Excel.

Kungani usebenzise amamakhro? Izinzuzo zangempela

Inhloso eyinhloko ye-macro ukugwema imisebenzi ephindaphindwayo nokunciphisa amaphutha . Uma ulungiselela umbiko ofanayo njalo ngenyanga, i-macro izokusindisa ezinyathelweni zomshini futhi ikuvumele ukuthi ugxile kulokho okubalulekile.

Izinzuzo zayo zifaka phakathi ukonga isikhathi, ukusebenza njalo (ngaphandle kwezinyathelo ezingabanjwanga), kanye nekhono lokudala izinqamuleli nezinkinobho zokusheshisa izinqubo . Uma usebenzisa i-Excel ngezikhathi ezithile kuphela, kungase kungabi kuhle; kodwa uma uyisebenzisa nsuku zonke, uzobona umehluko kusukela osukwini lokuqala.

Nika amandla ithebhu kanjiniyela (iWindows ne-Mac)

Ithebhu Yonjiniyela ifihliwe ngokuzenzakalelayo. Ku-Windows, chofoza kwesokudla iribhoni bese ukhetha okuthi "Yenza ngokwezifiso iribhoni." Maka okuthi "Unjiniyela" bese uchofoza okuthi Kulungile. Ngemuva kwalokho, uzobona ithebhu Yonjiniyela eduze kokuthi Buka.

Ku-Mac, iya ku-Excel > Izintandokazi > Amabha wamathuluzi kanye neRibhoni. Ngaphansi kokuthi "Amathebhu Ayinhloko," khetha "Unjiniyela" bese ulondoloza. Lokhu kuzokunika ukufinyelela ku-recorder, i-VBA, kanye nezilawuli . Uma umusha ku-Excel, buyekeza umhlahlandlela oyisisekelo we-Excel ukuze uzijwayeze neribhoni kanye nezinketho zayo.

Ungawakha kanjani amamakhro ngeRecorder

Irekhoda ilungele ukuqala. Ku-Developer > Record Macro (noma i-Alt+T+M+R ku-Windows), nika igama elichazayo. Khumbula: igama kumele liqale ngohlamvu, ngaphandle kwezikhala noma izimpawu.

Khetha ukuthi uzoyigcina kuphi: "Le ncwadi yokusebenza" izosebenza ezimweni eziningi. Uma ufuna ukuyigcina itholakala njalo, khetha "Incwadi Yokusebenzela Ye-Macro Yomuntu Siqu" bese i-Excel izoyidala/iyigcine ku- Personal.xlsb , evula i-hidden nge-Excel.

Uma uthanda, engeza isinqamuleli. Ukuze ugweme ukungqubuzana (isibonelo, ukulahlekelwa yi-Ctrl+Z), kunconywa ukusebenzisa izinqamuleli nge-Ctrl+Shift. Ungabhala futhi incazelo emfushane necacile (iwusizo kakhulu uma unama-macro amaningi).

Chofoza u-Kulungile bese wenza izenzo ezizoqoshwa. Isibonelo: khetha u-A1 bese usebenzisa ifomethi ethi "Amaphesenti". Gwema ukuchofoza okungadingekile noma izinguquko zokukhetha ezingeyona ingxenye yenqubo ehlosiwe, ngoba irekhoda iqopha cishe konke.

Uma usuqedile, yeka ukurekhoda. Ukuze uyisebenzise, ​​iya ku-Developer > Macros (noma i-Alt+F8), khetha i-macro yakho, bese uchofoza ku-Run. Uzobona i-Excel iphinda izinyathelo ngokulandelana okufanayo.

Qalisa, hlela, futhi uphathe amamakhro akho

Kusuka ku-Developer > Macros (Alt + F8) ungaqalisa, uhlele, noma ususe ama-macros. Uma ukhetha Hlela, i-Visual Basic Editor izovula ngekhodi eqoshiwe, ongayihlanza futhi uyilungiselele (ngokuvamile ifaka izinyathelo ezingafuneki).

Ukunikeza i-macro enkinobheni noma esimweni kulula kakhulu: faka isimo, chofoza kwesokudla > Nika i-Macro, khetha i-macro, bese uqedile. Uma uchofoza isimo, sizosebenza ngokushesha.

Ukuze uyigcine itholakala kalula ku-Quick Access Toolbar, vula imenyu yamathuluzi (umcibisholo ophansi) > Imiyalelo Eminingi > ngaphansi kokuthi "Imiyalo iyatholakala ku-", khetha "Ama-Macro", engeza eyakho, bese ushintsha isithonjana uma ufisa. Manje uzoba nenkinobho ehlala njalo noma ungekho kumodi yonjiniyela.

Uma udinga ukusebenzisa kabusha i-macro kwenye incwadi yokusebenza, ungakopisha i-module kusuka ku-Visual Basic Editor. Vula i-VBE (Alt+F11), hudula i-module uye kwenye incwadi yokusebenza evulekile, noma sebenzisa ifayela le-Export/Import .bas.

Ukwenza amamakhro ku-Excel nge-Visual Basic (VBA) Editor

Ukuze udlulele ngale kwerekhoda, vula i-Editor ngo-Alt+F11. Faka i-Module (Faka > i-Module) bese udala i-subroutine yakho yokuqala. Iphethini eyisisekelo yi -Sub Name() … End Sub.

Isibonelo, ukuze ubonise umlayezo futhi ubhale inani kuseli A1, ungaqala ngento elula. Ngale ndlela uyaqonda isakhiwo nokuthi ungayibiza kanjani i-macro kusuka ku-Excel (Alt+F8) noma uyinikeze inkinobho, enemigqa embalwa kakhulu yekhodi :

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

Esinye isibonelo esisheshayo sokusebenzisa isitayela samaphesenti ekukhetheni kwamanje. Le macro iphinda lokho okwenzile nge-recorder , kodwa yathayipha ngqo:

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

Inhlanganisela ekahle yile: rekhoda ukuze kusheshiswe izinto, bese uvula i-VBE ukuze uhlanze izinyathelo ezingadingekile, wengeze izinto eziguquguqukayo, izimo, noma izihibe. Ngale ndlela, uguqula ukuqopha "okungokoqobo" kube yi- macro eqinile futhi engasetshenziswa kabusha.

Okuphelele uma kuqhathaniswa namareferensi ahlobene

Ngokuzenzakalelayo, i-recorder idala ama-macros kwimodi ephelele: ihlala isebenza kumaseli afanayo (isibonelo, i-B3) kungakhathalekile ukuthi ukuphi lapho uyisebenzisa. Uma idatha ishintsha isikhundla enkambisweni yakho, lokhu kungase kungabi yilokho okudingayo.

Nge-"Sebenzisa izinkomba ezihlobene", i-macro isebenza ngokuphathelene neseli esebenzayo lapho kuqala ukusebenza. Isibonelo, uma uqale ukurekhoda kuseli B3 bese uthayipha "Igama Lokuqala", "Isibongo", njll., uye kwesokudla, uma usebenza kusuka kuseli C4, izothayipha ulwazi olufanayo ngokuphathelene namaseli C4, D4, E4, njll.

Isibonelo esijwayelekile: ukwengeza izihloko etafuleni ngezinye izikhathi eziqala kuseli B3 futhi ngezinye izikhathi kuseli C5. Ngemodi eqondile, izohlala ibhala kuseli B3. Ngemodi ehlobene, izozivumelanisa neseli eliqalwayo elikhethiwe.

Amaphutha ajwayelekile lapho uqopha izinqubo

Ingozi evamile ukuqopha ifomula ethi "hudula", ake sithi, imigqa emithathu, bese ucabanga ukuthi izosebenza kumathebula amade. Umrekhoda uzobhalisa into efana " nokugcwalisa amaseli amathathu phansi ," hhayi "ekugcineni kwethebula."

Isixazululo ukushintsha indlela yakho: sebenzisa amabanga aguquguqukayo (isibonelo, ukuthola umugqa wokugcina), ama-loop, noma indlela ye-CurrentRegion, noma ulungise i-macro ku-VBA ngemuva kokuyiqopha ukuze ifinyelele kunoma yisiphi isayizi.

Imikhuba emihle lapho uqopha

Ngaphambi kokurekhoda, qhubeka nenqubo ngesandla izikhathi ezimbalwa ukuze uyilungise. Yenza ukurekhoda okufushane nokuqondile esikhundleni sokurekhoda okukodwa okukhulu; lokhu kwenza kube lula ukukugcina nokuhlanganisa.

Gwema ukuchofoza okungadingekile, ukuskrola iribhoni, kanye nokukhetha okunganikeli lutho. Gcina igama le-macro kanye nencazelo kucacile ukuze wena noma omunye umuntu osebenza naye akwazi ukubona ngokushesha injongo yayo.

Sebenzisa ama-macros ngezindlela eziningi

Ngaphezu kwebhokisi lengxoxo le-Macros (Alt+F8), ungaqalisa ama-macro usebenzisa isimo, inkinobho ku-Quick Access Toolbar, noma ngokuvula incwadi yokusebenzela. Ungavumela i-Excel ukuthi isebenzise i-macro kusuka kwezinye izinhlelo zokusebenza ze-Office, okukuvumela ukuthi wenze imisebenzi yezinhlelo zokusebenza ezihlukene ngokuzenzakalelayo (isibonelo, ukubuyekeza ithebula nokuthumela i-imeyili nge-Outlook).

Cabanga ngokucophelela ngezinqamuleli zekhibhodi: uma unikeza i-Ctrl+Z ku-macro, uzolahlekelwa ikhono loku-Undo ngenkathi ibhuku lokusebenzela livuliwe. Ngakho-ke, kunconywa ukusebenzisa izinhlanganisela ze -Ctrl+Shift ukuze ugweme izingxabano.

Ukulondoloza izincwadi zokusebenza ngamamakhro

Uma ifayela liqukethe ikhodi ye-VBA, lilondoloze njengefayela le-.xlsm ukuze ikhodi igcinwe futhi ikwazi ukusebenza. Ukulondoloza njengefayela le-.xlsx kuzobhubhisa iphrojekthi ye-VBA . Uma ufuna ama-macro omhlaba wonke, sebenzisa i-Personal Macro Workbook (Personal.xlsb).

Ukuphepha kanye nokuvumela ama-macros

Ama-Macro angase avinjwe kuye ngezilungiselelo zakho zokuphepha. Hlola i-Trust Center ukuze uwavule ngokukhetha, futhi ungalokothi uwavule ama-macro avela emithonjeni engaziwa. Ukuqonda izinga lakho lokuphepha kukusiza ukuthi ulinganisele ukuvikelwa kanye nokukhiqiza.

Yabela amamakhro ezinkinobho, umumo, nezilawuli

Ukuze abasebenzisi bakwazi ukusebenzisa ama-macro ngokuzenzakalelayo, nikeza i-macro enkinobheni, esimweni, noma ekulawuleni. Chofoza ngakwesokudla into ethi > Nika i-Macro > khetha. Ungabeka futhi isithonjana ku-ribbon noma ku-Quick Access Toolbar bese uyixhumanisa ne-macro.

Uma usebenzisa amafomu noma izilawuli ze-ActiveX, ungabela ama-macro noma izinqubo zemicimbi (isibonelo, ngokuchofoza). Lokhu kuvumela izixhumi ezisebenziseka kalula nezinqubo eziqondiswayo.

Izibonelo eziwusizo zamamakhro (VBA)

Lezi zibonelo zibonisa imisebenzi evamile. Ungazinamathisela ku-Module (Alt+F11 > Insert > Module) bese uzivumelanisa nezimo. Khumbula ukusayina izincwadi zomsebenzi noma ukulungisa izilungiselelo zokuphepha uma inhlangano yakho ikufuna ukuze ukwazi ukuzisebenzisa ngaphandle kwezixwayiso.

Kopisha okuqukethwe okukhethiwe kwenye indawo eshidini elifanayo: Ukukhetha.Kopisha Indawo Oyiya Kuyo

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

Phrinta ishidi elisebenzayo: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Londoloza ifayela njengencwadi yokusebenza enikwe amandla amakhulu (.xlsm): ActiveWorkbook.SaveAs

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

Sebenzisa ukufometha kububanzi (lungisa ububanzi ngokwezidingo zakho): Nge-Range(…)

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

Thola inani kububanzi bese ubonisa umdlalo wokuqala: Ibanga(…).Thola

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

Isimo esingokoqobo: ukufometha njengephesenti

Ukurekhoda: Qala ukurekhoda, kuqambe igama, ukhethe ukuthi uzokulondoloza kuphi, bese unikeza isinqamuleli nencazelo ngokuzithandela. Khetha amaseli bese usebenzisa "Amaphesenti" kuthebhu ethi Ikhaya. Yeka ukurekhoda bese ukuphinda usebenzise i-Alt+F8. Uma ufuna ukudlala ngekhodi encane, sebenzisa i-Selection.Style = "Amaphesenti" ku-VBA.

Icebiso: Ngesikhathi sokurekhoda, gwema ukushintsha amaseli ngokungadingekile. Lokhu kunyakaza kokubuyela emuva nokubuyela emuva kuyaqoshwa futhi kunganciphisa ijubane futhi konakalise i-macro.

Yenza ngokuzenzakalelayo imisebenzi emide: amamakhro amancane angcono

Uma unenqubo enezinyathelo eziningi, cabanga ukuyihlukanisa ibe ama-macro amancane ambalwa bese uwabiza ngokulandelana. Lokhu kwenza ukulungiswa kube lula, kukuvumela ukuthi uphinde usebenzise ama-blocks , futhi kwenza kube lula ukulungisa amaphutha.

Isibonelo, i-macro eyodwa yokulungiselela idatha, enye ukuyifometha, enye ukukhiqiza iphrinti, kanye nenye yokuthumela ku-PDF noma ukuthumela nge-imeyili . Ungazishayela ngayinye ngayinye noma zonke ngesikhathi esisodwa.

Hlela futhi uhlanze ikhodi erekhodiwe

Irekhoda ithwebula "konke": izinketho, ukuchofoza, kanye nezinguquko zethebhu. Uma i-VBE ivuliwe, isusa izinketho ezingadingekile (Khetha/Khetha) ngokuzifaka esikhundleni sazo izinkomba eziqondile (isibonelo, i-Range("A1").Value = ... ) bese ihlanganisa amafomethi abe yi-With/End With blocks.

Ngaphezu kwalokho, ingeza iziguquguquko kanye nokuphathwa kwamaphutha ayisisekelo. Izitatimende ezimbalwa ze-if kanye nokuhlola okulula kuvimbela amaphutha lapho ububanzi bungenalutho noma ukukhetha kungenzeki njengoba bekulindelekile.

Imibuzo ebuzwa njalo

Ngingayakha kanjani i-UserForm ku-Excel? Vula i-Visual Basic Editor (Alt+F11), iya ku-Insert > UserForm, bese wengeza izilawuli (amabhokisi ombhalo, izinkinobho, njll.). Ungayibonisa usebenzisa i-macro.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Ukusuka lapho, nikeza izinqubo ezenzakalweni zezilawuli (isibonelo, ukuchofoza inkinobho) ukuze usebenzise i-logic oyidingayo.

Ngingalivikela/ngingalivikeli kanjani ishidi elinama-macro? Sebenzisa le mikhuba ngokushintsha iphasiwedi njengoba uthanda. Ikuvumela ukuthi usebenzise noma uvale ukuvikela ngokuchofoza okukodwa.

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

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

Ngingayisusa kanjani i-macro? Iya ku-Developer > Macros, khetha i-macro, bese ucindezela u-Delete. Uma ufuna ukususa yonke ikhodi ku-module, vula i-VBE (Alt + F11), thola i-module kuphrojekthi, chofoza kwesokudla, bese ukhetha u-Remove Module (ungayithumela kuqala uma ufuna ukuyigcina).

Icebiso lokugcina eliwusizo: Uma ufometha umbiko wanyanga zonke lapho amakhasimende anebhalansi engakhokhiwe kufanele agqanyiswe ngombala obomvu nogqamile, qopha ukulandelana kanye, lungisa ikhodi ye-VBA ukuze isetshenziswe ebangeni elifanele (usebenzisa ukutholwa komugqa wokugcina noma izihlungi), bese ulondoloza i-macro encwadini yakho yokusebenza ye-macro. Ngesikhathi esizayo, isinqamuleli noma inkinobho elula izoba yikho konke okudingekayo ukuze usebenzise ukufometha ngokushesha, ngokungenaphutha, ngemizuzwana.

I-athikili ehlobene:

Ama-Macros ku-Excel: Uyenza kanjani imisebenzi ngokuzenzakalelayo futhi wandise umkhiqizo wakho