Kumvetsetsa Zosiyanasiyana ndi Zosiyanasiyana mu Excel Macros

M'nkhaniyi, muphunzira zomwe zimasinthasintha ndi zosinthika zomwe zili mu macros, komwe zingagwiritsidwe ntchito, komanso kusiyana kwakukulu pakati pa mitundu yosiyanasiyana ya deta. Idzawululidwanso chifukwa chake zokhazikika zimafunikira, ngati mutha kungolemba zosinthika osasintha.

Monga zilankhulo zina zamapulogalamu, deta imatha kusungidwa muzosintha kapena zosinthika (zonse zomwe zimatchedwanso kuti zotengera za data). Uku ndiko kusiyana kwakukulu pakati pa mfundozi. Zakale zimatha kusintha malinga ndi zomwe zimachitika mu pulogalamuyi. Komanso, zokhazikika zimayikidwa kamodzi ndipo sizisintha mtengo wake.

Constants zitha kukhala zothandiza ngati mukufuna kugwiritsa ntchito mtengo womwewo kangapo. M'malo motengera nambalayo, mutha kungolemba dzina lokhazikika. Mwachitsanzo, mutha kugwiritsa ntchito nthawi zonse "Pi" kusunga Pi, yomwe ndi mtengo wokhazikika. Ndi yaikulu kwambiri, ndipo nthawi iliyonse imakhala yovuta kuilemba kapena kufufuza ndi kukopera. Ndipo kotero, ndikokwanira kulemba zilembo ziwiri, ndipo chilengedwe chimagwiritsa ntchito nambala yomwe mukufuna.

Wogwiritsa ntchito wa Excel amayenera kulengeza zosintha ngati akufuna kusintha mtengo womwe wasungidwa nthawi ndi nthawi. Mwachitsanzo, mutha kukhazikitsa zosintha zomwe zimatchedwa sVAT_Rate, zomwe zimasunga mtengo wamtengo wapatali wa VAT. Ngati zisintha, mutha kuzikonza mwachangu. Izi ndizothandiza makamaka kwa omwe amachita bizinesi ku United States, komwe katundu wina sangakhale ndi VAT konse (ndipo msonkho uwu umasiyananso ndi mayiko).

Mitundu Yambiri

Chidebe chilichonse cha data chingakhale chimodzi mwamitundu ingapo. Nali tebulo lofotokoza mitundu yokhazikika yazidziwitso zosinthidwa. Pali ambiri a iwo, ndipo zingawoneke kwa oyamba kumene kuti amabwerezana. Koma uku ndi kumverera konyenga. Werengani kuti mudziwe chifukwa chake kutchula mtundu wolondola wa data ndikofunikira kwambiri.

Sitikulimbikitsidwa kugwiritsa ntchito mtundu wa data womwe umatenga malo ambiri kukumbukira manambala ang'onoang'ono. Mwachitsanzo, pa nambala 1, ndikwanira kugwiritsa ntchito mtundu wa Byte. Izi zidzakhala ndi zotsatira zabwino pakugwira ntchito kwa gawo lomwe lingathe kuchitika, makamaka pamakompyuta ofooka. Koma m’pofunika kuti tisapite patali kwambiri pano. Ngati mugwiritsa ntchito mtundu wa data womwe uli wocheperako, mtengo wokulirapo sungakhale momwemo.

Kulengeza Constants ndi Zosintha

Kugwiritsa ntchito chidebe cha data popanda kulengeza koyamba ndikoletsedwa kwambiri. Ndiye mavuto angapo angabwere, kuti apewe zomwe ndizofunikira kulemba mizere yaying'ono ya code ndi kuwerengera kwa zosinthika kapena zokhazikika.

Kulengeza kusinthika, mawu a Dim amagwiritsidwa ntchito. Mwachitsanzo, monga chonchi:

Dim Variable_Name Monga Integer

Variable_Name ndi dzina la kusintha. Kenaka, As operator alembedwa, kusonyeza mtundu wa deta. M'malo mwa zingwe "Variable_Name" ndi "Integer", mutha kuyika dzina lanu ndi mtundu wa data.

Ma Constants amathanso kulengezedwa, koma choyamba muyenera kufotokoza mtengo wake. Chimodzi mwazosankha ndi:

