VBA学习

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