Zamkatimu
Ma Arrays mu Visual Basic for Application ndi zinthu zomwe nthawi zambiri zimasunga mitundu yofananira yamtundu womwewo. Zolemba zingapo zimafikiridwa ndi manambala awo.
Mwachitsanzo, pali gulu la anthu 20 omwe mayina awo ayenera kusungidwa kuti agwiritsidwe ntchito pambuyo pake mu code VBA. Mmodzi atha kungolengeza zosintha 20 kuti zizigwira dzina lililonse, monga:
Dim Team_Member1 Monga String Dim Team_Member2 Monga Chingwe ... Dim Team_Member20 Monga Chingwe
Koma mutha kugwiritsa ntchito njira yosavuta komanso yolongosoka - sungani mndandanda wa mayina amgulu lamagulu osiyanasiyana 20 monga Mzere:
Dim Team_Members (1 mpaka 20) Monga Chingwe
Mu mzere womwe uli pamwambapa, talengeza mndandanda. Tsopano tiyeni tilembe mtengo ku chilichonse mwazinthu zake, monga chonchi:
Team_Members(1) = "John Smith"
Ubwino wowonjezera wosunga deta mugulu, poyerekeza ndi kugwiritsa ntchito mitundu yosiyanasiyana, umawonekera pakafunika kuchita chimodzimodzi pagawo lililonse la gululo. Ngati mayina a mamembala a gululo adasungidwa mumitundu 20 yosiyana, ndiye kuti pamafunika mizere 20 ya code kuti alembe nthawi iliyonse kuti achite chimodzimodzi pa aliyense wa iwo. Komabe, ngati mayina asungidwa mugulu, ndiye kuti mutha kuchita zomwe mukufuna ndi aliyense wa iwo pogwiritsa ntchito lupu losavuta.
Momwe zimagwirira ntchito zikuwonetsedwa pansipa ndi chitsanzo cha code chomwe chimasindikiza mayina a membala aliyense wa gulu motsatizana m'maselo amzati. A ntchito ya Excel yogwira ntchito.
Kwa ine = 1 Mpaka 20 Maselo(i,1). Mtengo = Team_Members(i) Chotsatira i
Zachidziwikire, kugwira ntchito ndi gulu lomwe limasunga mayina 20 ndikosavuta komanso kolondola kuposa kugwiritsa ntchito mitundu 20 yosiyana. Koma bwanji ngati mayina awa si 20, koma 1000? Ndipo ngati, kuwonjezera, pakufunika kusunga surnames ndi patronymics padera?! Zikuwonekeratu kuti posachedwa zidzakhala zosatheka kukwanitsa kuchuluka kwa data mu VBA code popanda kuthandizidwa ndi gulu.
Multidimensional arrays mu Excel Visual Basic
Ma Visual Basic arrays omwe takambirana pamwambapa amatengedwa ngati gawo limodzi. Izi zikutanthauza kuti amasunga mndandanda wa mayina osavuta. Komabe, magulu akhoza kukhala ndi miyeso yambiri. Mwachitsanzo, gulu lamitundu iwiri lingathe kufananizidwa ndi gululi lamtengo wapatali.
Tiyerekeze kuti mukufuna kusunga ziwerengero zogulitsa tsiku lililonse za Januware zamagulu 5 osiyanasiyana. Izi zidzafunika magawo awiri okhala ndi ma seti 5 a ma metric kwa masiku 31. Tiyeni tifotokoze mndandanda monga chonchi:
Dim Jan_Sales_Figures(1 Mpaka 31, 1 Mpaka 5) Monga Ndalama
Kuti mupeze mndandanda wazinthu Jan_Sales_Figures, muyenera kugwiritsa ntchito milozera iwiri yosonyeza tsiku la mwezi ndi nambala ya lamulo. Mwachitsanzo, adilesi ya chinthu chomwe chili ndi ziwerengero zogulitsa 2-uwu magulu a 15 ndi January adzalembedwa motere:
Jan_Sales_Ziwerengero(15, 2)
Momwemonso, mutha kulengeza mndandanda wokhala ndi miyeso ya 3 kapena kupitilira apo - ingowonjezerani miyeso yowonjezereka ku chilengezo chamagulu ndikugwiritsa ntchito ma indices owonjezera kuti mutchule zigawo za gululi.
Kulengeza Ma Arrays mu Excel Visual Basic
Kumayambiriro kwa nkhaniyi, tawona kale zitsanzo zingapo za kulengeza magulu a VBA, koma mutuwu uyenera kuyang'anitsitsa. Monga momwe ziwonetsedwera, mndandanda wamtundu umodzi ukhoza kulengezedwa motere:
Dim Team_Members (1 mpaka 20) Monga Chingwe
Kulengeza koteroko kumauza wolemba VBA kuti gululo Team_Mamembala imakhala ndi mitundu 20 yomwe ingapezeke pa ma indices kuyambira 1 mpaka 20. Komabe, tikhoza kuganiza za kuwerengera zosintha zathu kuchokera ku 0 mpaka 19, momwemo mndandandawo uyenera kulengezedwa motere:
Dim Team_Members (0 mpaka 19) Monga Chingwe
M'malo mwake, mwachisawawa, kuwerengetsa kwazinthu zingapo kumayambira pa 0, ndipo pakulengeza koyambirira, mndandanda woyamba sungatchulidwe nkomwe, monga chonchi:
Dim Team_Members(19) Monga Chingwe
Wophatikiza wa VBA awona zolowera ngati kulengeza zinthu 20 zokhala ndi ma indices kuyambira 0 mpaka 19.
Malamulo omwewo amagwira ntchito polengeza ma multidimensional Visual Basic arrays. Monga tawonetsera kale mu chimodzi mwa zitsanzo, polengeza mndandanda wa mbali ziwiri, zizindikiro za miyeso yake zimasiyanitsidwa ndi koma:
Dim Jan_Sales_Figures(1 Mpaka 31, 1 Mpaka 5) Monga Ndalama
Komabe, ngati simunatchule cholozera choyambira pamiyeso yonse iwiri ndikulengeza motere:
Dim Jan_Sales_Figures(31, 5) Monga Ndalama
ndiye cholowacho chidzatengedwa ngati magawo awiri, gawo loyamba lomwe lili ndi zinthu 32 zokhala ndi ma indices kuyambira 0 mpaka 31, ndipo gawo lachiwiri la gululi lili ndi zinthu 6 zokhala ndi ma indices kuyambira 0 mpaka 5.
Zida zamphamvu
Zitsanzo zonse zomwe zili pamwambazi zimakhala ndi miyeso yokhazikika. Komabe, nthawi zambiri sitidziwiratu kukula kwa gulu lathu. Tikhoza kutuluka mumkhalidwewo mwa kulengeza gulu lalikulu, lomwe kukula kwake kudzakhala kwakukulu kuposa kofunikira pa ntchito yathu. Koma njira yotereyi idzafuna kukumbukira zambiri zowonjezera ndipo ikhoza kuchepetsa pulogalamuyo. Pali njira yabwinoko. Titha kugwiritsa ntchito gulu lamphamvu - iyi ndi gulu lomwe kukula kwake kumatha kukhazikitsidwa ndikusinthidwa kangapo pakuchita ma macro.
Dongosolo lamphamvu limalengezedwa ndi mabatani opanda kanthu, motere:
Dim Team_Members() Monga String
Chotsatira, muyenera kulengeza kukula kwa gululo panthawi yogwiritsira ntchito code pogwiritsa ntchito mawuwo ReDim:
ReDim Team_Members(1 mpaka 20)
Ndipo ngati pakupanga code muyenera kusinthanso kukula kwa gululo, ndiye kuti mutha kugwiritsanso ntchito mawu a ReDim:
Ngati Team_Size> 20 Ndiye ReDim Team_Members (1 mpaka Team_Size) Itha Ngati
Kumbukirani kuti kusinthanso kusanja kosinthika motere kumabweretsa kutayika kwa zinthu zonse zomwe zasungidwa pamndandanda. Kuti musunge deta kale pamndandanda, muyenera kugwiritsa ntchito mawu osakira Sunganimonga momwe tawonetsera m'munsimu:
Ngati Team_Size> 20 Ndiye ReDim Preserve Team_Members (1 mpaka Team_Size) Itha Ngati
Tsoka ilo mawu ofunika Sungani angagwiritsidwe ntchito kusintha malire apamwamba a array dimension. M'munsi mwa gulu silingasinthidwe motere. Komanso, ngati gululo lili ndi miyeso ingapo, ndiye gwiritsani ntchito mawu osakira Sungani, ndi gawo lomaliza lokha la gulu lomwe lingasinthidwe.