VBA programming in Excel

xiaoxiao2021-03-06  86

You can use VBA in Excel, which is a summary, and several code examples are Copy from Microsoft Excel Visual Basic for reference.

1.Excel "View", Toolbar, Control Toolbox. You can drag in various VBA controls from the control tool.

2. Open Tools, Macro, Visual Basic Editor, can program the controls. (You can enter the VBA code editing interface after double click.

3. Example of using the Range property and Cells property of the table:

This example sets the value of the A1 cell on the Sheet1 to 3.14159.

Worksheets ("sheet1"). Range ("a1"). Value = 3.14159

This example creates a formula in the A1 cell of Sheet1.

Worksheets ("sheet1"). Range ("a1"). Formula = "= 10 * Rand ()"

This example is loop on the cell zone A1: D10 of Sheet1. If the value of a cell is less than 0.001, this code will replace this value with 0 (zero).

For Each C in Worksheets ("Sheet1"). Range ("A1: D10")

IF C.Value <.001 THEN

C.Value = 0

END IF

Next C

This example is looped on the area called "TestRange" and displays the number of blank cells in the area.

Numblanks = 0

For Each C in Range ("TestRange")

If C.Value = "" "

Numblanks = Numblanks 1

END IF

Next C

Msgbox "There Are" & Numblanks & "EMPTY CELLS in this Range"

This example sets the font styles of the cell zone A1: C5 in Sheet1 to a bevel. This example uses the syntax 2 of the RANGE attribute 2. Worksheets ("Sheet1"). Range (Cells (1, 1), Cells (5, 3)). Font.italic = true

This example sets the font size of the cell C5 in Sheet1 to 14 pounds.

Worksheets ("sheet1"). Cells (5, 3) .font.size = 14

This example clears the formula of the first cell on Sheet1.

Worksheets ("Sheet1"). Cells (1) .clearContents

This example sets all cells of all cells on Sheet1 to 8 pounds "Arial" font.

WITH WORKSHEETS ("Sheet1"). Cells.Font

.Name = "arial"

.Size = 8

End with

This example cycle in the cell area A1: J4 on Sheet1, replacing the value of the other than 0.001 to 0 (zero).

For rwindex = 1 to 4

For colorex = 1 to 10

With worksheets ("sheet1"). Cells (rwindex, colIndex)

If .value <.001 Then .Value = 0

End with

Next Colindex

Next rwindex

转载请注明原文地址:https://www.9cbs.com/read-122527.html

New Post(0)