Menu

Pole Excel VBA a metody polí s příklady

excel vba array array methods with examples

Vyzkoušejte Náš Nástroj Pro Odstranění Problémů

Tento tutoriál vysvětlí pomocí příkladů programování pole VBA, různé typy polí, varianty polí a metody polí:

Běžná proměnná VBA je zástupný symbol, který ukládá hodnotu jednotlivých dat. Má vztah 1: 1, tj. 1 proměnná pro 1 hodnotu.

Nyní si představte ukládání více hodnot stejného typu. Místo vytváření více proměnných stačí vytvořit jednu proměnnou a uložit všechny stejné typy hodnot. Tato proměnná se nazývá POLE.

=> Navštivte zde a prohlédněte si sérii školení VBA pro všechny

Pole ve VBA

fáze cyklu vývoje softwaru, ve které se programování provádí, je:

V tomto kurzu se dozvíte, co je pole VBA, jednorozměrná a dvourozměrná pole spolu s různými typy polí, jako je pevná a dynamická. Budeme také rozumět různým metodám pole, které se používají ve VBA.

Co se naučíte:

Pole VBA

Pole jsou speciální druh proměnné, která může ukládat více hodnot stejného datového typu.

Například, pokud máte jména 100 zaměstnanců, pak místo vytvoření 100 proměnných řetězce datového typu můžete vytvořit pouze jednu proměnnou pole typu řetězec a přiřadit 100 hodnot stejné proměnné pole.

Jedno dimenzionální pole

Pole, které má všechny prvky v jednom řádku nebo v jednom sloupci, se nazývá jednorozměrné pole. Výpis jmen všech studentů ve třídě v jednom sloupci je příkladem jednorozměrného pole. Je deklarováno, jak je uvedeno níže.

Dim pole name (lowerbound To UpperBound) As DataType

Existuje několik způsobů, jak deklarovat pole. Níže uvádíme několik příkladů.

Příklad:

# 1) Dim MyArrayExample (0 až 3) jako celé číslo

Vytvoří pole s umístěním 0,1,2,3, které bude přijímat celočíselné hodnoty.

# 2) Dim MyArray2 (3) jako řetězec

Výchozí hodnota je od 0 do 3 a vytvoří pole s umístěním 0,1,2,3, které bude přijímat řetězcové hodnoty.

# 3) Dim MyArray2 (13 až 15) jako dvojnásobek

Vytvoří pole začínající od 13, tj. 13, 14 a 15, a přijímá Double hodnoty. Spodní hranici jsme zmínili jako 13, takže pole začne přidělovat hodnoty z umístění 13 spíše než 0.

Vytvořme jednoduchý kód a pochopme všechny 3 způsoby deklarace pole.

Poznámka: Psaní kódu VB Otevřete Microsoft Excel (podporované verze jsou Excel 2007, 2010, 2013, 2016, 2019). Navigovat do Karta Vývojář -> Visual Basic (Alternativně použijte klávesovou zkratku Alt + F11). V editoru VB klikněte na Vložit -> Modul a vložte níže uvedený kód.

Zvažte níže uvedený postup zobrazující různé typy prohlášení.

Private Sub arrayExample1() Dim firstQuarter(0 To 2) As String ‘creates array with index 0,1,2 firstQuarter(0) = 'Jan' firstQuarter(1) = 'Feb' firstQuarter(2) = 'Mar' MsgBox 'First Quarter in calendar ' & ' ' & firstQuarter(0) & ' ' & firstQuarter(1) & ' ' & firstQuarter(2) End Sub Private Sub arrayExample2() Dim secondQuarter(2) As String ‘creates array with index 0,1,2 secondQuarter(0) = 'April' secondQuarter(1) = 'May' secondQuarter(2) = 'June' MsgBox 'Second Quarter in calendar ' & ' ' & secondQuarter(0) & ' ' & secondQuarter(1) & ' ' & secondQuarter(2) End Sub Private Sub arrayExample3() Dim thirdQuarter(13 To 15) As String ‘creates array with index 13,14,15 thirdQuarter(13) = 'July' thirdQuarter(14) = 'Aug' thirdQuarter(15) = 'Sep' MsgBox 'Third Quarter in calendar ' & ' ' & thirdQuarter(13) & ' ' & thirdQuarter(14) & ' ' & thirdQuarter(15) End Sub

