Show Menu

VBA for Excel Cheat Sheet by

VBA for Excel (beginners level stuff)
excel     basic     visual     vba     office

VBA data types

Type
Desc­rip­tion
Variant
any data type
Integer
(2 bytes) integer
Long
(4 bytes) integer
Single
(4 bytes) floating point
Double
(8 bytes) floating point
String
non-nu­meric data
Object
any object reference
Date
a date
Boolean
True / False
Byte
1-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]

"­WIT­H" 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 inform­ation functions

IsArray
IsEmpty
IsError
IsMissing
IsNumeric
IsNull
IsObject

Download the VBA for Excel Cheat Sheet

1 Page
//media.cheatography.com/storage/thumb/guslong_vba-for-excel.750.jpg

PDF (recommended)

Alternative Downloads

Share This Cheat Sheet!

 
guslongguslong

Metadata

  • Languages: English
  • Published: 13th August, 2012
  • Rated: 5 stars based on 10 ratings

Favourited By

wizzdm Drumkill64 outlaw16 pakafloyd young.dev2016 vinceswann dockawash hqzxjczx mimdal route666 shrupy
and 6 more ...

Comments

stella stella, 08:57 14 Feb 14

thank u for the cheat sheet!!

Matthew Matthew, 13:47 29 Nov 14

Very helpful. Thanks. But a small error: the range of Byte is 0..255, not 1..255.

palolabo palolabo, 20:54 11 Apr 17

When I click on link "PDF (1 page)"
(https://www.cheatography.com/guslong/cheat-sheets/vba-for-excel/pdf/) nothing gets downloaded :(

pakafloyd pakafloyd, 16:33 5 Sep 17

Palolabo, try on "PDF (black and white)". This is working.

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          VB6 Cheat Sheet
          Documents professionnels Cheat Sheet
          Excel 2013 Keyboard Shortcuts Keyboard Shortcuts

          More Cheat Sheets by guslong

          Essential MySQL Cheat Sheet
          PHP Syntax for beginners Cheat Sheet