UkuCwangcisa iiMacros kwi-Excel: IsiKhokelo esiGqibelelweyo seNyathelo

EPortada » Inkqubo » UkuCwangcisa iiMacros kwi-Excel: IsiKhokelo esiGqibelelweyo seNyathelo

Ukuba uphinda imisebenzi kwi-Excel ngokuphindaphindiweyo, ii-macros yeyona nto ilungileyo yokuyeka ukuchitha ixesha. Ngabo, unako zenze ulandelelwano olupheleleyo lwezenzo (funda ngakumbi malunga VBA inkqubo), ukusuka ekufomathani ukuya ekurhwebeni ngaphandle kwedata okanye ubala ubalo, kwaye uzivuse nanini na xa ufuna ngonqakrazo okanye indlela emfutshane yebhodi yezitshixo.

Xa usenza i-macro, i-Excel irekhoda ukucofa kwakho kunye nezitshixo kwaye uzenze ngokuthembeka. Emva koko unokwenza ikhowudi ukuze ulungelelanise ukuphunyezwa kunye nokubonisana izixhobo zeExcelKuqhelekile ukuqala ngerekhoda kwaye emva koko ulungelelanise neVBA, ukufumana ukulawula ngakumbi, ukuguquguquka kunye neempazamo zero ngenxa yokungakhathali.

Yintoni kanye i-macro kwi-Excel?

Imakhro yi a uluhlu lweenkcazelo ezenziwa yi-Excel ngokolandelelwano oluchaziweyo. Ingalula njengokusebenzisa isimbo sepesenti okanye ibenzima njenge lungisa ingxelo ngeetafile zepivot, ifomethe, yenza i-spreadsheets, kwaye uthumele i-imeyile kwi-Outlook. Iimakhro zigcinwa kwincwadi yakho yokusebenza, kwaye ungaziqhuba nanini na ufuna.

Awudingi ukwazi ukwenza inkqubo ukuze uqalise: umshicileli uguqulela izenzo zakho kwikhowudi yeVBA. Nangona kunjalo, ukwazi iVBA ethile kuya kukuvumela ukuba wenze njalo Yenza imisebenzi yesiko, iifom, kunye neeapplets ngaphakathi kwe-Excel.

Kutheni usebenzisa iimacros? Iingenelo zokwenene

Eyona njongo ye-macro yi kuphephe ukuphindaphinda umsebenzi kwaye unciphise iimpazamoUkuba ulungiselela ingxelo efanayo rhoqo ngenyanga, i-macro ikugcina amanyathelo omatshini kwaye ikuvumela ukuba ugxininise kwizinto ezibalulekileyo.

Phakathi kweenzuzo zayo kukugcina ixesha, ukuphunyezwa ngokuqhubekayo (ngaphandle kokulibala), kunye nokwenzeka kokudala iindlela ezimfutshane kunye namaqhosha ukukhawulezisa iinkquboUkuba usebenzisa i-Excel ngamaxesha athile, isenokungafanelanga; kodwa ukuba uyisebenzisa yonke imihla, uya kuwuqaphela umahluko ukusuka kusuku lokuqala.

Yenza ithebhu yoPhuhlisi (iWindows kunye neMac)

Iribhoni yoPhuhlisi ifihliwe ngokungagqibekanga. Kwi-Windows, cofa ekunene kwiribhoni kwaye ukhethe "Yenza ngokwezifiso iRibhoni." Khetha "uMphuhlisi" kwaye wamkele. Emveni kwalonto, Uya kubona ithebhu yoMphuhlisi ecaleni kweVista.

KwiMac, yiya ku-Excel> Ezikhethwayo> Iibar zesixhobo kunye neRibhoni. Ngaphantsi kwe "Iithebhu eziphambili," khetha "uMphuhlisi" kwaye ugcine. Oku kuya kukunika ukufikelela kwibar yesixhobo. umshicileli, ukufikelela kwi-VBA kunye nokulawula. Ukuba usaqala nje, jonga i esisiseko Excel ukuphathwa ukukukhokela ngeribhoni kunye neenketho.

Uzenza njani iimacros ngeRecorder

Irekhoda yindawo entle yokuqala. Ngaphantsi koMdwelisi> Rekhoda iMacro (okanye Alt+T+M+R kwiWindows), yabela igama elichazayo. Khumbula: igama kufuneka liqale ngonobumba. ngaphandle kwezithuba okanye iisimboli.

Khetha apho ugcine khona: "Le ncwadi yomsebenzi" iya kusebenza kwiimeko ezininzi. Ukuba ufuna ukuhlala unayo, khetha u- "Personal Macro Workbook" kwaye i-Excel iya kuyidala/yigcine kuyo. Personal.xlsb, evula efihliweyo nge-Excel.