Stisknutím klávesy F5 nebo stisknutím tlačítka spuštění na panelu nástrojů spustíte kód.

One Dimensional Array - výstup

Pravidelná proměnná Vs Array Variable

Nyní víme, jak funguje jednorozměrné pole. Pojďme si tedy na chvíli uvědomit, proč jsou pole v programovacích jazycích tak zásadní.

Předpokládejme, že musíte zadat plat 5 zaměstnanců. Abyste toho dosáhli pomocí běžné proměnné, musíte vytvořit 5 proměnných.

Public Sub RegularVariable() Dim shet As Worksheet Set shet = ThisWorkbook.Worksheets('Sheet1') ' Declare variable for each student Dim Emp1 As String Dim Emp2 As String Dim Emp3 As String Dim Emp4 As String Dim Emp5 As String ' Read student marks from cell Emp1 = shet.Range('A' & 2).Value Emp2 = shet.Range('A' & 3).Value Emp3 = shet.Range('A' & 4).Value Emp4 = shet.Range('A' & 5).Value Emp5 = shet.Range('A' & 6).Value ' Print student marks Debug.Print 'Emp Name' Debug.Print Emp1 Debug.Print Emp2 Debug.Print Emp3 Debug.Print Emp4 Debug.Print Emp5 End Sub

Nyní vytvořme stejný kód pomocí proměnné pole.

Option Explicit Public Sub ArrayVarible() Dim shet As Worksheet Set shet = ThisWorkbook.Worksheets('Sheet1') Dim Employee(1 To 6) As String Dim i As Integer For i = 1 To 6 Employee(i) = shet.Range('A' & i).Value Debug.Print Employee(i) Next i End Sub

Zde jsme právě použili jednu proměnnou pole, která uloží všechna jména zaměstnanců. Předpokládejme, že musíte přidat dalších 100 jmen zaměstnanců, pak stačí změnit velikost pole a nemusíte vytvářet novou proměnnou.

Tím se sníží počet řádků v kódu a tím se stane snadno srozumitelný a čitelný.

Dvourozměrné pole

Dvourozměrné pole má 2 indexy - první index bude představovat řádky a 2. index bude představovat sloupec. Má více řádků a sloupců a je obvykle reprezentován ve formátu tabulky.

Deklarace 2 dim matice je následující:

Dim ArrayName (FirstIndex To LastIndex, FirstIndex To LastIndex) jako DataType.

Zvažte příklad ukládání známek 2 studentů získaných ve 3 předmětech. Vytvoříme tedy dvourozměrné pole, které bude mít 2 řádky a 3 sloupce.

Pole začneme od řádku 1 do řádku 2 a ze sloupce 1 do sloupce 3.

Sub Twodim() Dim totalMarks(1 To 2, 1 To 3) As Integer totalMarks(1, 1) = 23 totalMarks(2, 1) = 34 totalMarks(1, 2) = 33 totalMarks(2, 2) = 55 totalMarks(1, 3) = 45 totalMarks(2, 3) = 44 Msgbox “Total Marks in Row 2 and column 2 is “ &totalMarks(2,2) Msgbox “Total Marks in Row 1 and column 3 is “ &totalMarks(1,3) End Sub

Stisknutím klávesy F5 nebo stisknutím tlačítka spuštění na panelu nástrojů spustíte kód.

Řádek 2 a sloupec 2

Dvourozměrné pole - příklad

Řádek 1 a sloupec 3

