Ayyukan "Aiki" da "Sub" a cikin VBA

Ayyukan VBA da aka gina a ciki

Kafin ka fara ƙirƙirar ayyukan VBA naka, yana da kyau ka san cewa Excel VBA yana da tarin tarin ayyukan da aka gina a ciki wanda za ka iya amfani da su yayin rubuta lambar ku.

Ana iya duba jerin waɗannan ayyuka a cikin editan VBA:

• Bude littafin aikin Excel kuma ƙaddamar da editan VBA (danna don yin wannan Alt+F11), sannan kuma latsa F2.
• Zaɓi ɗakin karatu daga jerin zaɓuka a saman hagu na allon VBA.
• Jerin ginanniyar azuzuwan VBA da ayyuka zai bayyana. Danna sunan aikin don nuna taƙaitaccen bayaninsa a ƙasan taga. dannawa F1 zai buɗe shafin taimako na kan layi don wannan fasalin.

Bugu da kari, ana iya samun cikakken jerin ayyukan ginanniyar ayyukan VBA tare da misalai a Cibiyar Haɓaka Kayayyakin Kayayyakin Kayayyakin Kaya.

Hanyoyin al'ada "Aiki" da "Sub" a cikin VBA

A cikin Excel Visual Basic, ana sanya saitin umarni waɗanda ke yin takamaiman aiki a cikin hanya. aiki (Aiki) ko sub (Subroutine). Babban bambanci tsakanin hanyoyin aiki и sub ita ce hanya aiki dawo da sakamakon, hanya sub – ba.

Sabili da haka, idan kuna buƙatar yin ayyuka kuma ku sami sakamako (misali, tara lambobi da yawa), to yawanci ana amfani da hanyar aiki, kuma don kawai yin wasu ayyuka (misali, canza tsarin rukunin sel), kuna buƙatar zaɓar hanyar. sub.

Tambayoyi

Ana iya wuce bayanai daban-daban zuwa hanyoyin VBA ta amfani da muhawara. An ƙayyade lissafin gardama lokacin bayyana hanya. Misali, hanya sub a cikin VBA yana ƙara adadin da aka bayar (Integer) ga kowane tantanin halitta a cikin kewayon da aka zaɓa. Kuna iya shigar da wannan lambar zuwa tsarin ta amfani da hujja, kamar haka:

Sub AddToCells(i As Integer) ... Ƙarshe Sub

Ka tuna cewa samun gardama don hanyoyin aiki и sub a cikin VBA na zaɓi ne. Wasu hanyoyin ba sa buƙatar gardama.

Hujja Na Zabi

Hanyoyin VBA na iya samun gardama na zaɓi. Waɗannan gardama ne waɗanda mai amfani zai iya tantance idan yana so, kuma idan an cire su, hanyar tana amfani da tsoffin ƙima a gare su.

Komawa ga misalin da ya gabata, don yin hujjar intiger zuwa aikin zaɓin zaɓi, za a bayyana shi kamar haka:

Sub AddToCells (Na zaɓi i As Integer = 0)

A wannan yanayin, muhawarar lamba i tsoho zai zama 0.

Ana iya samun dalilai na zaɓi da yawa a cikin hanya, waɗanda duk an jera su a ƙarshen jerin gardama.

Wucewa muhawara ta ƙima kuma ta hanyar tunani

Ana iya ƙaddamar da muhawara a cikin VBA zuwa hanya ta hanyoyi biyu:

• ByVal – wuce hujja da kima. Wannan yana nufin cewa kawai ƙimar (wato kwafin hujja) an wuce zuwa tsarin, don haka duk wani canje-canjen da aka yi ga gardama a cikin hanyar zai ɓace lokacin da hanya ta fita.
• ByRef – wuce hujja ta hanyar tunani. Wato, ainihin adireshin wurin gardama a cikin ƙwaƙwalwar ajiya an wuce zuwa hanya. Duk wani canje-canje da aka yi ga gardama a cikin hanya za a adana shi lokacin da hanya ta fita.

Amfani da keywords ByVal or ByRef a cikin sanarwar hanya, za ku iya ƙayyade yadda aka ƙaddamar da hujja zuwa hanya. Ana nuna wannan a cikin misalan da ke ƙasa:

Sub AddToCells(ByVal da As Integer) ... Ƙarshe Sub

Sub AddToCells(ByRef i As Integer) ... Ƙarshe Sub

Ka tuna cewa muhawara a cikin VBA ana wucewa ta hanyar tunani ta tsohuwa. A wasu kalmomi, idan ba a yi amfani da kalmomi masu mahimmanci ba ByVal or ByRef, to, za a wuce hujja ta hanyar tunani.

Kafin ci gaba da hanyoyin aiki и sub dalla-dalla, zai zama da amfani mu sake duba fasali da bambance-bambancen da ke tsakanin waɗannan nau'ikan hanyoyin guda biyu. Wadannan taƙaitaccen tattaunawa ne na hanyoyin VBA aiki и sub kuma ana nuna misalai masu sauƙi.

Hanyar VBA "Aiki"

