Zosintha ndi zokhazikika mu VBA

Mu VBA, monga m'chinenero china chilichonse cha mapulogalamu, zosinthika ndi zokhazikika zimagwiritsidwa ntchito kusunga mfundo zilizonse. Monga momwe dzinalo likusonyezera, zosintha zimatha kusintha, pomwe zosintha zimasunga zokhazikika.

Mwachitsanzo, nthawi zonse Pi imasunga mtengo wa 3,14159265… Nambala ya "Pi" sidzasintha panthawi ya pulogalamuyo, komabe ndizosavuta kusunga mtengo wotere ngati wokhazikika.

Pa nthawi yomweyo, tikhoza kugwiritsa ntchito variable sVAT_Rate kusunga mtengo wa VAT pa katundu wogulidwa. Mtengo wosinthika sVAT_Rate zingasiyane malinga ndi zomwe zagulidwa.

Mitundu Yambiri

Zosintha zonse ndi zokhazikika ndi zamtundu wina wa data. Gome ili m'munsili limatchula mitundu ya data yomwe imagwiritsidwa ntchito mu VBA ndi kufotokozera ndi mitundu yosiyanasiyana ya zinthu zomwe zingatheke:

Mwachiwonekere, pogwiritsa ntchito tebulo pamwambapa ndikusankha mtundu woyenera wa data, mutha kugwiritsa ntchito kukumbukira mwachuma (mwachitsanzo, sankhani mtundu wa data Zambiri m'malo mwake Long or single m'malo mwake wachiphamaso). Komabe, mukamagwiritsa ntchito mitundu yophatikizika yama data, muyenera kusamala kuti ma code anu asayese kukwanirana ndi zinthu zazikulu kwambiri.

Kulengeza Zosintha ndi Zokhazikika

Zolemba Zomasulira: Ponena za zosinthika mu VBA, ndiyenera kutchula mfundo ina yofunika kwambiri. Ngati tilengeza kusintha koma osapereka mtengo uliwonse kwa izo, ndiye kuti zimayambitsidwa ndi mtengo wosasintha:

• Zingwe zamalemba zimayambitsidwa ndi zingwe zopanda kanthu;

• manambala - mtengo 0;

• mitundu yosiyanasiyana Boolean - Zabodza;

• masiku - December 30, 1899.

Isanayambe kugwiritsidwa ntchito kosinthika kapena kosasintha, iyenera kulengezedwa. Kuti muchite izi, onjezani mzere wosavuta wa code ku macro:

Dim Имя_Переменной As Тип_Данных

Pamwambapa mzere wa code Variable_name ndi dzina la kusintha komwe kudzagwiritsidwe ntchito mu code, ndi Data_Type ndi imodzi mwa mitundu ya data kuchokera patebulo loperekedwa kale pang'ono m'nkhaniyi. Mwachitsanzo:

Dim sVAT_Rate Monga Dim Imodzi Monga Integer

Constants amalengezedwa mofanana, koma polengeza zokhazikika, mtengo wake uyenera kuwonetsedwa nthawi yomweyo. Mwachitsanzo, monga chonchi:

Const iMaxCount = 5000 Const iMaxScore = 100

Sikofunikira kulengeza zosintha mu Excel. Mwachikhazikitso, zosintha zonse zomwe zidalowetsedwa koma zomwe sizinatchulidwe mu Excel zidzakhala ndi mtunduwo zosinthika ndipo adzatha kuvomereza manambala ndi malembedwe.

Chifukwa chake, wopanga mapulogalamu amatha kugwiritsa ntchito kusinthika kwatsopano nthawi iliyonse (ngakhale sikunafotokozedwe), ndipo Excel aziwona ngati mtundu wosinthika. zosinthika. Komabe, pali zifukwa zingapo zomwe siziyenera kuchitidwa:

1. Kugwiritsa ntchito kukumbukira komanso kuthamanga kwa ma computa. Ngati simulengeza zosinthika ndi chizindikiro cha mtundu wa deta, ndiye kuti mwachisawawa zidzakhazikitsidwa ku mtunduwo zosinthika. Mtundu wa data uwu umagwiritsa ntchito kukumbukira kwambiri kuposa mitundu ina ya data. Ma byte owonjezera pang'ono pamtundu uliwonse sangamveke ngati zambiri, koma pochita, mapulogalamu amatha kukhala ndi masauzande ambiri (makamaka akamagwira ntchito ndi magulu). Choncho, kukumbukira owonjezera ntchito zosiyanasiyana monga zosinthika, poyerekeza ndi mitundu yosiyanasiyana Zambiri or single, akhoza kuwonjezera pa ndalama zambiri. Komanso, ntchito ndi zosiyanasiyana za mtundu zosinthika amachitidwa pang'onopang'ono kusiyana ndi mitundu ya mitundu ina, motero, mitundu yowonjezereka yamitundu zosinthika akhoza kuchedwetsa kwambiri kuwerengera.
2. Kupewa typos mu mayina osinthika. Ngati zosintha zonse zalengezedwa, ndiye kuti mawu a VBA atha kugwiritsidwa ntchito - Njira Yachidule (tidzakambirana pambuyo pake) kuti tidziwe zosintha zonse zomwe sizinatchulidwe. Izi zimachotsa kuoneka kwa cholakwika mu pulogalamuyi chifukwa cha dzina losinthika lolembedwa molakwika. Mwachitsanzo, kugwiritsa ntchito variable dzina sVAT_Rate, mukhoza kupanga typo ndipo, perekani mtengo ku kusinthaku, lembani: "VATRate = 0,175". Zikuyembekezeka kuti kuyambira pano, kusinthaku sVAT_Rate iyenera kukhala ndi mtengo wa 0,175 - koma ayi. Ngati njira yolankhulira movomerezeka yamitundu yonse yogwiritsidwa ntchito yayatsidwa, ndiye kuti wophatikiza wa VBA adzawonetsa cholakwika nthawi yomweyo, chifukwa sichipeza zosinthika. Mtengo wa VAT mwa omwe adalengezedwa.
3. Kuwunikira zomwe sizikugwirizana ndi mtundu womwe walengezedwa. Ngati mulengeza kusintha kwa mtundu wina ndikuyesera kugawa deta yamtundu wina kwa izo, mudzapeza cholakwika, chomwe, ngati sichinakonzedwe, chingayambitse pulogalamuyo. Poyang'ana koyamba, izi zingawoneke ngati chifukwa chabwino chosalengeza zosinthika, koma kwenikweni, kusiyana ndi zisanachitike kuti chimodzi mwazosinthika chinalandira deta yolakwika yomwe imayenera kulandira - zabwino kwambiri! Apo ayi, ngati pulogalamuyo ikupitirirabe, zotsatira zake zingakhale zolakwika komanso zosayembekezereka, ndipo zidzakhala zovuta kwambiri kupeza chifukwa cha zolakwikazo. Ndizothekanso kuti ma macro aphedwa "mwabwino". Zotsatira zake, cholakwikacho sichidzazindikirika ndipo ntchito idzapitirira ndi deta yolakwika!

