Excel VBA Simple to use-data loss processing

VBA (Visual Basic for applications) is a macro language of VB. The functionality used to extend the application. Specifically Microsoft Office software.

Reprint please specify the original address: http://blog.csdn.net/ts_dchs/article/details/50318345

1 Use Reason

When processing the sensor data file. Some nodes have a serious problem of packet loss. This will result in a cumulative error of time.

Therefore, it is necessary to fill in the following sensor data according to the package number in the appropriate location, that is, the loss of packets out of the empty line.

This way of manual control is very inconvenient, so think of the code to solve.

VBA provides this opportunity. I

After the introduction of the content to be useful, not to do a specific introduction.

See network or related books.

2 Basic Preparation

The required Excel file is a file that can be loaded into a macro.

is different from normal Excel files.
Normal: xlsx can load macros: xlsm
Be able to have a file that can load macros and copy the past after processing the data.
Suppose you want to perform a macro to open the enable macro in the Trust Center of the option.

This setting is very easy for the machine to recruit. So you'd better turn it off when you're done.

3 Opening the programming environment

Right-sheet The tab and "View Code" to open the BVA edit page.
The code that is currently being edited is the corresponding sheet.

High-speed start: Two drop-down menus are selected for worksheet and BeforeDoubleClick to allow you to execute the code before double-clicking in a table.
Get a framework like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)...End Sub

Other options Baidu

4 Basic grammar

(The general initial capitalization is the keyword, all lowercase is the number of references)
Gets the value of a cell, and a value assigned to it

Sheet4.Cells(21) = Sheet4.Cells(11)

There is no semicolon at the end.

After saving, double-click in Excel file corresponding to sheet to see the result.

Variable Declaration
Declare a full Type I

DimAsInteger

Debugging related
F5 executes a script that is typically tested in a custom macro and placed in a macro that can be related to excel operations. For example, now in your own macro test and put into the double-click operation.

Print the statement. With the Debug object.

View the output in the "form Now". The immediate form opens in the view.

DimAsInteger510‘ 单引號作为行凝视‘ 打印多个元素，多个元素用分号隔开，VBA可能会自己加上。"x""=""  ";‘ 打印空行Debug.Print

Conditional statements

IfThen    [statements]ElseIfThen    [statements]Else    [statements]EndIf

For next syntax

ForToend [Stepstep]    [statements]    [ExitFor‘ like break    [statements]Next [counter]

eg. get a custom parameter element and fill in a column serial number:
(in Sheet (2,1) (2,2) This defines the starting value 1, the terminating value 20, (2,3) (2,4) respectively define the data starting line number 5, to place the result of the column number 7. ）

Sub copytest () Dim Tsart, tend, tcol, trow asIntegerTstart = Sheet4.Cells(2,1) tend = Sheet4.Cells(2,2) Trow = Sheet4.Cells(2,3) Tcol = Sheet4.Cells(2,4)Debug.Print    Debug.Print "Start index:"; TstartDebug.Print "End index:"; TendDebug.Print "Result start: ("; Trow;", "; Tcol;")"Dim I asInteger     fori = Tstart to tend Step1Sheet4.Cells(Trow + i-1, tcol) = iNextIEndSub

can also be done for a region:

ForInRange("a1:c5")  1  c.Value = iNext

This fills a row first, and then populates the next row.

* While Wend *

While condition(s)   1]   2]   ...   [statements n]Wend

51 instances

Use VBA to fill out a series of sensor data sequentially into a new location to voluntarily empty out the lost packets.
For example, if the packet order of the received data is 101,102,104,105, the new location is written with 103 rows, followed by empty. (in Sheet (2,1) (2,2) This defines the starting value. The terminating value. (2,3) (2,4) Define the data starting line number separately. The column number to place the result on.

Raw data occupies column 1-5. Start on line 5th.

The new data starts at the location defined in the table and adds a sequential serial number to the first column, for example, the following:

SubCopytest ()DimTsart, tend, tcol, trow as Integer Tstart = Sheet4.cells (2,1) tend = sheet4.cells (2,2) Trow = Sheet4.cells (2,3) Tcol = Sheet4.cells (2,4) Debug.Print Debug.Print"Start index:"; Tstart Debug.Print"End index:"; Tend Debug.Print"Result start: ("; Trow;", "; Tcol;")"    DimI, I2, j as Integer i =0I2 =0j =0     whileI < (Tend-tstart +1) Sheet4.cells (Trow + i, tcol) = i + TstartIfSheet4.cells (Trow + I2,1) = i + Tstart Then             forj =1  to 5 Step 1Sheet4.cells (Trow + i, Tcol + j) = Sheet4.cells (Trow + I2, j)NextJ I2 = I2 +1            ' Debug.Print i2        End Ifi = i +1    WendDebug.Print I2;"Total Data number:"; I2End Sub

Notification
Source: The data originates from the network. Personal learning is limited. If there are errors and deficiencies please advise, I will promptly correct. Terrence Zhou.
Http://blog.csdn.net/ts_dchs
Reprint please specify the original site

Excel VBA Simple to use-data loss processing