Обект ArrayList е подобен на обект Collection, но има много повече методи и свойства и следователно далеч по -голяма гъвкавост от гледна точка на програмирането.
Обект на колекция има само два метода (добавяне, премахване) и две свойства (брой, елемент), докато списък с масиви има много повече. Също така обектът Collection е само за четене. След като стойностите са добавени, индексираната стойност не може да бъде променена, докато в списък с масиви е възможно редактиране.
Много от методите на Array List използват параметри. За разлика от много от стандартните методи на VBA, нито един от тези параметри не е задължителен. Също така, някои от методите и свойствата не винаги се изписват с главни букви, когато се въвеждат по същия начин, както в Excel VBA. Те обаче все още работят.
Обектът ArrayList се разширява и свива по размер според това колко елементи съдържа. Не е необходимо да се оразмерява преди употреба като масив.
Списъкът с масиви е едноизмерен (същият като обекта на колекцията) и типът по подразбиране е Variant, което означава, че той ще приема всеки тип данни, независимо дали са числови, текстови или дата.
В много отношения списъкът с масиви адресира редица недостатъци на обекта Collection. Със сигурност е много по -гъвкав в това, което може да направи.
Обектът Array List не е част от стандартната VBA библиотека. Можете да го използвате във вашия Excel VBA код, като използвате късно или ранно свързване
| 1234 | Sub LateBindingExample ()Dim MyList As ObjectЗадайте MyList = CreateObject ("System.Collections.ArrayList")End Sub |
| 123 | Sub EarlyBindingExample ()Dim MyList As New ArrayListEnd Sub |
За да използвате примера за ранно свързване, първо трябва да въведете препратка във VBA към файла „mscorlib.tlb“
Можете да направите това, като изберете „Инструменти | Препратки ‘от прозореца на Visual Basic Editor (VBE). Ще се появи изскачащ прозорец с всички налични препратки. Превъртете надолу до „mscorlib.dll“ и поставете отметка в квадратчето до него. Щракнете върху OK и тази библиотека вече е част от вашия проект:
Един от големите недостатъци на обект Array List е, че той няма „Intellisense“. Обикновено, когато използвате обект във VBA, например диапазон, ще видите изскачащ списък с всички налични свойства и методи. Не получавате това с обект Array List и понякога се нуждае от внимателна проверка, за да се уверите, че сте написали правилно метода или свойството.
Също така, ако натиснете F2 в прозореца на VBE и потърсите „arraylist“, нищо няма да се покаже, което не е много полезно за разработчика.
Вашият код ще работи значително по -бързо с ранно свързване, защото целият е компилиран предварително. При късно свързване обектът трябва да се компилира, докато кодът се изпълнява
Разпространение на вашето приложение Excel, съдържащо списък с масиви
Както вече беше посочено, обектът ArrayList не е част от Excel VBA. Това означава, че всеки от вашите колеги, към които разпространявате приложението, трябва да има достъп до файла „mscorlib.tlb“
Обикновено този файл се намира в:
C: \ Windows \ Microsoft.NET \ Framework \ v4.0.30319
Може да си струва да напишете някакъв код (използвайки метода Dir), за да проверите дали този файл съществува, когато потребител зарежда приложението, така че да изпита „меко кацане“, ако не бъде намерен. Ако той не присъства и кодът работи, ще възникнат грешки.
Също така потребителят трябва да има инсталирана правилната версия .Net Framework. Дори ако потребителят има по -нова версия, V3.5 трябва да бъде инсталиран, в противен случай приложението ви няма да работи
Обхват на обект от списък с масиви
По отношение на обхвата, обектът Списък на масиви е достъпен само докато работната книга е отворена. Той не се запазва, когато работната книга е запазена. Ако работната книга е отворена отново, тогава обектът Списък на масиви трябва да бъде създаден отново с помощта на VBA код.
Ако искате вашият списък с масиви да е достъпен за целия код във вашия кодов модул, тогава трябва да декларирате обекта Списък на масиви в секцията Декларация в най -горната част на прозореца на модула
Това ще гарантира, че целият ви код в този модул има достъп до списъка с масиви. Ако искате някой модул във вашата работна книга да има достъп до обекта Списък на масиви, тогава го дефинирайте като глобален обект
| 1 | Глобална MyCollection като нов ArrayList |
Попълване и четене от вашия списък с масиви
Най -основното действие, което искате да предприемете, е да създадете списък с масиви, да поставите някои данни в него и след това да докажете, че данните могат да бъдат прочетени. Всички примери за код в тази статия предполагат, че използвате ранно свързване и сте добавили „mscorlib.tlb“ към препратките към VBA, както е описано по -горе
| 123456789101112 | Sub ArrayListExample ()„Създайте нов обект от списък с масивиDim MyList As New ArrayList„Добавяне на елементи към списъкаMyList.Add „Item1“MyList.Add „Item2“MyList.Add „Item3“„Итерация през масивния списък за доказване на стойностиЗа N = 0 към MyList.Count - 1MsgBox MyList (N)Следва NEnd Sub |
Този пример създава нов обект ArrayList, попълва го с 3 елемента и прави повторения през списъка, показващ всеки елемент.
Обърнете внимание, че индексът ArrayList започва с 0, а не с 1, така че трябва да извадите 1 от стойността Count
Можете също да използвате цикъл „За… Всеки“, за да прочетете стойностите:
| 123456789101112 | Sub ArrayListExample ()„Създайте нов обект от списък с масивиDim MyList As New ArrayList„Добавяне на елементи към списъкаMyList.Add „Item1“MyList.Add „Item2“MyList.Add „Item3“„Итерация през масивния списък за доказване на стойностиЗа всеки I в MyListMsgBox IСлед това азEnd Sub |
Редактиране и промяна на елементи в списък с масиви
Основно предимство на списък с масиви пред колекция е, че елементите в списъка могат да се редактират и променят във вашия код. Обектът Collection е само за четене, докато обектът Array List се чете / записва
| 123456789101112131415 | Sub ArrayListExample ()„Създайте нов обект от списък с масивиDim MyList As New ArrayList„Добавяне на елементи към списъкаMyList.Add „Item1“MyList.Add „Item2“MyList.Add „Item3“„Променете елемент 1 от„ Позиция2 “на„ Променено “MyList (1) = "Променено"„Повторете списъка с масиви, за да докажете, че промяната работиЗа всеки I в MyList„Показване на името на елементаMsgBox IСлед това азEnd Sub |
В този пример вторият елемент „Item2“ се променя до стойността „Changed“ (не забравяйте, че индексът започва от 0). Когато итерацията се изпълнява в края на кода, ще се покаже новата стойност
Добавяне на масив от стойности към списък с масиви
Можете да въвеждате стойности в списъка си с масиви, като използвате масив, съдържащ списък на тези стойности или препратки към стойности на клетки в работен лист
| 123456789101112131415161718 | Sub AddArrayExample ()„Създайте обект от списък с масивиDim MyList As New ArrayList‘Повторете през стойностите на масива, като ги добавите към списъка с масивиЗа всеки v In Array ("A1", "A2", "A3")„Добавете всяка стойност на масив към списъкаMyList.Add vСледващия‘Повторете през стойности на масиви с препратки към работен лист, като ги добавите към списъка с масивиЗа всеки v In Array (Диапазон ("A5"). Стойност, Диапазон ("A6"). Стойност)MyList.Add vСледващия„Итерация през масивния списък за доказване на стойностиЗа N = 0 към MyList.Count - 1„Показване на елемент от списъкаMsgBox MyList.Item (N)Следва NEnd Sub |
Четене / Извличане на диапазон от елементи от списък с масиви
Използвайки метода GetRange в списък с масиви, можете да посочите редица последователни елементи, които да бъдат извлечени. Изискваните два параметъра са началната позиция на индекса и броят на елементите, които трябва да бъдат извлечени. Кодът попълва втори обект Array List с подмножество от елементи, които след това могат да бъдат прочетени отделно.
| 123456789101112131415161718 | Sub ReadRangeExample ()„Определете обектиЗатъмнете MyList като нов ArrayList, MyList1 като обект„Добавяне на елементи към обекта„ MyList “MyList.Add „Item1“MyList.Add „Item2“MyList.Add „Item3“MyList.Add „Item6“MyList.Add „Item4“MyList.Add „Item7“„Заснемете 4 елемента в„ MyList “, започвайки от индекс позиция 2Задайте MyList1 = MyList.GetRange (2, 4)„Итерация през обекта„ MyList1 “, за да се покаже подмножеството елементиЗа всеки I In MyList1„Показване на името на елементаMsgBox IСлед това азEnd Sub |
Търсене на елементи в списък с масиви
Можете да проверите дали именован елемент е в списъка ви, като използвате метода „Съдържа“. Това ще върне True или False
| 1 | MsgBox MyList.Contains ("Item2") |
Можете също да намерите действителната позиция на индекса, като използвате метода „IndexOf“. Трябва да посочите началния индекс за търсенето (обикновено 0). Връщаната стойност е индексът на първия екземпляр на намерения елемент. След това можете да използвате цикъл, за да промените началната точка на следващата стойност на индекса, за да намерите допълнителни случаи, ако има няколко дублирани стойности.
Ако стойността не е намерена, се връща стойност -1
Този пример демонстрира използването на „Съдържа“, елемент не е намерен и циклично преглеждане на списъка с масиви, за да се намери позицията на всички дублирани елементи:
| 1234567891011121314151617181920212223242526 | Sub SearchListExample ()„Определете списък с масив и променливиDim MyList As New ArrayList, Sp As Integer, Pos As Integer„Добавете нови елементи, включително дубликатMyList.Add „Item1“MyList.Add „Item2“MyList.Add „Item3“MyList.Add „Item1“„Тестът за„ Item2 “е в списъка - връща TrueMsgBox MyList.Contains ("Item2")„Вземете индекс на несъществуваща стойност -връща -1MsgBox MyList.IndexOf ("Item", 0)„Задайте началната позиция на търсенето на нулаSp = 0„Итерация през списъка, за да получите всички позиции на„ Item1 “Направете„Вземете позицията на индекса на следващия„ елемент 1 “въз основа на позицията в променливата„ Sp “Pos = MyList.IndexOf ("Item1", Sp)„Ако не бъдат намерени други екземпляри на„ Item1 “, излезте от цикълаАко Pos = -1, тогава излезте от Do„Показване на следващия намерен екземпляр и позицията на индексаMsgBox MyList (Pos) & "at index" & Pos„Добавете 1 към последната намерена стойност на индекса - това сега става новата начална позиция за следващото търсенеSp = Pos + 1ЦикълEnd Sub |
Обърнете внимание, че използваният текст за търсене е чувствителен към малки и големи букви и не се приемат.
Поставяне и премахване на елементи
Ако не искате да добавяте елементите си в края на списъка, можете да ги вмъкнете на определена позиция на индекса, така че новият елемент да е в средата на списъка. Индексните номера автоматично ще се коригират за следващите елементи.
| 123456789101112131415 | Sub InsertExample ()„Определете обект от списък с масивиDim MyList As New ArrayList„Добавяне на елементи към списъка с масивиMyList.Add „Item1“MyList.Add „Item2“MyList.Add „Item3“MyList.Add „Item1“„Поставете„ Item6 “в позиция 2 на индексаMyList.Insert 2, "Item6"„Итерация през елементи в списъка с масиви, за да се покаже нов ред и позиция на индексаЗа N = 0 към MyList.Count - 1MsgBox MyList (N) & "Index" & NСледва NEnd Sub |
В този пример „Item6“ се добавя към списъка на индекс позиция 2, така че „item3“, който е бил на индекс позиция 2, сега се премества в позиция 3 на индекса
Отделен елемент може да бъде премахнат чрез метода „Премахване“.
| 1 | MyList.Remove "Item" |
Имайте предвид, че няма грешка, ако името на елемента не е намерено. Всички последващи индексни номера ще бъдат променени, за да отговарят на премахването.
Ако знаете позицията на индекса на елемента, можете да използвате метода ‘RemoveAt’, напр.
| 1 | MyList.RemoveAt 2 |
Имайте предвид, че ако дадената позиция на индекса е по -голяма от броя на елементите в списъка с масиви, тогава ще бъде върната грешка.
Можете да премахнете диапазон от стойности от списъка, като използвате метода „RemoveRange“. Параметрите са началният индекс и след това броят на елементите за премахване, напр.
| 1 | MyList.RemoveRange 3, 2 |
Обърнете внимание, че ще получите грешка в кода си, ако броят на елементите, изместен от началната стойност, е по -голям от броя на елементите в списъка с масиви.
И в методите „RemoveAt“ и „RemoveRange“ би било препоръчително да се провери дали посочените номера на индекса са по -големи от общия брой елементи в списъка с масиви, за да се уловят всички възможни грешки. Свойството ‘Count’ ще даде общия брой елементи в списъка с масиви.
| 12345678910111213141516171819202122232425 | Sub RemoveExample ()„Определете обект от списък с масивиDim MyList As New ArrayList„Добавяне на елементи към списъка с масивиMyList.Add „Item1“MyList.Add „Item2“MyList.Add „Item3“MyList.Add „Item1“MyList.Add „Item4“MyList.Add „Item5“„Поставете„ Item6 “в позиция 2 на индексаMyList.Insert 2, "Item6"„Премахване на„ Item2 “MyList.Remove "Item2"„Премахване на„ Item “ - това не съществува в списъка с масиви, но не грешиMyList.Remove "Item"„Извадете елемента на индекс позиция 2MyList.RemoveAt 2„Премахнете 2 последователни елемента, започващи от индекс позиция 2MyList.RemoveRange 3, 2„Повторете списъка с масиви, за да покажете какво е останало и в каква позиция на индекса е сегаЗа N = 0 към MyList.Count - 1MsgBox MyList (N) & "Index" & NСледва NEnd Sub |
Обърнете внимание, че ако използвате „RemoveAt“, за да премахнете елемент на определена позиция, веднага след като този елемент бъде премахнат, всички следващи позиции на индекса се променят. Ако имате няколко премахвания, използвайки позицията на индекса, тогава добра идея е да започнете с най -високия номер на индекса и да се върнете назад до позиция нула, така че винаги да премахвате правилния елемент. По този начин няма да имате проблем
Сортиране на списък с масиви
Друго голямо предимство пред колекция е, че можете да сортирате елементите във възходящ или низходящ ред.
Обектът Array List е единственият обект в Excel VBA с метод за сортиране. Методът за сортиране е много бърз и това може да бъде важно съображение за използването на списък с масиви.
В обекта за събиране беше необходимо известно мислене „извън кутията“, за да се сортират всички елементи, но със списък с масиви е много просто.
Методът „Сортиране“ сортира във възходящ ред, а методът „Обратно“ - в низходящ ред.
| 12345678910111213141516171819202122 | Sub ArrayListExample ()„Създайте обект Списък на масивиDim MyList As New ArrayList„Добавете елементи в несортиран редMyList.Add „Item1“MyList.Add „Item3“MyList.Add „Item2“„Сортирайте елементите във възходящ редMyList.Sort„Повтаряйте елементите, за да покажете възходящ редЗа всеки I в MyList„Показване на името на елементаMsgBox IСлед това аз„Сортирайте елементите в низходящ редMyList.Reverse„Повтаряйте елементите, за да покажете низходящ редЗа всеки I в MyList„Показване на името на елементаMsgBox IСлед това азEnd Sub |
Клониране на списък с масиви
Списъкът с масиви има възможност да създаде свой клонинг или копие. Това е полезно, ако потребителят прави промени в елементите, използвайки предния край и вашия VBA код, но трябва да запазите копие на елементите в първоначалното им състояние като резервно копие.
Това може да предостави на потребителя функция „Отмяна“. Възможно е те да са направили промените и искат да се върнат към първоначалния списък.
| 123456789101112131415 | Sub CloneExample ()„Определете два обекта - списък с масиви и обектЗатъмнете MyList като нов ArrayList, MyList1 като обект„Попълнете първия обект с елементиMyList.Add „Item1“MyList.Add „Item2“MyList.Add „Item3“„Копирайте Mylist в MyList1Задайте MyList1 = MyList.Clone„Повторете чрез MyList1, за да докажете клониранеЗа всеки I в MyList1„Показване на името на елементаMsgBox IСлед това азEnd Sub |
„MyList1“ вече съдържа всички елементи от „MyList“ в същия ред
Копиране на списъчен масив в обикновен VBA масив обект
Можете да използвате прост метод, за да копирате списъка с масиви в нормален VBA масив:
| 123456789101112131415 | Sub ArrayExample ()„Създайте обект от списък с масиви и стандартен обект от масивDim MyList As New ArrayList, NewArray As Variant„Попълнете списък с масиви с елементиMyList.Add „Item1“MyList.Add „Item2“MyList.Add „Item3“„Копирайте списъка с масиви в новия масивNewArray = MyList.ToArray„Итерация през новия масив - имайте предвид, че броят на списъка с масиви осигурява максималния индексЗа N = 0 към MyList.Count - 1„Показване на името на елементаMsgBox NewArray (N)Следва NEnd Sub |
Копиране на масив от списъци в диапазон от работни листове
Можете да копирате списъка си с масиви в конкретен работен лист и препратка към клетка, без да е необходимо да преглеждате списъка с масиви. Трябва само да посочите първата препратка към клетка
| 123456789101112131415 | Поддиапазон Пример ()„Създайте нов обект от списък с масивиDim MyList As New ArrayList„Добавяне на елементи към списъкаMyList.Add „Item1“MyList.Add „Item2“MyList.Add „Item3“„Изчистете целевия листЛистове ("Sheet1"). UsedRange.Clear„Копирайте елементи през един редЛистове ("Sheet1"). Диапазон ("A1"). Преоразмеряване (1, MyList.Count) .Value = MyList.toArray„Копирайте елементи надолу в колонаЛистове ("Sheet1"). Диапазон ("A5"). Преоразмеряване (MyList.Count, 1) .Value = _Работен лист Функция. Транспониране (MyList.toArray)End Sub |
Изчистете всички елементи от списък с масиви
Има проста функция (Изчистване), за да изчистите напълно списъка с масиви
| 1234567891011121314 | Sub ClearListExample ()„Създаване на обект от списък с масивиDim MyList As New ArrayList„Добавете нови елементиMyList.Add „Item1“MyList.Add „Item2“MyList.Add „Item3“„Показване на броя на елементитеMsgBox MyList.Count„Изчистете всички елементиMyList.Clear„Покажете броя на елементите, за да докажете, че ясното е работилоMsgBox MyList.CountEnd Sub |
Този пример създава елементи в списък с масиви и след това изчиства списъка с масиви. Кутиите за съобщения доказват преди и след броя на елементите в списъка с масиви.
Обобщение на методите на списъка с масиви за Excel VBA
| Задача | Параметри | Примери |
| Добавяне / Редактиране на елемент | Стойност | MyList.Add „Item1“ |
| MyList (4) = „Item2“ | ||
| Клониране на списък с масиви | Нито един | Dim MyList As Object |
| Задайте MyList2 = MyList.Clone | ||
| Копиране в масив | Нито един | Dim MyArray като вариант |
| MyArray = MyList.ToArray | ||
| Копиране в диапазон на работен лист (ред) | Нито един | Листове („Sheet1“). Диапазон („A1“). Преоразмеряване (1, MyList.Count) .Value = MyList.ToArray |
| Копиране в диапазон на работен лист (колона) | Нито един | Листове („Sheet1“). Диапазон („A3“). Преоразмеряване (MyList.Count, 1) .Value = WorksheetFunction.Transpose (MyList.ToArray) |
| Създайте | „System.Collections.ArrayList“ | Dim MyList As Object |
| Задайте MyList = CreateObject („System.Collections.ArrayList“) | ||
| Декларирайте | Н/Д | Dim MyList As Object |
| Намерете / проверете дали елементът съществува | Елемент за намиране | MyList.Contains („Item2“) |
| Намерете позицията на елемент в ArrayList | 1. Елемент за намиране. | Индекс на затъмняване Няма толкова дълго |
| 2. Позиция, от която да започнете търсене. | IndexNo = MyList.IndexOf („Item3“, 0) | |
| IndexNo = MyList.IndexOf („Item5“, 3) | ||
| Вземете брой елементи | Нито един | MsgBox MyList.Count |
| Вмъкване на елемент | 1. Индекс - позиция за вмъкване. | MyList.Insert 0, „Item5“ |
| 2 Стойност - обект или стойност за вмъкване. | MyList.Insert 4, „Item7“ | |
| Прочетете елемента | Индекс - дълго цяло число | MsgBox MyList.Item (0) |
| MsgBox MyList.Item (4) | ||
| Прочетете последния елемент, добавен | Индекс - дълго цяло число | MsgBox MyList.Item (list.Count - 1) |
| Първо прочетете добавения елемент | Индекс - дълго цяло число | MsgBox MyList.Item (0) |
| Прочетете всички елементи (за всеки) | Н/Д | Дим елемент като вариант |
| За всеки елемент в MyList | ||
| MsgBox елемент | ||
| Следващият елемент | ||
| Прочетете всички елементи (За) | Индекс - дълго цяло число | Dim i As Long |
| За i = 0 към MyList.Count - 1 | ||
| MsgBox i | ||
| Следва i | ||
| Премахнете всички елементи | Нито един | MyList.Clear |
| Извадете елемента на позиция | Индекс позиция, където е елементът | MyList.RemoveAt 5 |
| Премахнете елемента по име | Елементът за премахване от ArrayList | MyList.Remove “Item3” |
| Премахнете редица елементи | 1. Индекс - начална позиция. | MyList.RemoveRange 4,3 |
| 2. Брой - броят на елементите за премахване. | ||
| Сортирайте в низходящ ред | Нито един | MyList.Reverse |
| Сортирайте във възходящ ред | Не | MyList.Sort |
