"Umsebenzi" kanye "Nezinqubo ezingaphansi" ku-VBA

Imisebenzi ye-VBA eyakhelwe ngaphakathi

Ngaphambi kokuthi uqale ukudala eyakho imisebenzi ye-VBA, kuhle ukwazi ukuthi i-Excel VBA ineqoqo elicebile lemisebenzi eyakhelwe ngaphakathi ongayisebenzisa ngenkathi ubhala ikhodi yakho.

Uhlu lwale misebenzi lungabonwa kumhleli we-VBA:

• Vula incwadi yokusebenzela ye-Excel bese uvula umhleli we-VBA (chofoza ukwenza lokhu I-Alt + F11), bese ucindezela F2.
• Khetha umtapo wolwazi ohlwini lokudonsela phansi phezulu kwesokunxele kwesikrini VBA.
• Uhlu lwamakilasi e-VBA akhelwe ngaphakathi nemisebenzi luzovela. Chofoza igama lomsebenzi ukuze ubonise incazelo yalo emfushane ngaphansi kwewindi. ngokucindezela F1 izovula ikhasi losizo le-inthanethi laleso sici.

Ngaphezu kwalokho, uhlu oluphelele lwemisebenzi ye-VBA eyakhelwe ngaphakathi enezibonelo ingatholakala ku-Visual Basic Developer Center.

Izinqubo zangokwezifiso "Umsebenzi" kanye "ne-Sub" ku-VBA

Ku-Excel Visual Basic, isethi yemiyalo eyenza umsebenzi othile ifakwa enqubweni. Umsebenzi (Umsebenzi) noma Okungaphansi (I-Subroutine). Umehluko omkhulu phakathi kwezinqubo Umsebenzi и Okungaphansi yiyona nqubo Umsebenzi ibuyisela umphumela, inqubo Okungaphansi – hhayi.

Ngakho-ke, uma udinga ukwenza izenzo futhi uthole umphumela (isibonelo, hlanganisa izinombolo eziningana), inqubo ivame ukusetshenziswa Umsebenzi, futhi ukuze wenze izenzo ezithile (isibonelo, shintsha ukufometha kweqembu lamaseli), udinga ukukhetha inqubo. Okungaphansi.

Iziphakamiso

Idatha ehlukahlukene ingadluliselwa ezinqubweni ze-VBA kusetshenziswa izimpikiswano. Uhlu lwezimpikiswano lucacisiwe lapho kumenyezelwa inqubo. Ngokwesibonelo, inqubo Okungaphansi ku-VBA yengeza inombolo enikeziwe (Integer) kuseli ngayinye ebangeni elikhethiwe. Ungadlulisela le nombolo kunqubo usebenzisa impikiswano, kanje:

I-Sub AddToCells(i As Integer) ... Qeda I-sub

Khumbula ukuthi ukuba nezingxabano zezinqubo Umsebenzi и Okungaphansi ku-VBA uyazikhethela. Ezinye izinqubo azidingi izimpikiswano.

Izimpikiswano Ongazikhetha

Izinqubo ze-VBA zingaba nezimpikiswano ozikhethela zona. Lawa ama-agumenti umsebenzisi angakwazi ukuwacacisa uma efuna, futhi uma eshiywa, inqubo isebenzisa amanani azenzakalelayo kubo.

Uma sibuyela esibonelweni sangaphambilini, ukwenza i-agumenti ephelele emsebenzini ibe ngokuzikhethela, izomenyezelwa kanje:

I-Sub AddToCells(Ngokuzithandela mina Njengenani eliphelele = 0)

Kulokhu, i-agumenti ephelele i okuzenzakalelayo kuzoba ngu-0.

Kungaba nama-agumenti amaningana okuzithandela enqubweni, wonke afakwe ohlwini ekugcineni kohlu lokuphikisana.

Ukudlulisa izimpikiswano ngenani nangereferensi

Izingxabano ku-VBA zingadluliselwa kunqubo ngezindlela ezimbili:

• ByVal – ukudlulisa impikiswano ngenani. Lokhu kusho ukuthi inani kuphela (okungukuthi, ikhophi yempikiswano) elidluliselwa kunqubo, ngakho-ke noma yiziphi izinguquko ezenziwe kungxabano ngaphakathi kwenqubo zizolahleka uma inqubo iphuma.
• NgeRef – ukudlulisa ingxabano ngereferensi. Okusho ukuthi, ikheli langempela lendawo yempikiswano esenkumbulweni lidluliselwa kunqubo. Noma yiziphi izinguquko ezenziwe kungxabano ngaphakathi kwenqubo zizolondolozwa lapho inqubo iphuma.

