Ama-arrays kokuthi Visual Basic for Application

Ama-arrays kokuthi Visual Basic for Application izakhiwo ngokuvamile ezigcina amasethi okuguquguquka okuhlobene ohlobo olufanayo. Okufakiwe kwezinhlu kufinyelelwa ngenkomba yezinombolo.

Isibonelo, kuneqembu labantu abangu-20 amagama abo adinga ukulondolozwa ukuze asetshenziswe kamuva kukhodi ye-VBA. Umuntu angavele amemezele okuguquguqukayo okungu-20 ukubamba igama ngalinye, njengalokhu:

Fiphalisa Ithimba_Ilungu1 Njengeyunithi Yezintambo Ezifiphele Ithimba_Ilungu2 Njengochungechunge ... Dim Ithimba_Ilungu20 Njengeyunithi Yezinhlamvu

Kodwa ungasebenzisa indlela elula kakhulu nehleleke kakhulu - gcina uhlu lwamagama amalungu eqembu ngohlu lwama-variable angu-20 afana Umzila:

Nciphisa Ithimba_Amalungu(1 ukuya ku-20) Njengeyunithi yezinhlamvu

Emgqeni oboniswe ngenhla, simemezele uhlu. Manje ake sibhale inani ku-elementi yayo ngayinye, kanje:

Team_Members(1) = "John Smith"

Inzuzo eyengeziwe yokugcina idatha kulungu elifanayo, uma iqhathaniswa nokusebenzisa okuhlukile okuhlukene, ibonakala lapho kuba kudingekile ukwenza isenzo esifanayo kusici ngasinye samalungu afanayo. Uma amagama amalungu eqembu egcinwe eziguquguqukayo ezingu-20 ezihlukene, kuzothatha imigqa yekhodi engu-20 ukubhala isikhathi ngasinye ukwenza isenzo esifanayo komunye nomunye wabo. Nokho, uma amagama egcinwa ohlwini, ungakwazi ukwenza isenzo oyifunayo ngamunye wabo usebenzisa iluphu elula.

Indlela esebenza ngayo iboniswa ngezansi ngesibonelo sekhodi ephrinta amagama elungu leqembu ngalinye ngokulandelana kumaseli ekholomu. A ishidi lokusebenza le-Excel elisebenzayo.

Ku-i = 1 Amaseli angu-20(i,1).Value = Team_Members(i) Okulandelayo i

Ngokusobala, ukusebenza ngohlu olugcina amagama angama-20 akunzima kakhulu futhi kunembe kakhulu kunokusebenzisa okuguquguqukayo okuhlukene okungu-20. Kodwa kuthiwani uma la magama engewona ama-20, kodwa ayi-1000? Futhi uma, ngaphezu kwalokho, kuyadingeka ukugcina izibongo kanye ne-patronymics ngokwehlukana?! Kuyacaca ukuthi maduze kuzokwenza kube nzima ngokuphelele ukuphatha umthamo onjalo wedatha kukhodi ye-VBA ngaphandle kosizo lohlu.

Ama-multidimensional arrays ku-Excel Visual Basic

Amalungu afanayo e-Visual Basic okukhulunywe ngawo ngenhla athathwa njengohlangothi olulodwa. Lokhu kusho ukuthi bagcina uhlu olulula lwamagama. Nokho, amalungu afanayo angaba nobukhulu obuningi. Isibonelo, amalungu afanayo anezinhlangothi ezimbili angafaniswa negridi yamanani.

Ake sithi ufuna ukulondoloza izibalo zokuthengisa zansuku zonke zikaJanuwari ngamaqembu angu-5 ahlukene. Lokhu kuzodinga amalungu afanayo anezinhlangothi ezimbili ezihlanganisa amasethi angu-5 wamamethrikhi izinsuku ezingu-31. Ake simemezele uhlu olufana nalolu:

Dim Jan_Sales_Figures(1 ukuya ku-31, 1 kuya ku-5) Njengohlobo lwemali

Ukuze ufinyelele izakhi zamalungu afanayo Jan_Sales_Figures, udinga ukusebenzisa izinkomba ezimbili ezibonisa usuku lwenyanga kanye nenombolo yomyalo. Isibonelo, ikheli le-elementi equkethe izibalo zokuthengisa ze 2-awu amaqembu for awu 15 UMasingana uzobhalwa kanje:

Jan_Sales_Figures(15, 2)

Ngendlela efanayo, ungakwazi ukumemezela amalungu afanayo anobukhulu obu-3 noma ngaphezulu - vele ungeze izilinganiso ezengeziwe kusimemezelo samalungu afanayo futhi usebenzise izinkomba ezengeziwe ukuze ubhekisele kuzakhi zalolu hlelo.

Ukumemezela ama-Arrays ku-Excel Visual Basic

Ngaphambilini kulesi sihloko, sesivele sibheke izibonelo ezimbalwa zokumemezela ama-arrays ku-VBA, kodwa lesi sihloko sidinga ukubhekwa ngokucophelela. Njengoba kubonisiwe, amalungu afanayo anohlangothi olulodwa angamenyezelwa kanje:

