excel vba 语言基础（vba 语言基础 excel）.doc

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. 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), (power)The 2. logical operators are Not (non), And (and), Or (or)3. relational operators (equal) =, < > > (range), (greater than), (less than) <, > = < = (no less than), (less than)Data typeVBA standard data type (type name in parentheses is the type of character string): String ($Byte), byte type, Boolean Boolean, integer Integer (%), long integer type Long (&), single precision type (Single!), double Double (#), date type Date, Currency type of currency (), variant Variant, in addition to the user using Type custom data types.Variables and constants1. VBA allows undefined variables to be used, and defaults to variant variables.The 2. variable defines the statement and the scope of the variableThe Dim variable as type is defined as a local variable, such as Dim, x, as, integerThe Private variable as type is defined as a private variable, such as Private, y, as, byteThe Public variable as type is defined as a public variable, such as Public, Z, as, singleThe Global variable as type is defined as a global variable, such as Globlal, a, as, dateThe Static variable as type is defined as a static variable, such as Static, B, as, doubleThe principle of the scope of the general variable is that the part of the definition is in that part, and the function in the module is defined in that module. For convenience, you can add a type name after the variable name instead of the as type, when there is no space between the variable name and the type character.For example, Dim, x, as, integer, y, as, single, Z, as, string can also be simply defined as dim, x%, y, z$by type charactersThe 3. constant is a special case of a variable, defined by Const and assigned when defined. The program cannot change values, and the scope is like the scope of the variable. The following definition: Const Pi 3.1415926ArrayAn array is a collection of variables containing the same data type, and a reference to a single variable in an array is performed by array index subscript. In memory as a contiguous block of memory. Define the following rules:Dim array name (subscript) as type. A two-dimensional array is arranged in rows and columns, such as a (rows, columns).for exampleDim a (10) as integer defines a (0), a (1),. A (10) these 11 integer variables,Dim B (1, to 10) as integer defines B (1),. B (10) these 10 integer variables,Dim C (1, to, 3, 1, to, 4) as integer defines a two-dimensional array of variables in three rows and four columns.Writing norms1. VBA does not distinguish between the letters of the identifier, and are considered to be lowercase letters;2. lines can write more than one statement, each sentence with colon: separate;3. a multi line statement can be written with a space underlined _ to identify the behavior continues;4., the identifier is best concise and clear, without ambiguity.5. the part headed by a single quotation mark is referred to as the annotation section.Judgment statement1., If, -Then-, Else statementSyntax: If condition, Then statement 1 Else statement 2 EndifExample 1:If, A>B, And, C<D, ThenX=y+2ElseX=z+3EndifExample 2:If x>250 ThenX = X-100EndifTwo选择案例语句结案例：选择PID控制“它”如果PID =“它”价格= 200案例“A102”如果PID =“A102”价格= 300caseelse”否则价格= 900结案？循环语句1。下一语句以指定次数来重复执行一组语句。1：例i = 1到10循环体”表示我= 1,2,3，10依次执行循环体下一个我2：例i = 10到1步1循环体”表示我= 10,9,8，3,2,1依次执行循环体下一个我2。每一语句主要功能是对一个数组或集合对象进行，让所有元素重复执行一次语句。三.做而|直到环语句为当型循环（或而走），直到为直到型循环。例：做我10循环体”表示只要我10就反复执行循环体环注意，可以使用退出语句来退出为循环，用退出循环语句语句来退出做。4。与语句在一个单一对象上执行一系列的语句不用重复指出对象的名称。与对象声明结束？错误语句处理执行阶段有时会有错误的情况发生，利用OnError语句来处理错误，启动一个错误的处理程序。语法如下：OnError Goto线当错误发生时，会立刻转移到线行去接下来的当错误发生时OnError简历，会立刻转移到发生错误的下一行去onerro转到0当错误发生时，会立刻停止过程中任何错误处理过程？过程和函数过程是构成程序的一个模块，往往用来完成一个相对独立的功能。过程可以使程序更清晰、更具结构性。VBA具有四种过程：子过程、功能函数、财产属性过程和事件事件过程。1子过程。子过程是一系列由子和端子语句所包含起来的程序段，它们会执行动作却不能返回一个值。子过程可有参数，例如常数、变量、或是表达式等来调用它。如果一个子过程没有参数，则它的子语句必须包含一个空的圆括号。2。功能函数功能函数是一系列由功能和端功能所包含起来的程序段，它们会执行动作并能返回一个值。功能可有参数，例如常数、变量、或是表达式等来调用它。如果一个功能没有参数，则它的功能语句必须包含一个空的圆括号。子和功能的参数有两种传递方式：按值传递（ByVal）和按地址传递（ByRef或省略）。3。物业属性过程和事件事件过程这是VB在对象功能上添加的两个过程，与对象特征密切相关，也是面向对象程序设计最重要的组成部分。？Excel VBA的基本概念1、工作簿：工作簿工作簿、activeworkbook、工作簿集合包含Excel工作簿中所有当前打开的Excel工作簿，亦即所有打开的Excel工作簿工作簿文件；对应中的成员，即其中的Excel文件；activeworkbook代表当前处于活动状态的工作簿，即当前显示的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 open 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 table, 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: the 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 worksheet. 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 the 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 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 region.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 the 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 number, 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 with Cells, such as Range (Cells (1,1), Cells (10,10), which represents the cell region, A1:J10".5, charts: Chart, Charts, ChartObject, ChartObjects, ActiveChart"Chart" stands for the chart in the workbook. The chart can be either an embedded chart (included in ChartObject), or a separate (separate) chart 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 chart 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 (rightmost) 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, worksheet, 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 chart 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 handles 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 can 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 objects, 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 of 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 refers 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 continue 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 include 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 cell 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, 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 workbook（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”将工作簿以平铺方式排列（11）activeworkbook WindowState = xlmaximized”将当前工作簿最大化。工作表（12）activesheet.usedrange.rows.count”当前工作表中已使用的行数（13）activesheet.usedrange.columns.count”当前工作表中已使用的列数（14）行。计数的获取工作表的最大行数（注：考虑兼容性）（15）列。计数的获取工作表的最大列数（注：考虑兼容性）（16）表（表1）。名称=“和”“将Sheet1命名为总和表（一）。名称=“语”将第我个工作表命名为语（17）添加“添加一个新工作表在当前工作表前床单。表。加上之前介绍的添加一个新工作表在Sheet2工作表前：=表。后加：=表（表数）的添加一个新工作表在所有工作表之后（18）在当前工作表的移动（后：=表（表数）的将当前工作表移至工作表的最后（19）工作表（数组（“Sheet1”、“Sheet2”）。选择“同时选择工作表1和工作表2（20）表（“Sheet3”）。删除或sheet3.delete”删除工作表Sheet3表（3）。删除“删除第3个工作表（21）的。displaygridlines =不activewindow.displaygridlines”切换工作表中的网格线显示，这种方法也可以用在其它方面进行相互切换，即相当于开关按钮（22）的。displayheadings =不activewindow.displayheadings”切换工作表中的行列边框显示（23）activesheet.usedrange.formatconditions.delete”删除当前工作表中所有的条件格式（24）cells.hyperlinks.delete”取消当前工作表所有超链接（25）在当前工作表的pagesetup。定位= xllandscape或activesheet.pagesetup.orientation = 2“将页面设置更改为横向（26）在当前工作表的pagesetup。rightfooter = activeworkbook.fullname”在页面设置的表尾中输入文件路径（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).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"), ".