Editan VBA ya gane hanya aikilokacin da ta ci karo da rukunin umarni da ke kewaye tsakanin waɗannan bayanan buɗewa da rufewa:

Aiki ... Ƙarshen Aiki

Kamar yadda aka ambata a baya, hanya aiki a cikin VBA (kamar yadda ya saba da sub) mayar da darajar. Dokoki masu zuwa suna aiki don mayar da ƙima:

• Dole ne a bayyana nau'in bayanan ƙimar dawowar a cikin taken hanya aiki.
• Maɓallin da ya ƙunshi ƙimar dawowa dole ne a sanya masa suna daidai da tsarin aiki. Wannan canjin ba ya buƙatar bayyana shi daban, saboda koyaushe yana kasancewa a matsayin wani ɓangare na hanya. aiki.

An kwatanta wannan da kyau a misali na gaba.

Misalin Ayyukan VBA: Yin Aikin Lissafi akan Lambobi 3

Mai zuwa shine misalin lambar hanya ta VBA aiki, wanda ke ɗaukar dalilai guda uku na nau'in biyu (madaidaicin lambobi biyu masu iyo). A sakamakon haka, hanya ta dawo da wani nau'in nau'i biyudaidai da jimlar muhawara biyun farko ban da hujja ta uku:

Aiki SumMinus(dNum1 Kamar Biyu, dNum2 A Matsayin Biyu, dNum3 A Matsayin Biyu) Kamar SumMinus Biyu = dNum1 + dNum2 - dNum3 Ƙarshen Aiki

Wannan hanya mai sauƙi na VBA aiki yana kwatanta yadda ake isar da bayanai zuwa hanya ta hanyar muhawara. Kuna iya ganin cewa nau'in bayanan da aka dawo da shi an bayyana shi azaman biyu (kalmomin suna cewa Kamar Biyu bayan jerin dalilai). Wannan misali kuma yana nuna yadda sakamakon hanya aiki adana a cikin wani m tare da iri ɗaya da sunan hanya.

Kira tsarin VBA "Aiki"

Idan na sama sauki hanya aiki shigar a cikin wani tsari a cikin Editan Kayayyakin Kaya, ana iya kiran shi daga wasu hanyoyin VBA ko amfani da shi akan takardar aiki a cikin littafin aikin Excel.

Kira tsarin VBA "Aiki" daga wata hanya

hanya aiki ana iya kiran shi daga wata hanya ta VBA ta hanyar sanya wannan hanya zuwa mai canzawa. Misali mai zuwa yana nuna kira zuwa hanya Summinus, wanda aka bayyana a sama.

Babban babban () Dim jimlar azaman jimlar Biyu = SumMinus(5, 4, 3) Ƙarshe Sub

Kira tsarin VBA "Aiki" daga takardar aiki

Hanyoyin ciniki na VBA aiki ana iya kiran shi daga takardar aikin Excel kamar yadda kowane aikin Excel da aka gina a ciki. Saboda haka, hanyar da aka halitta a cikin misali na baya aiki - Summinus ana iya kira ta hanyar shigar da magana mai zuwa cikin tantanin halitta:

=SumMinus(10, 5, 2)

Hanyar VBA "Sub"

Editan VBA ya fahimci cewa akwai hanya a gabansa sublokacin da ta ci karo da rukunin umarni da ke kewaye tsakanin waɗannan bayanan buɗewa da rufewa:

Sub ... Ƙarshe Sub

Hanyar VBA “Sub”: Misali 1. Daidaita tsakiya da canjin girman rubutu a cikin kewayon sel da aka zaɓa

Yi la'akari da misalin hanyar VBA mai sauƙi sub, wanda aikinsa shine canza tsarin da aka zaɓa na sel. Kwayoyin suna tsakiya (duka a tsaye da a kwance) kuma ana canza girman font zuwa takamaiman mai amfani:

Sub Format_Centered_And_Sized(Zaɓi iFontSize As Integer = 10) Zaɓin.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize Ƙarshen Sub.

Wannan hanya sub yana aiwatar da ayyuka amma baya mayar da sakamako.

Wannan misalin kuma yana amfani da hujja na zaɓi Girman Font. Idan hujja Girman Font ba a wuce zuwa tsari ba sub, to ƙimar sa ta asali shine 10. Duk da haka, idan hujja Girman Font wuce zuwa hanya sub, to za a saita kewayon sel da aka zaɓa zuwa girman rubutun da mai amfani ya ƙayyade.

Karamin Tsari na VBA: Misali 2: Daidaita Tsakiya da Rubutun Ƙarfafa a Zaɓaɓɓen Kewayon Sel

Hanya mai zuwa tayi kama da wacce aka tattauna yanzu, amma a wannan karon, maimakon sake girmanta, tana amfani da salon rubutu mai ƙarfi ga zaɓaɓɓen kewayon sel. Wannan misali ne hanya sub, wanda ba ya ɗaukar hujja:

Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = Gaskiya Karshen Sub.

Kira tsarin "Sub" a cikin Excel VBA

