VBA

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