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