Kira tsarin VBA "Sub" daga wata hanya

Don kiran hanyar VBA sub daga wata hanya ta VBA, kuna buƙatar rubuta kalmar keyword Call, tsarin sunan sub kuma gaba a cikin bakan gizo akwai muhawarar hanya. Ana nuna wannan a cikin misalin da ke ƙasa:

Sub main() Tsarin Kira_Centered_And_Sized(20) Karshen Sub

Idan hanya Tsarin_Center_Da_ Girma yana da hujja fiye da ɗaya, dole ne a raba su da waƙafi. Kamar wannan:

Sub main() Tsarin Kira_Centered_And_Sized(arg1, arg2, ...) Ƙarshen Ƙarshe

Kira tsarin VBA "Sub" daga takardar aiki

hanya sub ba za a iya shigar da kai tsaye a cikin tantanin halitta na Excel ba, kamar yadda za a iya yi tare da hanya aikisaboda tsari sub baya mayar da kima. Duk da haka, hanyoyin sub, waɗanda ba su da hujja kuma an bayyana su azaman Jama'a (kamar yadda aka nuna a ƙasa) zai kasance samuwa ga masu amfani da takardar aikin. Don haka, idan hanyoyi masu sauƙi da aka tattauna a sama sub shigar a cikin wani module a cikin Kayayyakin Basic Edita, da hanya Tsarin_Center_Da_Bold Za a samu don amfani a cikin takardar aikin Excel, da kuma hanya Tsarin_Center_Da_ Girma - ba za a samu ba saboda yana da muhawara.

Anan akwai hanya mai sauƙi don gudanar da (ko aiwatar da) hanya sub, ana iya samun dama daga takardar aikin:

• latsa Alt+F8 (latsa maɓallin alt kuma yayin riƙe shi ƙasa, danna maɓallin F8).
• A cikin jerin macros da ya bayyana, zaɓi wanda kake son gudu.
• latsa Run (gudu)

Don aiwatar da hanya sub da sauri da sauƙi, zaku iya sanya masa gajeriyar hanyar madannai. Don wannan:

• latsa Alt+F8.
• A cikin jerin macros da ya bayyana, zaɓi wanda kake son sanya gajeriyar hanyar keyboard zuwa gare shi.
• latsa Siga (Zaɓuɓɓuka) kuma a cikin akwatin maganganu da ya bayyana, shigar da gajeriyar hanyar madannai.
• latsa OK kuma rufe maganganun Macro (Macro).

hankali: Lokacin sanya gajeriyar hanyar madannai zuwa macro, tabbatar da cewa ba a yi amfani da shi azaman misali a cikin Excel ba (misali, Ctrl + C). Idan ka zaɓi gajeriyar hanyar keyboard da ta riga ta kasance, za a sake sanya shi zuwa macro, kuma a sakamakon haka, mai amfani na iya fara macro ta hanyar haɗari.

Iyakar Tsarin VBA

Sashe na 2 na wannan koyawa ya yi magana game da iyawar masu canji da abubuwan da ke faruwa da kuma rawar da ke tattare da kalmomi. Jama'a и Private. Hakanan ana iya amfani da waɗannan kalmomi tare da hanyoyin VBA:

Jama'a Sub AddToCells(i As Integer) ... Ƙarshe Sub

Mai zaman kansa Sub AddToCells(i As Integer) ... Ƙarshe Sub

Ka tuna cewa idan kafin ayyana hanyar VBA aiki or sub Ba a shigar da kalmar maɓalli ba, an saita tsohuwar dukiya don hanya Jama'a (wato, zai kasance a ko'ina cikin wannan aikin na VBA). Wannan ya bambanta da m bayyanai, wanda ta tsohuwa ne Private.

Fita da wuri daga hanyoyin VBA "Aiki" da "Sub"

Idan kana buƙatar dakatar da aiwatar da hanyar VBA aiki or sub, ba tare da jiran ƙarewar halitta ba, to don wannan akwai masu aiki Ayyukan Fita и Mafita Sub. Ana nuna amfani da waɗannan masu aiki a ƙasa ta amfani da hanya mai sauƙi azaman misali. aikiA wanda ke tsammanin samun tabbataccen hujja don yin ƙarin ayyuka. Idan darajar da ba ta da kyau ba ta wuce hanyar ba, to ba za a iya yin ƙarin ayyuka ba, don haka ya kamata a nuna wa mai amfani da saƙon kuskure kuma tsarin ya kamata ya fita nan da nan:

Aiki VAT_Amount(sVAT_Rate A Matsayin Single) A Matsayin VAT_Amount = 0 Idan sVAT_Rate <= 0 Sai MsgBox "An Yi Tsammanin Ƙimar sVAT_Rate amma An Karɓi" & sVAT_Rate Aikin Fitar Ƙarshen Idan ... Ƙarshen Aiki

Lura cewa kafin kammala hanya aiki - VAT_Yawan, an saka aikin VBA da aka gina a cikin lambar MsgBox, wanda ke nuna faɗakarwar faɗakarwa ga mai amfani.