VBA for Excel Cheat Sheet

A Software › Excel cheat sheet in English by guslong

VBA for Excel (beginners level stuff)

151,949 views 43,575 downloads 13 Aug 2012 History

PRs

4.7 avg · 11 ratings

VBA data types

Type	Description
Variant	any data type
Integer	(2 bytes) integer
Long	(4 bytes) integer
Single	(4 bytes) floating point
Double	(8 bytes) floating point
String	non-numeric 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-contiguous range

Range("A1:G5,J10:J15").Select

offset syntax (move from A1 to A2)

Range("A1").Offset(1,0).Select

select down to first empty cell

Range(Selection, Selection.End(xlDown)).Select

set a cell's value

Range("A1").Value = i

deleting, moving, copying (VBA)

Delete entire row

Rows("2").Delete

Delete entire column

Columns("B").Delete

Copy / paste a range with destination

Range("A1:B6").Copy Destination:=Range("A1")

Clear a range

Range(“D:D").Clear

Delete a worksheet

Worksheets("Sheet2").Delete

Execute a formula calculation in a range

Range("A1:A3").Formula = 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 Worksheets("Sheet1")
.Rows
.Columns
...
End With

The "with" construction provides a shorthand way of accessing many properties and methods of the same object.

String functions

InStr ([start], "string", "what_to_find")	Returns position of string within a string
StrConv("string", vbProper\|Upper\|LowerCase)	converts string to proper\|upper\|lower case
Left ("string", x)	Return specified
Len ("string")	Return length of string
Trim ("string")	Trims string of leading and trailing spaces
Split("string", ",")	Split string by delimiter e.g. comma
Val("string")	Return numerical part only
StrComp("strA", "strB", vbTextCompare)	Compare two strings (0=true)

VBA information functions

IsArray

IsEmpty

IsError

IsMissing

IsNumeric

IsNull

IsObject

Badges

Great Cheat Sheet Useful Cheat Sheet Nice Cheat Sheet Good Cheat Sheet Appreciated Cheat Sheet Downloaded from Unusual Locations × 15

Comments (5)

jeffunsal 02:18 26 Jul 2022

Thank you very much. I would like to have another advanced vba cheatsheet also.

pakafloyd 16:33 5 Sep 2017

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

palolabo 20:54 11 Apr 2017

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

Matthew 13:47 29 Nov 2014

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

stella 08:57 14 Feb 2014

thank u for the cheat sheet!!