VBA入门

http://blog.csdn.net/sunxing007/article/details/5658384

1. alt+F11 vba编程界面

2. Excel cell color and color index map:

#00FF00 4

#CCFFCC 35
FFFF00 6
FF0000 3
C0C0C0 15
http://dmcritchie.mvps.org/excel/colors.htm

3. when copy sheet to another workbook, the color of some cell is lost. You
should first copy the custom color from source workbook to target workbook;
attention: when you change the color, but the color index is not change;
Such as: normally color index is map to #008000; when you changed the stardard
color to #CCFFCC, but the color index
is not change.
http://www.excelbanter.com/showthread.php?t=102323

‘ copy color
targetWorkbook.Colors = targetWorkbook1.Colors

4. select and open excel file
Dim NewFN As String
Dim targetWorkbook1 As Workbook
NewFN = Application.GetOpenFilename(FileFilter:=”Excel Files (.xls), .xls”,
title:=”Please select first file”)
If Len(NewFN) = 0 Then
‘ They pressed Cancel
MsgBox “Stopping because you did not select a file”
Exit Sub
Else
Set targetWorkbook1 = Workbooks.Open(Filename:=NewFN, ReadOnly:=False)
End If

5. the last sheet of excel can not be delete
Sub RemoveAllSheet(ByRef wb As Workbook)
Application.DisplayAlerts = False

Dim sh As Worksheet
For Each sh In wb.Worksheets
If sh.Name <> “Sheet1” Then sh.Delete
Next

Application.DisplayAlerts = True
End Sub

6. sheet copy
Sub CopyFirtstSheetToTarget(ByRef souce As Workbook, ByRef Target As Workbook)
Application.DisplayAlerts = False

souce.Sheets(1).Copy after:=Target.Sheets(“Sheet1”)
‘ rename
Dim sheetName As String
sheetName = ActiveSheet.Name

ActiveSheet.Name = souce.Name & “_” & sheetName
Application.DisplayAlerts = True
End Sub

7. get table range
Sub GetTableRange(ByRef lastTableIndex As Integer)
Last = ActiveSheet.Underline(Rows.count, “A”).End(xlUp).Row
Dim myInterior As Interior
For i = TABLE_START_INDEX To Last
Set myInterior = ActiveSheet.Underline(i, “B”).Interior
If (myInterior.ColorIndex = 5) Then
lastTableIndex = i
End If
Next i
End Sub

8. delete all content
Sub DeleteAllContent()
ActiveSheet.Underline.Clear
End Sub