"Ntchito" ndi "Sub" Njira mu VBA

Ntchito zomangidwa mu VBA

Musanayambe kupanga ntchito zanu za VBA, ndikwabwino kudziwa kuti Excel VBA ili ndi zinthu zambiri zomangidwira zomwe mungagwiritse ntchito polemba khodi yanu.

Mndandanda wa ntchitozi ukhoza kuwonedwa mu mkonzi wa VBA:

• Tsegulani buku la Excel ndikuyambitsa VBA mkonzi (dinani kuti muchite izi Alt + F11), ndiyeno pezani F2.
• Sankhani laibulale kuchokera pamndandanda wotsitsa pamwamba kumanzere kwa chinsalu VBA.
• Mndandanda wamakalasi omangidwa mu VBA ndi ntchito zidzawonekera. Dinani pa dzina la ntchito kuti muwonetse kufotokozera kwake mwachidule pansi pawindo. kukanikiza F1 adzatsegula tsamba lothandizira pa intaneti la izi.

Kuphatikiza apo, mndandanda wathunthu wa ntchito zomangidwa mu VBA yokhala ndi zitsanzo zitha kupezeka ku Visual Basic Developer Center.

Njira zamachitidwe "Function" ndi "Sub" mu VBA

Mu Excel Visual Basic, mndandanda wa malamulo omwe amagwira ntchito inayake amayikidwa mu ndondomeko. ntchito (ntchito) kapena Sub (Subroutine). Kusiyana kwakukulu pakati pa ndondomeko ntchito и Sub ndiye ndondomeko ntchito zotsatira, ndondomeko Sub - ayi.

Chifukwa chake, ngati mukufuna kuchitapo kanthu ndikupeza zotsatira (mwachitsanzo, kuwerengera manambala angapo), ndiye kuti njirayi imagwiritsidwa ntchito nthawi zambiri ntchito, ndipo kuti mungochita zina (mwachitsanzo, kusintha mawonekedwe a gulu la maselo), muyenera kusankha njirayo. Sub.

Mikangano

Deta zosiyanasiyana zitha kuperekedwa ku njira za VBA pogwiritsa ntchito mikangano. Mndandanda wa zotsutsana umatchulidwa polengeza ndondomeko. Mwachitsanzo, ndondomeko Sub mu VBA imawonjezera nambala yoperekedwa (Integer) ku selo iliyonse yomwe yasankhidwa. Mutha kupereka nambala iyi kunjira pogwiritsa ntchito mkangano, monga chonchi:

Sub AddToCells(i Monga Integer) ... End Sub

Kumbukirani kuti kukhala ndi mikangano pa ndondomeko ntchito и Sub mu VBA ndizosankha. Njira zina sizifuna kukangana.

Zosankha Zosankha

Njira za VBA zitha kukhala ndi zotsutsana. Izi ndi zotsutsana zomwe wogwiritsa ntchito angatchule ngati akufuna, ndipo ngati zisiyidwa, ndondomekoyi imagwiritsa ntchito zikhalidwe zokhazikika kwa iwo.

Kubwerera ku chitsanzo cham'mbuyo, kuti tipange mkangano wokwanira ku ntchito yosankha, ingalengezedwe motere:

Sub AddToCells(Mwasankha ine Monga Integer = 0)

Pankhaniyi, mkangano wonse i zosasintha zidzakhala 0.

Pakhoza kukhala zifukwa zingapo zomwe mungasankhire mu ndondomeko, zonse zomwe zalembedwa kumapeto kwa mndandanda wa mikangano.

Kupereka mikangano potengera mtengo wake komanso mwatchutchutchu

Zotsutsana mu VBA zitha kuperekedwa kunjira m'njira ziwiri:

