Show Menu

ME 232 Cheat Sheet by

Basic VBA cheat sheet
vba

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

Operators (Syntax)

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

Operators (Prece­dence)

1
^
2
* OR / (divi­sion)
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 [newty­pe?]
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
CBo­ol­(v­alue)
...value to a integer
CIn­t­(va­lue)
...value to a double
CDb­l­(va­lue)
...value to a string
CSt­r­(va­lue)
Val function accepts a string as input and returns the numbers found in that string.

Math Functions

Absolute
Abs([numeric value])
Square root
Sqr­(­[nu­meric value])
Expone­ntial, e
Exp­(­[nu­meric value])
Natural log, ln
Log­(­[nu­meric value])
Is it a number (boolean)?
IsNumeric([numeric value])
Truncate to integer
Int­(­[nu­meric value])
(Num1 / Num2) remainder?
[Num2] Mod [Num2]
Round to a decimal place
Round([numeric value],[# of digits])
[Si­n­/­Cos­T­an­](x) for trigon­ometric functions, [AS­in­/­AC­os­/­AT­an­](x) for inverse trig functions.

String Functions

All upper case
UCa­se­([­string value] )
All lower case
LCa­se­([­string value] )
Length of string (integer)
Len­(­[string value] )
Filters a string to a double
Val­(­[string value])
Convert number to string
Str­(­[nu­meric 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
Msg­Box "­dia­log­ue" [& variable etc.]
Prompt user for input
Inp­utBox ("di­alo­gue­")
...a single cell A1
Ran­ge­("A­1").S­elect
...an active cell
Act­ive­Cel­l.Se­lect
...a contiguous range
Ran­ge­("A­1:G­5").S­elect
...offset and select
[Ac­tiv­eCe­ll­/­Ra­nge­(­#)].Of­fse­t(1­,0).Se­lect
...set a cell's value
[ActiveCell/Range(#)].Value = [varname]
Use Ran­ge to select specific cells or group of cells. Use Act­ive­Cell to select highli­ghted cell in excel.

Array Functions

Highest element number
UBound(ArrayName [, Dimension] )
Lowest element number
LBo­und­(­Arr­ayName [, 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

Download the ME 232 Cheat Sheet

2 Pages
//media.cheatography.com/storage/thumb/takshimaro_me-232.750.jpg

PDF (recommended)

Alternative Downloads

Share This Cheat Sheet!

 

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