Ukusebenzisa amagama angukhiye ByVal or NgeRef esimemezelweni senqubo, ungacacisa ukuthi impikiswano idluliselwa kanjani kunqubo. Lokhu kuboniswa ezibonelweni ezingezansi:

I-Sub AddToCells(ByVal i As Integer) ... Qeda I-sub

I-Sub AddToCells(ByRef i As Integer) ... Qeda I-sub

Khumbula ukuthi izimpikiswano ku-VBA ziphasiswa ngereferensi ngokuzenzakalela. Ngamanye amazwi, uma amagama angukhiye engasetshenziswa ByVal or NgeRef, khona-ke impikiswano izodluliswa ngereferensi.

Ngaphambi kokuqhubeka nezinqubo Umsebenzi и Okungaphansi ngokuningiliziwe, kuzoba usizo ukuphinda sibheke izici kanye nomehluko phakathi kwalezi zinhlobo ezimbili zezinqubo. Okulandelayo yizingxoxo ezimfushane zezinqubo ze-VBA Umsebenzi и Okungaphansi futhi izibonelo ezilula ziyakhonjiswa.

Inqubo ye-VBA "Umsebenzi"

Umhleli we-VBA uyayibona inqubo Umsebenzilapho ihlangana neqembu lemiyalo evalelwe phakathi kwalezi zitatimende zokuvula nokuvala ezilandelayo:

Umsebenzi ... Qeda Umsebenzi

Njengoba kushiwo ngaphambili, inqubo Umsebenzi ku-VBA (ngokuphambene ne- Okungaphansi) ibuyisela inani. Imithetho elandelayo iyasebenza ekubuyiseni amanani:

• Uhlobo lwedatha yenani lokubuyisela kufanele lwaziswe enhlokweni yenqubo Umsebenzi.
• Okuguquguqukayo okuqukethe inani lokubuyisela kufanele kuqanjwe ngendlela efanayo nenqubo Umsebenzi. Lokhu okuguquguqukayo akudingi ukumenyezelwa ngokwehlukana, njengoba kuhlala kukhona njengengxenye ebalulekile yenqubo. Umsebenzi.

Lokhu kuboniswe kahle esibonelweni esilandelayo.

Isibonelo Somsebenzi we-VBA: Ukwenza Umsebenzi Wezibalo kuzinombolo ezi-3

Okulandelayo yisibonelo sekhodi yenqubo ye-VBA Umsebenzi, okuthatha izimpikiswano ezintathu zohlobo Double (izinombolo zamaphuzu antantayo anembayo kabili). Ngenxa yalokho, inqubo ibuyisela enye inombolo yohlobo Doubleilingana nesamba sezimpikiswano ezimbili zokuqala kukhishwe impikiswano yesithathu:

I-Function SumMinus(dNum1 Njengekabili, i-dNum2 Njengekabili, dNum3 Ngokuphindwe kabili) Njenge-Double SumMinus = dNum1 + dNum2 - dNum3 End Function

Le nqubo ye-VBA elula kakhulu Umsebenzi ibonisa ukuthi idatha idluliselwa kanjani enqubweni ngokusebenzisa izimpikiswano. Ungabona ukuthi uhlobo lwedatha olubuyiswe yinqubo luchazwa ngokuthi Double (amazwi athi NjengoMbili ngemva kohlu lwezimpikiswano). Lesi sibonelo sibonisa nendlela umphumela wenqubo Umsebenzi egcinwe kokuguquguqukayo okunegama elifanayo negama lenqubo.

Ukubiza inqubo ye-VBA "Umsebenzi"

Uma inqubo elula ngenhla Umsebenzi efakwe kumojula kusihleli se-Visual Basic, ingabizwa ngezinye izinqubo ze-VBA noma isetshenziswe ekhasini lokusebenza encwadini yokusebenzela ye-Excel.

Shayela inqubo ye-VBA “Umsebenzi” kwenye inqubo

Inqubo Umsebenzi ingabizwa kwenye inqubo ye-VBA ngokumane yabele leyo nqubo kokuguquguqukayo. Isibonelo esilandelayo sibonisa ukubizelwa kwenqubo Isifinyezo, echazwe ngenhla.

Ingqikithi encane() Fiphalisa inani njengenani eliphindwe kabili = SumMinus(5, 4, 3) Qeda Ingxenye