Nciphisa Ithimba_Amalungu(1 ukuya ku-20) Njengeyunithi yezinhlamvu

Isimemezelo esinjalo sitshela umdidiyeli we-VBA ukuthi uhlu Ithimba_Amalungu iqukethe okuguquguqukayo okungu-20 okungafinyelelwa kuma-indices ukusuka koku-1 kuye kokungu-20. Nokho, singase sicabange ngokufaka izinombolo zamalungu afanayo ukusuka ku-0 kuye ku-19, lapho amalungu afanayo kufanele amenyezelwe kanje:

Nciphisa Ithimba_Amalungu(0 ukuya ku-19) Njengeyunithi yezinhlamvu

Eqinisweni, ngokuzenzakalelayo, ukubalwa kwezinombolo zezakhi zamalungu afanayo kuqala ku-0, futhi kusimemezelo samalungu afanayo, inkomba yokuqala ingase ingacaciswa nhlobo, kanje:

Fiphalisa Ithimba_Amalungu(19) Njengeyunithi Yezinhlamvu

I-VBA compiler izophatha lokho okufakiwe njengokumemezela uhlu lwezinto ezingu-20 ezinezinkomba ukusuka ku-0 kuye ku-19.

Kusebenza imithetho efanayo lapho kumenyezelwa ama-multidimensional Visual Basic arrays. Njengoba sekuvele kubonisiwe kwesinye sezibonelo, lapho kumenyezelwa uhlu olunezinhlangothi ezimbili, izinkomba zobukhulu balo zihlukaniswa ngokhefana:

Dim Jan_Sales_Figures(1 ukuya ku-31, 1 kuya ku-5) Njengohlobo lwemali

Kodwa-ke, uma ungayicacisi inkomba yokuqala yazo zombili izinhlangothi zamalungu afanayo futhi uyimemezele kanje:

Dim Jan_Sales_Figures(31, 5) Njengohlobo Lwemali

khona-ke lokhu okufakiwe kuzophathwa njengohlelo olunezinhlangothi ezimbili, ubukhulu bokuqala obuqukethe ama-elementi angama-32 anezinkomba ukusuka ku-0 kuye ku-31, futhi uhlangothi lwesibili lwamalungu afanayo luqukethe ama-elementi angu-6 anezinkomba ukusuka ku-0 kuye ku-5.

Ukuhlelwa okunamandla

Wonke amalungu afanayo ezibonelweni ezingenhla anenombolo engashintshi yobukhulu. Nokho, ezimweni eziningi asazi kusengaphambili ukuthi iqoqo lethu kufanele libe usayizi onjani. Singaphuma kulesi simo ngokumemezela uhlu olukhulu, ubukhulu balo ngokuqinisekile buyoba bukhulu kunokudingekile emsebenzini wethu. Kodwa isisombululo esinjalo sizodinga inkumbulo eyengeziwe eyengeziwe futhi singanciphisa uhlelo. Kukhona isixazululo esingcono. Singasebenzisa amalungu afanayo aguquguqukayo - lena amalungu afanayo usayizi wayo ungasethwa futhi ushintshwe noma iyiphi inombolo yezikhathi ngesikhathi sokwenziwa kwe-macro.

Amalungu afanayo aguquguqukayo amenyezelwa ngabakaki abangenalutho, kanje:

Dim Team_Members() Njengeyunithi yezinhlamvu

Okulandelayo, uzodinga ukumemezela ubukhulu beqoqo ngesikhathi sokwenziwa kwekhodi usebenzisa isisho I-ReDim:

I-ReDim Team_Members(1 ukuya ku-20)

Futhi uma ngesikhathi sokwenziwa kwekhodi udinga ukushintsha usayizi we-array futhi, ungasebenzisa isisho se-ReDim futhi:

Uma Team_Size > 20 Bese ReDim Team_Members(1 To Team_Size) End Uma

Khumbula ukuthi ukushintsha usayizi wamalungu afanayo aguqukayo ngale ndlela kuzoholela ekulahlekeni kwawo wonke amanani agcinwe kuhlelo. Ukuze ulondoloze idatha esivele ikumalungu afanayo, udinga ukusebenzisa igama elingukhiye Gcinanjengoba kuboniswe ngezansi:

Uma Team_Size > 20 Bese ReDim Preserve Team_Members(1 To Team_Size) End if

Ngeshwa igama elingukhiye Gcina ingasetshenziswa kuphela ukushintsha umkhawulo ongaphezulu wobukhulu bamalungu afanayo. Isibopho esiphansi samalungu afanayo asinakushintshwa ngale ndlela. Futhi, uma amalungu afanayo anobukhulu obuningi, bese usebenzisa igama elingukhiye Gcina, ubukhulu bokugcina kuphela bamalungu afanayo obungashintshwa usayizi.