Canje-canje a cikin VBA

A cikin VBA, kamar yadda yake a cikin kowane yaren shirye-shirye, ana amfani da masu canji da masu daidaitawa don adana kowane ƙima. Kamar yadda sunan ke nunawa, masu canji na iya canzawa, yayin da madaidaitan ke adana ƙayyadaddun ƙima.

Alal misali, akai-akai Pi yana adana darajar 3,14159265… Lambar "Pi" ba zai canza ba yayin aiwatar da shirin, amma har yanzu ya fi dacewa don adana irin wannan darajar a matsayin dindindin.

A lokaci guda, za mu iya amfani da m sVAT_Rate don adana adadin VAT akan kayan da aka saya. Ƙimar canzawa sVAT_Rate na iya bambanta dangane da samfurin da aka saya.

Nau'in Bayanai

Duk masu canji da madaidaitan su na wani nau'in bayanai ne. Teburin da ke ƙasa ya lissafa nau'ikan bayanan da aka yi amfani da su a cikin VBA tare da kwatance da kewayon ƙima masu yuwuwa:

Babu shakka, ta yin amfani da teburin da ke sama da zabar nau'in bayanan da ya dace, za ku iya amfani da ƙwaƙwalwar ajiya fiye da tattalin arziki (misali, zaɓi nau'in bayanan. Intanet maimakon Long or guda maimakon biyu). Koyaya, lokacin amfani da ƙarin nau'ikan bayanai masu ɗanɗano, kuna buƙatar yin hankali don kada lambar ku tayi ƙoƙarin dacewa da manyan ƙima a cikin su.

Bayyana Canje-canje da Matsaloli

Bayanan Fassara: Da yake magana game da masu canji a cikin VBA, yana da daraja ambaton wani muhimmin batu. Idan muka ayyana maɓalli amma ba mu sanya masa wata ƙima ba, to an fara shi da tsohuwar ƙima:

• an fara kirtani rubutu tare da kirtani mara komai;

Lambobi - darajar 0;

Nau'in masu canji Boolean - Ƙarya;

• kwanakin - Disamba 30, 1899.

Kafin a iya amfani da maɓalli ko akai-akai, dole ne a bayyana shi. Don yin wannan, ƙara layin code mai sauƙi mai zuwa zuwa macro:

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

A cikin layin code na sama Suna mai canzawa shine sunan canjin da za a yi amfani da shi a cikin lambar, kuma Data_Nau'i yana ɗaya daga cikin nau'ikan bayanai daga teburin da aka bayar a baya a cikin wannan labarin. Misali:

Dim sVAT_Rate A Matsayin Dim Dim guda ɗaya da Matsayin lamba

Ana bayyana ma'auni iri ɗaya, amma lokacin da aka bayyana ƙima, dole ne a nuna ƙimar su nan da nan. Misali, kamar haka:

Const iMaxCount = 5000 Const iMaxScore = 100

Ba lallai ba ne a bayyana masu canji a cikin Excel. Ta hanyar tsoho, duk waɗanda aka shigar amma ba a bayyana masu canji a cikin Excel za su sami nau'in ba bambance-bambancen kuma za su iya karɓar duka lamba da ƙimar rubutu.

Don haka, mai tsara shirye-shirye na iya amfani da sabon canjin a kowane lokaci (ko da ba a bayyana shi ba), kuma Excel zai yi amfani da shi azaman canjin nau'in. bambance-bambancen. Duk da haka, akwai dalilai da yawa da ya sa bai kamata a yi haka ba:

1. Amfani da ƙwaƙwalwar ajiya da saurin lissafi. Idan baku bayyana maɓalli tare da alamar nau'in bayanan ba, to ta tsohuwa za'a saita shi zuwa nau'in bambance-bambancen. Wannan nau'in bayanan yana amfani da ƙarin ƙwaƙwalwar ajiya fiye da sauran nau'ikan bayanai. ƴan ƙarin bytes a kowace maɓalli bazai yi kama da yawa ba, amma a aikace, shirye-shirye na iya samun dubban masu canji (musamman lokacin aiki tare da tsararru). Saboda haka, ƙarin ƙwaƙwalwar ajiyar da masu canji ke amfani da su kamar bambance-bambancen, idan aka kwatanta da masu canji na nau'in Intanet or guda, zai iya ƙara har zuwa adadi mai mahimmanci. Bugu da ƙari, ayyuka tare da masu canji na nau'in bambance-bambancen ana aiwatar da su a hankali fiye da tare da masu canji na wasu nau'ikan, bi da bi, ƙarin nau'ikan masu canji dubu bambance-bambancen na iya raguwar lissafi sosai.
2. Rigakafin typos a cikin sunaye masu canji. Idan an bayyana duk masu canji, to ana iya amfani da bayanin VBA - Zabin Bayyananne (zamu yi magana game da shi daga baya) don gano duk masu canjin da ba a bayyana ba. Wannan yana kawar da bayyanar kurakurai a cikin shirin sakamakon sunan da aka rubuta ba daidai ba. Misali, ta amfani da maɓalli mai suna sVAT_Rate, za ka iya yin typo kuma, sanya ƙima ga wannan canjin, rubuta: "VATrate = 0,175". Ana sa ran cewa daga yanzu, mai canzawa sVAT_Rate ya kamata ya ƙunshi darajar 0,175 - amma ba shakka ba haka ba. Idan an kunna yanayin sanarwar dole na duk masu canji da aka yi amfani da su, to mai tara VBA zai nuna kuskure nan da nan, tunda ba zai sami madaidaicin ba. VATRATE cikin wadanda aka sanar.
3. Haskaka ƙima waɗanda basu dace da ayyana nau'in maɓalli ba. Idan ka bayyana maɓalli na wani nau'i kuma ka yi ƙoƙarin sanya masa bayanai na wani nau'i na daban, za ka sami kuskure, wanda, idan ba a gyara ba, zai iya haifar da rushewar shirin. Da farko kallo, wannan na iya zama kamar dalili mai kyau don kada a bayyana masu canji, amma a gaskiya, fiye da kafin ya bayyana cewa ɗaya daga cikin masu canji ya karbi bayanan da ba daidai ba wanda ya kamata ya karɓa - don haka mafi kyau! In ba haka ba, idan shirin ya ci gaba da gudana, sakamakon zai iya zama kuskure kuma ba zato ba tsammani, kuma zai fi wuya a gano dalilin kurakurai. Hakanan yana yiwuwa a aiwatar da macro "cikin nasara". A sakamakon haka, kuskuren zai tafi ba a lura da shi ba kuma aikin zai ci gaba da bayanan da ba daidai ba!

