Show Menu

VBA for Excel Cheat Sheet by guslong

VBA for Excel (beginners level stuff)

VBA data types

TypeDesc­rip­tion
Variantany data type
Integer(2 bytes) integer
Long(4 bytes) integer
Single(4 bytes) floating point
Double(8 bytes) floating point
Stringnon-nu­meric data
Objectany object reference
Datea date
BooleanTrue / False
Byte1-255

Cell selection

select a single cell
Range(­"­A1").Select
select a contiguous range
Range(­"­A1:­G5").Select
select a non-co­nti­guous range
Range(­"­A1:­G5,­J10­:J1­5").S­elect
offset syntax (move from A1 to A2)
Range(­"­A1").Of­fse­t(1­,0).Select
select down to first empty cell
Range(­Sel­ection, Select­ion.En­d(x­lDo­wn)­).S­elect
set a cell's value
Range(­"­A1").Value = i

deleting, moving, copying (VBA)

Delete entire row
Rows("2­"­).D­elete
Delete entire column
Column­s("B­"­).D­elete
Copy / paste a range with destin­ation
Range(­"­A1:­B6").Copy Destin­ati­on:­=Ra­nge­("A1­")
Clear a range
Range(­“D:­D").C­lear
Delete a worksheet
Worksh­eet­s("S­hee­t2").Delete
Execute a formula calcul­ation in a range
Range(­"­A1:­A3").Fo­rmula = 2*2/6

Use RANGE to select single cells.

 

Iterate through a selection

Dim cell As Range
For Each cell In Selection
...
Next cell

Control structures VBA

Do ... Until Loop

Do [Until condition]
...
Loop

Do ... While Loop
Do [While condition]
...
Loop

For...Next Loop
For counter = start To end [Step s]
...
Next [counter]

"WITH" syntax

With Worksh­eet­s("S­hee­t1")
.Rows
.Columns
...
End With

The "­wit­h" constr­uction provides a shorthand way of accessing many properties and methods of the same object.

 

String functions

InStr ([start], "­str­ing­", "­wha­t_t­o_f­ind­")Returns position of string within a string
StrCon­v("s­tri­ng", vbProp­er|­Upp­er|­Low­erCase)converts string to proper­|up­per­|lower case
Left ("st­rin­g", x)Return specified
Len ("st­rin­g")Return length of string
Trim ("st­rin­g")Trims string of leading and trailing spaces
Split(­"­str­ing­", "­,")Split string by delimiter e.g. comma
Val("st­rin­g")Return numerical part only
StrCom­p("s­trA­", "­str­B", vbText­Com­pare)Compare two strings (0=true)

VBA information functions

IsArray
IsEmpty
IsError
IsMissing
IsNumeric
IsNull
IsObject
 

Share This Cheat Sheet!

Favourited by 4 Members:

Akira vbgenie akipta The_Frail

Comments

stella stella, 08:57 14 Feb 14

thank u for the cheat sheet!!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.