VBA for Excel Cheat Sheet by guslong
VBA for Excel (beginners level stuff)
excel basic visual vba office
VBA data typesType | 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 selectionselect 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 selectionDim cell As Range
For Each cell In Selection
...
Next cell |
Control structures VBADo ... 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" syntaxWith Worksheets("Sheet1")
.Rows
.Columns
...
End With |
The "with" construction provides a shorthand way of accessing many properties and methods of the same object. | | String functionsInStr ([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 functionsIsArray | IsEmpty | IsError | IsMissing | IsNumeric | IsNull | IsObject |
|
Download the VBA for Excel Cheat Sheet
1 Page
http://www.cheatography.com/guslong/cheat-sheets/vba-for-excel/
//media.cheatography.com/storage/thumb/guslong_vba-for-excel.750.jpg
PDF (recommended)
Alternative Downloads
Your Download Will Begin Automatically in 5 Seconds.
Close
Cheatographer
Metadata
Favourited By
and 7 more ...
Comments
thank u for the cheat sheet!!
Very helpful. Thanks. But a small error: the range of Byte is 0..255, not 1..255.
When I click on link "PDF (1 page)"
(https://www.cheatography.com/guslong/cheat-sheets/vba-for-excel/pdf/) nothing gets downloaded :(
Palolabo, try on "PDF (black and white)". This is working.
Add a Comment
Related Cheat Sheets
More Cheat Sheets by guslong