A wannan batun, yana da kyawawa don gano nau'in bayanan da ba daidai ba kuma gyara irin waɗannan kurakurai a cikin lambar da wuri-wuri. Don waɗannan dalilai, ana ba da shawarar cewa ku bayyana duk masu canji yayin rubuta macro na VBA.

Zabin Bayyananne

Operator Zabin Bayyananne yana sa duk masu canji waɗanda za a yi amfani da su a cikin lambar VBA za a ayyana su, kuma suna ba da tutocin duk masu canjin da ba a bayyana su azaman kurakurai yayin haɗawa (kafin fara aiwatar da code). Aiwatar da wannan ma'aikacin ba shi da wahala - kawai rubuta wannan layin a saman babban fayil ɗin VBA:

Option Explicit

Idan kuna son sakawa koyaushe Zabin Bayyananne zuwa saman kowane sabon tsarin VBA da aka kirkira, ana iya yin wannan ta atomatik. Don yin wannan, kuna buƙatar kunna zaɓi Bukatar Sanarwa Mai Sauyawa a cikin saitunan editan VBA.

Ana yin wannan kamar haka:

• Daga menu na Visual Basic Edita, danna kayayyakin aiki, > Zabuka
• A cikin maganganun da ke bayyana, buɗe shafin Edita
• Duba akwatin Bukatar Sanarwa Mai Sauyawa kuma latsa OK

Lokacin da aka kunna, zaren Zabin Bayyananne za a saka ta atomatik a farkon kowane sabon tsarin da aka ƙirƙira.

Iyakar Canje-canje da Matsaloli

Kowacce bayyananne ko akai-akai yana da nasa iyaka, wato iyakataccen ɓangaren shirin da wannan canjin ya wanzu. Iyakar ya dogara da inda aka yi ayyana mai canzawa ko akai-akai. Dauki, alal misali, mai canzawa sVAT_Rate, wanda ake amfani dashi a cikin aikin Jimlar_Kudi. Teburin da ke gaba ya tattauna zaɓuka biyu don daidaitawa sVAT_RateAn bayyana a wurare daban-daban guda biyu a cikin module:

Zaɓin Bayyana Dim sVAT_Rate A Matsayin Aiki Guda Daya Jimlar Kudin () A Matsayin Biyu ... Ƙarshen Aiki

Zaɓan Tsararren Aiki Total_Cost() A Matsayin Dim sVAT_Rate Biyu A Matsayin Guda ... Ƙarshen Aiki

A cikin misalin da aka nuna a sama, ana ayyana mai canzawa a matakin ƙirar tare da kalmar maɓalli Dim. Koyaya, yana iya zama dole cewa za'a iya amfani da sauye-sauyen da aka bayyana a cikin wasu kayayyaki. A irin waɗannan lokuta, don ayyana maɓalli maimakon kalma Dim dole ne a yi amfani da keyword Jama'a.

Af, domin bayyana m a matakin module, maimakon keyword Dim za a iya amfani da keyword Private, wanda ke nuna cewa an yi nufin wannan canjin don amfani ne kawai a cikin tsarin yanzu.

Hakanan zaka iya amfani da kalmomi masu mahimmanci don ayyana ci gaba. Jama'a и Private, amma ba maimakon keyword ba Konton, tare da shi.

Misalai masu zuwa suna nuna amfani da kalmomi masu mahimmanci Jama'a и Private ana amfani da su ga masu canji da ma'auni.

Zaɓin Bayyanar Jama'a sVAT_Rate A Matsayin Ƙimar Jama'a Guda iMax_Count = 5000 ...    

Zaɓin Ƙirar sVAT_Rate Mai zaman kansa A Matsayin Ƙirar Mai Zaman Kanta iMax_Count = 5000 ...