Const iMaxCount = 5000

Mwachilungamo, nthawi zina mutha kuchita popanda kulengeza zosinthika, koma pakadali pano amapatsidwa mtundu wa Variant. Komabe, izi sizikulimbikitsidwa pazifukwa zotsatirazi:

1. Zosintha zimasinthidwa pang'onopang'ono, ndipo ngati pali zosintha zambiri zotere, kukonza zidziwitso kumatha kuchepetsedwa kwambiri pamakompyuta ofooka. Zikuwoneka kuti masekondi amenewo asankha? Koma ngati mukuyenera kulemba mizere yambiri yamakhodi, ndikuyendetsanso pamakompyuta ofooka (omwe amagulitsidwabe, chifukwa ma suites amakono amafunikira RAM yambiri), mukhoza kuyimitsa ntchitoyo. Pali zochitika pamene kulembedwa molakwika kwa macros kumapangitsa kuzizira kwa ma smartbook omwe ali ndi RAM pang'ono ndipo sanapangidwe kuti azigwira ntchito zovuta. 
2. Zolakwika m'maina zimaloledwa, zomwe zingalepheretsedwe pogwiritsa ntchito Mawu Osasintha Osasankha, omwe amakulolani kuti mupeze kusinthika kosadziwika, ngati mutapezeka. Imeneyi ndi njira yosavuta yodziwira zolakwika, chifukwa typo pang'ono imapangitsa womasulirayo kulephera kuzindikira kusinthasintha. Ndipo ngati muyatsa njira yowonetsera yosinthika, womasulira sangakulole kuti muthamangitse macro ngati zotengera za data zapezeka zomwe sizinalengezedwe koyambirira kwa gawolo.
3. Pewani zolakwika zobwera chifukwa chamitundu yosiyanasiyana yosafanana ndi mtundu wa data. Nthawi zambiri, kugawa mtengo wa mawu kumtundu wonse kumabweretsa cholakwika. Inde, kumbali imodzi, mtundu wa generic umaperekedwa popanda chilengezo, koma ngati alengezedwa pasadakhale, ndiye kuti zolakwika zosasinthika zitha kupewedwa.

Chifukwa chake, ngakhale zili zonse, tikulimbikitsidwa kulengeza zosintha zonse mu Excel macros.

Pali chinthu chinanso choyenera kukumbukira polengeza zosintha. Ndizotheka kuti musagawire zikhalidwe zilizonse pazosintha pozilengeza, koma pakadali pano zimapeza mtengo wokhazikika. Mwachitsanzo:

1. Mizere imakhala yopanda kanthu.
2. Manambala amatenga mtengo 0.
3. Zosintha zamtundu wa Boolean poyamba zimawonedwa ngati zabodza.
4. Tsiku losasinthika ndi Disembala 30, 1899.

Mwachitsanzo, simuyenera kugawira mtengo 0 ku chiwerengero cha chiwerengero ngati palibe mtengo womwe unatchulidwa kale. Ali ndi nambalayi kale.

Chosankha Chomveka Chomveka

Mawuwa amakulolani kulengeza zosintha zonse zomwe zimagwiritsidwa ntchito mu VBA code ndikuzindikira kukhalapo kwa zotengera zilizonse zomwe sizinatchulidwe codeyo isanayambike. Kuti mugwiritse ntchito izi, ingolembani mzere wa Option Explicit code pamwamba pa macro code.

Ngati mukufuna kuyika mawu awa mu code yanu nthawi zonse, mutha kutero pogwiritsa ntchito makonda apadera mu mkonzi wa VBA. Kuti mutsegule njira iyi, muyenera:

1. Pitani ku malo otukuka panjira - Zida> Zosankha.
2. Pazenera lomwe limatsegulidwa pambuyo pa izi, tsegulani tabu ya Editor.
3. Ndipo potsiriza, chongani bokosi pafupi ndi chinthu cha Require Variable Declaration.

Mukamaliza masitepe awa, dinani batani "Chabwino". 

Ndi momwemo, tsopano polemba macro atsopano aliwonse, mzerewu udzayikidwa pamwamba pa codeyo.

Kuchuluka kwa Constants ndi Zosintha

