"Basa" uye "Sub" Maitiro muVBA

Yakavakwa-muVBA Mabasa

Usati watanga kugadzira yako wega VBA mabasa, zvakanaka kuziva kuti Excel VBA ine yakapfuma muunganidzwa we prebuilt-mukati mabasa aunogona kushandisa uchinyora kodhi yako.

Rondedzero yeaya mabasa anogona kutariswa muVBA mupepeti:

• Vhura bhuku rebasa reExcel uye tanga iyo VBA mupepeti (tinya kuti uite izvi Alt + F11), wobva wadzvanya F2.
• Sarudza raibhurari kubva pane yekudonhedza pasi kumusoro kuruboshwe rwechidzitiro VBA.
• Rondedzero yeakavakwa-muVBA makirasi uye mabasa achaonekwa. Dzvanya pazita rebasa kuti uratidze tsananguro pfupi pazasi pehwindo. kudzvanya F1 ichavhura peji yekubatsira yepamhepo yechinhu ichocho.

Uye zvakare, runyoro rwakakwana rweakavakirwa-muVBA mabasa ane mienzaniso anogona kuwanikwa paVisual Basic Developer Center.

Tsika maitiro "Basa" uye "Sub" muVBA

MuExcel Visual Basic, seti yemirairo inoita rimwe basa inoiswa mukuita. basa (Basa) kana Sub (Subroutine). Musiyano mukuru pakati pemaitiro basa и Sub ndiwo maitiro acho basa inodzosera mhedzisiro, maitiro Sub - kwete.

Naizvozvo, kana iwe uchida kuita zviito uye kuwana imwe mhedzisiro (somuenzaniso, summary nhamba dzinoverengeka), ipapo maitiro anowanzo shandiswa basa, uye kuti ungoita zvimwe zviito (semuenzaniso, shandura mafomati eboka remasero), unofanirwa kusarudza maitiro. Sub.

Arguments

Yakasiyana-siyana data inogona kupfuudzwa kuVBA maitiro uchishandisa nharo. Rondedzero yenharo inotsanangurwa painozivisa nzira. Somuenzaniso, nzira Sub muVBA inowedzera iyo yakapihwa nhamba (Integer) kune yega yega sero mune yakasarudzwa renji. Iwe unogona kupfuudza iyi nhamba kune maitiro uchishandisa gakava, seizvi:

Sub AddToCells(I As Integer) ... End Sub

Ramba uchifunga kuti kuva nekukakavadzana kwemaitiro basa и Sub muVBA isarudzo. Mamwe maitiro haadi nharo.

Optional nharo

VBA maitiro anogona kuve nesarudzo nharo. Aya ndiwo nharo ayo mushandisi anogona kutsanangura kana achida, uye kana akasiiwa, maitiro anoshandisa iwo default tsika kwavari.

Kudzokera kumuenzaniso wapfuura, kuita nharo huru kune basa rekusarudza, raizoziviswa seizvi:

Sub AddToCells(Inosarudzika ini Sezvo Integer = 0)

Muchiitiko ichi, nhamba yakakwana i iyo default ichave 0.

Panogona kuve nekupokana kwakati wandei mukuita, ese akanyorwa panoperera rondedzero yenharo.

Kupfuura nharo nekukosha uye nereferensi

Nharo muVBA dzinogona kupfuudzwa kune imwe nzira nenzira mbiri:

• ByVal – kupfuudza nharo nekukosha. Izvi zvinoreva kuti kukosha chete (kureva, kopi yenharo) inopfuudzwa kune iyo nzira, uye saka chero shanduko inoitwa kune gakava mukati memaitiro icharasika kana maitiro abuda.
• ByRef - kupfuudza nharo nereferensi. Ndiko kuti, iyo chaiyo kero yenharo nzvimbo mundangariro inopfuudzwa kune maitiro. Chero shanduko yakaitwa kune gakava mukati memaitiro ichachengetedzwa kana maitiro abuda.

Kushandisa mazwi makuru ByVal or ByRef mukuzivisa kwemaitiro, unogona kutsanangura kuti gakava rinopfuudzwa sei kune maitiro. Izvi zvinoratidzwa mumienzaniso iri pasi apa:

Sub AddToCells(ByVal i As Integer) ... End Sub

Sub AddToCells(ByRef i As Integer) ... End Sub

Rangarira kuti nharo muVBA dzinopfuudzwa nereferensi nekukasira. Mune mamwe mazwi, kana mazwi makuru asina kushandiswa ByVal or ByRef, ipapo nharo yacho ichapfuudzwa nereferensi.

Usati waenderera mberi nemaitiro basa и Sub mune zvakadzama, zvichave zvinobatsira kuti titarise zvakare maitiro uye misiyano pakati pemhando mbiri idzi dzemaitiro. Izvi zvinotevera hurukuro pfupi dzeVBA maitiro basa и Sub uye mienzaniso iri nyore inoratidzwa.

VBA nzira "Basa"