• ByVal - kudutsa mkangano ndi mtengo. Izi zikutanthauza kuti mtengo wokha (ndiko kuti, kopi ya mkangano) umaperekedwa ku ndondomekoyi, choncho kusintha kulikonse komwe kunachitika pa mkangano mkati mwa ndondomekoyi kudzatayika pamene ndondomekoyo ikutuluka.
• NdiRef - kupititsa mkangano pofotokoza. Ndiko kuti, adiresi yeniyeni ya malo a mkangano mu kukumbukira imaperekedwa ku ndondomekoyi. Zosintha zilizonse zomwe zingachitike pakukangana mkati mwa njirayi zidzasungidwa njira ikatuluka.

Kugwiritsa ntchito mawu osakira ByVal or NdiRef mu chilengezo cha ndondomeko, mukhoza kufotokoza momwe mkangano umaperekedwa ku ndondomekoyi. Izi zikuwonetsedwa mu zitsanzo pansipa:

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

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

Kumbukirani kuti mikangano mu VBA imaperekedwa mwachisawawa. M'mawu ena, ngati mawu osagwiritsidwa ntchito ByVal or NdiRef, ndiye kuti mkanganowo udzaperekedwa ndi chidziwitso.

Musanayambe ndi ndondomeko ntchito и Sub mwatsatanetsatane, zidzakhala zothandiza kuyang'ananso mawonekedwe ndi kusiyana pakati pa mitundu iwiriyi ya njira. Zotsatirazi ndizokambirana mwachidule za njira za VBA ntchito и Sub ndipo zitsanzo zosavuta zikuwonetsedwa.

VBA ndondomeko "Function"

Mkonzi wa VBA amazindikira ndondomekoyi ntchitoikakumana ndi gulu la malamulo omwe ali pakati pa mawu otsegulira ndi otseka awa:

Ntchito ... Mapeto Ntchito

Monga tanena kale, ndondomeko ntchito mu VBA (mosiyana ndi Sub) imabweretsa mtengo. Malamulo otsatirawa amagwira ntchito pobweza:

• Mtundu wa data wa mtengo wobwerera uyenera kulengezedwa pamutu wa ndondomekoyi ntchito.
• Zosintha zomwe zili ndi mtengo wobwezera ziyenera kutchulidwa mofanana ndi ndondomekoyi ntchito. Kusintha kumeneku sikuyenera kulengezedwa mosiyana, chifukwa nthawi zonse kumakhalapo ngati gawo lofunikira la ndondomekoyi. ntchito.

Izi zikusonyezedwa bwino m’chitsanzo chotsatirachi.

Chitsanzo cha Ntchito ya VBA: Kuchita Ntchito ya Masamu pa Nambala 3

Chotsatira ndi chitsanzo cha ndondomeko ya ndondomeko ya VBA ntchito, zomwe zimatengera mfundo zitatu zamtundu wachiphamaso (nambala zoyandama zolondola kawiri). Zotsatira zake, ndondomekoyi imabwezeretsanso mtundu wina wamtundu wachiphamasoZofanana ndi kuchuluka kwa mfundo ziwiri zoyambirira kuchotsa mtsutso wachitatu:

Ntchito SumMinus(dNum1 Monga Pawiri, dNum2 Monga Pawiri, dNum3 Monga Pawiri) Monga SumMinus Pawiri = dNum1 + dNum2 - dNum3 End Function

Njira yosavuta iyi ya VBA ntchito ikuwonetsa momwe deta imapititsidwira ku ndondomeko kudzera mkangano. Mutha kuwona kuti mtundu wa data womwe wabwezedwa ndi njirayo umatanthauzidwa ngati wachiphamaso (mawu akuti Monga Pawiri pambuyo pa mndandanda wa mikangano). Chitsanzochi chikuwonetsanso momwe zotsatira za ndondomekoyi ntchito zosungidwa muzosintha zomwe zili ndi dzina lofanana ndi dzina la ndondomeko.

Kuyitana njira ya VBA "Function"

Ngati pamwamba yosavuta ndondomeko ntchito oikidwa mu gawo mu Visual Basic editor, akhoza kutchedwa kuchokera ku machitidwe ena a VBA kapena kugwiritsidwa ntchito pa tsamba la ntchito mu Excel workbook.

