Scribd
Upload
6 views4 pages

VBA Code Reference Guide

The VBA Programming Reference Guide provides essential code snippets and automation examples for Microsoft Excel, covering the basics of VBA, working with cells and ranges, control structures, and useful automation snippets. Key topics include writing to and formatting cells, using control structures like If...Then...Else and loops, and automating tasks such as finding the last row and protecting sheets. The guide emphasizes the importance of declaring variables with 'Option Explicit' to prevent errors.

Uploaded by

rahuljatain20
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views4 pages

VBA Code Reference Guide

The VBA Programming Reference Guide provides essential code snippets and automation examples for Microsoft Excel, covering the basics of VBA, working with cells and ranges, control structures, and useful automation snippets. Key topics include writing to and formatting cells, using control structures like If...Then...Else and loops, and automating tasks such as finding the last row and protecting sheets. The guide emphasizes the importance of declaring variables with 'Option Explicit' to prevent errors.

Uploaded by

rahuljatain20
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

VBA Programming Reference Guide

Essential Code Snippets and Automation Examples for Microsoft Excel

1. Introduction to VBA Basics

Visual Basic for Applications (VBA) is the programming language of Excel. It allows you to
automate repetitive tasks and create custom functions.

The Structure of a Procedure

Sub MyFirstMacro()
' This is a comment
MsgBox "Hello, World!"
End Sub

2. Working with Cells and Ranges

Interacting with cells is the core of Excel automation.

Writing to a Cell

Sub WriteData()
' Method 1: Range
Range("A1").Value = "Project Name"

' Method 2: Cells (Row, Column)


Cells(2, 1).Value = "Automation Task"
End Sub
Formatting Cells

Sub FormatCells()
With Range("A1:C1")
.[Link] = True
.[Link] = vbWhite
.[Link] = RGB(43, 87, 154)
.HorizontalAlignment = xlCenter
End With
End Sub
3. Control Structures

If...Then...Else Statement

Sub CheckValue()
Dim score As Integer
score = Range("B2").Value

If score >= 60 Then


Range("C2").Value = "Pass"
Else
Range("C2").Value = "Fail"
End If
End Sub

Loops: For Each

The "For Each" loop is the most efficient way to iterate through a range of cells.

Sub HighlightNegativeNumbers()
Dim cell As Range
For Each cell In Range("A1:A10")
If [Link] < 0 Then
[Link] = vbRed
End If
Next cell
End Sub

4. Useful Automation Snippets

Finding the Last Row

Crucial for dynamic datasets where the number of rows changes.

Sub FindLastRow()
Dim lastRow As Long
lastRow = Cells([Link], 1).End(xlUp).Row
MsgBox "The last row with data in Column A is " & lastRow
End Sub

Protecting All Sheets

Sub ProtectAllSheets()
Dim ws As Worksheet
For Each ws In [Link]
[Link] Password:="1234"
Next ws
End Sub

Pro Tip: Always include Option Explicit at the very top of your module. This forces
you to declare all variables, which prevents errors caused by typos.

You might also like