Pachifukwa ichi, ndikofunikira kuzindikira mtundu wolakwika wa data ndikuwongolera zolakwika zotere mu code posachedwa. Pazifukwa izi, tikulimbikitsidwa kuti mulengeze zosintha zonse polemba VBA macro.

Njira Yachidule

Woyendetsa Njira Yachidule imayambitsa zosintha zonse zomwe zidzagwiritsidwe ntchito mu code ya VBA kuti zilengezedwe, ndikuyika mbendera zonse zosazindikirika ngati zolakwika pakuphatikiza (kukhazikitsa ma code kusanayambe). Kugwiritsa ntchito opareshoni sikovuta - ingolembani mzerewu pamwamba pa fayilo ya VBA:

Option Explicit

Ngati mukufuna kuyika nthawi zonse Njira Yachidule pamwamba pa gawo lililonse latsopano la VBA lopangidwa, izi zitha kuchitika zokha. Kuti muchite izi, muyenera kuyatsa njira Amafunika Chilengezo Chosinthika m'makonzedwe a VBA editor.

Izi zachitika motere:

• Kuchokera ku Visual Basic Editor menyu, dinani zida > Zosintha
• Munkhani yomwe ikuwoneka, tsegulani tabu Editor
• Chongani bokosi Amafunika Chilengezo Chosinthika Ndi kukanikiza OK

Mukayatsa, chingwe Njira Yachidule adzalowetsedwa basi kumayambiriro kwa gawo latsopano lililonse lopangidwa.

Kuchuluka kwa Zosintha ndi Zosasintha

Chilichonse cholengezedwa kapena chokhazikika chimakhala ndi malire ake, ndiko kuti, gawo lochepa la pulogalamu yomwe kusinthaku kulipo. Kuchuluka kumatengera komwe kulengeza kwakusintha kapena kosalekeza kudapangidwa. Tengani, mwachitsanzo, kusinthasintha sVAT_Rate, zomwe zimagwiritsidwa ntchito Total_Cost. Gome lotsatirali likukamba za njira ziwiri zosinthira ma scoping sVAT_Rateadalengezedwa m'malo awiri osiyana mu module:

Kusankha Momveka Bwino Kwambiri sVAT_Rate Monga Ntchito Imodzi Total_Cost() Monga Pawiri ... Mapeto Ntchito

Kusankha Mwachidule Ntchito Total_Cost() Monga Dim Pawiri sVAT_Rate Monga Imodzi ... Mapeto Ntchito

Muchitsanzo chomwe chawonetsedwa pamwambapa, kusinthika kumalengezedwa pamlingo wa module ndi mawu osakira Choyipa. Komabe, zingakhale zofunikira kuti zosintha zomwe zalengezedwa zitha kugwiritsidwa ntchito m'ma module ena. Zikatero, kulengeza kusintha m'malo mwa mawu osakira Choyipa mawu ofunika ayenera kugwiritsidwa ntchito Public.

Mwa njira, kuti mulengeze zosinthika pamlingo wa module, m'malo mwa mawu osakira Choyipa mawu ofunika angagwiritsidwe ntchito Private, zomwe zikuwonetsa kuti kusinthaku kumangogwiritsidwa ntchito mu gawo lomwe lilipo.

Mutha kugwiritsanso ntchito mawu osakira kuti mulengeze zosintha. Public и Private, koma osati m'malo mwa mawu osakira Const, pamodzi ndi izo.

Zitsanzo zotsatirazi zikuwonetsa kugwiritsa ntchito mawu osakira Public и Private amagwiritsidwa ntchito pazosintha ndi zokhazikika.

Njira Yowonekera Pagulu sVAT_Rate Monga Gulu Limodzi Pagulu IMax_Count = 5000 ...    

Kusankha Kwachinsinsi sVAT_Rate Monga Single Private Const iMax_Count = 5000 ...