Cheatography
https://cheatography.com
VBA for Excel (beginners level stuff)
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 |
|
Created By
Metadata
Favourited By
and 8 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.
Thank you very much. I would like to have another advanced vba cheatsheet also.
Add a Comment
Related Cheat Sheets
More Cheat Sheets by guslong