VBA Excel programming three-day discussion (1)

Recently, I have learned how powerful VBA is to modify an Excel report. This report was written by Daniel three years ago. Just open this file every day, the Dev and UAT databases in Oracle are automatically connected to read the latest market data and six perspectives are generated, compare the data similarities and differences between Dev and UAT. The convenience of using VBA to operate data and the complexity of the report generated have deeply attracted me, so I am really sorry for not learning VBA. It took three days to study. Currently, I think it can basically meet most of the requirements. Even if there is something I don't know, I also know where to check the information and how to check the information. In order to prevent myself from forgetting quickly, I had a three-day discussion on VBA Excel programming. The content covered the object model, basic syntax, basic Excel table operations, events, and links to the ADODB database to read data and generate a perspective. I have never learned VB. It seems easy to write things, but I only want to help new users.

VBA Excel programming three-day discussion (1)

VBA Excel programming three-day discussion (2)

VBA Excel programming three-day discussion (3)

Preparations

VBA is a host language. Like Javascript in a browser, VBA exists in office applications. Therefore, please make sure that you have installed the office. I use office2007 when learning. The version differences may cause some problems, but the differences between office2003 and office2007 are not great.

Create an excelfile test.xls and press Alt + F11 to enter the VBA programming interface. Double-click thisworkbook on the left and enter:

Private sub workbook_open ()
Msgbox "Hello, world"
End sub

Save and exit the Excel file, and open the Excel file again. The pop-up message Hello, world is displayed. If you encounter a macro security warning, enable. This is the first VBA program, and workbook_open is the response function for event open (for more information about events ).

Of course, you can write the code in any sheet or run and debug the code locally. Double-click sheet1 and enter:

Sub test ()
Msgbox "Hello, world"
End sub

Move the cursor to the function name test and click the green arrow on the toolbar to test the function.

You can also add a control (such as a button) on the Excel worksheet and click the control to execute the function. Switch the menu to the developer tab. For example, click Insert to select the inserted control (if it is office2003, try-> toolbar-> control toolbox in the menu)

Click the button to draw a button anywhere in the worksheet, and click test, the function you just wrote, in the Selection box to save. Click the button to trigger the function.

Of course, you can also record macros, which is also a good way to learn VBA. We will not introduce it here.

OK. Now, we have learned how to define a process (function) and how to trigger it (run it through events, run debugging through VBA, and click). The preparation work ends here.

 

VBA Excel Object Model

Similar to the DOM model in Javascript, components and object models in Windows programming also have their own object models. there are many objects in the Excel model (You can Google them yourself), but these four or five objects are often used: Application-> workbooks-> worksheets-> range-> cells. the application is at the top, indicating that the Program (Excel) itself is running. A Workbook is an Excel file unit that corresponds to an xls file. worksheet is a worksheet. By default, a new workbook contains three worksheets. range indicates an area in the worksheet. For example, range ("A1: D10") indicates A1: in the area between D10, one range contains multiple cells, and one cell is a small grid in the worksheet. create a new file 1.xls and enter the content in the A1 cell of sheet1. The following program will clearly show the relationship between them. enter the following code in the code area of the just test.xls sheet1:

Sub test () <br/> 'reads the content of cells in this file. <br/> msgbox application. workbooks ("test.xls "). worksheets ("sheet1 "). range ("A1 "). value <br/> 'if it is the current application, the application can be omitted <br/> msgbox workbooks ("test.xls "). worksheets ("sheet1 "). range ("A1 "). value <br/> 'if it is the current workbooks, workbooks can be omitted <br/> msgbox worksheets ("sheet1 "). range ("A1 "). value <br/> 'if it is the current sheet, worksheet can be omitted <br/> msgbox range ("A1 "). value <br/> 'can also be used like this <br/> msgbox sheets ("sheet1 "). range ("A1 "). value <br/> 'activeworkbook represents the current active workbook <br/> ', which is more direct <br/> msgbox cells ). value <br/> 'can also rewrite the cell value <br/> cells ). value = "I'm learning VBA" <br/> msgbox activeworkbook. worksheets (1 ). range ("A1 "). value <br/> 'can also be used like this <br/> msgbox thisworkbook. worksheets (1 ). range ("A1 "). value <br/> 'can also read the content of an external xls file <br/> msgbox application. workbooks ("1.xls "). worksheets ("sheet1 "). range ("A1 "). value <br/> 'can also be applied by subscript. <br/> msgbox application. workbooks (2 ). worksheets (1 ). range ("A1 "). value <br/> end sub

Of course, each object has many attributes and methods to use. For example:

Sub test1 () <br/> msgbox application. name <br/> msgbox application. workbooks (2 ). name <br/> msgbox worksheets. count </P> <p> msgbox "there are" & CSTR (range ("A1: D10 "). cells. count) & "cells" </P> <p> sheets (2 ). select <br/> activesheet. cells (1, 1 ). value = "This is the first cell in sheet2" <br/> range ("A2 "). font. fontstyle = "bold" <br/> range ("A2 "). font. size = 13 <br/> range ("A3 "). borders. linestyle = xlcontinuous <br/> range ("A3 "). borders. weight = xlthin <br/> end sub <br/>

What should I do if I cannot remember such multiple methods and attributes? No, there is an automatic syntax prompt: Choose menu> Tools> options to check auto LIST members.

The object model can be acquired through recording macros, Automatic Code prompts, and Google channels.

 

Basic syntax

Data Types and definition Variables