Dvourozměrné pole - výstup

Pevná pole

Pevná pole, která se také nazývají statická pole, mají pevnou dolní a horní hranici a tuto velikost nelze za běhu změnit. Velikost pole je zadána během deklarace v závorkách. Všechny výše uvedené příklady jsou pevná pole, protože jsme během deklarace uvedli jeho velikost.

Pevná pole se obvykle používají, pokud jste si jisti velikostí pole. Například, počet dní v týdnu, můžete vytvořit pole se spodní mezí 0 a horní mezí 6 a ujistěte se, že nikdy nezměníte její velikost.

Dynamická pole

Dynamická pole nám umožňují měnit velikost pole během běhu. Jsou užitečné, pokud si nejste jisti velikostí pole. Předpokládejme, že při přijetí na vysokou školu si nejste jisti, kolik studentů skutečně přijme, takže nemůžete určit velikost v době návrhu nebo deklarace.

Deklarace dynamického pole je podobná statickému poli s prázdnými závorkami.

Dim Employee () As String

REDIM

Když chceme změnit velikost, kterou musíme použít REDIM , musíme si uvědomit, že dolní mez nelze změnit, můžeme změnit pouze horní hranici pole.

Sub dynamicArray() Dim dynArray() As String Dim curdate As Date curdate = Now ReDim dynArray(2) ‘ Redim will help to change the array size during runtime dynArray(0) = 'John' dynArray(1) = 'Tom' dynArray(2) = 'Tonny' MsgBox 'Students Enrolled after ' & curdate & ' are “ & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) End Sub

REDIM

Nyní víme, že můžeme změnit velikost pole za běhu, proto můžeme použít příkaz ReDim, kdykoli potřebujeme zvýšit ubound pole. Zkusme ještě jednou zvětšit velikost pole a přidat nové jméno studenta.

Sub RedimExample() Dim dynArray() As String Dim curdate As Date curdate = Now Dim size As Integer ReDim dynArray(2) dynArray(0) = 'John' dynArray(1) = 'Tom' dynArray(2) = 'Tonny' MsgBox 'Students Enrolled untill ' & curdate & ' are ' & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) ReDim dynArray(3) ‘ Redim will reinitialise the array and destroy the old values dynArray(3) = 'John' MsgBox 'Students Enrolled untill ' & curdate & ' are ' & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) & ' , ' & dynArray(3) End Sub

Příklad REDim

Zjistili byste, že výsledek neukazoval jména dříve přidaných studentů, dává nulovou hodnotu. Je to proto, že příkaz Redim vytvoří nové pole s novou velikostí a zničí staré hodnoty.

ReDim Zachovat

Příkaz Represerve nám pomáhá překonat omezení ReDim zachováním starých hodnot a tím zvětšením velikosti pole.

Přepíšeme výše uvedený kód pomocí ReDim Preserve.

Sub preserveExample() Dim dynArray() As String Dim curdate As Date curdate = Now Dim size As Integer ReDim dynArray(2) dynArray(0) = 'John' dynArray(1) = 'Tom' dynArray(2) = 'Tonny' MsgBox 'Students Enrolled untill ' & curdate & ' are ' & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) ReDim preserve dynArray(3) ‘ Redim preserve will retain the old values dynArray(3) = 'John' MsgBox 'Students Enrolled untill ' & curdate & ' are ' & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) & ' , ' & dynArray(3) End Sub

REDim Zachovat

Protože jsme použili klíčové slovo zachování, dříve zadané hodnoty se neztratí a nová hodnota se přidá úspěšně.

Variant Array

Dosud jsme viděli pole přijímající stejný typ hodnot. Nyní deklarujeme pole jako variantu a uložme různé typy dat, jako jsou String, Date, Long, Integer, do jednoho pole.

Příklad:

