VBA/Excel/Access/Word/Data Type/Array
Содержание
- 1 Arrays are typically initialized inside a loop
- 2 Assign range to an array
- 3 Convert number to String by using Array
- 4 Declaring and Working with Fixed Arrays
- 5 Declaring array and setting bounds
- 6 Declaring a static array is similar to declaring a variable
- 7 Declaring a static array is similar to declaring a variable, with one small exception
- 8 Define and use multidimensional array
- 9 Fill array by using a nested For- Next loop.
- 10 Get the element in an array by index
- 11 Override the Option Base setting by specifically setting the lower bound in the array declaration
- 12 Referencing Elements in a Multi-dimensional Array
- 13 Searching through an Array
- 14 Sorting an Array
- 15 Sorts the List array in ascending order
- 16 Specifying the Index Range of an Array
- 17 To assume that 1 is the lower index for your arrays
- 18 Understanding Errors in Arrays
- 19 Use count function to count array
- 20 Use count function to sum array
- 21 Use LBound and UBound in for statement
- 22 Use the For Each...Next to assign value to an array
- 23 Using a One-Dimensional Array
- 24 Using Arrays and Loops
- 25 Using a Two-Dimensional Array
- 26 Working with Parameter Arrays
Arrays are typically initialized inside a loop
<source lang="vb">
Sub arrayInit()
Dim I As Integer Dim myArray(10) As Integer For I = 0 To 9 myArray(I) = Cells(I + 1, "A").Value Next I
End Sub
</source>
Assign range to an array
<source lang="vb">
Sub QuickFillMax()
Dim myArray As Variant myArray = Worksheets("Sheet1").range("B2:C17") MsgBox "Maximum Integer is: " & WorksheetFunction.Max(myArray)
End Sub
</source>
Convert number to String by using Array
<source lang="vb">
Function NumberToString(lngNumber As Long) As String
Dim strNumber As String Dim intLoop As Integer Dim strRV As String Dim strTemp As String Dim astrNumbers As Variant Dim iNumber As Integer astrNumbers = Array("Zero", "One", "Two", "Three", "Four", _ "Five", "Six", "Seven", "Eight", "Nine") strNumber = lngNumber For intLoop = 1 To Len(strNumber) iNumber = Int(Mid$(strNumber, intLoop, 1)) strRV = strRV & astrNumbers(iNumber) & " " Next NumberToString = strRV
End Function
</source>
Declaring and Working with Fixed Arrays
<source lang="vb">
Sub FixedArray()
Dim astrNames(5) As String Dim intCounter As Integer astrNames(0) = "A" astrNames(1) = "B" astrNames(2) = "C" astrNames(3) = "Z" "Use a For...Next loop to loop through the elements of the array For intCounter = 0 To UBound(astrNames) Debug.Print astrNames(intCounter) Next intCounter
End Sub
</source>
Declaring array and setting bounds
<source lang="vb">
Sub MyTestArray()
Dim myArray(1 To 4) As String " Declaring array and setting bounds Dim Response As String Dim i As Integer Dim myFlag As Boolean myFlag = False myArray(1) = "A" myArray(2) = "B" myArray(3) = "C" myArray(4) = "D" Do Until myFlag = True Response = InputBox("Please enter your choice: (i.e. A,B,C or D)") For i = 1 To 4 If UCase(Response) = UCase(myArray(i)) Then myFlag = True: Exit For End If Next i Loop
End Sub
</source>
Declaring a static array is similar to declaring a variable
<source lang="vb">
Sub arrayTest()
Dim i As Integer Dim intMyScores(10) As Integer For i = 0 To 10 intMyScores(i) = i Next For i = 0 To 10 Debug.Print "For array element " & i & " the number is " & intMyScores(i) Next
End Sub
</source>
Declaring a static array is similar to declaring a variable, with one small exception
<source lang="vb">
Sub arrayTest()
Dim i As Integer Dim intMyScores(10) As Integer For i = 0 To 10 intMyScores(i) = InputBox("Enter number " & i, "Static Array Test") Next For i = 0 To 10 Debug.Print "For array element " & i & " the number is " & _ intMyScores(i) Next
End Sub
</source>
Define and use multidimensional array
<source lang="vb">
Sub MultiDimArray()
Dim i As Integer Dim j As Integer Dim intNum() As Integer "Create a dynamic array ReDim intNum(2 To 3, 3 To 5) "Resize the array For i = 2 To 3 "Populate the array For j = 3 To 5 intNum(i, j) = i ^ j Next j Next i For i = 2 To 3 "Print the contents... For j = 3 To 5 "...of the array Debug.Print i & "^" & j & "=" & intNum(i, j) Next j Next i
End Sub
</source>
Fill array by using a nested For- Next loop.
<source lang="vb">
Sub NestedLoops()
Dim MyArray(10, 10, 10) Dim i As Integer Dim j As Integer Dim k As Integer For i = 1 To 10 For j = 1 To 10 For k = 1 To 10 MyArray(i, j, k) = 0 Next k Next j Next i
End Sub
</source>
Get the element in an array by index
<source lang="vb">
Sub MonthNames()
Dim varMonth As Variant varMonth = Array("Jan", "Feb", "Mar", "Apr") Debug.Print varMonth(1) Debug.Print varMonth(2)
End Sub
</source>
Override the Option Base setting by specifically setting the lower bound in the array declaration
<source lang="vb">
Sub arrayDemo()
Dim myArray(1 To 10) As Integer
End Sub
</source>
Referencing Elements in a Multi-dimensional Array
<source lang="vb">
Sub MultiDimArray()
Dim i As Integer Dim j As Integer Dim intNum() As Integer "Create a dynamic array ReDim intNum(2 To 3, 3 To 5) "Resize the array For i = 2 To 3 "Populate the array For j = 3 To 5 intNum(i, j) = i ^ j Next j Next i For i = 2 To 3 "Print the contents... For j = 3 To 5 "...of the array Debug.Print i & "^" & j & "=" & intNum(i, j) Next j Next i
End Sub
</source>
Searching through an Array
<source lang="vb"> Option Base 1 Sub Linear_Search_of_Array() Dim intArray(10) As Integer Dim i As Integer Dim varUserNumber As Variant
For i = 1 To 10 intArray(i) = Int(Rnd * 10) Debug.Print intArray(i) Next i varUserNumber = 4 For i = 1 To UBound(intArray) If intArray(i) = varUserNumber Then Debug.Print "Your value, " & varUserNumber & ", was found at position " & i & " in the array." Exit For End If Next i End Sub </source>
Sorting an Array
<source lang="vb"> Option Explicit Option Base 1 Sub Sort_an_Array() Dim strArray(12) As String Dim strTemp As String Dim X As Integer, Y As Integer, i As Integer strArray(1) = "n" strArray(2) = "d" strArray(3) = "h" strArray(4) = "g" strArray(5) = "e" strArray(6) = "d" strArray(7) = "o" strArray(8) = "p" strArray(9) = "m" strArray(10) = "h" strArray(11) = "b" strArray(12) = "m" Debug.Print "Current items in array:" For i = 1 To UBound(strArray) Debug.Print strArray(i) Next i For X = LBound(strArray) To (UBound(strArray) - 1) For Y = (X + 1) To UBound(strArray) If strArray(X) > strArray(Y) Then strTemp = strArray(X) strArray(X) = strArray(Y) strArray(Y) = strTemp strTemp = "" End If Next Y Next X Debug.Print "Items in sorted array:" For i = 1 To UBound(strArray) Debug.Print strArray(i) Next i End Sub </source>
Sorts the List array in ascending order
<source lang="vb">
Sub BubbleSort(List())
Dim First As Integer, Last As Integer Dim i As Integer, j As Integer Dim Temp First = LBound(List) Last = UBound(List) For i = First To Last - 1 For j = i + 1 To Last If List(i) > List(j) Then Temp = List(j) List(j) = List(i) List(i) = Temp End If Next j Next i
End Sub
</source>
Specifying the Index Range of an Array
<source lang="vb">
Sub ArrayExample()
Dim acWeeklySales(1 To 7) As Currency Dim n As Integer Dim sDay As String acWeeklySales(1) = 55100.44 acWeeklySales(2) = 43666.43 acWeeklySales(3) = 67004.11 acWeeklySales(4) = 87121.29 acWeeklySales(5) = 76444.94 acWeeklySales(6) = 98443.84 acWeeklySales(7) = 87772.37 For n = 1 To 7 sDay = Choose(n, "Mon", "Tue", "Wed", "Thu","Fri", "Sat", "Sun") Debug.Print"Sales for " & sDay & " were $" & acWeeklySales(n) Next
End Sub
</source>
To assume that 1 is the lower index for your arrays
<source lang="vb">
Option Base 1
</source>
Understanding Errors in Arrays
<source lang="vb">
Sub Zoo1()
Dim zoo(3) As String Dim i As Integer Dim response As String zoo(5) = ""
End Sub
</source>
Use count function to count array
<source lang="vb">
Sub Array3()
Dim Data(10) As Integer Dim Message As String, i As Integer For i = LBound(Data) To UBound(Data) Data(i) = i Next i MsgBox "Num Elements = " & WorksheetFunction.Count(Data)
End Sub
</source>
Use count function to sum array
<source lang="vb">
Sub Array4()
Dim Data(10) As Integer Dim Message As String, i As Integer For i = LBound(Data) To UBound(Data) Data(i) = i Next i MsgBox "Sum Elements = " & WorksheetFunction.Sum(Data)
End Sub
</source>
Use LBound and UBound in for statement
<source lang="vb">
Sub arrayTest2()
Dim i As Integer Dim intMyScores(10) As Integer For i = LBound(intMyScores) To UBound(intMyScores) intMyScores(i) = i Next For i = 0 To 10 Debug.Print "For array element " & i & " the number is " & intMyScores(i) Next
End Sub
</source>
Use the For Each...Next to assign value to an array
<source lang="vb">
Sub ArrayWith()
Dim astrNames(5) As String Dim intCounter As Integer Dim vntAny As Variant astrNames(0) = "A" astrNames(1) = "B" astrNames(2) = "C" astrNames(3) = "D" For Each vntAny In astrNames Debug.Print vntAny Next vntAny
End Sub
</source>
Using a One-Dimensional Array
<source lang="vb">
"Option Base 1 Sub FavoriteCities()
Dim cities(6) As String cities(1) = "Baltimore" cities(2) = "Atlanta" cities(3) = "Boston" cities(4) = "Washington" cities(5) = "New York" cities(6) = "Trenton" MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) _ & cities(3) & Chr(13) & cities(4) & Chr(13) _ & cities(5) & Chr(13) & cities(6)
End Sub
</source>
Using Arrays and Loops
<source lang="vb">
"Option Base 1 Sub FavoriteCities2()
" declare the array Dim cities(6) As String Dim city As Variant " assign the values to array elements cities(1) = "Baltimore" cities(2) = "Atlanta" cities(3) = "Boston" cities(4) = "Washington" cities(5) = "New York" cities(6) = "Trenton" " display the list of cities in separate messages For Each city In cities MsgBox city Next
End Sub
</source>
Using a Two-Dimensional Array
<source lang="vb">
Sub Exchange()
Dim t As String Dim r As String Dim Ex(3, 3) As Variant t = Chr(9) " Tab r = Chr(13) " Enter Ex(1, 1) = "Japan" Ex(1, 2) = "Japanese Yen" Ex(1, 3) = 102.76 Ex(2, 1) = "Europe" Ex(2, 2) = "Euro" Ex(2, 3) = 0.744734 Ex(3, 1) = "Canada" Ex(3, 2) = "Canadian Dollar" Ex(3, 3) = 1.20892 MsgBox "Country " & t & t & "Currency" & t & t & "Value per US$" _ & r & r & Ex(1, 1) & t & t & Ex(1, 2) & t & Ex(1, 3) & r _ & Ex(2, 1) & t & t & Ex(2, 2) & t & t & Ex(2, 3) & r _ & Ex(3, 1) & t & t & Ex(3, 2) & t & Ex(3, 3), , _ "Exchange"
End Sub
</source>
Working with Parameter Arrays
<source lang="vb">
Function AddMultipleArgs(ParamArray myNumbers() As Variant)
Dim mySum As Single Dim myValue As Variant For Each myValue In myNumbers mySum = mySum + myValue Next AddMultipleArgs = mySum
End Function Sub arrayPara()
MsgBox AddMultipleArgs(1, 2, 3)
End Sub
</source>