On the VB programming of Excel

Excel is an excellent spreadsheet software, and it's a good choice if your program needs to show the end result in the form of a report. You can control Excel display data form by VB. If you are not willing to delve into the trivial details of Excel, there is no expediency: You can open excel in the tools → macros → record new macros, and then hand-operated, after the completion of the recorded macro code into your VB program on the line. The resulting program generally can run normally, but the macro code is often not concise, efficiency and readability are not high.

The first problem that----Excel programming encounters is the table header. Sometimes the form of a header is more complex, and you need to merge cells horizontally or vertically. Please be assured that Excel can handle as long as there are no slashes.

----such as merging A2~A5 4 cells, the macro code you recorded would be like this:

Range ("A2:a5"). Select

Withselection

. Horizontalalignment=xlcenter

. Verticalalignment=xlbottom

. Wraptext=false

. Orientation=0

. Addindent=false

. Shrinktofit=false

. Mergecells=false

Endwith

Selection.merge

----and programming as long as a range. ("A2:a5"). Mergecells=true can solve the problem.

----table header form is set, and then the contents of the table header. If the text in a cell is longer than the width of the column, it tends to show only part of the content, and the end of the line will "cross" into the blank cell on the right. This problem can be resolved by setting the column width in the program.

----Columns. columnwidth=12 ' Set 14th column width to 12 (default column width is 8.38)

----If you're not willing to bother to estimate the actual column widths by row, simply come to a line

----Columns ("A:I"). AutoFit ' A to I column automatically adjusts column widths

----Let Excel improvise.

----But perhaps you do not like this method, think the table head is big column width, make browsing a small form still scroll to the right, too inconvenient. If you can keep the default column width, it's good to have text wrap automatically. No problem, Excel package you satisfied.

----Rows (3). Wraptext=true ' Let text wrap in the cells of the third row

----But you'd better add a line (3). Verticalalignment=xltop makes the headers automatically align up, which is more in line with custom.

----You can also put an undertone to the table, so that your readers will not see yawning.

----Rows (2). Interior.colorindex=5 ' Set the 2nd line with blue color

----to paint the title of the table again, so it's a little more eye-catching.

----Rows (1). Font.colorindex=4

It is too slow to fill in the data after----the header is finished, and it is much simpler if your data is stored in a two-dimensional array.

Dimdata (3,4)

... ' data processing. '.

Range ("A2:d4"). Value=data

----so that you can fill in one table at a time all the data, fast enough! However, the Range object size is best matched to the array, which is too small to display all the data, and a "N/a" entry in the blank cell to indicate that no data has been obtained.

----If you need to display multiple tables of the same size in the results, you can add a loop variable to the Range object.

Dimcell11,cell2

Dimdata (3,4)

............

Fori=1to40

... ' data processing. '.

Setcell1=worksheets ("Sheet1"). Cells (5*i-4,1)

Setcell2=worksheets ("Sheet1"). Cells (5*i-2,4)

Worksheets ("Sheet1"). Range (cell1,cell2). Value=data

Nexti

----form is finished, now it's time to hit the form line, here are a few statements to meet your requirements:

Withworksheets ("Sheet1"). Range (cell1,cell2). Borders

. Linestyle=xlcontinuous

. Weight=xlthin

Endwith