Sub arrayVariant() Dim arrayData(3) As Variant arrayData(0) = 'Vikram Vikrant' arrayData(1) = 411234567890# arrayData(2) = 38 arrayData(3) = '06-09-1972' MsgBox 'Details of person ' & arrayData(0) & ' is ' & ' Phone No ' & arrayData(1) & ' ,Id ' & arrayData(2) & ' ,DOB ' & arrayData(3) End Sub

Variant Array

Metody VBA Array

V polích VBA existuje několik metod, které nám pomohou provádět různé funkce, jak je uvedeno níže.

Sl. NenázevSyntaxPopis
7 Připojit se Připojte se (sourcearray, (oddělovač))Spojí více podřetězců v poli a vrátí hodnotu řetězce.
1 Pole Pole (podvodné)Převede běžnou variantu
proměnná do pole.
dva Vymazat Vymazat název polePoužívá se k opětovné inicializaci pole pevné velikosti
a uvolní paměť pro dynamické pole.
3 IsArray IsArray (variablename)Určuje, zda je proměnná pole.
4 Lbound LBound (ArrayName, (Dimension))Vrátí nejnižší dolní index
pole.
5 Ubound UBound (ArrayName, (Dimension))Vrátí nejvyšší dolní index
pole.
6 Rozdělit Rozdělit (výraz, (oddělovač, (limit, (porovnat))))Rozdělí řetězec na několik podřetězců a vrátí pole založené na nule.
8 Filtr Filtr (sourcearray, shoda, (zahrnout, (porovnat)))Filtr nám umožní vyhledávat a
zadaná shoda z pole.

Pojďme si podrobně promluvit o každém z nich na příkladu.

# 1) Pole

Deklarujme proměnnou normální varianty a použijme ji jako pole. Chcete-li změnit běžnou variantní proměnnou na pole, musíme použít POLE funkce, jak je znázorněno v následujícím příkladu.

Funkce pole přijímají argument, který obsahuje hodnoty oddělené čárkami. Tyto hodnoty jsou přiřazeny jako prvek pole.

Sub variantArray() Dim varData As Variant varData = Array('Mon Bel', '+61 112334123', 567, '06-09-1972') MsgBox 'Details of person ' & varData(0) & ' is ' & ' Phone No ' & varData(1) & ' ,Id ' & varData(2) & ' ,DOB ' & varData(3) End Sub

Pole

Musíte identifikovat proměnnou pole pomocí indexu, proto ve výše uvedeném příkladu jsou hodnoty načteny jako varData (0) varData (2) varData (3).

# 2) Vymazat

Tato funkce vymaže všechny hodnoty zadané pro pole s pevnou velikostí a uvolní místo v paměti pro dynamické pole.

Syntaxe: Vymazat název pole

Vymazání má různé chování pro různé typy dat, jak je uvedeno níže.

  • Pro pevnou číselnou hodnotu: Všechny hodnoty se vynulují.
  • Pro datový typ s pevným řetězcem: Všechny hodnoty se resetují na nulovou délku.
  • Pro dynamické pole: Uvolní paměť použitou maticí.

Příklad:

Sub eraseExample() Dim NumArray(3) As Integer Dim decArray(2) As Double Dim strArray(2) As String NumArray(0) = 12345 decArray(1) = 34.5 strArray(1) = 'Erase Function' Dim DynaArray() ReDim DynaArray(3) MsgBox ' Values before Erase ' & (NumArray(0)) & ',' & (decArray(1)) & ' , ' & (strArray(1)) Erase NumArray Erase decArray Erase strArray Erase DynaArray ' Free the memory ' All values are erased. MsgBox ' Values after Erase ' & NumArray(0) & ',' & decArray(1) & ' , ' & strArray(1) End Sub

Výsledek před použitím funkce Vymazat

Výsledek před použitím funkce Vymazat

Výsledek po použití Vymazat

Výsledek po použití Vymazat

# 3) IsArray