Mupepeti weVBA anoziva maitiro basakana ikasangana neboka remirairo yakavharirwa pakati pezvinotevera zvekuvhura nekuvhara zvirevo:

Basa ... End Function

Sezvambotaurwa, maitiro basa muVBA (kusiyana ne Sub) inodzosera kukosha. Mitemo inotevera inoshanda pakudzorera kukosha:

• Iyo data yemhando yemutengo wekudzoka inofanira kuziviswa mumusoro weiyo maitiro basa.
• Musiyano une kukosha kwekudzoka unofanirwa kunzi zvakafanana nemaitiro basa. Iyi shanduko haidi kuziviswa zvakasiyana, sezvo inogara iripo sechikamu chakakosha chemaitiro. basa.

Izvi zvinonyatsoratidzwa mumuenzaniso unotevera.

VBA Basa Muenzaniso: Kuita Masvomhu Operation pa3 Nhamba

Izvi zvinotevera muenzaniso weVBA process code basa, iyo inotora nharo nhatu dzemhando Double (nhamba mbiri-chaizvo zvinoyangarara). Somugumisiro, nzira yacho inodzorera imwe nhamba yemhando Doublezvakaenzana nehuwandu hwenharo mbiri dzekutanga kubvisa nharo yechitatu:

Basa SumMinus(dNum1 SePeviri, dNum2 Yakapetwa Kaviri, dNum3 Yaviri) SeKaviri SumMinus = dNum1 + dNum2 - dNum3 End Function

Iyi nzira yakapusa yeVBA basa inoratidza nzira iyo data inopfuudzwa kune nzira kuburikidza nenharo. Iwe unogona kuona kuti iyo data data yakadzoserwa nemaitiro inotsanangurwa se Double (mazwi anoti Sezvo Double mushure merondedzero yenharo). Uyu muenzaniso unoratidzawo kuti mhedzisiro yemaitiro basa yakachengetwa mushanduko ine zita rimwechete sezita remaitiro.

Kudaidza iyo VBA maitiro "Basa"

Kana pamusoro nzira nyore basa yakaiswa mumodule muVisual Basic edhita, inogona kudanwa kubva kune mamwe maitiro eVBA kana kushandiswa pabepa rekushanda muExcel workbook.

Dana VBA maitiro "Basa" kubva kune imwe nzira

nzira basa inogona kudanwa kubva kune imwe nzira yeVBA nekungopa iyo nzira kune inoshanduka. Muenzaniso unotevera unoratidza kudanwa kune maitiro Summinus, iyo yakatsanangurwa pamusoro apa.

Sub main() Dim total as Double total = SumMinus(5, 4, 3) End Sub

Dana VBA maitiro "Basa" kubva papepa rebasa

VBA nzira basa inogona kudanwa kubva kuExcel pepa rekushanda nenzira imwechete sechero imwe yakavakirwa-muExcel basa. Nokudaro, nzira yakagadzirwa mumuenzaniso wekare basa - Summinus inogona kudanwa nekuisa chirevo chinotevera musero repepa rebasa:

=SumMinus(10, 5, 2)

VBA nzira "Sub"

Iyo VBA mupepeti inonzwisisa kuti kune maitiro pamberi payo Subkana ikasangana neboka remirairo yakavharirwa pakati pezvinotevera zvekuvhura nekuvhara zvirevo:

Sub ... End Sub

VBA maitiro "Sub": Muenzaniso 1. Kurongeka kwepakati uye saizi yefonti shanduko mune yakasarudzwa masero

Chimbofunga muenzaniso wenzira iri nyore yeVBA Sub, ane basa rekushandura mafomati emhando yakasarudzwa yemasero. Masero akatarisana (zvese zvakadzika uye zvakachinjika) uye saizi yefonti inochinjirwa kumushandisi-akatsanangurwa:

