Encapsulating Excel VBA with vb.net (Visual Basic 2010) as a dll_com component (ii)

--Will EXCEL VBA code porting to vb.net

. NET is a new programming framework that Microsoft introduced in 2002 to support multiple language application development. Programming on the Microsoft. NET framework using Visual Basic, which is the Visual Basic.NET, referred to as vb.net.

Vb. NET is Microsoft Visual Studio. NET component, which is a later version of VB6.0, vb.net still uses the basic syntax of VB, which remains similar or identical in almost 90%, although Excel VBA code cannot be ported directly to Visual Basic as it is ported to VB6.0 2010, but in most cases, porting is fairly straightforward.

An object model of an Excel program.

In Excel, Excel program objects (application) are top-level objects, followed by Workbook objects (Workbook), sheet Objects (Worksheet), and Cell objects (Range). Only when you open an Excel program (Appliation object) can you create or open a workbook (the Workbook object) to select a sheet (Worksheet object) and to manipulate a Range object.

Many workbooks, several worksheets, countless cells, in the "Book1" workbook in the "Sheet1" worksheet in the "A1" cell Entry "A1", you must like the home address of the individual needs to refer to the relationship of the object from the largest to the small narrative clear. The following code:

1 Sub Rng2 () 2  Application.workbooks ("book1.xls"). Worksheets ("Sheet1"). Range ("A1""A1"3End Sub

The Application object represents the Excel program, which belongs to the topmost object, which can often be omitted, and the code may be modified as follows:

1 Sub Rng3 () 2  Workbooks ("book1.xls"). Worksheets ("Sheet1"). Range ("A1""A1"3End Sub

In Excel VBA, when you reference or manipulate an object, it allows you to omit its ancestor object, but it does not indicate that the object's upper-level objects do not exist.

Second, Visual Basic.NET write processing Excel table code (that is, code porting)

1. When declaring an object variable, the predecessor "Excel" is required, for example:

Dim  as ' defining an Excel object Dim  as ' Defining Workbook Objects Dim  as ' defining A sheet object Dim  as ' Defining Cell Objects

2. You cannot directly use "application" (that is, the Excel.exe process), but you need a variable call.

First declare a variable Vbapp in the module:

 Public  as ' defining an Excel object

Then assign a value to the variable Vbapp:

(1), reference Excel.exe process

In Excel referencing DLL dynamic link library Excel programs are already open, that is, through the Task Manager view process can see the Excel.exe process, we need to process the open Excel document, must refer to the current Excel.exe process. The code is as follows:

GetObject " Excel.Application " ' refer to the current Excel.exe process to make sure the Excel program is open otherwise error

(2), create Excel.exe process

The following statements allow you to create a new Excel.exe process, Visual Basic. NET can manipulate Excel tables by processing this process:

New ' Create a new Excel.exe process

In subsequent applications, variable Vbapp can be called instead of "application", such as:

After the execution of a new Excel.exe process statement, through the task Manager to see the process to find out the Excel.exe process, when the Excel program run interface is not displayed, after this statement to add the following code, Excel running interface is displayed:

True ' display the Excel program's running interface

Although the Excel runtime interface is displayed, but there are no tables, the following code creates a new table in the Excel program:

VbApp.Workbooks.Add

3. All objects must display a declaration that indicates that it is an object of Excel.

In Excel VBA, when referencing or manipulating an object allows you to omit its ancestor object, but porting the code to vb.net, we must use the complete expression to represent an object exactly. For example:

Visual Basic. NET processing of Excel tables is achieved by referencing (or creating) the Excel.exe process. The Excel.exe process referencing (or creating) corresponds to Vbapp in the preceding code, where the variable Vbapp represents the Excel program object (application). The following code assigns the value "A1" to the "A1" cell of worksheet 1th of workbook Book1.xls:

Vbapp. Bookexcel.worksheets ("book1.xls"). Range ("A1""A1 "

When you finish editing the Excel table, you can close the Excel table and destroy the Excel.exe process by using the following statement:

Vbapp. Bookexcel.close ()' close Excel table vbapp.quit ()' destroy Excel.exe process   Nothing ' Releasing object variables

As we can see, when we write VBA code in Excel, we strictly use the complete expression to represent the object exactly, and we use the variable Vbapp to call "Application" when porting the code.

Third, VBA and vb.net syntax modification

With the introduction of VB.net, VB has ushered in the biggest changes since its release, the entire runtime model has become a new common language runtime (CLR) environment, the language from object-based to object-oriented, the programmer familiar with Excel VBA porting package code is difficult. Not only need to master vb.net grammar, but also need to understand and apply the new functions, concepts and viewpoints proposed by vb.net. Of course it needs to be mastered. NET Framework, understanding the CLR (Common Language Runtime, when common language is in progress). The following syntax changes are especially necessary when porting code.

1. Data type

The data type used to store any type of data the variant is substituted by object; The data type used to store the large floating-point number currency is replaced by decimal and supports a higher number of degrees; in VBA, you can specify its length when you declare a string, and vb.net does not support fixed-length strings.

2. Assigning values to variables

You cannot declare and initialize variables at the same time in VBA, while vb.net supports the initialization of variables while declaring. For example:

Dim  as String  "VBA encapsulation "

In the vb.net can be two in unity:

Dim  as String " VBA Encapsulation "

Assigning a value to an object variable in VBA must use the SET statement, and the SET statement is canceled in vb.net, which means that the object variable is assigned the same value as a normal variable. Such as:

Excelvba Initialize object variable: Set rng= nothing

The SET statement is not required in vb.net: rng= Nothing

3. Arrays

The lower bounds of the VBA array can be customized, as the following statement declares an array of 10 elements with a lower bound of 1 and an upper bound of 10 arr:

Dim Arr (1totenasString

In vb.net, the lower bound of the array always starts at 0, and if you declare an array using the Dim Arr (1 to) As String, you will get an error.

In VBA, you can specify the length of an array, such as:

Dim Arr (2asInteger

The arr array length here is fixed and cannot be changed with the ReDim statement. Vb. NET does not support fixed-length arrays, so ReDim is always valid.

In VBA, ReDim is used to initialize dynamic arrays, In vb.net, the use of ReDim keyword changes slightly, the first change is: You must first define the array instance, you cannot declare an array with ReDim statement, the second change is: ReDim can only be used to change the length of the array, you cannot change the dimensions of the array. Special attention is paid to dynamic array declarations in vb.net, such as:

Dim  as Integer ' declaring a one-dimensional dynamic array

The above declaration can only create a one-dimensional dynamic array, to declare a two-dimensional dynamic array, the above declaration should contain a comma, such as:

Dim  as Integer ' declaring a two-dimensional dynamic array

4. Variable Scope

In VBA, variable scopes are: Process-level, module-level, and global. A new block scope, also known as a structure scope, is added to the vb.net.

A structure is an encoding structure consisting of two statements rather than a statement, such as if ... Then decision structure, for ... Next loop structure. In vb.net, if a variable is declared in a struct, its scope is scoped to the structure, and the variable is created after encountering the corresponding Dim statement and destroyed at the end of the structure. Such as:

1 If True  Then 2 3     Dim  as Integer 4 5 End If

5. Circular statements

Vb. NET adds the Continue keyword, using the continue for statement, in the for ... Next loop structure is able to enter the next time before the next statement for ... Next loop, of course, you can also use the Continue do statement in the Do ... The loop loop structure allows you to go to the next do ... before you reach the Loop statement ... Loop loops.

The above grammatical changes must be mastered, of course, we also need to gradually master some of the old and new syntax substitution, such as: Although vb.net also support data type conversion function, but in vb.net should strive to avoid use, the more common way to handle data type conversion is to use the System.Convert class; Excel VBA is handling errors through the On Error statement, which is still supported in vb.net, but has been discarded, and it is strongly recommended to use try ... Catch... Finally struct to handle exceptions (errors); vb.net still supports the old MsgBox () function, but is not recommended, but instead uses the MessageBox.Show () function instead.

The above introduction does not allow the reader to understand all aspects of vb.net grammar, if you want to learn more about the need to read some professional books. Such as:

Visual Basic 2010 Getting Started Classic (Chinese version)--[US] James Foxall, Mechenwen, People's post and telecommunications press.

Visual Basic &. NET 4 Advanced Programming (6th Edition)--(US) Billy Hollis, Peng Yukoyang, Tsinghua University Press.

Encapsulating Excel VBA with vb.net (Visual Basic 2010) as a dll_com component (ii)