Tiêu đề 14.9 VBA - Arrays.pdf ✅

IT & ITES COPA - Programming with VBA Create and Use One Dimensional Arrays Objectives: At the end of this exercise you shall be able to • create and use a one dimensional array by specifying the size • create a one dimensional array by specifying lower and upper limits • create an array with integers • accept Array elements through a loop • create an Array using the Array() function PROCEDURE TASK 1 : Create and use a one dimensional array by specifying the size 1 Insert a new module in Excel VBA window. 2 Type the following code in the Module1code window. Sub test_array() Dim classmate(5) As String classmate(1) = "Uma" classmate(2) = "Rama" classmate(3) = "Reema" classmate(4) = "Suma" classmate(5) = "Garima" For n = 1 To 5 Debug.Printclassmate(n) Next n End Sub 3 Run the code from the immediate window. 4 Check the result in the immediate window. (Refer Fig 1) Fig 1 TASK 2 : Create a one dimensional array by specifying lower and upper limits 1 Type the following code in the Module1 code window. Sub test_array2() Dim subject(0 To 5) As String subject(0) = "Hindi" subject(1) = "Sanskrit" subject(2) = "English" subject(3) = "Science" subject(4) = "Social Studies" subject(5) = "Maths" For n = 0 To 5 Debug.Printsubject(n) Next n End Sub 2 Run the code from the immediate window. 3 Check the result in the immediate window. (Refer Fig 2) Fig 2
TASK 3 : Create an array with integers 1 Type the following code in the Module1 code window. Sub num_array() Dim n(5), i, sum As Integer n(1) = 10 n(2) = 4 n(3) = 6 n(4) = 11 n(5) = 3 For i = 1 To 5 Debug.Print " Element no. " &i& " is "; n(i) sum = sum + n(i) Next i Debug.Print "the sum of the array elements is : " & sum End Sub 2 Run the code from the immediate window. 3 Check the result in the immediate window. (Refer Fig 3 ) Fig 3 TASK 4 : Accept Array elements through a loop 1 Type the following code in the Module1 code window. Sub array1_test() Dim n(5), i, sum As Integer For i = 1 To 5 n(i) = InputBox("enter the array element number " &i) Next i For i = 1 To 5 Debug.Print " Element no. " &i& " is "; n(i) sum = sum + n(i) Next i Debug.Print "the sum of the array elements is : " & sum End Sub 2 Run the code from the immediate window. 3 Enter the array elements in the Input boxes. 4 Check the result in the immediate window. (Refer Fig 4 ) Fig 4 TASK 5 : Create an Array using the Array() function 1 Type the following code in the Module1 code window. Sub Test_Array3() Dim A As Variant A = Array("11", "25", "83", "60") For i = LBound(A) To UBound(A) Debug.Print A(i) Next End Sub 2 Run the code from the immediate window. 3 Check the result in the immediate window. (Refer Fig 5) Fig 5
IT & ITES COPA - Programming with VBA Create and Use Dynamic Arrays Objectives: At the end of this exercise you shall be able to • create and use dynamic array • change the array dimensions dynamically. PROCEDURE TASK 1 : Create and use dynamic array 1 Type the following code in the Module1 code window. Sub dyna_array() Dim names() As String 'Array without size specification Dim i As Integer 'Change the array dimensions using ReDim to 4 elements ReDim names(4) names(0) = "Ganga" names(1) = "Yamuna" names(2) = "Narmada" names(3) = "Sindhu" names(4) = "Kaveri" For i = 0 To 4 Debug.Print names(i); vbCrLf; Next i End Sub 2 Run the code from the immediate window. 3 Check the result in the immediate window. (Refer Fig 1) 4 Add the following line of code to the program shown above. names(5) = "Godavari". Fig 1 This tries to add a 6th element to an array defined for five elements. 5 Check the result. (Refer Fig 2) Fig 2 An array subscript out of range error message is generated. To avoid this redefine the array size and preserve data using ReDim and preserve statement. TASK 2 : Change the array dimensions dynamically 1 Add the Redim and Preserve statements to the program as shown below.