Excel 2007 VBA Programmer’s Reference


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, "")
    End With
End Sub


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.


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(“C5:Z100”).Activate ' C5 will be actived since it's within B3:E10
ActiveCell.Offset(2, 0).EntireRow.Select
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


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

    'Application.ScreenUpdating = False + : 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

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


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


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


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


format(date, "yyyy/mm/dd")  'TimeZone:

Dim TInfo As CTime
Dim D As Double

If TInfo Is Nothing Then
    Set TInfo = New CTime
End If

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