Usage of Excel objects in VB

Usage of Excel objects in VB
2000-11-13 · Li baofu · yesky

Excel not only has powerful computing functions, but also supports complex and fast operations on Excel Through Visual Basic programming, which is called macro function. in earlier versions, this feature was implemented using VBA. VBA sets visual basiC LanguageAfter modification, you can use VBA like Visual Basic, but cannot leave the Excel environment. in the new version, Visual Basic has been used to replace VBA. this is because the New Visual Basic language supports Excel objects. even so, this is used in a specific environment, and this environment defines its own special objects, so this programming will encounter the following unavoidable problems. to facilitate the following description, we will still writeProgramIt is called VBA.

1. How to use the cell object of a worksheet in VBA.

In a worksheet, you can select the region to use, or directly reference it or use a name or sub-reference. for example, to calculate the sum of a region (A1: A10) in Sheet 1, you can directly use the formula "= sum (A1: A10)", but it cannot be directly referenced in the VBA program, because Visual Basic does not know what objects are represented in this form. however, you can use the worksheet object worksheet in Visual Basic to perform operations on the worksheet. there are lower-level objects under this object. so it can also reference a cell object range lower than its level. that is, the cell area. other Object references are also similar. to intuitively understand this problem, use a small program to verify it. if you have already stored data in the (A1: A10) area of the sheet1 table, you can enter the following program to start the experiment:

Sub test macro 1 ()

X = worksheetfunction. sum (worksheets ("sheet1"). Range ("A1: A10 "))

'X before the equal sign is the variable that stores the calculation result.

Msgbox x' dialog box for displaying calculation results

End sub

Execute this program, and result X is displayed in the dialog box.

From the brackets of the sum function in the program, we can see that the program references objects layer by layer. First, the worksheets object. The parameter "sheet1" in the brackets is the worksheet that is actually used; next, use a dot to reference a lower-level cell object. The parameter "A1: A10" in the brackets is also the region we really want to process. note that double quotation marks are required for parameters and cannot be removed.

So what is the role of the worksheetfunction after the equal sign? This is the second question we will discuss.

Ii. How to Use worksheet functions in VBA

We have solved the problem of how to use cell objects and how to use function objects? Just as cell objects can be used directly in worksheets rather than in Visual Basic statements, can worksheet functions also be used as benzene mail fans? In the Visual Basic statement. to use a worksheet function in a visual basic statement, you must use the worksheetfunction object to reference it. with this flexibility, most worksheet functions can be used in Visual Basic statements. For example, the sum function above is a worksheet function. When you directly use it without adding the worksheetfunction "modifier", the program prompts "function or process is not defined when executed! ", The solution is to add" worksheetfunction "before sum as in the previous example ". that is to say, through this object, we can further reference the object sum with a lower level than it. if you are familiar with the Visual Basic language and have some knowledge of object-oriented programming, this knowledge is just a piece of cake!

Iii. How to differentiate worksheet Functions

to view which worksheet functions can be used in Visual Basic, find "list of available worksheet functions in Microsoft Excel Visual Basic" in the Help File of excel97. it should be noted that some worksheet functions are not used at all in Visual Basic and will not be discussed here. You may also encounter this situation: functions that implement a function can be found in Visual Basic and work function tables. The difference is that the names are different. in this case, functions in Visual Basic should be preferentially used as long as they are allowed. although the use of worksheet functions is also legal, it is more troublesome after all.