Current location - Training Enrollment Network - Mathematics courses - How to use arrays in vba
How to use arrays in vba
Open the Excel table, click the development tools and Visual Basic on the menu bar, and open the VBA editor.

Please click to enter a picture description.

Click Insert and Module on the VBA Editor menu bar.

Please click to enter a picture description.

In the code window of the module, use the Array array to enter the following program code. Compared with the previous VBA program code, we will find that the latter program is more concise.

Sub CreateText2()

Dim i,j,k,arr

Continue to the next step when an error occurs.

Set myseet1= thisworkbook. Worksheet ("Sheet 1")

Set fs = createobject ("script. File system object ")' to access computer system files.

Set fi = fs. create textfile(" d:\ code 12345 . txt ",True)? Create the Code 12345.txt text file in drive d.

arr = Array("[User]"," uid= "," last_name= "," frist_name= "," accessibility= ",_

"password =", "sap me: defaultsite =", "role =", "group =")' writes fixed content into an array.

For i = 1 to 1000? From the first line to 1000 line

K = application. worksheet function . CountIf(my sheet 1。 Scope (myseet1. Cell (i, 1), _

Mysheet 1。 Cells(i, 8)), "")' Count the number of blank cells.

If k = 0, "If the number of blank cells is 0:

j = 0? J initialization, calling array from 0

Fi. WriteLine (arr(j))? Writes the contents of the array to a text document.

From the first column to the eighth column for j = 1 to 8'.

Fi. WriteLine (arr(j) and amp myseet1. Cell (i, j))? Get content from arrays and cells and write it to a text document.

then

If ... it will be over.

then

Fi. close

End joint

Please click to enter a picture description.

Please click to enter a picture description.

Interpretation of VBA program code;

(1) is used to ... when nesting the next loop, execute the external For once, execute the external For again after executing the internal For, and so on, and the loop ends after executing the external for.

(2)Array is an array function, in which the parameters are usually separated by commas (,), and the serial number of the corresponding values starts from 0. For example, arr=Array("AA ","BB ","CC "), and the value of arr (1) is the second in the array (that is," BB ").

(3) If you want to get the corresponding values in the array, click the view and local window, and then press F8 to run the program line by line. After passing through the array program line, you can see the corresponding values in the expanded array in the Local Window.

Please click to enter a picture description.

Please click to enter a picture description.

Running VBA program, the result of its execution is the same.

Please click to enter a picture description.