Introduction to VB programming in Excel

Introduction to VB programming in Excel

Excel is an excellent spreadsheet software. If your program needs to display the final result in the form of a report, it will be a good choice. You can use VB to control the display of data tables in Excel. If you do not want to gain an in-depth understanding of the trivial details of Excel, it is not an option: You can open the Excel tool → macro → record new macros, and then perform manual operations, after that, paste the macro code into your vbprogram. Generally, the program can run normally, but the macro code is not concise enough, and the efficiency and readability are not high.

---- The first problem encountered in Excel programming is the header. Sometimes the format of the table header is complex. You need to merge cells horizontally or vertically. Please rest assured that Excel can handle it without a slash.

---- For example, merge A2 ~ For the four cells A5, the macro code you recorded will look like this:

Range ("A2: A5"). Select

WithSelection

. HorizontalAlignment = xlCenter

. Verticalignment = xlBottom

. WrapText = False

. Orientation = 0

. AddIndent = False

. ShrinkToFit = False

. MergeCells = False

EndWith

Selection. Merge

---- You only need to specify Range. ("A2: A5"). mergecells = True to solve the problem.

---- The Header Format is fixed, and the content of the header is added. If the text length in a cell exceeds the column width, only part of the content can be displayed. The content at the end of the row will "Cross-border" into the blank cell on the right, which is not beautiful. This problem can be solved by setting the column width in the program.

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

---- If you don't want to work hard to estimate the column width you actually need one row by one

---- Columns ("a: I"). autofit 'a automatically adjusts the column width from column I

---- Let Excel work at random.

---- But maybe you don't like this method. I think the header is too wide to scroll to the Right to browse a small table, which is too inconvenient. If you can keep the default column width, let the Text wrap automatically. No problem. You are satisfied with the Excel pack.

---- Rows (3). WrapText = true' automatically wrap the text in cells in the third row

---- However, you 'd better add a Rows (3). VerticalAlignment = xlTop to automatically align the header up. This is quite a habit.

---- You can also set a background color for the header so that your readers do not see the source image.

---- Rows (2). Interior. ColorIndex = 5' set the base color of 2nd Rows to blue

---- Color the table title to make it more eye-catching.

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

---- After the header is complete, it is too slow to enter data one by one. If your data is stored in a two-dimensional array, the problem is much simpler.

DimData (3, 4)

............ 'Data Processing

Range ("a2: d4"). Value = Data

---- This way, you can enter all the data in a table at a time. It's quick enough! However, it is better to match the size of the Range object with the array. If it is small, all data cannot be displayed. If it is large, only "N/A" is entered in the blank cell to indicate that no data is obtained.

---- If you want to display multiple data tables of the same type in the result and want to add a circular variable to the Range object, this is fine.

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

---- After filling out the form, you should enter the form line. The following statements can meet your requirements:

WithWorksheets ("Sheet1"). Range (cell1, cell2). borders

. LineStyle = xlContinuous

. Weight = xlThin

EndWith