Imbani njira ya VBA "Function" kuchokera munjira ina

Kayendesedwe ntchito ikhoza kuyitanidwa kuchokera ku njira ina ya VBA pongopereka njirayo ku kusintha. Chitsanzo chotsatirachi chikuwonetsa kuyitanira ku ndondomeko Summinus, zomwe zafotokozedwa pamwambapa.

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

Imbani njira ya VBA "Function" kuchokera patsamba

ndondomeko ya VBA ntchito Itha kuyitanidwa kuchokera patsamba la Excel monga momwe ntchito ina iliyonse yopangidwira mu Excel. Choncho, ndondomeko analengedwa m'mbuyomu chitsanzo ntchito - Summinus itha kuyitanidwa polowetsa mawu otsatirawa mu cell sheet sheet:

=SumMinus(10, 5, 2)

Njira ya VBA "Sub"

Mkonzi wa VBA amamvetsetsa kuti pali njira patsogolo pake Subikakumana ndi gulu la malamulo omwe ali pakati pa mawu otsegulira ndi otseka awa:

Sub ... End Sub

Njira ya VBA "Sub": Chitsanzo 1. Kuyanjanitsa kwapakati ndi kusintha kwa mafonti pamaselo osankhidwa

Taganizirani chitsanzo cha njira yosavuta ya VBA Sub, omwe ntchito yake ndikusintha masanjidwe amitundu yosankhidwa. Maselo ali pakati (mowirikiza ndi mopingasa) ndipo kukula kwa mafonti kumasinthidwa kukhala otchulidwa:

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

Njirayi Sub imagwira ntchito koma osabweza zotsatira.

Chitsanzochi chimagwiritsanso ntchito mtsutso wa Optional FontSize. Ngati mkangano FontSize sichinapitirire ku ndondomeko Sub, ndiye mtengo wake wosasinthika ndi 10. Komabe, ngati mkangano FontSize kupita ku ndondomeko Sub, ndiye kuti maselo osankhidwa adzakhazikitsidwa ku kukula kwa font komwe wogwiritsa ntchito akufotokozera.

Njira Yachigawo cha VBA: Chitsanzo 2: Gwirizanitsani Pakati ndi Zolemba Zolimba M'maselo Osankhidwa

Njira yotsatilayi ndi yofanana ndi imene tangokambitsirana kumene, koma nthawi ino, m’malo mosintha makulidwe ake, imagwiritsa ntchito masitayilo olimba mtima pamaselo osankhidwa. Ichi ndi chitsanzo ndondomeko Sub, zomwe sizimatsutsana:

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

Kuyitana "Sub" Njira mu Excel VBA

Imbani njira ya VBA "Sub" kuchokera munjira ina

Kuyitana njira ya VBA Sub kuchokera ku njira ina ya VBA, muyenera kulemba mawu osakira kuitana, dzina la ndondomeko Sub ndipo m'mabungwe muli mfundo za ndondomekoyi. Izi zikuwonetsedwa mu chitsanzo chomwe chili pansipa:

Sub main() Call Format_Centered_And_Sized(20) End Sub

Ngati ndondomeko Format_Centered_And_Size ali ndi mkangano wopitilira umodzi, uyenera kulekanitsidwa ndi koma. Ngati chonchi:

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

Imbani njira ya VBA "Sub" kuchokera patsamba lantchito

Kayendesedwe Sub silingalowetsedwe mwachindunji mu cell sheet ya Excel, monga momwe tingachitire ndi ndondomeko ntchitochifukwa ndondomeko Sub sichibweza mtengo. Komabe, ndondomeko Sub, zomwe zilibe zotsutsana ndipo zimalengezedwa ngati Public (monga momwe zasonyezedwera pansipa) zidzapezeka kwa ogwiritsa ntchito. Choncho, ngati njira zosavuta tafotokozazi Sub anayikidwa mu gawo mu Visual Basic Editor, ndondomeko Format_Centered_And_Bold idzapezeka kuti igwiritsidwe ntchito mu Excel worksheet, ndi ndondomeko Format_Centered_And_Size - sizipezeka chifukwa zili ndi mikangano.

