Niz VBA - Kako prijaviti in inicializirati niz nizov v Excelu VBA?

Kazalo

Niz nizov Excel VBA

Niz nizov Excel VBA

V VBA niz String ni nič drugega kot spremenljivka polja, ki lahko vsebuje več kot eno vrednost niza z eno spremenljivko.

Na primer, poglejte spodnjo kodo VBA.

Koda:

Sub String_Array_Example () Dim CityList (1 do 5) Kot Variant CityList (1) = "Bangalore" CityList (2) = "Mumbai" CityList (3) = "Kolkata" CityList (4) = "Hyderabad" CityList (5) = "Orissa" MsgBox CityList (1) & "," & CityList (2) & "," & CityList (3) & "," & CityList (4) & "," & CityList (5) End Sub

V zgornji kodi sem razglasil kot spremenljivko polja in določil dolžino polja od 1 do 5.

Dim CityList (1 do 5) kot različica

Za to spremenljivko matrike sem dodelil 5 imen mest, ki v oklepaju omenjajo vsako število nizov.

CityList (1) = "Bangalore" CityList (2) = "Mumbai" CityList (3) = "Kolkata" CityList (4) = "Hyderabad" CityList (5) = "Orissa"

Nato sem napisal kodo za prikaz teh mest v sporočilnem polju.

MsgBox CityList (1) & "," & CityList (2) & "," & CityList (3) & "," & CityList (4) & "," & CityList (5)

Ko zaženem to kodo, dobimo okno za sporočila, ki prikazuje vsa imena mest v enem polju za sporočila.

Vsi vemo, da je to prihranilo toliko časa iz našega urnika z odpravo naloge razglasitve posameznih spremenljivk za vsako mesto. Vendar se morate še ene stvari naučiti, da lahko še vedno zmanjšujemo kodo vrstice, ki jo pišemo za nizne vrednosti. Poglejmo, kako pišemo kodo za nize VBA.

Primeri niza nizov v Excelu VBA

Spodaj so primeri matričnega niza excel VBA.

Primer # 1

Kot smo videli v zgornji kodi, smo se naučili, da lahko v spremenljivko shranimo več kot eno vrednost glede na določeno velikost polja.

Zdaj, kar moramo storiti, je, da se dolžine polja ne odločimo vnaprej.

Koda:

Sub String_Array_Example1 () Dim CityList () Kot Variant End Sub

Kot vidite zgoraj v oklepaju, nisem zapisal dolžine. Zdaj za to spremenljivko vstavimo vrednosti s pomočjo funkcije VBA ARRAY.

V matriki posredujte vrednosti v dvojnih narekovajih, ločenih z vejico (,).

Koda:

Sub String_Array_Example () Dim CityList () Kot Variant CityList = Array ("Bangalore", "Mumbai", "Kolkata", "Hyderabad", "Orissa") End Sub

Zdaj obdržite staro kodo, da se v polju za sporočila v VBA prikaže rezultat imen mest.

Koda:

Sub String_Array_Example1 () Dim CityList () Kot Variant CityList = Array ("Bangalore", "Mumbai", "Kolkata", "Hyderabad", "Orissa") MsgBox CityList (0) & "," & CityList (1) & " , "& CityList (2) &", "& CityList (3) &", "& CityList (4) End Sub

Ena sprememba, ki sem jo naredil v zgornji kodi, je ta, da nismo določili spodnje in zgornje meje spremenljivke matrike in smo uporabili število matrik funkcije ARRAY, ki se začne od 0, ne od 1.

To je razlog, da smo vrednosti omenili kot CityList (0), ClityList (1), CityList (2), CityList (3) in CityList (4).

Zdaj zaženite kodo prek Excelove bližnjične tipke F5 ali ročno. Dobimo enak rezultat kot prejšnjo kodo.

2. primer

VBA String Array with LBOUND & UBOUND Functions

In case if you don’t want to show all the city lists in a single message box, then you need to include loops, define one more variable for loops.

Now to include FOR NEXT loop, we are not sure how many times we need to run the code. In this case, we can decide it like 5 times, but that is not the right way to approach the problem. So how about the idea of auto lower and higher level array length identifier???

When we open FOR NEXT loop, we usually decide the loop length as 1 to 5 or 1 to 10 depending upon the situation. Instead of entering the numbers manually, let’s use LBOUND and UBOUND function to decide on the lower value and upper value automatically.

For LBound and Ubound, I have supplied an array name, i.e., CityList. VBA LBound identifies the lower value of the array variable, and VBA UBound function identifies the upper value of the array variable.

Now show the value in the message box, instead of inserting the serial number, let the loop variable “k” takes the array value automatically.

Code:

Sub String_Array_Example1() Dim CityList() As Variant Dim k As Integer CityList = Array("Bangalore", "Mumbai", "Kolkata", "Hyderabad", "Orissa") For k = LBound(CityList) To UBound(CityList) MsgBox CityList(k) Next k End Sub

Now the message box will show each city name separately.

Example #3

VBA String Array with Split Function

Now assume you have city names like the below.

Bangalore;Mumbai;Kolkata;Hydrabad;Orissa

In this case, all the cities are combined together with the colon separating each city. In such cases, we need to use the SPLIT function to separate each city.

For Expression, supply the city list.

Code:

Sub String_Array_Example2() Dim CityList() As String Dim k As Integer CityList = Split("Bangalore;Mumbai;Kolkata;Hydrabad;Orissa", For k = LBound(CityList) To UBound(CityList) MsgBox CityList(k) Next k End Sub

The next argument is “Delimiter,” i.e., what is the one character that is separating each city from other cities. In this case, “Colon.”

Code:

Sub String_Array_Example2() Dim CityList() As String Dim k As Integer CityList = Split("Bangalore;Mumbai;Kolkata;Hydrabad;Orissa", ";") For k = LBound(CityList) To UBound(CityList) MsgBox CityList(k) Next k End Sub