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