The basic data types of VBA are byte Boolean integer long single double currency decimal date... the length accuracy is different.

The following shows the definition and usage of basic data types. Note that values of the date type are special. Include them with ##. Common types also have simpler definition methods:

Common type specifiers
% Integer
& Long
! Single
# Double
$ String
@ Currency

Sub test1 () <br/> dim I as integer, J as integer <br/> dim s as string <br/> I = 2 <br/> J = 3 <br/> msgbox I + j <br/> S =" the result is: "& (I + J) <br/> msgbox S <br/> dim D as date <br/> d = #12/12/2002 3:23:00 am # <br/> msgbox d <br/> dim K %, L & <br/> K = 5 <br/> L = 6 <br/> msgbox K + L <br/> dim M as currency <br/> M = 123.456 <br/> msgbox m <br/> end sub <br/>

If the defined variable is of the object type, such as worksheet, the value must be set.

Sub test1 () <br/> dim sh as worksheet <br/> set SH = activeworkbook. sheets (1) <br/> msgbox Sh. cells (1, 1 ). value <br/> end sub

Define and use Arrays:

Sub test6 () <br/> dim myarr (3) as integer <br/> myarr (1) = 3 <br/> myarr (2) = 4 <br/> myarr (3) = 5 <br/> msgbox myarr (1) + myarr (2) <br/> end sub

Process & Function

Sub subname (param1, param2 ...)...... end sub can define a process or function. The difference between a function and a sub is that a function has a return value. the rest are the same. function will not be discussed here.

One process can call other processes.

Sub test1 () <br/> msgbox application. name <br/> msgbox worksheets. count <br/> activesheet. cells (1, 1 ). value = "This is the first cell in sheet2" <br/> end sub </P> <p> sub Test2 () <br/> test1 <br/> 'inputbox is a system process <br/> dim name as string <br/> name = inputbox ("Please input your name :") <br/> msgbox "Your name is:" & name <br/> end sub

The process can contain parameters. There are two methods for passing parameters: Passing values and transferring references. Anyone with programming basics should understand the differences between the two methods.

'Upload Reference call <br/> sub Test2 () <br/> dim I as integer <br/> I = 123 <br/> test3 I <br/> msgbox I <br/> end sub </P> <p> sub test3 (byref I as integer) <br/> I = 321 <br/> msgbox I <br/> end sub </P> <p> 'value transfer call <br/> sub test4 () <br/> dim I as integer <br/> I = 123 <br/> test5 I <br/> msgbox I <br/> end sub <br/> sub test5 (byval I as integer) <br/> I = 321 <br/> msgbox I <br/> end sub <br/>

Process Control

If statement:

Sub testif () <br/> dim I & <br/> I = inputbox ("Please input your score:", "score", 60) <br/> If I> 90 then <br/> msgbox "your credit is a" <br/> elseif I> 80 then <br/> msgbox "your credit is B" <br/> elseif I> 70 then <br/> msgbox "your credit is C" <br/> elseif I> 60 then <br/> msgbox "your credit is D" <br/> else <br/> msgbox "your credit is E" <br/> end if <br/> end sub

Select case statement:

Sub testselectcase () <br/> sheets ("sheet3 "). select <br/> select case activesheet. cells (1, 1 ). value <br/> case is <60 <br/> msgbox "bad" <br/> case is <70 <br/> msgbox "so" <br/> case is <80 <br/> msgbox "good" <br/> case is <90 <br/> msgbox "very good" <br/> case else <br/> msgbox "Excellent "<br/> end select <br/> end sub <br/>

Loop Control:

Sub testfor1 () <br/> dim I &, total & <br/> total = 0 <br/> for I = 1 to 1000 Step 1 <br/> total = total + I <br/> next <br/> msgbox "Total: "& CSTR (total) <br/> total = 0 <br/> for I = 1000 to 1 step-2 <br/> total = total + I <br/> next <br/> msgbox" total: "& CSTR (total) <br/> end sub </P> <p> sub testfor2 () <br/> dim sh as worksheet <br/> for each SH in activeworkbook. worksheets <br/> msgbox Sh. name <br/> next <br/> end sub </P> <p> sub testdowhile () <br/> dim I &, total & <br/> I = 1000 <br/> total = 0 <br/> do while I> 0 <br/> total = total + I <br/> I = i-1 <br/> loop <br/> msgbox total <br/> end sub </P> <p> sub testdountil () <br/> dim I &, total & <br/> I = 1000 <br/> total = 0 <br/> DO <br/> total = total + I <br/> I = I-1 <br/> loop until I <0 <br/> msgbox total <br/> end sub <br/>

Finally, let's talk about the with syntax and line feed of VBA statements. the with syntax is used to reduce the input burden of the program. In the with range class, the default current object is the object specified by with. The following program with specifies the font, then the following. name. fontstyle and so on all refer to range ("A1 "). font members:

Public sub testwith () <br/> with range ("A1 "). font <br/>. name = "文" <br/>. fontstyle = "bold" <br/>. size = 18 <br/>. colorindex = 3 <br/>. underline = 2 <br/> end with <br/> end sub <br/>

The basic unit of a VBA program is a line. To wrap a line, you must use the symbol "_" to process it:

Sub test7 () <br/> dim s as string <br/> S = "12345" & _ <br/> "67890" <br/> msgbox S <br/> msgbox activeworkbook. worksheets (1) _ <br/>. range ("A1 "). value <br/> end sub

OK, it's too tired. I will write this article here.