VBA for Excel Cheat Sheet by guslong
Comments
| 
Add a Comment
| 
PDF Download
| 
Find:
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 |
|
Favourited by 2 Members:
Comments
No comments yet. Add yours below!
Add a Comment
You are posting a reply. Cancel Reply.
Contents
VBA for Excel (beginners level stuff)
Cheatographer
More by guslong
Cheat Sheet Stats
Tags
Related (shares tags with):
Thumbnail