Ngokuzithandela yongeza indlela emfutshane. Ukunqanda ungquzulwano (umzekelo ukuphulukana no-Ctrl+Z), kuyacetyiswa ukuba usebenzise imidibaniso nge-Ctrl+Shift. Ungachwetheza kwakhona indlela emfutshane. inkcazelo emfutshane necacileyo (iluncedo kakhulu xa unemacros ezininzi).

Cofa u-Kulungile kwaye wenze iintshukumo ofuna ukuzirekhoda. Umzekelo, khetha i-A1 kwaye usebenzise ifomathi ethi "Ipesenti". Kuphephe ucofa ngokungeyomfuneko okanye utshintsho olungeyonxalenye yenkqubo ecetywayo, kuba umshicileli ubhala phantse yonke into.

Xa ugqibile, yeka ukurekhoda. Ukuyiqhuba, yiya kuMphuhlisi> IiMacros (okanye Alt+F8), khetha i-macro yakho, kwaye ucinezele u-Run. Uya kubona indlela i-Excel phinda amanyathelo ngendlela efanayo.

Baleka, uhlele, kwaye ulawule iimacros zakho

Ukusuka kwiNkqubo> IiMacros (Alt+F8) ungabaleka, uhlele, okanye ucime iimakhro. Ukuba ukhetha Hlela, uMhleli oSiseko oMbonwayo uya kuvula ngekhowudi erekhodiweyo, onokuyihlela. coca kwaye ulungiselele (idla ngokubandakanya amanyathelo angafunekiyo).

Ukwabela i-macro kwiqhosha okanye imilo kulula kakhulu: faka imilo, cofa ekunene> Yabela iMacro, khetha i-macro, kwaye yiloo nto. Xa ucofa ubume, iya kwenziwa ngoko nangoko.

Ukuhlala uyiphethe ngesandla kwiThulbha yoFikelelo oluKhawulezayo, vula imenyu yebha (utolo olusezantsi)> Imiyalelo Eyongeziweyo> phantsi kwe-"Imiyalelo ekhoyo", khetha u-"Macros", yongeza eyakho kwaye utshintshe i icon ukuba uyafuna. Ngoku uya kuba nakho iqhosha elisisigxina naxa ungekho kwiProgram.

Ukuba ufuna ukusebenzisa kwakhona i-macro kwenye incwadi yokusebenza, unokukopa imodyuli kuMhleli oSiseko oMbonwayo. Vula i-VBE (Alt+F11), tsala imodyuli kwenye incwadi yokusebenza evulekileyo, okanye sebenzisa Thumela ngaphandle/Ngenisa .bas ifayile.

Iimakhro zokucwangcisa kwi-Excel kunye ne-Visual Basic (VBA) Umhleli

Ukuya ngaphaya komshicileli, vula uMhleli nge-Alt+F11. Faka iModyuli (Faka > Imodyuli) kwaye uyile indlela yakho yokuqala engaphantsi. Ipatheni esisiseko NONE.

Umzekelo, ukubonisa umyalezo kwaye ubhale ixabiso kwi-A1, ungaqala ngento elula. Ngale ndlela, uyasiqonda isakhiwo kunye nendlela yokubiza i-macro kwi-Excel (Alt+F8) okanye uyinike iqhosha, nge imigca embalwa kakhulu:

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

Omnye umzekelo okhawulezayo wokusebenzisa isimbo sepesenti kukhetho lwangoku. Le macro phinda into oyenzileyo ngomshicileli, kodwa ibhalwe ngokuthe ngqo:

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

Indibaniselwano efanelekileyo yile: rekhodisha ukukhawulezisa izinto, emva koko uvule i-VBE ukucoca amanyathelo angeyomfuneko, ukongeza izinto eziguquguqukayo, iimeko, okanye iilophu. Ngale ndlela, uguqula "ukurekhoda" kwi-macro. yomelele kwaye isebenziseka kwakhona.

Ngokupheleleyo vs. izingqinisiso ezizalanayo

Ngokungagqibekanga, umshicileli wenza ii-macros kwimodi epheleleyo: ihlala isebenza kwiiseli ezifanayo (umzekelo, B3) kungakhathaliseki ukuba uphi na xa uphumeza. Ukuba idatha itshintsha indawo kwinkqubo yakho, oku kunokwenzeka ingeyiyo le nto uyidingayo.

Ngo "Sebenzisa iimbekiselo ezizalanayo," i-macro isebenza ngokunxulumene neseli esebenzayo xa iqala ukusebenza. Umzekelo, ukuba uqalile ukurekhoda kwi-B3 kwaye uchwetheze "Igama Lokuqala," "Igama Lokugqibela," njl., usiya ngasekunene, xa ubaleka kwi-C4, iya kuyibhala ukusuka C4, D4, E4... ngokwentelekiso.

