The notes---------Excel are actually very powerful

The first contact with the documentation software, called WPS, the main impression is the layout of the newspaper. A few female staff in a small room, sitting in a soft chair, to a big yellow box, typing, in fact, there are beautiful women. There is a place where all forget, can also learn computer, in fact, is to practice typing. Curiosity had let me open a document that was a text file with little commas. Should already be in Chinese, I was the endless words of the infinite frightened to close quickly, I do not believe that someone will see this kind of thing, I think is really a kind of torture.

Hey! ... Omit 100 million words

Remember a rainy evening, the small lights in the house seems to be lit, a woman in the typing, next to a few people, praise said the teacher typing really fast, a minute fast 200 or 100. In fact, I was vaguely aware that they just wanted to talk to her. Because she is also a beauty with makeup powder.

I soon did not go there, after all, there is no skilled wubi typing. The beauty of the newspaper and the beauty of the class, I did not have any desire to be close to the feeling, I did not even remember the appearance of both of them. But I do not know why, want to recall the point of editing software, but suddenly think of them.

Later look at these software, always top-down perspective, feel very small pediatric. After contact with Word, although also carefully studied the day, but soon skipped. I've even wondered why Excel has to make another access, not function duplication, for a long time. I think these tools and software are basically people.

Today exclamation of Excel is its macro. I think I have been at least half a century later than others. I didn't want to work hard at the beginning, eh! ... Omit 1 billion words. But the welcome is that I can start now. Moreover, I now, as long as I want, I can start at a very fast pace. I still have hope. As I went through the aftermath of the Cultural Revolution.

Excel should compile the script at all times, as long as a newline, it produces the code. The Excel function on MSDN also has a description, and I think it is actually a VB script, it should be possible to implement WMI and so on. Excel also has settings for macro security.

Some common code snippets are documented below:

• Like other environments, you can write specific response functions for sheet events such as Worksheet_change, and if you call AA again, the AA is not declared and is written directly in a place called a module.

If Target.Column = 1 and target.row > 1 then call AA

Sub AA ()
Dim arr, D as Object
Columns (3). ClearContents
Set d = CreateObject ("Scripting.Dictionary")
arr = range ("A1:a" & Range ("a65536"). End (Xlup). Row)
For i = 1 to UBound (arr)
D (Arr (I, 1)) = ""--------d is a horizontal array, the member can be understood as the first name + value
Next I
Range ("C1"). Resize (d.count, 1) = Application.WorksheetFunction.Transpose (D.keys)
Range ("C1:c" & Range ("c65536"). End (Xlup). Row). Sort Key1:=range ("C2"), order1:=xlascending, header:=xlguess------This is whether there is a title
End Sub

Slightly to the following, more suitable for my situation

Dim arr, D as Object
Dim str1 as String
Set d = CreateObject ("Scripting.Dictionary")
arr = range ("A1:a" & Range ("A1"). End (Xldown). Row)
For i = 1 to UBound (arr)
D (Arr (I, 1)) = ""
Next I
Range ("B1"). Resize (d.count, 1) = Application.WorksheetFunction.Transpose (D.keys)

• In addition, when pasting, there are

. PasteSpecial Paste:=xlpasteformats,-----There's xlpastevalue,xlpasteformula or something like that.

• In addition there are match, lookup, VLOOKUP, index and other such in-table functions

• In the case of color sorting, ctl-h can be replaced with a number, sorted by numbers, and Get.cell (63,A1), where 63 is the index number of the fill color (the function can return 66 different properties). You can associate a table function with a script in the insert name, and it should be limited to functions that can be completed within a single line. This is called the macro table function, complete the simple VBA, in fact, this way cumbersome and function is too weak, seems to have been limbo.

In fact, it is best to write the script directly, as follows: Write in the module

Function Getcellcolor (Thecell as Range)
Getcellcolor = TheCell.Interior.ColorIndex
End Function

Functiongetfontcolor (Thecell as Range)

Getfontcolor = TheCell.Font.ColorIndex
End Function
This function can then be used as a function in the table. For example, in the C1 input =getcellcolor (B1), if the cell font color =getfontcolor (B1)

• Pay a piece of code, nothing to see, it first on the class sort, the same class again by the grade, then the number of classes. Excel's subtotals seem to have similar functionality.

Sub Lqxs ()

Dim Arr, Myr&, I&, Arr1 (), KS, JS, J&, d

Set d = CreateObject ("Scripting.Dictionary")

[E2]. Resize (100, 1). ClearContents

Randomize

Myr = Cells (Rows.Count, 1). End (Xlup). Row

Do

AA = Int (Rnd * 100) + 1

If not d.exists (AA) Then

D (aa) = ""

n = n + 1

Cells (n + 1, 5) = AA

End If

Loop while n < Myr-1

[A1]. Currentregion.sort Key1:=range ("B2"), Order1:=xlascending, Key2:=range ("E2"), Order2:=xlascending, Header:=xlYes

ARR = [A1]. CurrentRegion

For i = 2 to UBound (ARR)

If arr (i, 2) <> Arr (i-1, 2) Then

R = R + 1

ReDim Preserve Arr1 (1 to R)

ARR1 (r) = i

End If

Next

For i = 1 to R

If I <> R then

JS = Arr1 (i + 1)-1

Else

JS = UBound (ARR)

End If

KS = ARR1 (i): n = 0

For j = ks to JS

n = n + 1

Cells (j, 1) = n

Next

Next

End Sub

Notes---------Excel is also very powerful