Books

Excel 2007 VBA Programmer’s Reference

Basic

sheets = worksheet + chart function: can be used in cell IIF = IF THEN ELSE

' Force explicit variable declaration.
Option Explicit On

'turns off all error handling for subsequent statements
On Error Resume Next

Dim owner_index As Integer 'default = 0

String & Regex

Sub Split_Cell_String()
    With ActiveSheet
        For r = 2 To 37
            s = .Range("B" & r).Value

            Set objRegExp_1 = CreateObject("vbscript.regexp")
            objRegExp_1.Global = True
            objRegExp_1.IgnoreCase = True
            objRegExp_1.Pattern = "[\d:]+"

            Set regExp_Matches = objRegExp_1.Execute(s)
            For Each m In regExp_Matches
                .Cells(r, 6).Value = m
                .Cells(r, 7).Value = objRegExp_1.Replace(s, "")
            Next
        Next
    End With
End Sub

MsgBox

MsgBox(prompt[, buttons] [, title] [, helpfile, context])
MsgBox prompt:="xxx" , title:=... , Msgbox Buttons:=vbOKOnly/vbOKCancel/vbAbortRetryIgnore.. (Note the COMMA)
Answer = MsgBox(Prompt:=”Delete this record?”, Buttons:=vbYesNo + vbQuestion) (Note the PARENTHESES)
If Answer = vbYes/vbNo/vb...
UserName = InputBox(Prompt:=”Please enter your name”)

Dim & ReDim

Dim advantage: preservation of capitalization.
ReDim will re-initialize the array and destroy any data in it, unless you use the Preserve keyword. It is necessary to declare sht as the generic Object type if you want to allow it to refer to different sheet types.

Sheets

There is a Sheets collection in the Excel object model, but there is no Sheet object.

With Workbooks.Add
    With .Worksheets.Add(After:=.Sheets(.Sheets.Count))
        .Name = “January”
        .Range(“A1”).Value = “Sales Data”
    End With
    .SaveAs Filename:=”JanSales.xlsx”
End With

icount = Worksheets.Count
Worksheets(1).Copy After:=Worksheets(icount) // icount won't ++

Range & Cell

[A1] = 10
Range(“B3:E10”).Select
Range(“C5:Z100”).Activate ' C5 will be actived since it's within B3:E10
ActiveCell.Offset(2, 0).EntireRow.Select
Range(“A1”).End(xlDown)
Range(“B3”, Range(“B3”).End(xlToRight).End(xlDown)).Select
Range(“A1:B5,C6:D10,E11:F15”).Rows.Count ' return rows number of A1:B5

ColorIndex Property


Rows.Interior.ColorIndex = xlColorIndexNone

Test cell empty

The IsEmpty function is the best way to test that a cell is empty.
If you use If Cells(row,col) = “”, the test will be true for a formula that calculates a zero-length string.

MsgBox Evaluate(“=ISBLANK(A1)”)
MsgBox [ISBLANK(A1)]

Find string & delete row

column.find

Sub DeleteRows2()
    Dim rngFoundCell As Range
    ‘Freeze screen
    Application.ScreenUpdating = False

    'Application.ScreenUpdating = False + workbook.open+close : open workbook in background
    ‘Find a cell containing Mangoes
    Set rngFoundCell = Range(“C:C”).Find(What:=”Mangoes”)

    ‘Keep looping until no more cells found
    Do Until rngFoundCell Is Nothing

    ‘Delete found cell row
    rngFoundCell.EntireRow.Delete

    ‘Find next
    Set rngFoundCell = Range(“C:C”).FindNext
    Loop
End Sub

Array

For array_member = LBound(array) To UBound(array)
For each array_member in array
keywords_auto_route = Array("KMS, PK ", "GPS", "logo([^n]|$)")

Filter

Sheet.FilterMode = Data has been filtered?
.Range("A1").AutoFilterMode = false
.Range("A1").AutoFilter

Hyperlinks

With ActiveSheet
    .Hyperlinks.Add Anchor:=.Range("G17"), _
    Address:="", _
    SubAddress:="other_sheet!cell", _
    TextToDisplay:="TextToDisplay"
End With

Time

format(date, "yyyy/mm/dd")  'TimeZone: http://www.cpearson.com/Zips/TimeZone.ZIP

Dim TInfo As CTime
Dim D As Double

If TInfo Is Nothing Then
    Set TInfo = New CTime
Else
    TInfo.Refresh
End If

today = Format(TInfo.GMT + 8 / 24, "yyyy/mm/dd") 'force to be +8:00