VBA Programming in Excel

Purpose : Sometimes we need to organize a large amount of data in an Excel file, which can be tedious and error-prone if you use manual grooming. In the case of VBA, it is possible to automate a large amount of data collation by writing a simple code in Excel based on requirements.

1, in Excel 2007, for example, if you want to do VBA programming, you need to enable the " Development tools " option. In the Excel Options dialog box, tick the Show Development Tools tab in the Ribbon check box.

Open the Microsoft Visual basic interface by tapping " View Code " in the Developer Tools tab.

2 . In the Microsoft Visual basic interface, click on the "plug-in Module" menu and Add a "Module 1". and add a function named Test in the module, as follows:

Adds a function body between sub and End Sub and saves it. Note Be sure to save the Excel file as a macro-enabled workbook (*.xlsm) format.

3 . Then click on the Macro button in the Development Tools option to open the following dialog box

Select Test, and click Execute. You can run the test function that you wrote.

Basic syntax:

1. Assign a value of 10 to the A1 unit of the current worksheet

ActiveSheet.Range ("A1"). Value = 10 or Range ("A1"). Value = 10 or ActiveSheet.Cells (1, 1). Value = 10 or Cells (1, 1). Value = 10

2. Assign 10 to the A2 unit of the SHEET2 worksheet

Sheet2.range ("A2"). Value = 10 or Sheet2.cells (2, 1). Value = 10 or Worksheets ("Sheet2"). Cells (2, 1). Value = 10

3. Copy the contents of Sheet2 A1 to B2 four units to Sheet1 A1 to B2 four units

Sheet1.range ("A1:b2"). Value = Sheet2.range ("A1:b2"). Value

4. Define and use worksheet variables

Dim MySheet as ObjectSet MySheet = Sheet1mysheet.cells (2, 1). Value = 10 ' Sets the A2 cell content in Sheet1 to 10

5. Define and use an integer variable and array

Dim Num As Integer ' defines an integer variable Numdim Color as Variant ' defines a variable type variable colornum = 1 ' assigns Num a value of 1Color = Array (36, 33, 38, 35, 40) ' Will C The olor assignment is an array sheet1.cells (1, 1) that holds the color index number. Interior.ColorIndex = Clor (Num) ' Changes the color of the A1 cell of Sheet1 to number 36th color

6. Define and use a string variable

Dim MyString as Stringmystring = "Const" & "Const1" Sheet1.range ("A1").  the contents of Value = MyString ' A1 become constconst1

7. For Loop

For i = 0 to 10 Next I

8. If statement

If i = 2 Then ' If I equals 2ElseEnd if

9. While statement

List = 1Do while sheet1.cells (1, list). Value <> "  ' traverse Sheet1 cell A1, A2, Straight                                            ' to a cell that encounters an empty content, exit while loop  list = list + 1Loop

10. Implement characters that wrap content in a cell

CHR (10)

11. Check whether the file exists

Dir (file name of full path) ' file exists then return file name, not present then return to empty

12. Look up from the last cell in column B and return the row number of the first non-empty cell encountered

Range ("B65536"). End (Xlup). Row

13. Remove the "200" string from the string "100/200"

Split ("100/200", "/") (1)

14. Get the index number of the current worksheet and get the table name by index number

Activesheet.index ' Gets the index number sheets. Name ' Get table name '

15. Set table Sheet1 to the current worksheet

Sheet1.activate

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

VBA Programming in Excel