Contents
Arrays in Visual Basic don Aikace-aikace ginshiƙi ne waɗanda galibi ke adana saitin masu canji iri ɗaya. Ana samun isa ga shigarwar tsararru ta hanyar fihirisarsu.
Misali, akwai ƙungiyar mutane 20 waɗanda suna buƙatar adana sunayensu don amfani da su daga baya a lambar VBA. Mutum zai iya kawai ayyana masu canji guda 20 don riƙe kowane suna, kamar haka:
Dim Team_Member1 As String Dim Team_Member2 As String ... Dim Team_Member20 As String
Amma kuna iya amfani da hanya mafi sauƙi kuma mafi tsari - adana jerin sunayen membobin ƙungiyar a cikin tsararrun masu canji 20 kamar kirtani:
Dim Team_Members (1 Zuwa 20) A Matsayin Zauren
A cikin layin da aka nuna a sama, mun ayyana tsararru. Yanzu bari mu rubuta ƙima ga kowane ɗayan abubuwansa, kamar haka:
Team_Members(1) = "John Smith"
Ƙarin fa'idar adana bayanai a cikin tsararru, idan aka kwatanta da yin amfani da masu canji daban-daban, yana bayyana lokacin da ya zama dole a yi aiki iri ɗaya akan kowane ɓangaren tsararrun. Idan an adana sunayen membobin ƙungiyar a cikin mabambanta 20 daban-daban, to zai ɗauki layukan lamba 20 don rubuta kowane lokaci don aiwatar da aiki iri ɗaya akan kowannensu. Koyaya, idan an adana sunayen a cikin tsararru, to zaku iya aiwatar da aikin da ake so tare da kowane ɗayansu ta amfani da madauki mai sauƙi.
Ana nuna yadda yake aiki a ƙasa tare da misalin lamba wanda ke buga sunayen kowane memba na ƙungiyar bi da bi a cikin sel ɗin shafi. A Active takardar aikin Excel.
Don i = 1 Zuwa 20 Sel
A bayyane yake, yin aiki tare da tsararrun da ke adana sunaye 20 ba shi da wahala sosai kuma ya fi dacewa fiye da amfani da masu canji daban-daban 20. Amma idan waɗannan sunayen ba 20 ba, amma 1000 fa? Kuma idan, a Bugu da kari, shi ake bukata don ci gaba da surnames da patronymics dabam?! A bayyane yake cewa ba da daɗewa ba zai zama ba zai yiwu ba don sarrafa irin wannan ƙarar bayanai a cikin lambar VBA ba tare da taimakon tsararru ba.
Multidimensional arrays a cikin Excel Visual Basic
Tsarukan Kayayyakin Kayayyakin Kayayyakin da aka tattauna a sama ana ɗaukar su mai girma ɗaya ne. Wannan yana nufin cewa suna adana jerin sunaye masu sauƙi. Duk da haka, tsararraki na iya samun girma dabam dabam. Misali, ana iya kwatanta jeri mai girma biyu da grid na dabi'u.
Bari mu ce kuna son adana adadin tallace-tallace na yau da kullun na Janairu don ƙungiyoyi 5 daban-daban. Wannan zai buƙaci tsari mai girma biyu wanda ya ƙunshi saiti 5 na ma'auni na kwanaki 31. Bari mu ayyana tsari kamar haka:
Dim Jan_Sales_Figures(1 Zuwa 31, 1 Zuwa 5) A Matsayin Kudi
Don samun dama ga abubuwan tsararru Figures_Jan_Sales, kuna buƙatar amfani da fihirisa biyu masu nuna ranar wata da lambar umarni. Misali, adireshin wani abu mai ɗauke da alkalumman tallace-tallace don 2- ku ƙungiyoyi don 15 ta Janairu za a rubuta kamar haka:
Figures_Janairu (15, 2)
Hakazalika, zaku iya ayyana jeri mai girma 3 ko sama da haka - kawai ƙara ƙarin girma zuwa sanarwar tsararru kuma yi amfani da ƙarin fihirisa don komawa ga abubuwan wannan jeri.
Bayyana Arrays a cikin Kayayyakin gani na Excel
Tun da farko a cikin wannan labarin, mun riga mun kalli misalai da yawa na ayyana tsararraki a cikin VBA, amma wannan batu ya cancanci dubawa. Kamar yadda aka nuna, ana iya bayyana tsararru mai girma ɗaya kamar haka:
Dim Team_Members (1 Zuwa 20) A Matsayin Zauren
Irin wannan sanarwa yana gaya wa mai tarawa VBA cewa tsararru Tawagar_Membobi ya ƙunshi ma'auni guda 20 waɗanda za a iya isa ga ma'aunin ƙididdiga daga 1 zuwa 20. Duk da haka, muna iya tunanin ƙidayar ƙididdiga na tsararrun mu daga 0 zuwa 19, wanda ya kamata a bayyana tsarin kamar haka:
Dim Team_Members (0 Zuwa 19) A Matsayin Zauren
A zahiri, ta tsohuwa, adadin abubuwan tsararru yana farawa daga 0, kuma a cikin sanarwar tsararru, ƙila ba za a iya fayyace fihirisar farko ba kwata-kwata, kamar haka:
Dim Tawagar_Membobi(19) A Matsayin Kiriri
Mai tarawa na VBA zai kula da irin wannan shigarwa kamar bayyana jerin abubuwa 20 tare da fihirisa daga 0 zuwa 19.
Dokokin iri ɗaya suna aiki lokacin da ake bayyana tsararrun Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kaya). Kamar yadda aka riga aka nuna a ɗaya daga cikin misalan, lokacin da ake bayyana tsararru mai girma biyu, ana raba fihirisar girmansa da waƙafi:
Dim Jan_Sales_Figures(1 Zuwa 31, 1 Zuwa 5) A Matsayin Kudi
Koyaya, idan ba ku ƙididdige fihirisar farawa don nau'ikan jeri biyu ba kuma ku ayyana shi kamar haka:
Dim Jan_Sales_Figures(31, 5) A Matsayin Kuɗi
to wannan shigarwar za a yi la'akari da shi azaman nau'i mai nau'i biyu, wanda girmansa na farko ya ƙunshi abubuwa 32 tare da indices daga 0 zuwa 31, kuma nau'i na biyu na array ya ƙunshi abubuwa 6 tare da indices daga 0 zuwa 5.
Tsari mai ƙarfi
Duk tsararraki a cikin misalan da ke sama suna da ƙayyadaddun adadin girma. Duk da haka, a yawancin lokuta ba mu sani ba tukuna ko girman girman tsararrun mu. Za mu iya fita daga cikin halin da ake ciki ta hanyar ayyana babban tsari, wanda girmansa tabbas zai fi girma fiye da wajibi don aikinmu. Amma irin wannan bayani zai buƙaci ƙarin ƙwaƙwalwar ajiya mai yawa kuma zai iya rage shirin. Akwai mafita mafi kyau. Za mu iya amfani da tsararru mai ƙarfi - wannan tsararru ce wadda za'a iya saita girmanta da canza kowane adadin lokuta yayin aiwatar da macro.
Ana ayyana tsararraki mai ƙarfi tare da baban ƙima, kamar haka:
Dim Team_Members() A matsayin String
Na gaba, kuna buƙatar bayyana girman tsararrun yayin aiwatar da lambar ta amfani da furci ReDim:
ReDim Team_Membobi (1 Zuwa 20)
Kuma idan yayin aiwatar da lambar kuna buƙatar sake canza girman tsararrun, to zaku iya sake amfani da furcin ReDim:
Idan Girman Ƙungiya> 20 To ReDim Team_Members(1 Zuwa Ƙungiya_Girman) Ya ƙare Idan
Ka tuna cewa canza girman tsararru mai ƙarfi ta wannan hanyar zai haifar da asarar duk ƙimar da aka adana a cikin tsararru. Domin adana bayanai riga a cikin tsararru, kuna buƙatar amfani da kalmar maɓalli Tsarekamar yadda aka nuna a kasa:
Idan Girman Ƙungiya> 20 To ReDim Kiyaye Team_Membobi (1 Zuwa Girman Ƙungiya) Ya ƙare Idan
Abin baƙin ciki da keyword Tsare za a iya amfani da shi kawai don canza babban iyaka na girman tsararru. Ba za a iya canza ƙananan iyaka na tsararru ta wannan hanyar ba. Har ila yau, idan tsararru tana da nau'i-nau'i masu yawa, to, amfani da maɓalli Tsare, Ƙarshe kawai girma na tsararrun za a iya sake girma.