All-in-One VBA Cheat Sheet

Len(string) - Returns the length of a string.
Dim length As Integer
length = Len("Hello")  ' Result: 5

Mid(string, start, length) - Returns a substring.
Dim subStr As String
subStr = Mid("Hello", 2, 3)  ' Result: "ell"

Left(string, length) - Returns leftmost characters.
Dim leftStr As String
leftStr = Left("Hello", 2)  ' Result: "He"

Right(string, length) - Returns rightmost characters.
Dim rightStr As String
rightStr = Right("Hello", 3)  ' Result: "llo"

UCase(string) - Converts to uppercase.
Dim upperStr As String
upperStr = UCase("hello")  ' Result: "HELLO"

LCase(string) - Converts to lowercase.
Dim lowerStr As String
lowerStr = LCase("HELLO")  ' Result: "hello"

Trim(string) - Removes leading/trailing spaces.
Dim trimmedStr As String
trimmedStr = Trim("  Hello  ")  ' Result: "Hello"

Replace(string, find, replace) - Replaces occurrences.
Dim newStr As String
newStr = Replace("Hello World", "World", "VBA")  ' Result: "Hello VBA"

Abs(number) - Returns the absolute value.
Dim absValue As Double
absValue = Abs(-10)  ' Result: 10

Sqr(number) - Returns the square root.
Dim squareRoot As Double
squareRoot = Sqr(16)  ' Result: 4

Rnd - Generates a random number between 0 and 1.
Dim randomNum As Double
randomNum = Rnd  ' Random value between 0 and 1

Int(number) - Returns the integer part.
Dim intValue As Integer
intValue = Int(5.7)  ' Result: 5

Now - Returns the current date and time.
Dim currentDateTime As Date
currentDateTime = Now

Date - Returns the current date.
Dim currentDate As Date
currentDate = Date

Time - Returns the current time.
Dim currentTime As Date
currentTime = Time

DateAdd(interval, number, date) - Adds time to a date.
Dim newDate As Date
newDate = DateAdd("d", 5, Now)  ' Adds 5 days

If...Then...Else - Executes code based on a condition.
If x > 10 Then
    MsgBox "Greater than 10"
Else
    MsgBox "10 or less"
End If

Select Case - A more readable way to handle multiple conditions.
Select Case dayOfWeek
    Case 1
        MsgBox "Sunday"
    Case 2
        MsgBox "Monday"
    ' Add additional cases as needed
End Select

Array(elements) - Creates an array.
Dim myArray As Variant
myArray = Array("A", "B", "C")

UBound(array) - Returns the upper bound of an array.
Dim upperBound As Integer
upperBound = UBound(myArray)  ' Result: 2

LBound(array) - Returns the lower bound of an array.
Dim lowerBound As Integer
lowerBound = LBound(myArray)  ' Result: 0

Columns.AutoFit - Automatically adjusts column width.
Columns.AutoFit

Rows.AutoFit - Automatically adjusts row height.
Rows.AutoFit

Range.Font.Size - Changes font size.
Range("A1:A10").Font.Size = 12  ' Sets font size to 12

Range.Font.Bold - Makes the font bold.
Range("B1:B10").Font.Bold = True

Range.Orientation - Adjusts text orientation.
Range("C1").Orientation = 90  ' Rotates text to vertical

Charts.Add - Creates a new chart.
Dim chartObj As ChartObject
Set chartObj = Charts.Add

Chart.SetSourceData - Sets the data source.
chartObj.Chart.SetSourceData Source:=Range("A1:B10")

Chart.ChartType - Changes the chart type.
chartObj.Chart.ChartType = xlColumnClustered

Chart.HasTitle - Enables/disables chart title.
chartObj.Chart.HasTitle = True
chartObj.Chart.ChartTitle.Text = "Sales Data"

Range.Value - Adds a single value.
Range("A1").Value = "Hello"

Range.Formula - Adds a formula.
Range("B1").Formula = "=SUM(A1:A10)"

Range.Value (Array) - Adds multiple values.
Dim data As Variant
data = Array(1, 2, 3, 4, 5)
Range("A1:A5").Value = Application.Transpose(data)

Range.Copy and Range.PasteSpecial - Copies data.
Range("A1:A10").Copy
Range("B1").PasteSpecial Paste:=xlPasteValues

Tips for Good Practice

• Comment Your Code: Use comments (') to explain functionality.
• Use Descriptive Variable Names: Improves readability and reduces errors.
• Keep Your Code Organized: Use indentation and spacing for clarity.
• Test Your Code Regularly: Test small sections to ensure accuracy before scaling.