Nayi njira yosavuta yoyendetsera (kapena kuchita) ndondomeko Sub, kupezeka kuchokera patsamba lantchito:

• Press Alt + F8 (dinani batani alt ndipo poigwira pansi, dinani batani F8).
• Pamndandanda wa macros omwe akuwoneka, sankhani yomwe mukufuna kuyendetsa.
• Press Thamangani (thamanga)

Kuchita ndondomeko Sub mwachangu komanso mosavuta, mutha kupereka njira yachidule ya kiyibodi. Za ichi:

• Press Alt + F8.
• Pamndandanda wa macros omwe akuwoneka, sankhani yomwe mukufuna kuyika njira yachidule ya kiyibodi.
• Press magawo (Zosankha) ndi m'bokosi la zokambirana lomwe likuwonekera, lowetsani njira yachidule ya kiyibodi.
• Press OK ndi kutseka kukambirana Macro (Macro).

chisamaliro: Mukapereka njira yachidule ya kiyibodi ku macro, onetsetsani kuti sikugwiritsidwa ntchito ngati muyezo mu Excel (mwachitsanzo, Ctrl + C). Mukasankha njira yachidule ya kiyibodi yomwe ilipo kale, idzaperekedwanso ku macro, ndipo chifukwa chake, wogwiritsa ntchito akhoza kuyambitsa macro mwangozi.

VBA Procedure Scope

Gawo 2 la phunziroli lidakambirana za kuchuluka kwa zosinthika ndi zosinthika komanso gawo la mawu osakira. Public и Private. Mawu osakirawa atha kugwiritsidwanso ntchito ndi njira za VBA:

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

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

Kumbukirani kuti ngati musanayambe kulengeza ndondomeko ya VBA ntchito or Sub mawu ofunika sanalowetsedwe, katundu wokhazikika amakhazikitsidwa kuti achitidwe Public (ndiko kuti, ipezeka paliponse mu polojekitiyi ya VBA). Izi ndizosiyana ndi zolengeza zosiyanasiyana, zomwe mwachisawawa zimakhala Private.

Kutuluka koyambirira kuchokera ku njira za VBA "Function" ndi "Sub"

Ngati mukufuna kuthetsa kuchitidwa kwa ndondomeko ya VBA ntchito or Sub, popanda kuyembekezera kutha kwake kwachilengedwe, ndiye kuti izi pali ogwira ntchito Tulukani Ntchito и Tulukani Sub. Kugwiritsiridwa ntchito kwa ogwira ntchitowa kukuwonetsedwa pansipa pogwiritsa ntchito njira yosavuta monga chitsanzo. ntchitoA yomwe ikuyembekeza kulandira mtsutso wabwino kuti igwire ntchito zina. Ngati mtengo wosakhala wabwino uperekedwa ku ndondomekoyi, ndiye kuti palibe ntchito zina zomwe zingachitike, chifukwa chake wogwiritsa ntchito ayenera kuwonetsedwa uthenga wolakwika ndipo njirayo iyenera kutuluka nthawi yomweyo:

Ntchito VAT_Amount(sVAT_Rate Monga Imodzi) Monga VAT_Amount Imodzi = 0 Ngati sVAT_Rate <= 0 Ndiye MsgBox "Timayembekezera mtengo Wabwino wa sVAT_Rate koma Walandilidwa " & sVAT_Rate Kutuluka Ntchito Itha Ngati ... Mapeto Ntchito

Chonde dziwani kuti musanamalize ndondomekoyi ntchito - VAT_Ndalama, ntchito yomangidwa mu VBA imayikidwa mu code MsgBox, yomwe imawonetsa mphukira yochenjeza kwa wogwiritsa ntchito.