Zosintha zilizonse kapena zokhazikika zimakhala ndi gawo lochepa chabe. Zimatengera komwe mwalengeza.

Tiyerekeze kuti tili ndi ntchito Total_Cost(), ndipo imagwiritsa ntchito kusintha sVAT_Rate. Kutengera ndi momwe gawoli lilili, lidzakhala ndi kukula kosiyana:

Ngati mukufuna kuti kusinthaku kuwerengedwe ndi ma modules ena, muyenera kugwiritsa ntchito mawu achinsinsi a Public m'malo mwa Dim keyword. Momwemonso, mutha kuchepetsa kuchuluka kwa zosinthika kukhala gawo lapano lokha pogwiritsa ntchito mawu a Public, omwe amalembedwa m'malo mwa mawu akuti Dim.

Mutha kuyika kuchuluka kwa zosinthika mwanjira yofananira, koma mawu osakira apa alembedwa limodzi ndi Const operator.

Nayi tebulo lomwe lili ndi chitsanzo chabwino cha momwe limagwirira ntchito ndi zosinthika komanso zosinthika.

Chifukwa chiyani zosintha ndi zosinthika ndizofunikira

Kugwiritsa ntchito zosinthika ndi zosinthika kumakupatsani mwayi wowonjezera kumvetsetsa kwa code. Ndipo ngati oyamba ambiri alibe mafunso okhudza chifukwa chake zosinthika zimafunikira, ndiye kuti pali zambiri zosamveka zokhudzana ndi kufunikira kokhazikika. Ndipo funsoli likuwoneka, poyang'ana koyamba, lomveka. Kupatula apo, mutha kulengeza zosinthika kamodzi osasinthanso.

Yankho limakhala penapake mu ndege yomweyo ponena za kugwiritsa ntchito mitundu ya data yomwe imakhala ndi malo akulu kukumbukira. Ngati tikuchita ndi kuchuluka kwamitundumitundu, titha kusintha mwangozi chidebe chomwe chilipo. Ngati wogwiritsa ntchitoyo anena kuti mtengo wake sudzasintha, ndiye kuti chilengedwe chidzawongolera izi.

Izi ndizofunikira makamaka pamene macro amalembedwa ndi olemba mapulogalamu angapo. Wina angadziwe kuti zosintha zina siziyenera kusintha. Ndipo winayo alibe. Mukatchula woyendetsa Const, wopanga wina adzadziwa kuti mtengowu susintha.

Kapena, ngati pali nthawi zonse ndi dzina limodzi, ndipo kusinthika kuli ndi dzina losiyana, koma lofanana. Wokonza akhoza kungowasokoneza. Mwachitsanzo, chosintha chimodzi chomwe sichiyenera kusinthidwa chimatchedwa Variable11, ndipo china chomwe chingasinthidwe chimatchedwa Variable1. Munthu amatha, polemba khodi, mwangozi kudumpha gawo lowonjezera ndipo osazindikira. Zotsatira zake, chidebe chamitengo chidzasinthidwa, chomwe sichiyenera kukhudzidwa.

Kapena woyambitsa mwiniyo akhoza kuiwala mitundu yomwe angagwire ndi yomwe sangathe. Izi nthawi zambiri zimachitika pamene code imalembedwa kwa masabata angapo, ndipo kukula kwake kumakhala kwakukulu. Panthawiyi, n'zosavuta kuiwala ngakhale zomwe izi kapena zosiyana zimatanthauza.

Inde, mutha kuchita ndi ndemanga pankhaniyi, koma sikophweka kutchula mawu akuti Const?

Mawuwo

Zosintha ndi gawo lofunikira pakupanga ma macro, omwe amakulolani kuti muzichita zinthu zovuta, kuyambira kuwerengera mpaka kudziwitsa wogwiritsa za zochitika zina kapena kufotokoza zofunikira m'maselo a spreadsheet.

Zosintha ziyenera kugwiritsidwa ntchito ngati wopanga akudziwa motsimikiza kuti zomwe zili m'mabokosiwa sizisintha mtsogolo. Ndibwino kuti musagwiritse ntchito zosinthika m'malo mwake, chifukwa n'zotheka kulakwitsa mwangozi.