Umzekelo oqhelekileyo: ukubhala itafile ngamanye amaxesha iqala kwi-B3 kwaye ngamanye amaxesha kwi-C5. Ngemo egqibeleleyo, uyakuhlala ubhala kwi-B3. Ngemowudi ehambelanayo, iya kuziqhelanisa ne iseli yokuqala ekhethiweyo.

Iimpazamo eziqhelekileyo xa urekhoda iinkqubo

Umgibe oqhelekileyo kukurekhoda utsalo lwefomula, yithi, imiqolo emithathu, kwaye ucinga ukuba iyakusebenza kwiitafile ezinde. Umshicileli uya kurekhoda into elingana "gcwalisa iiseli ezintathu phantsi", hayi" ukuya ekupheleni kwetafile.

Isisombululo kukutshintsha indlela: sebenzisa uluhlu oluguquguqukayo (umzekelo, fumana umqolo wokugqibela), iilophu, okanye indlela yeCurrentRegion, okanye uhlengahlengise i-macro kwi-VBA emva kokurekhoda ukuze izikali nakweyiphi na isayizi.

Izenzo ezilungileyo xa urekhoda

Phambi kokurekhoda, yenza inkqubo yesandla izihlandlo ezimbalwa ukuyipholisha. Yenza ushicilelo mfutshane kwaye ngokuthe ngqo endaweni yesikhulu esinye; oku kwenza kube lula ukuzigcina kunye nokudibanisa.

Kuphephe ukucofa okungeyomfuneko, ukuskrola ngokungeyomfuneko, kunye nokhetho olungenamsebenzi. Gcina igama elikhulu kunye nenkcazo ecacileyo ukuze wena okanye nabaphi na osebenza nabo nikwazi uyibona injongo yayo ngoko nangoko.

Yenza iimacros ngeendlela ezininzi

Ukongeza kwi-Macros ibhokisi (Alt+F8), ungandulula iimakhro ezinemilo, iqhosha kwiThulbha yoFikelelo oluKhawulezayo, okanye xa uvula incwadi yokusebenza. Unokuba ne-Excel iqhube i-macro kwezinye ii-apps zeOfisi, ikuvumela ukuba wenze njalo zenzele imisebenzi yezicelo ezininzi (umzekelo, ukuhlaziya itafile kunye nokuthumela i-imeyile nge-Outlook).

Cinga ngocoselelo malunga neendlela ezimfutshane zebhodi yezitshixo: ukuba wabela u-Ctrl+Z kwimacro, uya kuphulukana namandla oKuhlehlisa ngelixa incwadi yokusebenza ivuliwe. Yiyo loo nto kucetyiswa ukuba kusetyenziswe indibaniselwano kunye Ctrl + Shift ukuphepha iingxabano.

Ukugcina iincwadi zokusebenza ngeemakhro

Xa ifayile iqulethe i-VBA, yigcine njenge .xlsm ukuze ikhowudi igcinwe kwaye ingaphunyezwa. Ukuba uyigcina njenge .xlsx, uya kulahlekelwa yiprojekthi ye-VBAUkuba ufuna iimacros zehlabathi, sebenzisa iPersonal Macro Workbook (Personal.xlsb).

Ukhuseleko kunye neemacros ezivumelayo

IiMacros zinokuvalwa ngokuxhomekeke kukhuseleko lwakho. Jonga iZiko leTrasti ukwenza ngokukhetha kwaye ungaze usebenzise iimakhro kwimithombo engaziwayo. Ukwazi inqanaba lokhuseleko lwakho kukunceda ukulungelelanisa ukhuseleko kunye nemveliso.

Yabela iimakhro kumaqhosha, iimilo, kunye nolawulo

Ukuze umsebenzisi azenze ngaphandle kokucinga, yabela i-macro kwiqhosha, imilo, okanye ulawulo. Cofa ekunene kwinto> Yabela iMacro> khetha. Unako kwakhona beka i-icon kwiribhoni okanye kwibar yokufikelela ngokukhawuleza kwaye uyidibanise ne-macro.

Ukuba usebenzisa iifom okanye ulawulo lwe-ActiveX, ungabanika ii-macros okanye iinkqubo zesiganeko (umzekelo, ngokucofa). Oku kuvumela ujongano olusebenziseka lula kunye neenkqubo ezikhokelwayo.

Imizekelo eluncedo enkulu (VBA)

Le mizekelo ibonisa imisebenzi eqhelekileyo. Unokuzincamathisela kwimodyuli (Alt+F11 > Faka > Imodyuli) kwaye uzenze ngokwezifiso. Khumbula ukusayina iincwadi okanye uhlengahlengise ukhuseleko ukuba umbutho wakho uyakufuna. ukwazi ukubaqhuba ngaphandle kwezilumkiso.

