Show Menu
Cheatography

Basic VBA cheat sheet

VBA Data Types

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 (declared with quotes)
Object
any object reference
Date
a date
Boolean
True / False
Byte
0-255

Operators (Syntax)

Comparison
=, <> (not equal to), >, < , >= , <=
Logical (Boolean)
NOT (opposite), AND (if all true then returns true), OR (at least 1 true returns true)
Mathem­atical
+, -, *, / , \ (integer division), Mod (remai­nder), ^ (remember to put space)
String Concat­enation
&
The concat­enate operator eg. "­A" & "­B" becomes "­AB".

Operators (Prece­dence)

1
^
2
* OR / (division)
3
\ (integer division)
4
Mod
5
+ OR -

Declar­ations

Variables
Dim [varname] As [type]
Arrays
Dim [array­nam­e(i­ndex)] As [type]
Re-declare Array
ReDim [array­nam­e(n­ewi­ndex?)] As [newtype?]
ReDim and keep values
ReDim Preserve [arrayname(newindex?)] As [newtype?]
Option Explicit is used to require declar­ations of all variables. Option Base 1 makes the index of all arrays starts from 1. If declared array has no numeral index provided, then it is dynamic in size.
 

Data Functions (Conve­rsions)

...value to a boolean
CBool(value)
...value to a integer
CInt(value)
...value to a double
CDbl(value)
...value to a string
CStr(value)
Val function accepts a string as input and returns the numbers found in that string.

Math Functions

Absolute
Abs([numeric value])
Square root
Sqr([numeric value])
Expone­ntial, e
Exp([numeric value])
Natural log, ln
Log([numeric value])
Is it a number (boolean)?
IsNumeric([numeric value])
Truncate to integer
Int([numeric value])
(Num1 / Num2) remainder?
[Num2] Mod [Num2]
Round to a decimal place
Round([numeric value],[# of digits])
Sin/Cos/Tan for trigon­ometric functions, ASin/ACos/ATan for inverse trig functions.

String Functions

All upper case
UCase([string value] )
All lower case
LCase([string value] )
Length of string (integer)
Len([string value] )
Filters a string to a double
Val([string value])
Convert number to string
Str([numeric value])
Val function accepts a string as input and returns the numbers found in that string.

User Intera­ction and Cell Selection

A popup dialogue box
MsgBox "­dia­log­ue" [& variable etc.]
Prompt user for input
InputBox ("di­alo­gue­")
...a single cell A1
Range("A1­"­).S­elect
...an active cell
ActiveCell.Select
...a contiguous range
Range("A1­:G5­"­).S­elect
...offset and select
[ActiveCell/Range(#)].O­ffs­et(­1,0­).S­elect
...set a cell's value
[ActiveCell/Range(#)].Value = [varname]
Use Range to select specific cells or group of cells. Use ActiveCell to select highli­ghted cell in excel.

Array Functions

Highest element number
UBound(ArrayName [, Dimension] )
Lowest element number
LBound(ArrayName [, Dimension] )
Highest element number is the size of possible entries a array can hold.

Array Iteration

Function MinIntegerofArray(TheArray As Variant) As Integer
Dim i As Integer, placeholder As Integer
placeholder = 0

For i = 1 To UBound(TheArray)
    If TheArray(i) < TheArray(placeholder) Then
        placeholder = i
    End If
Next
'index of min value is at placeholder
MinIntegerofArray = TheArray(placeholder)
End Function

Error Handling with GoTo & Labels

...
tempstudentName = InputBox("Please enter student name (type exit to end): ")
'error check and force reentry of input
    If IsText(tempstudentName) = False Then
checker1:
        tempstudentName = InputBox("Please enter a valid student name [not blank and letter] (type exit to end): ")        
    End If
    
    If tempstudentName = "exit" Then
        End '(the program)    
    ElseIf IsText(tempstudentName) = False Then
        GoTo checker1 'label    
    Else    
        studentName = tempstudentName        
    End If
...
'check­er1:' is a label: labels only include the next line

IsText, Case Statem­ents, For loop

Function IsText(streng As Variant) As Boolean
    Dim i As Integer
    For i = 1 To Len(streng)
    'checks if the text follows the ACII numerials (a-z AND A-Z)
        
        Select Case Asc(Mid(streng, i, 1))
            Case 65 To 90, 97 To 122
                IsText = True
            Case Else
                IsText = False
                Exit For
        End Select
    Next
End Function
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          VBA for Excel Cheat Sheet
          Excel VBA Editor Keyboard Shortcuts