Shayela inqubo ye-VBA “Umsebenzi” ekhasini lokusebenza

Inqubo ye-VBA Umsebenzi ingabizwa ekhasini lokusebenzela le-Excel ngendlela efanayo nanoma yimuphi omunye umsebenzi owakhelwe ngaphakathi we-Excel. Ngakho-ke, inqubo edalwe esibonelweni sangaphambilini Umsebenzi - Isifinyezo ingabizwa ngokufaka isisho esilandelayo kuseli yekhasi lokusebenzela:

=SumMinus(10, 5, 2)

Inqubo ye-VBA "Sub"

Umhleli we-VBA uyaqonda ukuthi kunenqubo phambi kwayo Okungaphansilapho ihlangana neqembu lemiyalo evalelwe phakathi kwalezi zitatimende zokuvula nokuvala ezilandelayo:

Sub ... Qeda I-sub

Inqubo ye-VBA “I-Sub”: Isibonelo 1. Ukuqondanisa maphakathi nosayizi wefonti kuhlu olukhethiwe lwamaseli

Cabanga ngesibonelo senqubo ye-VBA elula Okungaphansi, umsebenzi wakhe owukushintsha ukufometha kohlu olukhethiwe lwamaseli. Amaseli amaphakathi (kokubili mpo nakwezivundlile) futhi usayizi wefonti ushintshelwa kulowo oshiwo umsebenzisi:

Ifomethi_Emaphakathi_Kanti_Nobukhulu(Inketho ye-iFontSize Njengenani eliphelele = 10) Ukukhethwa.Ukuqondanisa Okuvundlile = Ukukhetha kwe-xlCenter.VerticalAlignment = Ukukhetha kwe-xlCenter.Font.Size = iFontSize End Sub

Le nqubo Okungaphansi yenza izenzo kodwa ayibuyisi umphumela.

Lesi sibonelo futhi sisebenzisa i-agumenti Yokuzikhethela Usayizi Wefonti. Uma ingxabano Usayizi Wefonti akudluliswanga kunqubo Okungaphansi, khona-ke inani layo elizenzakalelayo lingu-10. Nokho, uma impikiswano Usayizi Wefonti kudluliselwe kunqubo Okungaphansi, bese uhla olukhethiwe lwamaseli luzosethwa kusayizi wefonti oshiwo umsebenzisi.

Inqubo Engaphansi ye-VBA: Isibonelo sesi-2: Qondanisa Maphakathi Nefonti Egqamile Ebangeni Elikhethiwe Lamaseli

Inqubo elandelayo ifana naleyo esisanda kuxoxwa ngayo, kodwa kulokhu, esikhundleni sokushintsha usayizi, isebenzisa isitayela sefonti esigqamile ebangeni elikhethiwe lamaseli. Lena inqubo eyisibonelo Okungaphansi, engathathi izingxabano:

Ifomethi Engaphansi_Emaphakathi_Ne_Bold() Ukukhetha.Ukuqondanisa Okuvundlile = Ukukhetha kwe-xlCenter.Ukuqondanisa okuqondile = Ukukhetha kwe-xlCenter.Font.Bold = Isihloko Sokuphela Kwangempela

Inqubo yokubiza "Sub" ku-Excel VBA

Shayela inqubo ye-VBA “Sub” kwenye inqubo

Ukushayela inqubo ye-VBA Okungaphansi kwenye inqubo ye-VBA, udinga ukubhala igama elingukhiye Call, igama lenqubo Okungaphansi futhi kubakaki kukhona izimpikiswano zenqubo. Lokhu kuboniswa esibonelweni esingezansi:

I-Sub main() Ifomethi Yekholi_I-Centered_And_Sized(20) Qeda Ingqikithi

Uma inqubo Ifomethi_Imaphakathi_Futhi_Usayizi zinezimpikiswano ezingaphezu kweyodwa, kufanele zihlukaniswe ngokhefana. Thanda lokhu:

I-Sub main() Ifomethi Yekholi_Imaphakathi_Ne_Kakhulu(arg1, arg2, ...) Qeda Isihloko

Shayela inqubo ye-VBA “Sub” ekhasini lokusebenza

