《excel vba 语言基础（vba 语言基础 excel）.doc》由会员分享，可在线阅读，更多相关《excel vba 语言基础（vba 语言基础 excel）.doc（27页珍藏版）》请在三一文库上搜索。
1、excel vba 语言基础（vba 语言基础 excel）I. Basic VBA languageIdentifier1. definitionsAn identifier is a symbol that identifies variables, constants, processes, functions, classes, and other languages that can be completed by using themA reference to variables, constants, processes, functions, classes, etc.2.
2、naming rules1) initial letters, consisting of letters, numbers, and underscores, such as A987b_23Abc2) with VB reserved word names, such as public, private, dim, goto, next, with, integer, single etc.Operator1. mathematical operator + (plus), - (minus), Mod (take over), (divisible), * (x) / (except)
3、, (power)The 2. logical operators are Not (non), And (and), Or (or)3. relational operators (equal) =, (range), (greater than), (less than) = B, And, C250 ThenX = X-100EndifTwo选择案例语句结案例：选择PID控制“它”如果PID =“它”价格= 200案例“A102”如果PID =“A102”价格= 300caseelse”否则价格= 900结案？循环语句1。下一语句以指定次数来重复执行一组语句。1：例i = 1到10循环体
4、”表示我= 1,2,3，10依次执行循环体下一个我2：例i = 10到1步1循环体”表示我= 10,9,8，3,2,1依次执行循环体下一个我2。每一语句主要功能是对一个数组或集合对象进行，让所有元素重复执行一次语句。三.做而|直到环语句为当型循环（或而走），直到为直到型循环。例：做我10循环体”表示只要我10就反复执行循环体环注意，可以使用退出语句来退出为循环，用退出循环语句语句来退出做。4。与语句在一个单一对象上执行一系列的语句不用重复指出对象的名称。与对象声明结束？错误语句处理执行阶段有时会有错误的情况发生，利用OnError语句来处理错误，启动一个错误的处理程序。语法如下：OnError
5、 Goto线当错误发生时，会立刻转移到线行去接下来的当错误发生时OnError简历，会立刻转移到发生错误的下一行去onerro转到0当错误发生时，会立刻停止过程中任何错误处理过程？过程和函数过程是构成程序的一个模块，往往用来完成一个相对独立的功能。过程可以使程序更清晰、更具结构性。VBA具有四种过程：子过程、功能函数、财产属性过程和事件事件过程。1子过程。子过程是一系列由子和端子语句所包含起来的程序段，它们会执行动作却不能返回一个值。子过程可有参数，例如常数、变量、或是表达式等来调用它。如果一个子过程没有参数，则它的子语句必须包含一个空的圆括号。2。功能函数功能函数是一系列由功能和端功能所包含
6、起来的程序段，它们会执行动作并能返回一个值。功能可有参数，例如常数、变量、或是表达式等来调用它。如果一个功能没有参数，则它的功能语句必须包含一个空的圆括号。子和功能的参数有两种传递方式：按值传递（ByVal）和按地址传递（ByRef或省略）。3。物业属性过程和事件事件过程这是VB在对象功能上添加的两个过程，与对象特征密切相关，也是面向对象程序设计最重要的组成部分。？Excel VBA的基本概念1、工作簿：工作簿工作簿、activeworkbook、工作簿集合包含Excel工作簿中所有当前打开的Excel工作簿，亦即所有打开的Excel工作簿工作簿文件；对应中的成员，即其中的Excel文件；ac
7、tiveworkbook代表当前处于活动状态的工作簿，即当前显示的Excel工作簿代表其中有Visual Basic代码正在运行的工作簿文件。在具体使用中可用工作簿（指数）来引用工作簿对象，其中指数为工作簿名称或编号；Such as Workbooks (1) and Workbooks (annual report.Xls). The number is determined by the order in which you create or open the workbook. The first open workbook number is 1, and the second op
8、en workbook is 2.2, worksheet: Worksheets, Worksheet, ActiveSheetThe Worksheets collection contains all of the worksheet in the workbook, that is all the data pages in a Excel file; and Worksheet represents one of the work table; ActiveSheet represents the current in the active state of the work tab
9、le, which is currently showing a work table.The Worksheet object can be referenced by Worksheets (index), where index is the worksheet name or index number, such as Worksheets (1) and Worksheets (first quarter data). The worksheet index number indicates where the worksheet is in the worksheet tab: t
10、he first (leftmost) worksheet has an index of 1, and the last (rightmost) is Worksheets.Count. It should be noted that during use, Excel will automatically rearrange the worksheet index marks and keep them aligned from left to right in the worksheet labels, increasing the index number of the workshe
11、et. Therefore, the worksheet index quotes do not always remain unchanged due to the addition or deletion of possible worksheets.3, rows and columns: Rows, Columns, Row, ColumnThe Rows and Columns represent the active worksheet, cell range, Range, the specified number of rows in the worksheet, and th
12、e number of columns. For a multi cell area of Range Rows, Columns returns only the first region of the number of rows in the range, the number of columns. For example, if the Range object has two regions (areas), A1:B2, and C3:D4, Rows.Count returns 2 instead of 4.You can refer to the corresponding
13、rows and columns by Rows (line number) and Columns (column number). For example, Rows (3) and Columns (4) correspond to the third row and the D column, respectively.Using Rows and Columns, you can get the first line number, the first column number, and the decimal number of the first block in the re
14、gion.4, cells: Cells, ActiveCell, Range, AreasCells (row, column) stands for a single cell, where row is the line number and column is the column number. If you can use Cells (1,1) and Cells (10,4) to refer to the A1 and D10 cells. ActiveCell represents the active cell of the active worksheet, or th
15、e active cell that specifies the worksheet.Range represents a cell in a worksheet, a row, a column, a selected area (the selected area can contain one or several contiguous cell regions), or a three-dimensional region.You can use Range (ARG) to reference cells or cell ranges, where arg is the cell n
16、umber, cell number range, and cell area name. Such as Range (A5), Range (A1:H8) and Range (Criteria). Although Range (A1) can be used to return the cell A1, it is more convenient to use Cells because the rows and columns can be specified with variables at this time. Range can be used in conjunction
17、with Cells, such as Range (Cells (1,1), Cells (10,10), which represents the cell region, A1:J10.5, charts: Chart, Charts, ChartObject, ChartObjects, ActiveChartChart stands for the chart in the workbook. The chart can be either an embedded chart (included in ChartObject), or a separate (separate) ch
18、art worksheet.Charts represents a collection of all chart worksheets in a specified workbook or active workbook, but does not include diagrams embedded in the worksheet or dialog edit table. You can refer to a single Chart chart using Charts (index), where index is the index number or name of the ch
19、art worksheet, such as Charts (1) and Charts (sales chart). The index number of the chart worksheet indicates where the chart worksheet is located on the worksheet tab bar of the workbook. Charts (1) is the first (leftmost) chart worksheet in the workbook; Charts (Charts.Count) is the last (rightmos
20、t) chart worksheet.ChartObject represents the embedded chart in the worksheet, which acts as a container for the Chart object. Using ChartObject, you can control the appearance and size of embedded charts on a worksheet.ChartObjects represents the set of all embedded graphs on a specified chart, wor
21、ksheet, dialog box, edit table, or worksheet. A single ChartObject can be referenced by ChartObjects (index), where index is the number or name of the embedded chart. For example, Worksheets (Sheet1),.ChartObjects (1), Worksheets (Sheet1), .ChartObjects (chart1) correspond to the first embedded char
22、t in the Sheet1 worksheet, and the embedded chart called Chart1.ActiveChart can refer to diagrams in active state, whether the chart is a chart worksheet, or an embedded chart. As for the chart worksheet as active worksheet, you can also refer to it through the ActiveSheet property.6, objectsVBA han
23、dles the objects contained in its main application (in this case, Excel is the primary application). Excel provides more than 100 classes of objects that can be processed. Examples of objects include workbooks, worksheets, cell areas on the worksheet, and rectangles for diagrams and paintings. You c
24、an also process more objects according to the developers wishes, and you can use VBA code to process these objects. The object classes are arranged in a hierarchical structure. An object can be a container for other objects. For example, Excel is an object called Application, which contains other ob
25、jects, such as Workbook objects and CommandBar objects. A Workbook object can contain some other objects, such as Worksheet objects and Chart objects. An object that a Worksheet object can contain, such as cells, Range objects, and so on. The arrangement of these objects is called the object model o
26、f Excel.7, object hierarchyWhen the reference when an internal member or object, by using the period between the container and the members as a delimiter to specify its location in the object hierarchy. For example, you can refer to a workbook called Bookl.Xls:Application.Workbooks (Bookl.xls)This r
27、efers to the Bookl.xls workbook located in the Workbooks collection. The Workbooks collection is included in the Excel Application object and is extended to another level, and you can refer to the Sheetl worksheet in the Bookl workbook:Application.Workbooks (Bookl.xls),.Worksheets (Sheetl)You can co
28、ntinue to extend to the next layer and refer to a particular cell as follows:Application.Workbooks (Book1.xls), .Worksheets (Sheetl), .Range (Al)8, object attributesObjects have attributes. You can think of an attribute as a setting for an object. For example, the attributes of a Range object includ
29、e Value and Name. Chart objects include properties such as HasTitle and Type. You can use VBA to define properties of objects, and you can also change them. Attribute references, the objects and attributes together references, separated by periods. For example, you can refer to the values in the cel
30、l A1 in the Sheetl worksheet as follows:Worksheets (Sheetl),.Range (Al),.Value9, object methodObjects have methods. A method is an action performed with an object. For example, one of the methods for the Range object is ClearContents, which clears the contents of the cell area. The specified method,
31、 the combination of objects and methods are specified together, separated by periods. For example, to clear the contents of the cell A1 on the active worksheet, you can use the following statement:Range (Al).ClearContentsTwo, some commonly used VBA statementworkbook(1) Workbooks.Add creates a new wo
32、rkbook（2）练习册（“1 .xls”）。激活”激活名为1的工作簿（3）基本保存保存当前工作簿。（4）thisworkbook.close”关闭当前工作簿（5）activeworkbook.sheets.count”获取活动工作薄中工作表数（6）activeworkbook.name”返回活动工作薄的名称（7）基本返回当前工作簿名称名称”。（8）thisworkbook.fullname”返回当前工作簿路径和名称（9）的。enableresize =假”禁止调整活动工作簿的大小（10）application.window.arrange xlarrangestyletiled”将工作簿以平
33、铺方式排列（11）activeworkbook WindowState = xlmaximized”将当前工作簿最大化。工作表（12）activesheet.usedrange.rows.count”当前工作表中已使用的行数（13）activesheet.usedrange.columns.count”当前工作表中已使用的列数（14）行。计数的获取工作表的最大行数（注：考虑兼容性）（15）列。计数的获取工作表的最大列数（注：考虑兼容性）（16）表（表1）。名称=“和”“将Sheet1命名为总和表（一）。名称=“语”将第我个工作表命名为语（17）添加“添加一个新工作表在当前工作表前床单。表。加上
34、之前介绍的添加一个新工作表在Sheet2工作表前：=表。后加：=表（表数）的添加一个新工作表在所有工作表之后（18）在当前工作表的移动（后：=表（表数）的将当前工作表移至工作表的最后（19）工作表（数组（“Sheet1”、“Sheet2”）。选择“同时选择工作表1和工作表2（20）表（“Sheet3”）。删除或sheet3.delete”删除工作表Sheet3表（3）。删除“删除第3个工作表（21）的。displaygridlines =不activewindow.displaygridlines”切换工作表中的网格线显示，这种方法也可以用在其它方面进行相互切换，即相当于开关按钮（22）的。d
35、isplayheadings =不activewindow.displayheadings”切换工作表中的行列边框显示（23）activesheet.usedrange.formatconditions.delete”删除当前工作表中所有的条件格式（24）cells.hyperlinks.delete”取消当前工作表所有超链接（25）在当前工作表的pagesetup。定位= xllandscape或activesheet.pagesetup.orientation = 2“将页面设置更改为横向（26）在当前工作表的pagesetup。rightfooter = activeworkbook.f
36、ullname”在页面设置的表尾中输入文件路径（27）在当前工作表的pagesetup leftfooter =应用。UserName put the user name in the footer of the active worksheetRows, columns, cells(28) rows (I) or rows (5) I or fifth of the current worksheetColumns (5) or columns (5) or columns (E) the fifth column of the current worksheet(29) rows (I)
37、.Select selects line I of the current worksheetRows (I).ClearContents clears the contents of line I of the current worksheetRows (I).Clear clears the contents of the line I of the current worksheet and the format in itRows (I).Delete removes line I of the current worksheet (move up the following lines)(30) cells (I, J), column I, column J, of the current worksheetSheet3. cells (I, J) row sheet3, column I, column J cellSheets (ABCD), .