Khuphela umxholo okhethiweyo kwenye indawo kwiphepha elifanayo: Ukhetho.Khuphela Indawo ekuyiwa 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

Shicilela iphepha elisebenzayo: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Gcina ifayile njengencwadi yokusebenza eyenziwe yasebenza kakhulu (.xlsm): Incwadi yoMsebenzi esebenzayo.GcinaAs

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

Fomata uluhlu (lungisa uluhlu kwiimfuno zakho): Ngoluhlu (…)

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

Fumana ixabiso kuluhlu kwaye ubonise umdlalo wokuqala: Uluhlu(…).Fumana

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

Umzekelo osebenzayo: ukufomatha njengepesenti

Ukurekhoda: Qala ukurekhodwa, uyinike igama, khetha indawo oza kuyigcina, kwaye ukhethe ngokukhetha indlela emfutshane kunye nenkcazo. Khetha iiseli kwaye usebenzise "Ipesenti" kwimenyu yokuQala. Misa ukurekhoda uze uqale nge Alt+F8. Ukuba ufuna ukuyidlala ngekhowudi encinci, sebenzisa Selection.Style = "Ipesenti" kwiVBA.

Ingcebiso: Musa ukutshintsha iiseli ngaphandle kwesizathu ngexesha lokurekhoda. Oku kuza kunye nokuhamba kubhalwe kwaye kunokuba ucothe kwaye ungcolise i-macro.

Yenza imisebenzi emide: iimakhro ezincinci zingcono

Ukuba unenkqubo enamanyathelo amaninzi, cinga ukuwaphula ube zii-macros ezincinci kwaye uzibize ngekhonkco. Oku kwenza ukugcinwa kube lula, kukuvumela ukuba phinda usebenzise iibhloko kwaye yenza lula ukulungisa ingxaki.

Ngokomzekelo, enye i-macro yokulungisa idatha, enye ukufomatha, enye ukuvelisa iphepha lokushicilela kunye nomnye ukuthumela ngaphandle kwi-PDF okanye thumela nge-imeyileUngababiza nganye nganye okanye zonke ngaxeshanye.

Hlela kwaye ucoce ikhowudi erekhodiweyo

Irekhoda ibamba "yonke into": ukhetho, ukucofa, kunye notshintsho lwethebhu. Xa uvula i-VBE, isusa ukhetho olungeyomfuneko (Khetha / Ukukhetha) kwaye ubeke endaweni yazo ngeembekiselo ezithe ngqo (umzekelo, Uluhlu("A1").Ixabiso = ...) kunye neefomati zamaqela zibe Nge/Phelisa ngeebhloko.

Yongeza kwakhona izinto eziguquguqukayo kunye nokuphathwa kweempazamo ezisisiseko. Isibini se Ukuba iingxelo kunye neetshekhi ezilula zithintela iimpazamo xa uluhlu lungenanto okanye ukhetho ayisiyiyo le nto ibilindelwe.

Imibuzo ebuzwa rhoqo

Ndiyenza njani ifom (UserForm) kwi-Excel? Vula i-Visual Basic Editor (Alt + F11), yiya kwi-Insert> UserForm, kwaye ungeze izilawuli (iibhokisi zombhalo, amaqhosha, njl.). Unokuzibonisa ngemacro:

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Ukusuka apho, yabela iinkqubo kwiziganeko zolawulo (umzekelo, Cofa iqhosha) ukuze yenza ingqiqo oyifunayo.

Ndilikhusela/ndilikhusela njani iphepha elinemakhro? Sebenzisa ezi nkqubo ukutshintsha igama lokugqitha ngendlela othanda ngayo. Bakuvumela ukuba wenze njalo vula okanye ususe ukhuseleko ngonqakrazo olunye:

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

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

Ndiyicima njani imacro? Yiya kuMdwelisi> IiMacros, khetha i-macro, kwaye ucinezele Cima. Ukuba ufuna ukucima yonke ikhowudi kwimodyuli, vula iVBE (Alt+F11), khangela imodyuli kwiprojekthi, cofa ekunene, uze ukhethe Cima. Susa iModyuli (ungayithumela ngaphandle kuqala ukuba ufuna ukuyigcina).

Inqaku lokugqibela elisebenzayo: ukuba ufomatha ingxelo yenyanga apho abathengi abanetyala kufuneka babebomvu kwaye bagqame, bhala ulandelelwano kube kanye, uhlengahlengise ikhowudi ye-VBA ukuze uyisebenzise kuluhlu oluchanekileyo (usebenzisa ukufunyanwa komgca wokugqibela okanye izihlungi), kwaye ugcine i-macro kwincwadi yakho yomsebenzi we-macro. Kwixesha elizayo, indlela emfutshane okanye iqhosha liya kwanela ukusebenzisa ukufomatha ngoko nangoko. iintsilelo zero kunye nemizuzwana.