1. Range / Cells / Columns / Rows
2. 绝对引用 $F$13 / 相对引用 F13
公式所在单元格的被复制到其他位置时，绝对引用不变
3. VLookup / NLookup / LLookup
4. =if(ISNA(C1:C17); "There are #N/A's in one of the cells"; "")
other similars: ISERROR(), ISERR(), ISBLANK(), ISEVEN(), ISODD(), ISLOGICAL(), ISNONTEXT(), ISNUMBER(), ISREF(), ISTEXT(), ISPMT()
5. 全角半角转换
StrConv("TestString", vbWide|vbNarrow|.., 1041)
6. Choose
=CHOOSE(1, "Tech", "on", "the", "Net") Result: "Tech"
=CHOOSE(5, "Tech", "on", "the", "Net") Result: #VALUE!
=CHOOSE(3.7, "Tech", "on", "the", "Net") Result: "the"
7. Sheets(0).Range("I7") = "=RC[-1] + 100"
RC[-1] -> R[0]C[-1] -> 当前行列变换为：行不变，列减1 -〉 H7
R[x]C[y] -> 行 + x， 列 + y
8. Sub Add(a as integer, b as integer)
End Sub
调用sub而不是function的格式为Add 3， 5 ，或者Call Add (3, 5)
9. 录制宏 -> Record Macro
将操作转换成代码，万事不求人
10. workbook保存
ActiveWorkbook.SaveAs Filename:=svFile, FileFormat:=xlExcel8
11. 超级隐藏sheet页
("Sheet2").Visible = xlSheetVeryHidden
Sheet2只能通过vba编辑器打开看到，直接通过双击打开Excel文件，sheet2是不可见的
12. 判断一个数组是否已经初始化
dim x() as integer
如果x()没有初始化，那么LBound(x) 的时候会有异常，
解决办法Len(Join(x)) > 0，x()被初始化
13. Speedup scripts
Option Explicit Public glb_origCalculationMode As Integer Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
End Sub Sub SpeedOff()
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub Sub FillSlow()
Dim c As Range, r As Range, startTime, EndTime
Set r = Range("A1:C1000")
r.ClearContents
startTime = Timer
For Each c In r
c.Select
c.Formula = "=Row()*Column()"
Next c
DoEvents
EndTime = Timer MsgBox "Total Time: " & EndTime - startTime
[A1].Select
End Sub Sub FillFast()
Dim c As Range, r As Range, startTime, EndTime
Set r = Range("A1:C1000")
r.ClearContents
startTime = Timer On Error GoTo ResetSpeed
SpeedOn For Each c In r
c.Select
c.Formula = "=Row()*Column()"
Next c
DoEvents
EndTime = Timer MsgBox "Total Time: " & EndTime - startTime
[A1].Select ResetSpeed:
SpeedOff
End Sub
14. Some functions
Combine2 | OFFSET | MATCH | INDEX | INDIRECT | ADDRESS | CELL