Tato funkce se používá k určení, zda je daná vstupní proměnná pole nebo ne. Vrátí true, pokud je zadaná proměnná true, jinak vrátí false.

Syntaxe: IsArray (variablename)

Příklad:

Sub isArrayTest() Dim arr1, arr2 As Variant arr1 = Array('Jan', 'Feb', 'Mar') arr2 = '12345' MsgBox ('Is arr1 an Array : ' & IsArray(arr1)) MsgBox ('Is arr2 an Array : ' & IsArray(arr2)) End

Výsledek z prvního Msgboxu

IsArray - výsledek z prvního Msgboxu

Výsledek z druhého msgboxu

IsArray - výsledek z druhého Msgboxu

# 4) Lbound

Vrátí nejnižší dolní index pole zadaného jako argument pro funkci Lbound.

Syntaxe: LBound (ArrayName, (Dimension))

ArrayName je název pole.

Dimenze je volitelná celočíselná hodnota, pokud má pole více dimenzí, můžete určit, ke které dimenzi chcete určit mezní hodnotu.

Příklad:

Sub lboundTest() Dim Result1, Result2, Result3 Dim ArrayValue(1 To 10, 5 To 15, 10 To 20) ' Declare array variables. Dim Arraywithoutlbound(10) Result1 = LBound(ArrayValue, 1) ' Returns 1. Result2 = LBound(ArrayValue, 3) ' Returns 10. Result3 = LBound(Arraywithoutlbound) MsgBox 'Lowest subscript in first array ' & Result1 & ' lowest subscript in 3rd array ' & Result2 & ' Lowest subscript in Arraywithoutlbound ' & Result3 End Sub

lbound

# 5) Ubound

Vrátí horní dolní index pole zadaného jako argument ve funkci Ubound.

Syntaxe: UBound (ArrayName, (Dimension))

ArrayName je název pole.

Dimension je volitelná celočíselná hodnota, pokud má pole více dimenzí, můžete určit, u které dimenze chcete určit Ubound.

Příklad:

Sub UboundTest() Dim Result1, Result2, Result3 Dim ArrayValue(1 To 10, 5 To 15, 10 To 20) ' Declare array variables. Dim ArraywithoutUbound(10) Result1 = UBound(ArrayValue, 1) Result2 = UBound(ArrayValue, 3) Result3 = UBound(ArraywithoutUbound) MsgBox 'Lowest subscript in first array ' & Result1 & ' lowest subscript in 3rd array ' & Result2 & ' Lowest subscript in Arraywithoutlbound ' & Result3 End Sub

Ubound

# 6) Rozdělit

Vrátí pole s počtem podřetězců odvozených od daného celého řetězce.

Syntaxe: Split (výraz, (oddělovač, (limit, (srovnání))))

  • Výraz: Toto je celý řetězec, který bude použit k výrobě podřetězců.
  • Oddělovač: Pomocí zadaného oddělovače budou vygenerovány podřetězce. Pokud to není uvedeno, pak se za oddělovač považuje prostor.
  • Omezit: Počet podřetězců, které mají být vráceny.
  • Porovnat: Po vytvoření podřetězce můžete k otestování výsledku použít různé možnosti porovnání.

Příklad: V níže uvedeném příkladu používáme oddělovač jako - a limit jako 3.

jak spustit soubor jar s java

Funkce split tedy oddělí celý řetězec do podřetězce na základě oddělovače. Ale my jsme také zmínili limit jako 3, takže podřetězce nebudou vytvořeny po limitu 3. Poslední oddělovač - bude tedy přeskočen.

Sub splitExample() Dim MyString As String Dim Result() As String Dim DisplayText As String MyString = 'This is the example for-VBA-Split-Function' Result = Split(MyString, '-',3) MsgBox Result(0) & vbNewLine & Result(1) & vbNewLine & Result(2) & vbNewLine & Result(3) End Sub

Rozdělit

# 7) Připojte se