Inqubo Okungaphansi ayikwazi ukufakwa ngqo kuseli leshidi le-Excel, njengoba kungenziwa ngenqubo Umsebenzingoba inqubo Okungaphansi ayibuyisi inani. Nokho, izinqubo Okungaphansi, ezingenazo izimpikiswano futhi ezimenyezelwa njenge Public (njengoba kukhonjisiwe ngezansi) izotholakala kubasebenzisi beshidi lokusebenza. Ngakho, uma izinqubo ezilula okuxoxwe ngazo ngenhla Okungaphansi ifakwe kumojula ku-Visual Basic Editor, inqubo Ifomethi_Imaphakathi_Futhi_Bold izotholakala ukuze isetshenziswe ku-Excel ishidi, kanye nenqubo Ifomethi_Imaphakathi_Futhi_Usayizi – ngeke ibe khona ngoba inezingxabano.

Nansi indlela elula yokusebenzisa (noma ukwenza) inqubo Okungaphansi, kufinyeleleka ekhasini lokusebenza:

• Cindezela I-Alt + F8 (cindezela ukhiye Alt futhi ngenkathi uyibambe phansi, cindezela inkinobho F8).
• Ohlwini lwamamakhro oluvelayo, khetha ofuna ukulisebenzisa.
• Cindezela Qalisa (gijima)

Ukwenza inqubo Okungaphansi ngokushesha futhi kalula, ungakwazi ukunikeza isinqamuleli sekhibhodi kuyo. Kwalokhu:

• Cindezela I-Alt + F8.
• Ohlwini lwamamakhro avelayo, khetha leyo ofuna ukwabela kuyo isinqamuleli sekhibhodi.
• Cindezela Amapharamitha (Izinketho) nasebhokisini lengxoxo elivelayo, faka isinqamuleli sekhibhodi.
• Cindezela OK bese uvala ibhokisi I-Macro (Makhro).

ukunakwa: Lapho unikeza isinqamuleli sekhibhodi kumakhro, qiniseka ukuthi asisetshenziswa njengokujwayelekile ku-Excel (isibonelo, Ctrl + C). Uma ukhetha isinqamuleli sekhibhodi esivele sikhona, sizokwabelwa kabusha ku-macro, futhi ngenxa yalokho, umsebenzisi angaqala i-macro ngengozi.

I-VBA Procedure Scope

Ingxenye 2 yalesi sifundo idingide ububanzi bokuguquguquka nokushintshashintsha kanye nendima yamagama angukhiye. Public и Private. Lawa magama angukhiye angasetshenziswa nezinqubo ze-VBA:

I-Public Sub AddToCells(i As Integer) ... Qeda I-sub

I-Private Sub AddToCells(i As Integer) ... Qeda I-sub

Khumbula ukuthi uma ngaphambi kokumemezela inqubo ye-VBA Umsebenzi or Okungaphansi Igama elingukhiye alifakiwe, impahla emisiwe isethelwe inqubo Public (okungukuthi, izotholakala yonke indawo kule phrojekthi ye-VBA). Lokhu kuphambene nezimemezelo eziguquguqukayo, okuthi ngokuzenzakalelayo zibe Private.

Ukuphuma ngaphambi kwesikhathi ezinqubweni ze-VBA "Umsebenzi" kanye "Ne-Sub"

Uma udinga ukunqamula ukwenziwa kwenqubo ye-VBA Umsebenzi or Okungaphansi, ngaphandle kokulinda ukuphela kwayo kwemvelo, khona-ke kulokhu kukhona ama-opharetha Phuma Emsebenzini и Phuma ku-Sub. Ukusetshenziswa kwalaba opharetha kuboniswe ngezansi kusetshenziswa inqubo elula njengesibonelo. UmsebenziA elindele ukuthola i-agumenti evumayo ukuze yenze eminye imisebenzi. Uma inani elingelona iqiniso lidluliselwa kunqubo, akukho okunye ukusebenza okungenziwa, ngakho-ke umsebenzisi kufanele aboniswe umlayezo wephutha futhi inqubo kufanele iphume ngokushesha:

Umsebenzi VAT_Inani(sVAT_Rate Njengenye) Njenge-VAT_Amount Eyodwa = 0 Uma sVAT_Rate <= 0 Bese MsgBox "Bekulindeleke inani Elihle le-sVAT_Rate kodwa Yamukelwe " & sVAT_Rate Umsebenzi Wokuphuma Uphela Uma ... Qeda Umsebenzi

Sicela uqaphele ukuthi ngaphambi kokuqeda inqubo Umsebenzi - VAT_Inani, umsebenzi we-VBA owakhelwe ngaphakathi ufakwa kukhodi I-MsgBox, ebonisa isigelekeqe esiyisixwayiso kumsebenzisi.