Sub Format_Centered_And_Sized(Optional iFontSize As Integer = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

Maitiro aya Sub inoita zviito asi haidzore mhedzisiro.

Uyu muenzaniso unoshandisawo Optional nharo FontSize. Kana nharo FontSize haina kupfuudzwa kune process Sub, ipapo kukosha kwayo kwekutanga ndeye 10. Zvisinei, kana nharo FontSize yakapfuudzwa kune nzira Sub, ipapo mutsara wakasarudzwa wemasero uchaiswa kune saizi yefonti inotsanangurwa nemushandisi.

VBA Sub Procedure: Muenzaniso 2: Pakati Rongedza uye Bold Font mune Yakasarudzwa Range Yemasero

Maitiro anotevera akafanana neachangobva kukurukurwa, asi panguva ino, pachinzvimbo chekuchinja saizi, inoshandisa yakashinga font kurudzi rwakasarudzwa rwemasero. Uyu ndiwo muenzaniso maitiro Sub, izvo zvisingatore nharo:

Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub

Kufona "Sub" Maitiro muExcel VBA

Fonera VBA maitiro "Sub" kubva kune imwe maitiro

Kufonera VBA maitiro Sub kubva kune imwe nzira yeVBA, unofanirwa kunyora izwi rakakosha kufona, zita remaitiro Sub uyezve mumaparenthesi pane nharo dzemaitirwo acho. Izvi zvinoratidzwa mumuenzaniso uri pasi apa:

Sub main() Call Format_Centered_And_Sized(20) End Sub

Kana maitiro Format_Centered_Uye_Size ine nharo dzinopfuura imwe, dzinofanirwa kupatsanurwa nemakoma. Sezvizvi:

Sub main() Call Format_Centered_And_Sized(arg1, arg2, ...) End Sub

Fonera VBA maitiro "Sub" kubva papepa rebasa

nzira Sub haigone kupinzwa zvakananga muExcel sheet sero, sezvingaitwa nemaitiro basanokuti maitiro Sub haidzoreri kukosha. Zvisinei, maitiro Sub, dzisina nharo uye dzinonzi ruzhinji rwevanhu (sezvinoratidzwa pazasi) ichave iripo kune vashandisi vepepa rebasa. Saka, kana nzira dziri nyore dzakakurukurwa pamusoro apa Sub yakaiswa mumodule muVisual Basic Edhiyo, maitiro Format_Centered_Uye_Bold ichavepo kuti ishandiswe muExcel worksheet, uye maitiro Format_Centered_Uye_Size - hazvizovepo nekuti zvine nharo.

Heino nzira iri nyore yekumhanyisa (kana kuita) maitiro Sub, inowanikwa kubva papepa rekushanda:

• dzvanya Alt + F8 (dzvanya kiyi alt uye pawakaibata pasi, dzvanya kiyi F8).
• Mune runyorwa rwemacros anooneka, sarudza yaunoda kumhanya.
• dzvanya manya (mhanya)

Kuita maitiro Sub nekukurumidza uye zviri nyore, unogona kugovera nzira yekudimbudzira kiibhodhi kwairi. Nokuda kweizvi:

• dzvanya Alt + F8.
• Mune runyorwa rwemacros anooneka, sarudza iyo yaunoda kupa iyo keyboard nzira yekudimbudzira.
• dzvanya Parameters (Sarudzo) uye mubhokisi rebhokisi rinoonekwa, isa iyo keyboard yekudimbudzira.
• dzvanya OK wovhara dialog Macro (Makoro).

Attention: Paunenge uchipa nzira yekudimbudzira yekhibhodi kune macro, ita shuwa kuti haisi kushandiswa seyakajairwa muExcel (semuenzaniso, Ctrl + C) Kana ukasarudza yatovepo kabhodhi kapfupi, inozopihwa kune iyo macro, uye semhedzisiro, mushandisi anogona kutanga macro netsaona.

VBA Procedure Scope

Chikamu chechipiri chechidzidzo ichi chakakurukura nezve chiyero chezvinosiyana uye zvinogara uye basa remazwi akakosha. ruzhinji rwevanhu и Private. Aya mazwi akakosha anogona zvakare kushandiswa neVBA maitiro:

Public Sub AddToCells(I As Integer) ... End Sub

Private Sub AddToCells(I As Integer) ... End Sub

Rangarira kuti kana usati wazivisa nzira yeVBA basa or Sub keyword haina kuiswa, iyo default pfuma inogadzirirwa maitiro ruzhinji rwevanhu (ndiko kuti, ichave iripo kwese kwese mune ino VBA chirongwa). Izvi zvakasiyana nezvirevo zvakasiyana-siyana, izvo nekusingaperi zviri Private.

Kubuda kwekutanga kubva kuVBA maitiro "Function" uye "Sub"

Kana iwe uchida kumisa kuitwa kweVBA maitiro basa or Sub, pasina kumirira kuguma kwayo kwechisikigo, zvino kune izvi kune vashandi Buda Basa и Buda Sub. Kushandiswa kwevashandi ava kunoratidzwa pazasi uchishandisa nzira iri nyore semuenzaniso. basaA inotarisira kugamuchira nharo yakanaka yekuita mamwe mabasa. Kana iyo isiri-yakanaka kukosha ikapfuudzwa kune iyo maitiro, saka hapana kumwe kuvhiya kunogona kuitwa, saka mushandisi anofanira kuratidzwa meseji yemhosho uye maitiro anofanira kubuda nekukurumidza:

Basa VAT_Amount(sVAT_Rate SeImwechete) SeIngle VAT_Amount = 0 Kana sVAT_Rate <= 0 Zvino MsgBox "Taitarisira kukosha Kwakanaka kwesVAT_Rate asi Kwagamuchirwa " & sVAT_Rate Kubuda Basa Rinopera Kana ... Kupera Basa

Ndapota cherechedza kuti usati wapedza maitiro basa - VAT_Mari, yakavakirwa-mukati VBA basa inoiswa mukodhi MsgBox, iyo inoratidza yambiro popup kumushandisi.