Toto je jen opak rozdělení, join vytvoří jeden řetězec kombinací několika podřetězců.

Syntaxe: Připojte se (sourcearray, (oddělovač))

Sourcearray: Jednorozměrné pole řetězců, které chcete spojit do jednoho.

Oddělovač: Zadaný oddělovač bude přidán po každém řetězci při připojování.

Příklad:

Sub joinExample() Dim Result As String Dim dirarray(0 To 2) As String dirarray(0) = 'D:' dirarray(1) = 'SoftwareTestingHelp' dirarray(2) = 'Arrays' Result = Join(dirarray, '') MsgBox 'Date after joining ' & Result End Sub

Všechny 3 hodnoty jsou spojeny a je umístěno mezi každé slovo, jak jsme uvedli jako oddělovač.

Připojit se

# 8) Filtr

Filtr nám umožní vyhledat zadanou shodu z pole. Na základě kritérií filtru bude vrácena podmnožina řetězcového pole.

Syntaxe: Filtr (sourcearray, shoda, (zahrnout, (porovnat)))

Příklad:

Sub filterExample() Dim Mystring As Variant Mystring = Array('Software Testing', 'Testing help', 'Software help') filterString = Filter(Mystring, 'help') MsgBox 'Found ' & UBound(Mystring) - LBound(Mystring) + 1 & ' words matching the criteria ' End Sub

Tento příklad vyhledá slovo „help“ v celém řetězci pole pomocí funkce filtru.

Filtr

Často kladené otázky

Otázka č. 1) Jak zjistit délku pole ve VBA?

Odpovědět: Abychom dostali délku pole, použijeme funkci Ubound. Tato funkce nám poskytne horní dolní index zadaného pole.

Otázka 2) Jak deklarovat pole ve VBA?

Odpověď: Jednorozměrné pole je deklarováno, jak je uvedeno níže.

Dim pole name (lowerbound To UpperBound) As DataType

Příklad: Dim Myarray (0 až 2) jako celé číslo

Dvourozměrné pole je deklarováno, jak je znázorněno níže.

Dim ArrayName (FirstIndex To LastIndex, FirstIndex To LastIndex) jako DataType.

Příklad: Ztlumit značky (1 až 3, 0 až 2) jako celé číslo

Otázka č. 3) Jak převést rozsah na pole?

Odpovědět: Můžeme použít funkci Transpose k převodu rozsahu na pole. Tento kód vytvoří Mys (10)

Sub Example() Dim Mys As Variant Mys = Application.Transpose(Range('A1:A10')) End Sub

převést Rozsah na pole

Otázka č. 4) Co je varianta pole ve VBA?

Odpovědět: Varianta pole přijme pro svůj index všechny druhy datových typů, tj. Do jednoho pole můžete uložit různé typy hodnot.

Příklad:

Dim arrayData (3) As Variant
arrayData (0) = 'Vikas Vipal'
arrayData (1) = 411234567890 #

Otázka č. 5) Co je to základní výpis možností?

Odpovědět: Slouží k deklaraci dolního dolního indexu pole a používá se na začátku modulu. Ve výchozím nastavení je dolní dolní index 0, pokud deklarujete Option Base 1 na úrovni modulu, pak bude pro všechna pole výchozí dolní dolní index 1.

Závěr

V tomto kurzu jsme se naučili, jak se pole používají ve VBA, a viděli jsme, jak se pole liší od běžné proměnné. Prozkoumali jsme jednorozměrná a dvourozměrná pole s příklady. Diskutovali jsme také o pevných a dynamických polích.

Způsoby, jak změnit velikost pole za běhu a také zachovat hodnoty pomocí redim preserve, byly diskutovány s příklady. Nakonec jsme se naučili metody Array, které nám pomohou při provádění několika operací.

=> Zkontrolujte VŠECHNY výukové programy VBA zde

Doporučené čtení

^