B a s ic E x p la n a ti o n

B a s ic C o d e & F o r m u la s

B a s ic To p ic s

A d v a n c e d To p ic s

A d va n ce d C o n ce p ts

W e b S c r a p in g
 Basic Explanation
1. What is Option Explicit?
2. Procedures
3. Functions
4. Dim(Define in Memory)
5. Class
6. Object
7. Set and New
8. Array
9. API
10. VBS
11. INSTR
12. Loop and Types of Loops
13. Types of Error
14. What is On Error and Types of On Error
15. Debug.Print
16. Immediate Window
17. Breakpoint
18. Watch Window
19. Immediate Window vs. Watch Window
20. Call Statement
21. DoEvents
22. With...End With Statement
23. Exit Statement
24. Select Case Statement
25. InputBox and MsgBox Functions
26. CreateObject and GetObject Functions
27. String Functions (Left, Right, Mid, Len, InStr)
28. Syntax
29. Variable
30. Range
31. Location
 Basic Code & Formulas Basic Topics
1. Auto Fit Columns
2. Auto Fit Rows
3. Formatting
Coloring
Font
Interior
1. LOOKUP Bold Font
2. VLOOKUP LineStyle
3. HLOOKUP 4. Apply and Remove Text Wrap
4. AND 5. Merge and UnMerge Cell
5. OR 6. Remove Duplicate
6. COUNTIF 7. Sorting (Ascending, Descending)
7. COUNTIFS 8. Text To Column
8. COUNT 9. Types Of Error
9. COUNTA Compiler error
10. SUMIF Runtime error
11. SUMIFS Syntax error
12. IF Logical error
13. Nested IF
14. IFERROR
15. ISERROR
16. ISBLANK
17. INDEX
18. MATCH
19. INDEX & MATCH
20. RIGHT
21. LEFT
22. MID
23. SEARCH
24. SUBSTITUTE
25. CONCATENATE
26. TEXT
27. VALUE
28. CLEAR
29. CHAR
30. ABS
31. LEN
32. CHOOSE
1. VLOOKUP with IF
2. RIGHT with LEFT and LEN
3. CONCATENATE with IF and ISERROR
4. INDEX & MATCH with IFERROR
5. SUMIF with AND
6. COUNTIF with OR
 Advanced Topics Advanced Concepts
1. Auto filter 1. Create Folder with FSO
Apply Autofilter 1. Delete Folder
Remove Autofilter 2. Folder Copy
2. Sheets 3. Folder Exist
Add 4. If Not Exist Then Create
Delete 5. TextFile Create
Copy 6. FSO TextFile Delete
Move 7. TextFile Open
Save As PDF 2. Functions
Insert Multiple Sheets Operators with functions
Protect all Worksheets FSO with functions
UnProtect Worksheet
Delete_Blank_Sheet 3. Others
3. IF Condition 1. Pivot Table
IF…EndIF 2. Pivot Charts
IF…Else…EndIF 3. Charts
Nested if 4. Sparklines
4. Loops 5. Slicer
For Next 6. Conditional Formatting
For Each Next 7. VBS
Do While 8. User Forms
Do Until 9. Class module
5. Error Handling 10. Protect Sheet
On error goto resume next 11. Protect Workbook
On error goto label 4. Consolidation
On error goto 0 Worksheet To Worksheet
On error goto -1 Worksheet to Another Workbook/Worksheet
goto jump Workbook To Workbook
 Web Scraping
Data extraction 1. Data Extraction
Table reading 2. Table Reading
Alert 3. Handling Alerts
Frames 4. Handling Frames
Windows 5. Handling Windows
Dropdowns 6. Handling Dropdowns
Inputbox 7. Handling Input Boxes
File downloading (Excel, PDF) 8. File Downloading (Excel, PDF)
Screenshot 9. Taking Screenshots
Convert txt to Excel 10. Converting Text to Excel
Convert PDF to Excel 11. Converting PDF to Excel
Convert Word to Excel 12. Converting Word to Excel
API 13. Using APIs
VBS 14. Using VBS (VBScript)
Wait until element present 15. Waiting Until an Element is Present
1. Basic Explanation 2. Basic Code & Fo
1. What is Option Explicit? 1. Declaring Workbook
Worksheet
2. Procedures
2. Finding Last Row and
Column
3. Functions
3. Storing Cell Value i
4. Dim(Define in Memory) Variable
5. Class 4. Working with Rang
6. Object 5. Assigning Values to C
7. Set and New
1. LOOKUP
8. Array
2. VLOOKUP
9. API
3. HLOOKUP
10. VBS
4. AND
11. INSTR
5. OR
12. Loop and Types of
Loops
6. COUNTIF
13. Types of Error
7. COUNTIFS
14. What is On Error
and Types of On Error 8. COUNT
15. Debug.Print
9. COUNTA
16. Immediate Window
10. SUMIF
17. Breakpoint
11. SUMIFS
18. Watch Window
12. IF
19. Immediate Window
vs. Watch Window 13. Nested IF
20. Call Statement 14. IFERROR
21. DoEvents
15. ISERROR
22. With...End With
Statement 16. ISBLANK

23. Exit Statement 17. INDEX

 20. Call Statement 14. IFERROR
21. DoEvents
15. ISERROR
22. With...End With
Statement 16. ISBLANK

23. Exit Statement 17. INDEX

24. Select Case 18. MATCH

Statement
19. INDEX & MATCH
25. InputBox and
MsgBox Functions 20. RIGHT
26. CreateObject and
GetObject Functions 21. LEFT

27. String Functions 22. MID

(Left, Right, Mid, Len,
InStr) 23. SEARCH

28. Syntax 24. SUBSTITUTE

29. Variable 25. CONCATENATE
30. Range 26. TEXT
31. Location 27. VALUE

28. CLEAR

29. CHAR

30. ABS

31. LEN

32. CHOOSE

1. VLOOKUP with IF

2. RIGHT with LEFT and

3. CONCATENATE with I
ISERROR

4. INDEX & MATCH w

IFERROR

5. SUMIF with AND

6. COUNTIF with OR
4. INDEX & MATCH w
IFERROR

5. SUMIF with AND

6. COUNTIF with OR
sic Code & Formulas 3. Basic Topics
1. Declaring Workbook and
Worksheet 1. Auto Fit Columns

. Finding Last Row and Last

Column 2. Auto Fit Rows

3. Storing Cell Value in a

Variable 3. Formatting
4. Working with Ranges
Coloring
5. Assigning Values to Cells
Font
1. LOOKUP
Interior
2. VLOOKUP
Bold Font
3. HLOOKUP

4. AND LineStyle

5. OR 4. Apply and Remove Text Wrap

6. COUNTIF 5. Merge and UnMerge Cell

7. COUNTIFS 6. Remove Duplicate

8. COUNT 7. Sorting (Ascending,
Descending)
9. COUNTA
8. Text To Column
10. SUMIF
9. Types Of Error
11. SUMIFS
Compiler error
12. IF
Runtime error
13. Nested IF

14. IFERROR Syntax error

15. ISERROR Logical error

16. ISBLANK

17. INDEX
 14. IFERROR Syntax error

15. ISERROR Logical error

16. ISBLANK

17. INDEX

18. MATCH

19. INDEX & MATCH

20. RIGHT

21. LEFT

22. MID

23. SEARCH

24. SUBSTITUTE

25. CONCATENATE

26. TEXT

27. VALUE

28. CLEAR

29. CHAR

30. ABS

31. LEN

32. CHOOSE

1. VLOOKUP with IF

. RIGHT with LEFT and LEN

CONCATENATE with IF and

ISERROR

4. INDEX & MATCH with

IFERROR

5. SUMIF with AND

6. COUNTIF with OR
4. INDEX & MATCH with
IFERROR

5. SUMIF with AND

6. COUNTIF with OR
 4. Advanced Topics 5.
1. Auto filter

Apply Autofilter

Remove Autofilter

2. Sheets

Add

Delete

Copy

Move
Wrap
Save As PDF
ell
Insert Multiple Sheets

Protect all Worksheets

UnProtect Worksheet

Delete_Blank_Sheet

3. IF Condition

IF…EndIF

IF…Else…EndIF

Nested if

4. Loops

For Next
 Nested if

4. Loops

For Next

For Each Next

Do While

Do Until

5. Error Handling

On error goto resume next

On error goto label

On error goto 0

On error goto -1

goto jump
5. Advanced Concepts 6. Web Sc
1. Create Folder with FSO 1. Data Ex

1. Delete Folder 2. Table R

2. Folder Copy 3. Handlin

3. Folder Exist 4. Handlin

4. If Not Exist Then Create 5. Handling

5. TextFile Create 6. Handling

6. FSO TextFile Delete 7. Handling I

7. TextFile Open 8. File Download

2. Functions 9. Taking Sc

1. Operators with functions 10. Converting

2. FSO with functions 11. Converting

3. Excel Enhancements 12. Converting

1. Pivot Table 13. Usin

2. Pivot Charts 14. Using VB

15. Waiting Unti

3. Charts Pres

4. Sparklines Logical

5. Slicer

6. Conditional Formatting

7. VBS
 5. Slicer

6. Conditional Formatting

7. VBS

8. User Forms

9. Class module

10. Protect Sheet

11. Protect Workbook

4. Consolidation

Worksheet To Worksheet

Worksheet to Another
Workbook/Worksheet

Workbook To Workbook
6. Web Scraping
1. Data Extraction

2. Table Reading

3. Handling Alerts

4. Handling Frames

5. Handling Windows

6. Handling Dropdowns

7. Handling Input Boxes

8. File Downloading (Excel, PDF)

9. Taking Screenshots

10. Converting Text to Excel

11. Converting PDF to Excel

12. Converting Word to Excel

13. Using APIs

14. Using VBS (VBScript)

15. Waiting Until an Element is

Present

Logical error
 Web Scraping
Web scraping in VBA using Selenium can be a powerful tool for automating tasks that involve interac

Prerequisites
Before you begin, ensure you have the following:
1. Selenium Basic installed. You can download it from here.
2. ChromeDriver compatible with your Chrome browser version. You can download it from here.

1. Data Extraction
Data extraction involves retrieving specific information from a web page.
Example:
Let's extract the titles of articles from a news website.
Example Data:
Website: https://news.ycombinator.com/
Example Code:
Sub ExtractData()
Dim browser As New ChromeDriver
Dim elements As WebElements
Dim element As WebElement
Dim i As Integer

browser.Start "Chrome"
browser.Get "https://news.ycombinator.com/"

Set elements = browser.FindElementsByCss("a.storylink")

For i = 1 To elements.Count
Set element = elements.Item(i - 1)
Debug.Print element.Text
Next i

browser.Quit
End Sub

Steps:
1. Start the Chrome browser.
2. Navigate to the website.
3. Find elements using the CSS selector.
4. Loop through the elements and print their text.
Result: This code will print the titles of the articles on the Hacker News front page in the Immediate
Explanation:
FindElementsByCss("a.storylink"): This finds all anchor tags with the class storylink.
 element.Text: This retrieves the text content of each element.

2. Table Reading
Reading data from a table on a webpage involves finding the table element and iterating through its
Example:
Read the data from a table on a sample website.
Example Data:
Website: https://www.w3schools.com/html/html_tables.asp
Example Code:
Sub ReadTable()
Dim browser As New ChromeDriver
Dim table As WebElement
Dim rows As WebElements
Dim cells As WebElements
Dim row As WebElement
Dim cell As WebElement
Dim i As Integer, j As Integer
browser.Start "Chrome"
browser.Get "https://www.w3schools.com/html/html_tables.asp"
Set table = browser.FindElementById("customers")
Set rows = table.FindElementsByTag("tr")
For i = 2 To rows.Count
Set row = rows.Item(i - 1)
Set cells = row.FindElementsByTag("td")
For j = 1 To cells.Count
Set cell = cells.Item(j - 1)
Debug.Print cell.Text
Next j
Next i
browser.Quit
End Sub
Steps:
1. Start the Chrome browser.
2. Navigate to the website.
3. Find the table element by its ID.
4. Loop through the rows and cells, printing the cell text.
Result: This code will print the contents of each cell in the "customers" table on the W3Schools HTM
Explanation:
FindElementById("customers"): Finds the table element by its ID.
FindElementsByTag("tr"): Finds all the row elements within the table.
FindElementsByTag("td"): Finds all the cell elements within a row.

3. Handling Alerts
Handling alerts involves switching the focus to the alert and accepting or dismissing it.
Example Data:
Website: A website that triggers a JavaScript alert (for demonstration purposes).
Example Code:
Sub HandleAlert()
Dim browser As New ChromeDriver
browser.Start "Chrome"
browser.Get "http://example.com/alert"
If browser.IsAlertPresent Then
browser.SwitchToAlert.Accept
End If
browser.Quit
End Sub
Steps:
1. Start the Chrome browser.
2. Navigate to the website.
3. Check if an alert is present.
4. Accept the alert.
Result: The code will accept any alert that appears on the page.
Explanation:
IsAlertPresent: Checks if an alert is present.
SwitchToAlert.Accept: Switches to the alert and accepts it.

4. Handling Frames
Handling frames involves switching the focus to a specific frame before interacting with elements ins
Example Data:
Website: https://www.w3schools.com/tags/tryit.asp?filename=tryhtml_iframe
Example Code:
Sub HandleFrame()
Dim browser As New ChromeDriver
browser.Start "Chrome"
browser.Get "https://www.w3schools.com/tags/tryit.asp?filename=t
browser.SwitchToFrame ("iframeResult")
browser.SwitchToFrame (0)
Debug.Print browser.FindElementByTag("h1").Text
browser.Quit
End Sub
Steps:
1. Start the Chrome browser.
2. Navigate to the website.
3. Switch to the frame.
4. Interact with elements inside the frame.
Result: The code will print the text of the h1 tag inside the iframe.
Explanation:
SwitchToFrame("iframeResult"): Switches to the frame with the ID iframeResult.
SwitchToFrame(0): Switches to the first frame within the current frame.

5. Handling Windows
Handling multiple windows involves switching the focus between different browser windows or tabs
Example Data:
Website: A website that opens a new window (for demonstration purposes).
Example Code:
Sub HandleWindow()
Dim browser As New ChromeDriver
Dim handles As Collection

browser.Start "Chrome"
browser.Get "http://example.com"
browser.FindElementByLinkText("Open new window").Click

Set handles = browser.WindowHandles

browser.SwitchToWindow (handles.Item(2))
Debug.Print browser.Title

browser.Quit
End Sub

Steps:
1. Start the Chrome browser.
2. Navigate to the website.
3. Click a link that opens a new window.
4. Switch to the new window and print its title.
Result: The code will print the title of the newly opened window.
Explanation:
WindowHandles: Retrieves a collection of all open window handles.
SwitchToWindow(handles.Item(2)): Switches to the second window.

6. Handling Dropdowns
Handling dropdowns involves selecting an option from a dropdown list.
Example Data:
Website: https://www.w3schools.com/tags/tryit.asp?filename=tryhtml_select
Example Code:
Sub HandleDropdown()
Dim browser As New ChromeDriver

browser.Start "Chrome"
browser.Get "https://www.w3schools.com/tags/tryit.asp?filename=t
 browser.SwitchToFrame ("iframeResult")
browser.FindElementByTag("select").AsSelect.SelectByValue "saab"

browser.Quit
End Sub

Steps:
1. Start the Chrome browser.
2. Navigate to the website.
3. Switch to the frame.
4. Select an option from the dropdown.
Result: The code will select the "Saab" option from the dropdown list.
Explanation:
AsSelect.SelectByValue("saab"): Selects the option with the value saab.

7. Handling Input Boxes

Handling input boxes involves entering text into a text field.
Example Data:
Website: https://www.w3schools.com/html/html_forms.asp
Example Code:
Sub HandleInputBox()
Dim browser As New ChromeDriver
browser.Start "Chrome"
browser.Get "https://www.w3schools.com/html/html_forms.asp"
browser.FindElementByName("firstname").SendKeys "John"
browser.FindElementByName("lastname").SendKeys "Doe"
browser.Quit
End Sub
Steps:
1. Start the Chrome browser.
2. Navigate to the website.
3. Enter text into the input boxes.
Result: The code will enter "John" into the first name field and "Doe" into the last name field.
Explanation:
SendKeys "John": Enters the text John into the input box.

8. File Downloading (Excel, PDF)

Downloading files involves clicking a link or button that triggers a download and handling the downlo
Example Data:
Website: A website with a downloadable file (for demonstration purposes).

Example Code:
Sub DownloadFile()
 Dim browser As New ChromeDriver
Dim downloadPath As String

downloadPath = "C:\Downloads\" ' Change this to your download pa

browser.AddArgument "--headless"
browser.AddArgument "--disable-gpu"
browser.AddArgument "--window-size=1920,1200"
browser.AddArgument "--disable-extensions"
browser.AddArgument "--proxy-server='direct://'"
browser.AddArgument "--proxy-bypass-list=*"
browser.AddArgument "--start-maximized"
browser.AddArgument "--disable-dev-shm-usage"
browser.AddArgument "--no-sandbox"
browser.AddPreference "download.default_directory", downloadPath
browser.AddPreference "download.prompt_for_download", False
browser.AddPreference "download.directory_upgrade", True
browser.AddPreference "plugins.always_open_pdf_externally", True

browser.Start "Chrome"
browser.Get "http://example.com/download"
browser.FindElementByLinkText("Download file").Click

' Add code to wait for download to complete if necessary

browser.Quit
End Sub

Steps:
1. Start the Chrome browser with download preferences.
2. Navigate to the website.
3. Click the link to download the file.

Result: The file will be downloaded to the specified directory.

Explanation:
AddPreference "download.default_directory", downloadPath: Sets the default download dire
AddPreference "download.prompt_for_download", False: Disables the download prompt.

9. Taking Screenshots
Taking screenshots involves capturing the current view of the browser window.
Example Data:
Website: https://www.example.com
Example Code:
Sub TakeScreenshot()
Dim browser As New ChromeDriver
Dim screenshotPath As String
screenshotPath = "C:\Screenshots\screenshot.png" ' Change this to
browser.Start "Chrome"
browser.Get "https://www.example.com"
browser.GetScreenshot.SaveAs screenshotPath
 browser.Quit
End Sub
Steps:
1. Start the Chrome browser.
2. Navigate to the website.
3. Take a screenshot and save it to the specified path.
Result: The screenshot will be saved to the specified directory.
Explanation:
GetScreenshot.SaveAs screenshotPath: Takes a screenshot and saves it to the specified path.

10. Converting Text to Excel

Converting text to Excel involves reading text data and writing it to an Excel worksheet.
Example Data:
Text File: A text file with data (for demonstration purposes).
Example Code:
Sub ConvertTextToExcel()
Dim filePath As String
Dim textLine As String
Dim i As Integer
filePath = "C:\Data\data.txt" ' Change this to your text file path
Open filePath For Input As #1
i=1
Do Until EOF(1)
Line Input #1, textLine
Cells(i, 1).Value = textLine
i=i+1
Loop
Close #1
End Sub
Steps:
1. Open the text file.
2. Read each line of text.
3. Write the text to an Excel worksheet.
Result: The text data will be written to the worksheet, one line per cell.
Explanation:
Line Input #1, textLine: Reads a line of text from the file.
Cells(i, 1).Value = textLine: Writes the text to a cell.

11. Converting PDF to Excel

Converting PDF to Excel involves using a library to read PDF data and write it to an Excel worksheet.
Example Data:
PDF File: A PDF file with data (for demonstration purposes).
Example Code:
' This task typically requires a third-party library such as PDFBox or iTe
' Below is a pseudo-code example demonstrating the logic.
Sub ConvertPDFToExcel()
' Use a third-party library to read PDF data
' Write the PDF data to an Excel worksheet
End Sub
Steps:
1. Use a third-party library to read PDF data.
2. Write the PDF data to an Excel worksheet.
Result: The PDF data will be written to the worksheet.
Explanation:
This task requires a third-party library that can read PDF files.

12. Converting Word to Excel

Converting Word to Excel involves reading data from a Word document and writing it to an Excel wo
Example Data:
Word File: A Word file with data (for demonstration purposes).
Example Code:
Sub ConvertWordToExcel()
Dim wordApp As Object
Dim wordDoc As Object
Dim text As String
Dim i As Integer

Set wordApp = CreateObject("Word.Application")

Set wordDoc = wordApp.Documents.Open("C:\Data\data.docx") ' Ch

text = wordDoc.Content.Text
wordDoc.Close
wordApp.Quit

i=1
For Each line In Split(text, vbCrLf)
Cells(i, 1).Value = line
i=i+1
Next line
End Sub

Steps:
1. Open the Word document.
2. Read the text data.
3. Write the text data to an Excel worksheet.
Result: The Word data will be written to the worksheet, one line per cell.
Explanation:
wordDoc.Content.Text: Reads the text content of the Word document.
 Split(text, vbCrLf): Splits the text into lines.

13. Using APIs

Using APIs involves sending HTTP requests and handling the responses.
Example Data:
API Endpoint: A sample API endpoint (for demonstration purposes).
Example Code:
Sub UseAPI()
Dim http As Object
Dim url As String
Dim response As String
url = "https://api.example.com/data" ' Change this to your API endp
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", url, False
http.send
response = http.responseText
Debug.Print response
End Sub
Steps:
1. Create an HTTP request.
2. Send the request to the API endpoint.
3. Handle the response.
Result: The response data will be printed in the Immediate Window.
Explanation:
http.Open "GET", url, False: Opens a GET request to the specified URL.
http.send: Sends the request.
http.responseText: Retrieves the response text.

14. Using VBS (VBScript)

Using VBS involves running VBScript code from VBA.
Example Data:
VBScript File: A sample VBScript file (for demonstration purposes).
Example Code:
Sub RunVBS()
Dim vbsFile As String

vbsFile = "C:\Scripts\script.vbs" ' Change this to your VBS file path

Shell "cscript.exe " & vbsFile

End Sub

Steps:
1. Specify the VBScript file path.
 2. Run the VBScript file using cscript.exe.
Result: The VBScript code will be executed.
Explanation:
Shell "cscript.exe " & vbsFile: Runs the VBScript file using the cscript.exe command.

15. Waiting Until an Element is Present

Waiting until an element is present involves checking for the presence of an element in a loop.
Example Data:
Website: A website with dynamic content (for demonstration purposes).
Example Code:
Sub WaitForElement()
Dim browser As New ChromeDriver
Dim element As WebElement
Dim timeout As Date
browser.Start "Chrome"
browser.Get "http://example.com"
timeout = Now + TimeValue("00:00:30") ' 30-second timeout
Do While Now < timeout
On Error Resume Next
Set element = browser.FindElementByCss("div.dynamic-content")
On Error GoTo 0
If Not element Is Nothing Then Exit Do
Application.Wait Now + TimeValue("00:00:01") ' Wait for 1 secon
Loop
If element Is Nothing Then
Debug.Print "Element not found"
Else
Debug.Print "Element found"
End If
browser.Quit
End Sub
Steps:
1. Start the Chrome browser.
2. Navigate to the website.
3. Check for the presence of the element in a loop until the timeout.
4. Print whether the element was found or not.
Result: The code will print whether the dynamic content was found on the page.
Explanation:
timeout = Now + TimeValue("00:00:30"): Sets a 30-second timeout.
Application.Wait Now + TimeValue("00:00:01"): Waits for 1 second between checks.
b Scraping
g tasks that involve interacting with web pages.

download it from here.

storylink")

nt page in the Immediate Window of the VBA editor.

class storylink.
t and iterating through its rows and cells.

html_tables.asp"

ble on the W3Schools HTML table page.

ismissing it.

eracting with elements inside it.

html_iframe

tryit.asp?filename=tryhtml_iframe"
rameResult.

browser windows or tabs.

ndow").Click

html_select

tryit.asp?filename=tryhtml_select"
SelectByValue "saab"

html_forms.asp"
dKeys "John"
Keys "Doe"

the last name field.

d and handling the download dialog.

to your download path

ctory", downloadPath
download", False
grade", True
df_externally", True

necessary

the default download directory.

the download prompt.

ng" ' Change this to your desired path

es it to the specified path.

el worksheet.

our text file path

it to an Excel worksheet.
uch as PDFBox or iTextSharp, which are not native to VBA.

d writing it to an Excel worksheet.

ata\data.docx") ' Change this to your Word file path

his to your API endpoint

o your VBS file path

.exe command.

n element in a loop.

econd timeout

iv.dynamic-content")

") ' Wait for 1 second

between checks.
 Basic Explanation
1. What is Option Explicit?
Explanation:
Option Explicit forces explicit declaration of all variables, reducing errors from typos and en
Example:
Option Explicit

Sub Example()
Dim i As Integer
i = 10
MsgBox i
End Sub

Explanation: The Option Explicit statement ensures that the variable i is declared before u
compile-time error.

2. Procedures
Explanation:
i) When we write a macro in vba that is called Procedure.
ii) Procedures are blocks of code that perform specific tasks. There are two types: Sub (Sub
Sub: Does not return a value.
Function: Returns a value.
Example:
Sub ShowMessage()
MsgBox "Hello, World!"
End Sub

Function AddNumbers(a As Integer, b As Integer) As Integer

AddNumbers = a + b
End Function

Explanation: ShowMessage is a Sub procedure that displays a message. AddNumbers is a

and returns the result.

3. Functions
Explanation:
i) Function is group of variable which we can use anywhere in a program.
ii)Function is group of reusable code we can use anywhere in a program
iii)Functions perform calculations and return values. They can be called within ot

Example:
Function SquareNumber(Number As Integer) As Integer
SquareNumber = Number * Number
 End Function

Sub Example()
MsgBox SquareNumber(5) ' Displays 25
End Sub

Explanation: The SquareNumber function calculates the square of a number and returns i
displays the result.

4. Dim(Define in Memory)
Explanation:
i) Dim is used to declare variables and allocate storage space.
Example:
Sub DeclareVariables()
Dim i As Integer
Dim s As String
Dim d As Double
i = 10
s = "Hello"
d = 3.14
MsgBox i & " " & s & " " & d
End Sub

Explanation: Variables i, s, and d are declared with Dim and assigned values. The MsgBox

5. Class
Explanation:
i) Classes are blueprints for creating objects, encapsulating data (properties) and behavior (
ii) A Class describes the variables, Properties, Procedures and events of an Object.

Example:
' In a class module named "Car"
Private pColor As String

Public Property Get Color() As String

Color = pColor
End Property

Public Property Let Color(Value As String)

pColor = Value
End Property

Public Sub Drive()

MsgBox "The car is driving"
End Sub

' In a regular module

Sub TestCar()
Dim myCar As Car
Set myCar = New Car
 myCar.Color = "Red"
MsgBox myCar.Color ' Displays "Red"
myCar.Drive
End Sub

Explanation: The Car class has a property Color and a method Drive. The TestCar Sub de
setting its Color property, and calling the Drive method.

6. Object
Explanation:
i) Object is an instance of a class. Through the class we can access the class Properties, Me
ii) Objects represent elements like workbooks, worksheets, and ranges. Objects have prope
Example:
Sub UseObject()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Range("A1").Value = "Hello, World!"
End Sub

Explanation: ws is declared as a Worksheet object and set to the first sheet in the workbo
set to "Hello, World!".

7. Set and New

Explanation:
i) Set is keyword & new is reserve keyword
ii) Set assigns an object to a variable. New creates a new instance of an object.
Example:
Sub CreateObject()
Dim dict As Object
Set dict = New Scripting.Dictionary
dict.Add "Key1", "Value1"
MsgBox dict("Key1")
End Sub

Explanation: dict is declared as an Object and set to a new Scripting.Dictionary instance

value is displayed.

8. Array
Explanation 1:-
Array is a group of variable and collection of variable
Array is like a mini storage box where we can store the value and data.
There are the five types of array:-
Static Array
Dynamic Array
Dimension
2 Dimension
multidimensional Arrays

Explanation 2:- Arrays store multiple values in a single variable. Arrays can be static (fixed s
Example:
Sub StaticArray()
Dim arr(1 To 5) As Integer
Dim i As Integer
For i = 1 To 5
arr(i) = i * 2
Next i
MsgBox arr(3) ' Displays 6
End Sub

Sub DynamicArray()
Dim arr() As Integer
Dim i As Integer
ReDim arr(1 To 5)
For i = 1 To 5
arr(i) = i * 2
Next i
MsgBox arr(3) ' Displays 6
End Sub

Explanation: StaticArray uses a fixed-size array, while DynamicArray uses a resizable arra
and display the value at the third position.

9. API
Explanation:

i) The Full form of API is "Application Programming interface" it is set of methods that direc
ii) API allow VBA to call functions from external libraries, such as the Windows API.

Example:

Declare PtrSafe Function GetTickCount Lib "kernel32" () As Lon

Sub UseAPI()
Dim tick As Long
tick = GetTickCount()
MsgBox tick
End Sub

Explanation: GetTickCount is a Windows API function that returns the number of milliseco
UseAPI Sub calls this function and displays the result.

10. VBS
Explanation:
VBScript (VBS) is a scripting language similar to VBA but used for different purposes, such a
applications.
Example:
' Save this as a .vbs file and run it
Dim message
message = "Hello from VBScript!"
MsgBox message

Explanation: This VBScript code displays a message box with the text "Hello from VBScript!
it to see the message.

11. INSTR
Explanation:
i) INSTR return the position of string value in numeric value
ii) INSTR is a function which search the position of the character from a string
iii) INSTR function returns the position of the first occurrence of a substring within another
Example:
Sub FindSubstring()
Dim pos As Integer
pos = InStr(1, "Hello, World!", "World")
MsgBox pos ' Displays 8
End Sub

Explanation: InStr searches for "World" in "Hello, World!" starting from position 1. It return

12. Loop and Types of Loops

Explanation: Loops repeat a block of code multiple times. Common types of loops in VBA a
While, and Do Until.
Examples:
For loop it will executed a specific number of times.
' For...Next loop
Sub ForLoop()
Dim i As Integer
For i = 1 To 5
MsgBox i
Next i
End Sub

For Each loop is used to execute a statements or group of statements for each elements in
' For Each...Next loop
Sub ForEachLoop()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
MsgBox ws.Name
Next ws
End Sub

Do While repeat itself until True condition is met

 ' Do While loop
Sub DoWhileLoop()
Dim i As Integer
i=1
Do While i <= 5
MsgBox i
i=i+1
Loop
End Sub

Do Until repeat itself until False conditions is met

' Do Until loop
Sub DoUntilLoop()
Dim i As Integer
i=1
Do Until i > 5
MsgBox i
i=i+1
Loop
End Sub

Explanation: Each example demonstrates a different type of loop, displaying values from 1

13. Types of Error

Explanation: Understanding different types of errors helps in debugging and writing robust
Syntax errors: Errors in the structure of the code.
Runtime errors: Errors that occur during code execution.
Logical errors: Code runs but gives incorrect results.
Compile Error: Compile error will occur when incomplete statement found e.g we use If
Examples:
' Syntax Error: Incorrect code structure
Sub SyntaxError()
Dim i As Integer
i = "Hello" ' Type mismatch error
End Sub

' Runtime Error: Occurs during code execution

Sub RuntimeError()
Dim i As Integer
i = 1 / 0 ' Division by zero error
End Sub

' Logical Error: Code runs but gives incorrect result

Sub LogicalError()
Dim i As Integer
i = 10
i = i + 5 ' Expected 10, but logical error may change this resu
 MsgBox i
End Sub

Explanation: SyntaxError demonstrates a type mismatch, RuntimeError shows a division

a logical error where the expected and actual results differ.

14. What is On Error and Types of On Error

Explanation: On Error statements are used for error handling in VBA.
On Error Resume Next: Continues to the next statement after an error occurs.
Once we add on error resume next in the beginning of the module it will skip the error line
On Error GoTo 0: Disables error handling (default mode).
Once we add on error Go to 0 it will restore the VBAs default error handler which Shows er
On Error GoTo Label: Jumps to a specified label for error handling.
Once we add on error Go to Label at the starting of our Procedure Once error will occur it g
where we wrote the Error.
Example:

Sub ErrorHandler()
On Error GoTo ErrorHandler
Dim i As Integer
i = 1 / 0 ' Division by zero error
MsgBox "After error" ' This line is skipped
Exit Sub

ErrorHandler:
MsgBox "Error occurred!"
Resume Next ' Resume next line after error
End Sub

Explanation: The On Error GoTo ErrorHandler directs the code to ErrorHandler upon en
continues execution after handling the error.

15. Debug.Print
Explanation: Debug.Print outputs messages to the Immediate window during code executi
Example:
Sub DebugPrintExample()
Dim i As Integer
For i = 1 To 5
Debug.Print "Iteration: " & i
Next i
End Sub

Explanation: Debug.Print prints each iteration of the loop to the Immediate window.

16. Immediate Window

Explanation: The Immediate window in the VBA editor allows direct execution of statemen
Example:
Enter ? "Hello, Immediate Window!" in the Immediate window and press Enter to see th

17. Breakpoint
Explanation: A breakpoint pauses code execution at a specified line for inspection during d
Example:
Click on the left margin of a line in the VBA editor to set or remove a breakpoint.

18. Watch Window

Explanation: The Watch window allows monitoring of variable values and expressions durin
Example:
Add i to the Watch window to track its value during execution.

19. Immediate Window vs. Watch Window

Explanation:
Immediate Window: Executes commands and prints output directly.
Watch Window: Monitors variables and expressions during runtime.

20. Call Statement

Explanation: The Call statement is used to call a procedure or function in VBA.
Example:
Sub CallExample()
Call ShowMessage ' Calls the ShowMessage subroutine
End Sub

Sub ShowMessage()
MsgBox "Hello, World!"
End Sub

Explanation: CallExample calls the ShowMessage subroutine using the Call statement.

21. DoEvents
Explanation: DoEvents yields execution to other applications or processes during lengthy
Example:
Sub DoEventsExample()
Dim i As Integer
For i = 1 To 10000
Cells(1, 1).Value = i
DoEvents ' Allows Excel to process other events
Next i
End Sub
Explanation: DoEvents ensures Excel processes other events while the loop updates cell v

22. With...End With Statement

Explanation: With...End With improves code readability and efficiency by specifying a seri
Example:
Sub WithEndWithExample()
With Range("A1")
.Value = "Hello"
.Font.Bold = True
.Interior.Color = RGB(255, 0, 0)
End With
End Sub

Explanation: With Range("A1") applies formatting properties to cell A1 without repeated

23. Exit Statement

Explanation: Exit statements prematurely exit procedures or loops.
Example:
Sub ExitExample()
Dim i As Integer
For i = 1 To 10
If i = 5 Then Exit For ' Exits loop when i equals 5
MsgBox i
Next i
End Sub

Explanation: Exit For stops the loop when i equals 5 in the ExitExample procedure.

24. Select Case Statement

Explanation: Select Case selects one of several groups of statements to execute based on
Example:
Sub SelectCaseExample()
Dim i As Integer
i=2
Select Case i
Case 1
MsgBox "One"
Case 2
MsgBox "Two"
Case Else
MsgBox "Other"
End Select
End Sub

Explanation: Select Case evaluates i and displays a message box accordingly.

25. InputBox and MsgBox Functions
Explanation:
InputBox: Displays a dialog box prompting for user input.
MsgBox: Displays a message in a dialog box.
Example:
Sub InputBoxExample()
Dim userName As String
userName = InputBox("Enter your name:")
MsgBox "Hello, " & userName & "!"
End Sub

Explanation: InputBox prompts for a name, and MsgBox greets the user.

26. CreateObject and GetObject Functions

Explanation:
CreateObject: Creates a new instance of a specified object.
GetObject: Retrieves an existing instance of an object from a file.
Example:
Sub CreateObjectExample()
Dim wordApp As Object
Set wordApp = CreateObject("Word.Application")
wordApp.Visible = True
End Sub

Explanation: CreateObject opens Microsoft Word programmatically.

27. String Functions (Left, Right, Mid, Len, InStr)

Explanation:
Left: Returns a specified number of characters from the left side of a string.
Right: Returns a specified number of characters from the right side of a string.
Mid: Returns a specified number of characters from a string, starting at a specified positio
Len: Returns the length of a string.
InStr: Returns the position of the first occurrence of one string within another.
Example:
Sub StringFunctionsExample()
Dim myString As String
myString = "Hello, World!"
MsgBox Left(myString, 5) ' Displays "Hello"
MsgBox Right(myString, 6) ' Displays "World!"
MsgBox Mid(myString, 8, 5) ' Displays "World"
MsgBox Len(myString) ' Displays 13
MsgBox InStr(myString, "World") ' Displays 8
End Sub

Explanation: Demonstrates various string manipulation functions in VBA.

28. Syntax
Explanation: Syntax refers to the rules that define the combinations of symbols and words
language. Correct syntax is essential for the compiler or interpreter to understand and exec
Example:
Sub ExampleSyntax()
Dim x As Integer
x = 10
MsgBox "Value of x is " & x
End Sub
Explanation:
Sub: Indicates the start of a subroutine.
Dim x As Integer: Declares a variable x as an integer.
x = 10: Assigns the value 10 to x.
MsgBox "Value of x is " & x: Displays a message box with the value of x.

29. Variable
Explanation: A variable is a named storage location in memory used to hold data during pro
declared before they can be used, specifying their data type.
Example:
Sub ExampleVariable()
Dim age As Integer
age = 30
MsgBox "Age is " & age
End Sub
Explanation:
Dim age As Integer: Declares a variable age as an integer.
age = 30: Assigns the value 30 to age.
MsgBox "Age is " & age: Displays a message box with the value of age.

30. Range
Explanation: In Excel VBA, a range refers to a collection of cells within a worksheet. It can b
entire row or column.
Example:
Sub ExampleRange()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim myRange As Range
Set myRange = ws.Range("A1:B5")
myRange.Value = "Hello"
End Sub
Explanation:
Dim ws As Worksheet: Declares a worksheet variable ws.
Set ws = ThisWorkbook.Sheets("Sheet1"): Sets ws to refer to "Sheet1" in the curren
Dim myRange As Range: Declares a range variable myRange.
 Set myRange = ws.Range("A1:B5"): Sets myRange to cover cells A1 to B5 on "Sheet
myRange.Value = "Hello": Sets the value of all cells in myRange to "Hello".

31. Location
Explanation: Location in programming generally refers to where something is stored or pos
Physical Location: Such as the address of a cell in Excel (Range("A1")).
Memory Location: Where variables or objects are stored in computer memory.
Example:
Sub ExampleLocation()
Dim cellValue As String
cellValue = Range("A1").Value
MsgBox "Value in A1 is: " & cellValue
End Sub
Explanation:
Dim cellValue As String: Declares a variable cellValue to store string data.
cellValue = Range("A1").Value: Assigns the value of cell A1 to cellValue.
MsgBox "Value in A1 is: " & cellValue: Displays a message box with the value of cellV
xplanation

ng errors from typos and enhancing code clarity.

ariable i is declared before use. If i were not declared, VBA would raise a

ere are two types: Sub (Subroutine) and Function.

Integer) As Integer

message. AddNumbers is a Function that adds two numbers

y can be called within other procedures or functions.

er) As Integer
e of a number and returns it. The Example Sub calls this function and

igned values. The MsgBox function displays these values.

(properties) and behavior (methods).

vents of an Object.
Drive. The TestCar Sub demonstrates creating an instance of the Car class,

ess the class Properties, Methods and Events.

ranges. Objects have properties and methods.

he first sheet in the workbook. The Value property of cell A1 is

ce of an object.

ipting.Dictionary instance. A key-value pair is added, and the

 Arrays can be static (fixed size) or dynamic (resizable).

cArray uses a resizable array. Both arrays store multiples of 2

is set of methods that direct interaction with the operating system.

s the Windows API.

b "kernel32" () As Long

urns the number of milliseconds since the system started. The

r different purposes, such as automating tasks outside of Office

e text "Hello from VBScript!". Save the code as a .vbs file and run

from a string
a substring within another string. It is case-sensitive.

ng from position 1. It returns 8, the starting position of "World".

mon types of loops in VBA are For...Next, For Each...Next, Do

ments for each elements in a Collection.

p, displaying values from 1 to 5 using MsgBox.

ebugging and writing robust code.

ement found e.g we use If condition but we forgot to add End IF.

ect result

may change this result

timeError shows a division by zero, and LogicalError highlights

an error occurs.
ule it will skip the error line and continues with second line.

ror handler which Shows error messages each time an error occurs.

ure Once error will occur it go directly on top of the module

e to ErrorHandler upon encountering an error. Resume Next

window during code execution, useful for debugging.

he Immediate window.

irect execution of statements and displays output.

ow and press Enter to see the message.

line for inspection during debugging.

move a breakpoint.

values and expressions during debugging.

unction in VBA.

sage subroutine

using the Call statement.

r processes during lengthy operations, allowing events to be processed.

ther events
hile the loop updates cell values.

fficiency by specifying a series of statements that apply to a single object.

o cell A1 without repeated references.

n i equals 5

Example procedure.

ements to execute based on the value of an expression.

ox accordingly.
plication")

de of a string.
t side of a string.
tarting at a specified position.

g within another.
tions of symbols and words that are valid in a programming
eter to understand and execute code correctly.

he value of x.

used to hold data during program execution. Variables must be

alue of age.

within a worksheet. It can be a single cell, multiple cells, or an

er to "Sheet1" in the current workbook.

ver cells A1 to B5 on "Sheet1".
Range to "Hello".

e something is stored or positioned. In VBA, this could refer to:

mputer memory.

ore string data.

1 to cellValue.
box with the value of cellValue.
 Basic Code & For
Steps for Running a VBA Macro:-
1. Open the VBA editor (ALT + F11).
2. Insert a new module (Insert > Module).
3. Copy and paste the provided code into the module.
4. Run the macro (F5).

Basic Code
1. Declaring Workbook and Worksheet:-
Explanation: Declaring workbook and worksheet allows us to refer to specific Excel files and sheets in
Data: Use ThisWorkbook to refer to the workbook containing the VBA code, and use Worksheets to
Example:
Dim wb As Workbook
Set wb = ThisWorkbook ' Refers to the current workbook

Dim ws As Worksheet
Set ws = wb.Worksheets("Sheet1") ' Refers to a specific worksheet na

Example Code: This code sets the current workbook to the variable wb and the worksheet named "S
Steps:
Dim wb As Workbook: Declare a variable wb as type Workbook.
Set wb = ThisWorkbook: Assign the current workbook to the variable wb.
Dim ws As Worksheet: Declare a variable ws as type Worksheet.
Set ws = wb.Worksheets("Sheet1"): Assign the worksheet named "Sheet1" to the variab

2. Finding Last Row and Last Column:

Explanation: Determining the last row and column helps in dynamically referencing data ranges in Ex
Data: Use End(xlUp) and End(xlToLeft) to find the last used row and column, respectively
Example:
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Dim lastColumn As Long

lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

Example Code: This code sets the variable lastRow to the last used row number in column A and last
worksheet ws.
Steps:
Dim lastRow As Long: Declare a variable lastRow to store the last row number.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row: Set lastRow to the last used row
Dim lastColumn As Long: Declare a variable lastColumn to store the last column numbe
lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column: Set lastColumn to th

3. Storing Cell Value in a Variable:

Explanation: Storing a cell's value in a variable allows us to manipulate or use that value in VBA code
Data: Access a cell's value using the .Value property.

Example:

Dim cellValue As Variant

cellValue = ws.Cells(1, 1).Value ' Value of cell A1

Example Code: This code stores the value of cell A1 in the variable cellValue.
Steps:
Dim cellValue As Variant: Declare a variable cellValue to store the cell's value.
cellValue = ws.Cells(1, 1).Value: Assign the value of cell A1 to the variable cellValue.

4. Working with Ranges:

Explanation: Ranges allow us to work with multiple cells or data areas in Excel.
Data: Specify ranges using cell addresses or the Range object.

Example:

Dim rng As Range

Set rng = ws.Range("A1:B10") ' Range from A1 to B10

Example Code: This code sets the variable rng to represent the range from A1 to B10 in worksheet w
Steps:
Dim rng As Range: Declare a variable rng to represent a range of cells.
Set rng = ws.Range("A1:B10"): Assign the range A1
in worksheet ws to the variable rng.

5. Assigning Values to Cells:

Explanation: Assigning values to cells or ranges allows us to update data dynamically in Excel.
Data: Use the .Value property to assign values to cells or ranges.

Example:

ws.Cells(1, 1).Value = "Hello" ' Put "Hello" in cell A1

Dim rng As Range

Set rng = ws.Range("A1:B10") ' Define the range A1 to B10
rng.Value = "Sample" ' Put "Sample" in the range A1 to B10

Example Code: This code puts the value "Hello" into cell A1 of worksheet ws and sets the value "Sam
Steps:
ws.Cells(1, 1).Value = "Hello": Assign the value "Hello" to cell A1.
Dim rng As Range: Declare a variable rng to represent a range of cells.
 Set rng = ws.Range("A1:B10"): Assign the range A1 in worksheet ws to the variable rng
rng.Value = "Sample": Assign the value "Sample" to the entire range A1

Formulas
Notes:-

Now, let's start with the first formula

1. LOOKUP
Explanation: The LOOKUP function searches for a value in a range and returns a corresponding valu
Data: Assume we have the following data in Sheet1:
A B
1 Apple
2 Banana
3 Cherry

We want to find the fruit name corresponding to the number 2.

Example Code:
Sub UseLookup()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert LOOKUP formula in cell D1 using R1C1 notation

ws.Range("D1").FormulaR1C1 = "=LOOKUP(2, R1C1:R3C1, R1C2:R3C
End Sub

Result: Cell D1 in Sheet1 will show "Banana".

2. VLOOKUP
Explanation: The VLOOKUP function searches for a value in the first column of a table and returns a

Data: Assume we have the same data in Sheet1.

Example Code:
 Sub UseVLookup()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert VLOOKUP formula in cell D2 using R1C1 notation

ws.Range("D2").FormulaR1C1 = "=VLOOKUP(2, R1C1:R3C2, 2, FALSE
End Sub

Result: Cell D2 in Sheet1 will show "Banana".

3. HLOOKUP
Explanation: The HLOOKUP function searches for a value in the first row of a table and returns a val
Data: Assume we have the following data in Sheet1:
A 1 2 3
B Apple Banana Cherry
We want to find the fruit name corresponding to the number 2.
Example Code:
Sub UseHLookup()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert HLOOKUP formula in cell D3 using R1C1 notation

ws.Range("D3").FormulaR1C1 = "=HLOOKUP(2, R1C2:R2C4, 2, FALS
End Sub

Result: Cell D3 in Sheet1 will show "Banana".

4. AND
Explanation: The AND function checks if all arguments are TRUE, and returns TRUE if they are, other
Data: Assume we have the following data in Sheet1:
A B
5 10
15 20
25 30

We want to check if the value in cell A1 is greater than 4 and less than 6.
Example Code:

Sub UseAnd()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert AND formula in cell D4 using R1C1 notation

ws.Range("D4").FormulaR1C1 = "=AND(R1C1>4, R1C1<6)"
End Sub

Result: Cell D4 in Sheet1 will show TRUE.

5. OR
Explanation: The OR function checks if any of the arguments are TRUE, and returns TRUE if any argu
Data: Assume we have the same data in Sheet1.
Example Code:
Sub UseOr()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert OR formula in cell D5 using R1C1 notation

ws.Range("D5").FormulaR1C1 = "=OR(R1C1=5, R1C1=15)"
End Sub

Result: Cell D5 in Sheet1 will show TRUE.

6. COUNTIF
Explanation: The COUNTIF function counts the number of cells within a range that meet a single con
Data: Assume we have the following data in Sheet1:
A
Apple
Banana
Cherry
Apple
We want to count the number of cells containing "Apple".
Example Code:
Sub UseCountIf()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert COUNTIF formula in cell B1 using R1C1 notation

ws.Range("B1").FormulaR1C1 = "=COUNTIF(R1C1:R4C1, ""Apple"")"
End Sub

Result: Cell B1 in Sheet1 will show 2.

7. COUNTIFS
Explanation: The COUNTIFS function counts the number of cells that meet multiple criteria.
Data: Assume we have the following data in Sheet1:
A B
Apple Red
Banana Yellow
Cherry Red
Apple Green

We want to count the number of "Apple" that are "Red".

Example Code:
Sub UseCountIfs()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert COUNTIFS formula in cell C1 using R1C1 notation

ws.Range("C1").FormulaR1C1 = "=COUNTIFS(R1C1:R4C1, ""Apple"",
End Sub

Result: Cell C1 in Sheet1 will show 1.

8. COUNT
Explanation: The COUNT function counts the number of cells that contain numbers.

Data: Assume we have the following data in Sheet1:

A
1
2
Apple
4

We want to count the number of numeric cells.

Example Code:

Sub UseCount()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert COUNT formula in cell B2 using R1C1 notation

ws.Range("B2").FormulaR1C1 = "=COUNT(R1C1:R4C1)"
End Sub

Result: Cell B2 in Sheet1 will show 3.

9. COUNTA
Explanation: The COUNTA function counts the number of non-empty cells.
Data: Assume we have the same data in Sheet1.
Example Code:
Sub UseCountA()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
' Insert COUNTA formula in cell B3 using R1C1 notation
ws.Range("B3").FormulaR1C1 = "=COUNTA(R1C1:R4C1)"
End Sub
Result: Cell B3 in Sheet1 will show 4.
10. SUMIF
Explanation: The SUMIF function adds the cells specified by a given condition or criteria.
Data: Assume we have the following data in Sheet1:
A B
Apple 10
Banana 20
Cherry 30
Apple 40

We want to sum the values in column B for cells in column A that contain "Apple".
Example Code:
Sub UseSumIf()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert SUMIF formula in cell C2 using R1C1 notation

ws.Range("C2").FormulaR1C1 = "=SUMIF(R1C1:R4C1, ""Apple"", R1C
End Sub

Result: Cell C2 in Sheet1 will show 50.

11. SUMIFS
Explanation: The SUMIFS function adds the cells specified by multiple conditions or criteria.
Data: Assume we have the same data in Sheet1.
Example Code:
Sub UseSumIfs()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert SUMIFS formula in cell D2 using R1C1 notation

ws.Range("D2").FormulaR1C1 = "=SUMIFS(R1C2:R4C2, R1C1:R4C1,
End Sub

Result: Cell D2 in Sheet1 will show 50.

12. IF
Explanation: The IF function checks whether a condition is met, and returns one value if TRUE, and a
Data: Assume we have the following data in Sheet1:
A
5
10
15
20

We want to check if the value in cell A1 is greater than 10.

Example Code:
Sub UseIf()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert IF formula in cell B4 using R1C1 notation

ws.Range("B4").FormulaR1C1 = "=IF(R1C1>10, ""Greater"", ""Not G
End Sub

Result: Cell B4 in Sheet1 will show "Not Greater".

13. Nested IF
Explanation: Nested IF functions allow multiple conditions to be evaluated in a single formula.
Data: Assume we have the following data in Sheet1:
A
5
10
15
20
We want to categorize the values in column A as "Low", "Medium", or "High".
Example Code:
Sub UseNestedIf()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert nested IF formula in cell B5 using R1C1 notation

ws.Range("B5").FormulaR1C1 = "=IF(R1C1<10, ""Low"", IF(R1C1<=1
End Sub

Result: Cell B5 in Sheet1 will show "Low".

14. IFERROR
Explanation: The IFERROR function returns a value if an expression evaluates to an error; otherwise
Data: Assume we have the following data in Sheet1:
A B
10 2
10 0
10 5
We want to divide column A by column B and handle any division errors.
Example Code:
Sub UseIfError()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert IFERROR formula in cell C5 using R1C1 notation

 ws.Range("C5").FormulaR1C1 = "=IFERROR(R1C1/R1C2, ""Error"")"
End Sub

Result: Cell C5 in Sheet1 will show "5" and cell C6 will show "Error".

15. ISERROR
Explanation: The ISERROR function returns TRUE if the expression is an error, and FALSE otherwise.
Data: Assume we have the same data in Sheet1.
Example Code:
Sub UseIsError()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert ISERROR formula in cell D5 using R1C1 notation

ws.Range("D5").FormulaR1C1 = "=ISERROR(R1C1/R1C2)"
End Sub

Result: Cell D5 in Sheet1 will show FALSE and cell D6 will show TRUE.

16. ISBLANK
Explanation: The ISBLANK function returns TRUE if the cell is empty, and FALSE otherwise.
Data: Assume we have the following data in Sheet1:
A

10
We want to check if cell A1 is blank.
Example Code:
Sub UseIsBlank()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert ISBLANK formula in cell B6 using R1C1 notation

ws.Range("B6").FormulaR1C1 = "=ISBLANK(R1C1)"
End Sub

Result: Cell B6 in Sheet1 will show TRUE.

17. INDEX
Explanation: The INDEX function returns the value of an element in a table or array, selected by the
Data: Assume we have the following data in Sheet1:
A B
Apple Red
Banana Yellow
Cherry Red
We want to find the value in the second row and second column.
Example Code:
Sub UseIndex()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert INDEX formula in cell C6 using R1C1 notation

ws.Range("C6").FormulaR1C1 = "=INDEX(R1C1:R3C2, 2, 2)"
End Sub

Result: Cell C6 in Sheet1 will show "Yellow".

18. MATCH
Explanation: The MATCH function searches for a value in an array and returns the relative position o
Data: Assume we have the following data in Sheet1:
A
Apple
Banana
Cherry
We want to find the position of "Banana" in column A.
Example Code:
Sub UseMatch()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert MATCH formula in cell B7 using R1C1 notation

ws.Range("B7").FormulaR1C1 = "=MATCH(""Banana"", R1C1:R3C1, 0
End Sub

Result: Cell B7 in Sheet1 will show 2.

19. INDEX & MATCH

Explanation: Combining INDEX and MATCH provides a powerful way to look up values in a table.
Data: Assume we have the same data in Sheet1.
Example Code:
Sub UseIndexMatch()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert INDEX & MATCH formula in cell C7 using R1C1 notation
ws.Range("C7").FormulaR1C1 = "=INDEX(R1C1:R3C2, MATCH(""Bana
End Sub

Result: Cell C7 in Sheet1 will show "Yellow".

20. RIGHT
Explanation: The RIGHT function returns the last character or characters in a text string, based on th
Data: Assume we have the following data in Sheet1:
A
Apple
Banana
Cherry
We want to get the last 2 characters from the text in cell A1.
Example Code:
Sub UseRight()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert RIGHT formula in cell B8 using R1C1 notation

ws.Range("B8").FormulaR1C1 = "=RIGHT(R1C1, 2)"
End Sub

Result: Cell B8 in Sheet1 will show "le".

21. LEFT
Explanation: The LEFT function returns the first character or characters in a text string, based on the
Data: Assume we have the same data in Sheet1.
Example Code:
Sub UseLeft()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert LEFT formula in cell B9 using R1C1 notation

ws.Range("B9").FormulaR1C1 = "=LEFT(R1C1, 2)"
End Sub

Result: Cell B9 in Sheet1 will show "Ap".

22. MID
Explanation: The MID function returns a specific number of characters from a text string, starting at
Data: Assume we have the same data in Sheet1.
Example Code:
Sub UseMid()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert MID formula in cell B10 using R1C1 notation

ws.Range("B10").FormulaR1C1 = "=MID(R1C1, 2, 3)"
End Sub

Result: Cell B10 in Sheet1 will show "ppl".

23. SEARCH
Explanation: The SEARCH function returns the number of the character at which a specific characte
Data: Assume we have the following data in Sheet1:
A
Apple
We want to find the position of the letter "p" in the text.
Example Code:
Sub UseSearch()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert SEARCH formula in cell B11 using R1C1 notation

ws.Range("B11").FormulaR1C1 = "=SEARCH(""p"", R1C1)"
End Sub

Result: Cell B11 in Sheet1 will show 2.

24. SUBSTITUTE
Explanation: The SUBSTITUTE function replaces existing text with new text in a text string.

Data: Assume we have the following data in Sheet1:

A
Apple

We want to replace "p" with "b" in the text.

Example Code:

Sub UseSubstitute()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert SUBSTITUTE formula in cell B12 using R1C1 notation

ws.Range("B12").FormulaR1C1 = "=SUBSTITUTE(R1C1, ""p"", ""b"")"
End Sub

Result: Cell B12 in Sheet1 will show "Abble".

25. CONCATENATE
Explanation: The CONCATENATE function joins two or more text strings into one string.
Data: Assume we have the following data in Sheet1:
A B
Apple Pie

We want to join the values in cells A1 and B1.

Example Code:
 Sub UseConcatenate()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert CONCATENATE formula in cell C12 using R1C1 notation

ws.Range("C12").FormulaR1C1 = "=CONCATENATE(R1C1, "" "", R1C2
End Sub

Result: Cell C12 in Sheet1 will show "Apple Pie".

26. TEXT
Explanation: The TEXT function converts a value to text in a specified number format.
Data: Assume we have the following data in Sheet1:
A
0.5
We want to format the number as a percentage.
Example Code:
Sub UseText()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert TEXT formula in cell B13 using R1C1 notation

ws.Range("B13").FormulaR1C1 = "=TEXT(R1C1, ""0.0%"")"
End Sub

Result: Cell B13 in Sheet1 will show "50.0%".

27. VALUE
Explanation: The VALUE function converts a text string that represents a number to a number.
Data: Assume we have the following data in Sheet1:
A
123
We want to convert the text "123" to the number 123.
Example Code:
Sub UseValue()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert VALUE formula in cell B14 using R1C1 notation

ws.Range("B14").FormulaR1C1 = "=VALUE(R1C1)"
End Sub

Result: Cell B14 in Sheet1 will show 123.

28. CLEAR
Explanation: The CLEAR function does not exist as a worksheet function, but you can clear cell cont
Example Code:
Sub UseClear()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Clear the contents of cell A1

ws.Range("A1").ClearContents
End Sub

Result: Cell A1 in Sheet1 will be cleared.

29. CHAR
Explanation: The CHAR function returns the character specified by a number.
Data: Assume we want to get the character for the ASCII code 65.
Example Code:

Sub UseChar()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert CHAR formula in cell B15 using R1C1 notation

ws.Range("B15").FormulaR1C1 = "=CHAR(65)"
End Sub

Result: Cell B15 in Sheet1 will show "A".

30. ABS
Explanation: The ABS function returns the absolute value of a number.
Data: Assume we have the following data in Sheet1:
A
-5
We want to get the absolute value of the number.
Example Code:
Sub UseAbs()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert ABS formula in cell B16 using R1C1 notation

ws.Range("B16").FormulaR1C1 = "=ABS(R1C1)"
End Sub

Result: Cell B16 in Sheet1 will show 5.

31. LEN
Explanation: The LEN function returns the number of characters in a text string.
Data: Assume we have the following data in Sheet1:
A
Apple
We want to get the length of the text in cell A1.
Example Code:
Sub UseLen()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
' Insert LEN formula in cell B17 using R1C1 notation
ws.Range("B17").FormulaR1C1 = "=LEN(R1C1)"
End Sub
Result: Cell B17 in Sheet1 will show 5.

32. CHOOSE
Explanation: The CHOOSE function returns a value from a list of values based on an index number.
Data: Assume we have the following data in Sheet1:
A
2

We want to return the second value from the list "Apple", "Banana", "Cherry".
Example Code:
Sub UseChoose()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert CHOOSE formula in cell B18 using R1C1 notation

ws.Range("B18").FormulaR1C1 = "=CHOOSE(R1C1, ""Apple"", ""Ban
End Sub

Result: Cell B18 in Sheet1 will show "Banana".

Multiple Formul
1. VLOOKUP with IF
Explanation: Using VLOOKUP with IF to return different results based on a condition.

Data: Assume we have the following data in Sheet1:

A B
1 Apple
2 Banana
3 Cherry

We want to use VLOOKUP to find the value in column B based on column A and use IF to modify the

Example Code:
 Sub VlookupWithIf()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Insert VLOOKUP with IF formula in cell C19 using R1C1 notation
ws.Range("C19").FormulaR1C1 = "=IF(VLOOKUP(2, R1C1:R3C2, 2, FA
End Sub

Result: Cell C19 in Sheet1 will show "Fruit".

2. RIGHT with LEFT and LEN

Explanation: Using RIGHT, LEFT, and LEN together to manipulate text strings.
Data: Assume we have the following data in Sheet1:
A
Apple
We want to extract parts of the text in cell A1.
Example Code:
Sub RightLeftLen()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
' Insert combined RIGHT, LEFT, and LEN formula in cell B20 using R1
ws.Range("B20").FormulaR1C1 = "=RIGHT(LEFT(R1C1, LEN(R1C1)-1)
End Sub
Result: Cell B20 in Sheet1 will show "ppl".

3. CONCATENATE with IF and ISERROR

Explanation: Use CONCATENATE (or &) to join strings together, and IF with ISERROR to conditionally
and handling potential errors.
Data:
Part 1: "Hello"
Part 2: "World"
Example Code:
Dim part1 As String
Dim part2 As String
Dim combined As String

part1 = "Hello"
part2 = "World"

If Not IsError(part1) And Not IsError(part2) Then

combined = part1 & " " & part2 ' Concatenate with a space
MsgBox "Combined string: " & combined
Else
MsgBox "One of the parts is an error"
End If
Result:
Combined string: "Hello World" (if no error in parts)

4. INDEX & MATCH with IFERROR

Explanation: Use INDEX to return a value from a table based on row and column numbers and MATC
Data:
Lookup Value: "Banana"
Lookup Range:
A
1 Apple
2 Banana
3 Cherry
Result Range:
B
1 10
2 20
3 30
Example Code:
Dim lookupValue As String
Dim matchRow As Long
Dim result As Variant

lookupValue = "Banana"

' Find the row number of the lookup value in the first column
matchRow = -1
For i = 1 To ws.Range("A1:A3").Cells.Count
If ws.Cells(i, 1).Value = lookupValue Then
matchRow = i
Exit For
End If
Next i

' Check if matchRow is valid (greater than zero) to avoid errors

If matchRow > 0 Then
result = ws.Cells(matchRow, 2).Value ' Get the value from column B
MsgBox "Found value: " & result
Else
MsgBox "Value not found"
End If

Result: If "Banana" is found in the range A1, the corresponding value from column B (row 2, which is
displayed.

5. SUMIF with AND

Explanation: Use SUMIF to sum values based on a single condition, and combine with AND for additi
Data:
*Criteria Range:
A
1 Category1
2 Category2
3 Category1
*Sum Range:
B
1 100
2 200
3 150
Criteria: "Category1"
Example Code:
Dim total As Double
Dim criteriaRange As Range
Dim sumRange As Range
Set criteriaRange = ws.Range("A1:A3")
Set sumRange = ws.Range("B1:B3")
total = Application.Sum(Application.SumIfs(sumRange, criteriaRange,
' Check if the total is within the specified range
If total > 0 And total < 300 Then
MsgBox "Total is between 0 and 300: " & total
Else
MsgBox "Total is out of range: " & total
End If
Result:
Total: 250 (sum of values in column B where column A is "Category1")
Message: "Total is between 0 and 300: 250"

6. COUNTIF with OR
Explanation: Use COUNTIF to count cells based on a single condition, and combine with OR for multi
Data:
*Range:
A
1 Apple
2 Banana
3 Apple
4 Cherry

Criteria1: "Apple"
Criteria2: "Banana"

Example Code:
 Dim count1 As Long
Dim count2 As Long

count1 = Application.Count(Application.If((ws.Range("A1:A4") = "Appl

count2 = Application.Count(Application.If((ws.Range("A1:A4") = "Bana

' Display the results

If count1 > 0 Or count2 > 0 Then
MsgBox "At least one condition is met. Count1: " & count1 & ", Coun
Else
MsgBox "No conditions met"
End If

Result:
Count1: 2 (number of times "Apple" appears)
Count2: 1 (number of times "Banana" appears)
Message: "At least one condition is met. Count1: 2, Count2: 1"
Code & Formulas
Notes:-
1. Do not make any type of edit or modification to this file. Follow the
steps provided below exactly as they are given.
2. First, create a new folder named "VBA Practice Files
3. Inside the "VBA Practice Files" folder, create two new folders named
"Input"
4. Now, and "Output
within the "VBA Practice Files" folder, create a new Excel file
named "VBA Learning Journey". Include your name and employee ID in
the format "VBA Learning Journey_Zubair Mistry_CI14602", and save it
in xlsm format so that VBA coding can be done

asic Code

fic Excel files and sheets in VBA.

e, and use Worksheets to refer to specific sheets

pecific worksheet named "Sheet1"

the worksheet named "Sheet1" to the variable ws.

"Sheet1" to the variable ws.

erencing data ranges in Excel.

d column, respectively.

xlToLeft).Column

mber in column A and lastColumn to the last used column in row 1 of the
 row number.
ow to the last used row in column A of worksheet ws.
he last column number.
n: Set lastColumn to the last used column in row 1 of worksheet ws.

se that value in VBA code.

able cellValue.

A1 to B10 in worksheet ws.

namically in Excel.

A1 to B10
A1 to B10

s and sets the value "Sample" for the range A1 to B10.

 ws to the variable rng.

Formulas

urns a corresponding value in another range.

1C1:R3C1, R1C2:R3C2)"

n of a table and returns a value in the same row from another column.
R1C1:R3C2, 2, FALSE)"

f a table and returns a value in the same column from another row.

R1C2:R2C4, 2, FALSE)"

ns TRUE if they are, otherwise, it returns FALSE.

, R1C1<6)"
 returns TRUE if any argument is TRUE, otherwise, it returns FALSE.

R1C1=15)"

nge that meet a single condition.

1:R4C1, ""Apple"")"

multiple criteria.
C1:R4C1, ""Apple"", R1C2:R4C2, ""Red"")"
on or criteria.

R4C1, ""Apple"", R1C2:R4C2)"

ditions or criteria.

2:R4C2, R1C1:R4C1, ""Apple"")"

s one value if TRUE, and another value if FALSE.

""Greater"", ""Not Greater"")"

in a single formula.

""Low"", IF(R1C1<=15, ""Medium"", ""High""))"

tes to an error; otherwise, it returns the value of the expression.

1/R1C2, ""Error"")"

ror, and FALSE otherwise.

ALSE otherwise.

or array, selected by the row and column number indexes.

R3C2, 2, 2)"

rns the relative position of that item.

ana"", R1C1:R3C1, 0)"

ok up values in a table.

R1C1 notation
R3C2, MATCH(""Banana"", R1C1:R3C1, 0), 2)"
 a text string, based on the number of characters specified.

a text string, based on the number of characters specified.

m a text string, starting at the position you specify.

 which a specific character or text string is first found, starting from the leftmost character.

"", R1C1)"

t in a text string.

1C1 notation
(R1C1, ""p"", ""b"")"

nto one string.

R1C1 notation
TE(R1C1, "" "", R1C2)"

""0.0%"")"

umber to a number.
ut you can clear cell contents using VBA.
sed on an index number.

C1, ""Apple"", ""Banana"", ""Cherry"")"

iple Formulas

A and use IF to modify the result.

ng R1C1 notation
(2, R1C1:R3C2, 2, FALSE)=""Banana"", ""Fruit"", ""Not Fruit"")"

in cell B20 using R1C1 notation

(R1C1, LEN(R1C1)-1), 3)"

ISERROR to conditionally handle errors. This combination is useful for constructing strings

with a space
 lumn numbers and MATCH to find the row or column number. Use IFERROR to handle errors gracefully.

rst column

avoid errors

value from column B

column B (row 2, which is 20) is displayed. If not, a message "Value not found" is

mbine with AND for additional conditional checks.

ange, criteriaRange, "Category1"))

ombine with OR for multiple conditions.

ge("A1:A4") = "Apple"), 1, 0))
ge("A1:A4") = "Banana"), 1, 0))

" & count1 & ", Count2: " & count2

 Basic Topics
1. Auto Fit Columns
Explanation: Auto fit columns to adjust their width automatically based on the content.
Data: Sample data for demonstrating auto fit columns.
A B C
Departme
Name Location
nt
John Doe IT New York
Jane
HR London
Smith
Michael Finance Sydney
Example Code:
Sub AutoFitColumns()
' Select the range you want to autofit
Range("A1:C1").EntireColumn.AutoFit
End Sub

Result: The columns A, B, and C will adjust their widths to fit the content.

2. Auto Fit Rows

Explanation: Auto fit rows to adjust their height automatically based on the content.
Data: Sample data for demonstrating auto fit rows.
A B C
Departme
Name Location
nt
John Doe IT New York
Jane
HR London
Smith
Michael Finance Sydney
Example Code:
Sub AutoFitRows()
' Select the range you want to autofit
Range("A1:C3").EntireRow.AutoFit
End Sub

Result: The rows 1 to 3 will adjust their heights to fit the content.
3. Formatting
Explanation: Apply various formatting styles such as coloring, font, interior, bold font, and line style.
Coloring:-
Data: Sample data for applying coloring.
A B C
Departme
Name Location
nt
John Doe IT New York
Jane
HR London
Smith
Michael Finance Sydney
Example Code:
Sub ApplyColoring()
' Apply background color to range A1:C1
Range("A1:C1").Interior.Color = RGB(255, 255, 0) ' Yellow color
End Sub

Result: The range A1

will have a yellow background color.
Font:-
Example Code:
Sub ApplyFont()
' Apply font style to range A1:C1
With Range("A1:C1").Font
.Name = "Arial"
.Size = 12
.Color = RGB(0, 0, 255) ' Blue color
End With
End Sub

Result: The range A1

will have Arial font, size 12, and blue color.
Interior:-
Example Code:
Sub ApplyInterior()
' Apply interior color to range A1:C1
Range("A1:C1").Interior.Color = RGB(192, 192, 192) ' Light gray color
End Sub

Result: The range A1

will have a light gray interior color.
Bold Font:-
Example Code:
Sub ApplyBoldFont()
' Apply bold font to range A1:C1
Range("A1:C1").Font.Bold = True
End Sub

Result: The range A1

will have bold font.
LineStyle:-
Example Code:
Sub ApplyLineStyle()
' Apply border style to range A1:C3
With Range("A1:C3").Borders
.LineStyle = xlContinuous
.Color = RGB(0, 0, 0) ' Black color
.Weight = xlThin
End With
End Sub

Result: The range A1

will have a continuous black border.

4. Apply and Remove Text Wrap

Explanation: Apply and remove text wrap to fit long text within a cell.
Data: Sample data for text wrap.
A B C
Departme
Name Location
nt
John Doe IT New York
Jane
HR London
Smith
Michael Finance Sydney
Example Code:
Sub ApplyTextWrap()
' Apply text wrap to range A1:C1
Range("A1:C1").WrapText = True
End Sub

Sub RemoveTextWrap()
' Remove text wrap from range A1:C1
Range("A1:C1").WrapText = False
End Sub

Result: Text wrap will be applied or removed from the range A1

5. Merge and Unmerge Cell
Explanation: Merge and unmerge cells to combine multiple cells into one or split a merged cell back into
Data: Sample data for merging cells.
A B C
Departme
Name Location
nt
John Doe IT New York
Jane
HR London
Smith
Michael Finance Sydney
Example Code:
Sub MergeCells()
' Merge cells in range A1:C1
Range("A1:C1").Merge
End Sub

Sub UnmergeCells()
' Unmerge cells in range A1:C1
Range("A1:C1").UnMerge
End Sub

Result: Cells in range A1

will be merged or unmerged.

6. Remove Duplicate
Explanation: Remove duplicate values from a range.
Data: Sample data with duplicates.
A B C
Departme
Name Location
nt
John Doe IT New York
Jane
HR London
Smith
John Doe IT New York
Michael Finance Sydney
Example Code:
Sub RemoveDuplicates()
' Remove duplicates from range A1:C4
Range("A1:C4").RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xl
End Sub

Result: Duplicates in the range A1

will be removed.

7. Sorting (Ascending, Descending)

Explanation: Sort data in ascending or descending order.
Data: Sample data for sorting.
A B C
Departme
Name Location
nt
John Doe IT New York
Jane
HR London
Smith
Michael Finance Sydney
Example Code:
Sub SortAscending()
' Sort range A1:C3 in ascending order by Name
Range("A1:C3").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:
End Sub

Sub SortDescending()
' Sort range A1:C3 in descending order by Name
Range("A1:C3").Sort Key1:=Range("A1"), Order1:=xlDescending, Heade

Result: The data in the range A1

will be sorted in ascending or descending order based on the Name column.

8. Text To Column
Explanation: Split the text in a single column into multiple columns based on a delimiter.
Data: Sample data for text to column.
A
Name,De
partment,
Location
John
Doe,IT,Ne
w York
Jane
Smith,HR,
London
Michael,Fi
nance,Syd
ney

Example Code:
 Sub TextToColumn()
' Split the text in column A into multiple columns using a comma as the
Range("A1:A4").TextToColumns Destination:=Range("A1"), DataType:=x
ConsecutiveDelimiter:=False,_ Tab:=False, Semicolon:=False, Comma:=Tru
End Sub

Result: The text in column A will be split into multiple columns based on the comma delimiter.

9. Types Of Error
Compiler Error:-
Explanation: Compiler errors occur when the code does not comply with the syntax rules of the VBA lang
Example Code:
Sub CompilerErrorExample()
' This code will cause a compiler error due to incorrect syntax
Dim x As Integer
x = 10
' Missing End Sub statement will cause a compiler error

Result: A compiler error will be displayed indicating that there is a syntax error.
Runtime Error:-
Explanation: Runtime errors occur during the execution of the code, usually due to invalid operations or
Example Code:
Sub RuntimeErrorExample()
' This code will cause a runtime error due to division by zero
Dim x As Integer
Dim y As Integer
x = 10
y=0
MsgBox x / y
End Sub

Result: A runtime error will be displayed indicating that division by zero is not possible.
Syntax Error:-
Explanation: Syntax errors occur when the code is written with incorrect syntax.
Example Code:
Sub SyntaxErrorExample()
' This code will cause a syntax error due to incorrect syntax
Dim x As Integer
x = "Hello" ' Assigning a string to an integer variable will cause a syntax
End Sub

Result: A syntax error will be displayed indicating that there is an issue with the code.
Logical Error:-
Explanation: Logical errors occur when the code runs without errors but produces incorrect results due t
Example Code:
Sub LogicalErrorExample()
' This code will cause a logical error due to incorrect logic
Dim x As Integer
Dim y As Integer
x = 10
y = 20
MsgBox "The sum of x and y is " & x - y ' Incorrect logic for addition
End Sub

Result: The message box will display an incorrect result due to the logical error in the code.
sic Topics

the content.
, bold font, and line style.

0) ' Yellow color

192) ' Light gray color

or split a merged cell back into individual cells.

rray(1, 2, 3), Header:=xlYes

1:=xlAscending, Header:=xlYes

1:=xlDescending, Header:=xlYes

on a delimiter.
s using a comma as the delimiter
ange("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
olon:=False, Comma:=True, Space:=False, Other:=False

he comma delimiter.

he syntax rules of the VBA language.

correct syntax

ly due to invalid operations or data.

ision by zero

not possible.

rrect syntax

riable will cause a syntax error

roduces incorrect results due to logical flaws.

rrect logic

ct logic for addition

error in the code.

 Advanced Topics
1. Auto Filter
Apply Autofilter:-
Explanation: Autofilter is used to filter data in Excel. In VBA, you can apply an autofilter to a range of dat
Data: Let's assume we have data in Range A1
Example Code:

Sub ApplyAutoFilter()
' Select the range where you want to apply the filter
Range("A1:C10").AutoFilter
End Sub

Result: The autofilter will be applied to the range A1

Remove Autofilter:-
Explanation: Removing an autofilter clears all the applied filters from a range.
Example Code:
Sub RemoveAutoFilter()
' Check if there is an autofilter applied
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
End Sub

Steps and Result: Same as above. Running this macro will remove any autofilter applied on the active sh

2. Sheets
Add:-
Explanation: Adding a new worksheet to the workbook.
Example Code:
Sub AddSheet()
Sheets.Add After:=Sheets(Sheets.Count)
End Sub

Steps and Result: Running this macro will add a new worksheet at the end of the existing worksheets.

Delete:-

Explanation: Deleting a specific worksheet.

Example Code:
Sub DeleteSheet()
Application.DisplayAlerts = False
Sheets("SheetName").Delete
Application.DisplayAlerts = True
End Sub

Steps and Result: Replace "SheetName" with the actual sheet name you want to delete. This will delete
message.

Copy:-
Explanation: Copying a worksheet within the same workbook.
Example Code:
Sub CopySheet()
Sheets("SheetName").Copy After:=Sheets(Sheets.Count)
End Sub

Steps and Result: Replace "SheetName" with the actual sheet name you want to copy. This will create a

Move:-
Explanation: Moving a worksheet to a different position in the workbook.
Example Code:
Sub MoveSheet()
Sheets("SheetName").Move Before:=Sheets(1)
End Sub

Steps and Result: Replace "SheetName" with the actual sheet name you want to move. This will move t

Save As PDF:-
Explanation: Saving a specific worksheet as a PDF.
Example Code:
Sub SaveAsPDF()
Sheets("SheetName").ExportAsFixedFormat Type:=xlTypePDF, Filenam
End Sub

Steps and Result: Replace "SheetName" with the actual sheet name and "C:\Path\SheetName.pdf" with
specified sheet as a PDF file.

Insert Multiple Sheets:-

Explanation: Inserting multiple sheets at once.
Example Code:
Sub InsertMultipleSheets()
Sheets.Add Count:=3
End Sub
Steps and Result: Running this macro will add three new worksheets to the workbook.

Protect All Worksheets:-

Explanation: Protecting all worksheets in the workbook.
Example Code:
Sub ProtectAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="yourpassword"
Next ws
End Sub

Steps and Result: Replace "yourpassword" with the desired password. Running this macro will protect a

UnProtect Worksheet:-
Explanation: Unprotecting a specific worksheet.
Example Code:
Sub UnProtectSheet()
Sheets("SheetName").Unprotect Password:="yourpassword"
End Sub

Steps and Result: Replace "SheetName" with the actual sheet name and "yourpassword" with the corre

Delete_Blank_Sheet:-
Explanation: Deleting all blank worksheets in the workbook.
Example Code:
Sub DeleteBlankSheets()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If WorksheetFunction.CountA(ws.UsedRange) = 0 Then ws.Delete
Next ws
Application.DisplayAlerts = True
End Sub

Steps and Result: Running this macro will delete all blank worksheets in the workbook without displayin

3. IF Condition
IF…EndIF:-
Explanation: Basic If condition to perform a task if a condition is met.
Example Code:
Sub SimpleIf()
 Dim x As Integer
x = 10
If x > 5 Then
MsgBox "x is greater than 5"
End If
End Sub

Steps and Result: Running this macro will display a message box if the value of x is greater than 5.

IF…Else…EndIF:-
Explanation: If-Else condition to perform tasks based on whether the condition is met or not.
Example Code:
Sub IfElse()
Dim x As Integer
x=3
If x > 5 Then
MsgBox "x is greater than 5"
Else
MsgBox "x is not greater than 5"
End If
End Sub

Steps and Result: Running this macro will display a message box saying "x is not greater than 5" because

Nested IF:-
Explanation: Using multiple If conditions within each other.
Example Code:
Sub NestedIf()
Dim x As Integer
x=7
If x > 5 Then
If x < 10 Then
MsgBox "x is between 5 and 10"
Else
MsgBox "x is greater than or equal to 10"
End If
Else
MsgBox "x is 5 or less"
End If
End Sub

Steps and Result: Running this macro will display a message box saying "x is between 5 and 10" because

4. Loops
For Next:-
Explanation: Looping a specific number of times.
Example Code:
Sub ForNextLoop()
Dim i As Integer
For i = 1 To 5
Cells(i, 1).Value = "Row " & i
Next i
End Sub

Steps and Result: Running this macro will fill cells A1 to A5 with the text "Row 1" to "Row 5".

For Each Next:-

Explanation: Looping through each item in a collection.
Example Code:
Sub ForEachLoop()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A1").Value = "Sheet Name: " & ws.Name
Next ws
End Sub

Steps and Result: Running this macro will set the value of cell A1 in each worksheet to the sheet's name

Do While:-
Explanation: Looping as long as a condition is true.
Example Code:
Sub DoWhileLoop()
Dim i As Integer
i=1
Do While i <= 5
Cells(i, 1).Value = "Row " & i
i=i+1
Loop
End Sub

Steps and Result: Running this macro will fill cells A1 to A5 with the text "Row 1" to "Row 5".

Do Until:-
Explanation: Looping until a condition becomes true.
Example Code:
Sub DoUntilLoop()
Dim i As Integer
i=1
Do Until i > 5
 Cells(i, 1).Value = "Row " & i
i=i+1
Loop
End Sub

Steps and Result: Running this macro will fill cells A1 to A5 with the text "Row 1" to "Row 5".

5. Error Handling
On Error Resume Next:-
Explanation: Skipping the line that causes an error and continuing with the next line.
Example Code:
Sub ResumeNext()
On Error Resume Next
' This will cause an error if there is no "Sheet1"
Sheets("Sheet1").Delete
' This line will still execute
MsgBox "Continuing after error"
End Sub

Steps and Result: Running this macro will try to delete "Sheet1". If it doesn't exist, it will skip the error a

On Error Goto Label:-

Explanation: Redirecting code execution to a label when an error occurs.
Example Code:
Sub GotoLabel()
On Error GoTo ErrorHandler
' This will cause an error if there is no "Sheet1"
Sheets("Sheet1").Delete
Exit Sub
ErrorHandler:
MsgBox "An error occurred"
End Sub

Steps and Result: Running this macro will try to delete "Sheet1". If it doesn't exist, it will display the mes

On Error Goto 0:-

Explanation: Turning off error handling.
Example Code:
Sub GotoZero()
On Error Resume Next
' This will cause an error if there is no "Sheet1"
Sheets("Sheet1").Delete
On Error GoTo 0
' This line will cause the macro to stop if there is an error
 Sheets("Sheet2").Delete
End Sub

Steps and Result: Running this macro will try to delete "Sheet1" and then "Sheet2". If either doesn't exis

On Error Goto -1:-

Explanation: Clearing the error state.
Example Code:
Sub GotoNegativeOne()
On Error Resume Next
' This will cause an error if there is no "Sheet1"
Sheets("Sheet1").Delete
On Error GoTo -1
' This line will still execute even if there's an error
MsgBox "Continuing after clearing error state"
End Sub

Steps and Result: Running this macro will try to delete "Sheet1". If it doesn't exist, it will clear the error

Goto Jump:-
Explanation: Using GoTo for control flow.
Example Code:
Sub GoToJump()
Dim x As Integer
x=1
If x = 1 Then GoTo JumpHere
MsgBox "This will be skipped"
JumpHere:
MsgBox "This is after the jump"
End Sub

Steps and Result: Running this macro will skip the first message box and display "This is after the jump".
nced Topics

an autofilter to a range of data.

ofilter applied on the active sheet.

of the existing worksheets.

want to delete. This will delete the specified worksheet without displaying a warning

ts.Count)

want to copy. This will create a copy of the specified sheet at the end of the workbook.

want to move. This will move the specified sheet to the first position in the workbook.

pe:=xlTypePDF, Filename:="C:\Path\SheetName.pdf"

C:\Path\SheetName.pdf" with the desired path and filename. This will save the
e workbook.

nning this macro will protect all worksheets in the workbook with the specified password.

ourpassword"

yourpassword" with the correct password. This will unprotect the specified worksheet.

) = 0 Then ws.Delete

e workbook without displaying any warning messages.

ue of x is greater than 5.

ition is met or not.

is not greater than 5" because the value of x is 3.

is between 5 and 10" because the value of x is 7.

Row 1" to "Row 5".

worksheet to the sheet's name.

Row 1" to "Row 5".

Row 1" to "Row 5".

n't exist, it will skip the error and display the message box.

n't exist, it will display the message box "An error occurred".

s an error
"Sheet2". If either doesn't exist, it will handle the first error but stop at the second.

n't exist, it will clear the error state and display the message box.

splay "This is after the jump".

 Advanced Concep
1. Create Folder with FSO
Explanation:

FSO (FileSystemObject) is a powerful tool in VBA that allows you to manipulate files and folders. You
among other operations.
Data:
We'll use a base directory, C:\ExampleFolder, for our operations.
Example Codes:
1. Create Folder:
Objective: Create a folder if it doesn't exist.
Code:
Sub CreateFolder()
Dim fso As Object
Dim folderPath As String

' Define the folder path

folderPath = "C:\ExampleFolder"

' Create FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")

' Check if folder exists

If Not fso.FolderExists(folderPath) Then
' Create folder
fso.CreateFolder(folderPath)
MsgBox "Folder created successfully at " & folderPath
Else
MsgBox "Folder already exists at " & folderPath
End If
End Sub

2. Delete Folder:
Objective: Delete a folder if it exists.
Code:
Sub DeleteFolder()
Dim fso As Object
Dim folderPath As String

' Define the folder path

folderPath = "C:\ExampleFolder"

' Create FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")

' Check if folder exists

 If fso.FolderExists(folderPath) Then
' Delete folder
fso.DeleteFolder folderPath
MsgBox "Folder deleted successfully at " & folderPath
Else
MsgBox "Folder does not exist at " & folderPath
End If
End Sub

3. Copy Folder:
Objective: Copy a folder to a new location.
Code:
Sub CopyFolder()
Dim fso As Object
Dim sourceFolder As String
Dim destinationFolder As String

' Define the folder paths

sourceFolder = "C:\ExampleFolder"
destinationFolder = "C:\ExampleFolderCopy"

' Create FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")

' Check if source folder exists

If fso.FolderExists(sourceFolder) Then
' Copy folder
fso.CopyFolder sourceFolder, destinationFolder
MsgBox "Folder copied successfully from " & sourceFolder & " to
Else
MsgBox "Source folder does not exist at " & sourceFolder
End If
End Sub

4. Check if Folder Exists:

Objective: Check if a folder exists.
Code:
Sub CheckFolderExists()
Dim fso As Object
Dim folderPath As String

' Define the folder path

folderPath = "C:\ExampleFolder"

' Create FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")

' Check if folder exists

If fso.FolderExists(folderPath) Then
MsgBox "Folder exists at " & folderPath
Else
MsgBox "Folder does not exist at " & folderPath
 End If
End Sub

5. Create Text File:

Objective: Create a text file within a folder.
Code:
Sub CreateTextFile()
Dim fso As Object
Dim folderPath As String
Dim textFilePath As String
Dim textFile As Object

' Define the folder path and text file path

folderPath = "C:\ExampleFolder"
textFilePath = folderPath & "\ExampleFile.txt"

' Create FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")

' Check if folder exists

If Not fso.FolderExists(folderPath) Then
' Create folder if it does not exist
fso.CreateFolder(folderPath)
End If

' Create text file

Set textFile = fso.CreateTextFile(textFilePath, True)
textFile.WriteLine "This is an example text file."
textFile.Close

MsgBox "Text file created successfully at " & textFilePath

End Sub

6. Delete Text File:

Objective: Delete a text file within a folder.
Code:
Sub DeleteTextFile()
Dim fso As Object
Dim textFilePath As String

' Define the text file path

textFilePath = "C:\ExampleFolder\ExampleFile.txt"

' Create FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")

' Check if text file exists

If fso.FileExists(textFilePath) Then
' Delete text file
fso.DeleteFile textFilePath
MsgBox "Text file deleted successfully at " & textFilePath
Else
 MsgBox "Text file does not exist at " & textFilePath
End If
End Sub

7. Open Text File:

Objective: Open and read a text file.
Code:
Sub OpenTextFile()
Dim fso As Object
Dim textFilePath As String
Dim textFile As Object
Dim fileContent As String

' Define the text file path

textFilePath = "C:\ExampleFolder\ExampleFile.txt"

' Create FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")

' Check if text file exists

If fso.FileExists(textFilePath) Then
' Open text file
Set textFile = fso.OpenTextFile(textFilePath, 1)
fileContent = textFile.ReadAll
textFile.Close

MsgBox "Text file content: " & vbCrLf & fileContent

Else
MsgBox "Text file does not exist at " & textFilePath
End If
End Sub

Results:
You will see message boxes confirming the creation, deletion, copying, and existence of folders an
Code Explanation:
FileSystemObject (FSO): An object that allows you to work with the file system.
CreateObject("Scripting.FileSystemObject"): Creates an instance of the FSO.
FolderExists(): Checks if a folder exists.
CreateFolder(): Creates a new folder.
DeleteFolder(): Deletes an existing folder.
CopyFolder(): Copies a folder to a new location.
FileExists(): Checks if a file exists.
CreateTextFile(): Creates a new text file.
DeleteFile(): Deletes an existing text file.
OpenTextFile(): Opens and reads a text file.

2. Functions (Operators with functions, FSO with functions)

Explanation:
In VBA, functions are blocks of code that perform specific tasks. You can use operators within functio
use FileSystemObject (FSO) functions to work with files and folders.
In VBA, functions are blocks of code that perform specific tasks. You can use operators within functio
use FileSystemObject (FSO) functions to work with files and folders.
Data:
We'll demonstrate functions using a few simple examples.
Example Codes:
1. Operators with Functions:
Objective: Create a function that adds two numbers.
Code:
Function AddNumbers(a As Double, b As Double) As Double
AddNumbers = a + b
End Function
Sub TestAddNumbers()
Dim result As Double
result = AddNumbers(10, 20)
MsgBox "The sum of 10 and 20 is " & result
End Sub

2. FSO with Functions:

Objective: Create a function that returns the size of a file.
Code:
Function GetFileSize(filePath As String) As Long
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(filePath) Then
GetFileSize = fso.GetFile(filePath).Size
Else
GetFileSize = -1 ' Return -1 if file does not exist
End If
End Function
Sub TestGetFileSize()
Dim filePath As String
Dim fileSize As Long
filePath = "C:\ExampleFolder\ExampleFile.txt"
fileSize = GetFileSize(filePath)
If fileSize <> -1 Then
MsgBox "The size of the file is " & fileSize & " bytes."
Else
MsgBox "File does not exist."
End If
End Sub
Results:
You will see message boxes displaying the results of the function operations, such as the sum of nu
Code Explanation:
Function AddNumbers: Takes two arguments, adds them, and returns the result.
Sub TestAddNumbers: Calls the AddNumbers function and displays the result.
Function GetFileSize: Takes a file path as an argument, checks if the file exists, and returns the file
Sub TestGetFileSize: Calls the GetFileSize function and displays the result.
3. Excel Enhancements
Explanation:
This section covers various advanced Excel features that can be automated using VBA.
Example Codes:

1. Create Pivot Table:

Objective: Create a Pivot Table from a data range.
Code:

Sub CreatePivotTable()
Dim ws As Worksheet
Dim dataRange As Range
Dim pivotTable As PivotTable
Dim pivotCache As PivotCache

' Set the worksheet and data range

Set ws = ThisWorkbook.Sheets("Sheet1")
Set dataRange = ws.Range("A1:D20")

' Create Pivot Cache

Set pivotCache = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=dataRange)

' Create Pivot Table

Set pivotTable = pivotCache.CreatePivotTable( _
TableDestination:=ws.Range("F1"), _
TableName:="PivotTable1")

' Add fields to the Pivot Table

With pivotTable
.PivotFields("Category").Orientation = xlRowField
.PivotFields("Sales").Orientation = xlDataField
End With

MsgBox "Pivot Table created successfully."

End Sub

2. Pivot Charts
Explanation:
Pivot Charts allow you to visualize data from a Pivot Table in a graphical format. You can create Pivo
Example Code:
Objective: Create a Pivot Chart from a Pivot Table.
Code:
Sub CreatePivotChart()
 Dim ws As Worksheet
Dim pivotTable As PivotTable
Dim chartObj As ChartObject

' Set the worksheet and Pivot Table

Set ws = ThisWorkbook.Sheets("Sheet1")
Set pivotTable = ws.PivotTables("PivotTable1")

' Create a Pivot Chart

Set chartObj = ws.ChartObjects.Add(Left:=300, Width:=375, Top:=5
With chartObj.Chart
.SetSourceData Source:=pivotTable.TableRange2
.ChartType = xlColumnClustered
.HasTitle = True
.ChartTitle.Text = "Pivot Chart Example"
End With

MsgBox "Pivot Chart created successfully."

End Sub

3. Create Chart:
Objective: Create a Chart from a data range.
Code:

Sub CreateChart()
Dim ws As Worksheet
Dim chartObj As ChartObject

' Set the worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

' Create a Chart

Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=5
With chartObj.Chart
.SetSourceData Source:=ws.Range("A1:B10")
.ChartType = xlColumnClustered
.HasTitle = True
.ChartTitle.Text = "Sales Chart"
End With

MsgBox "Chart created successfully."

End Sub

4. Sparklines
Explanation:
Sparklines are mini charts within a single cell that provide a visual representation of data trends ove

Example Code:
Objective: Create Sparklines for a range of data.
Code:
 Sub CreateSparklines()
Dim ws As Worksheet

' Set the worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

' Create Sparklines

ws.Range("E1").SparklineGroups.Add Type:=xlSparkLine, SourceDat

MsgBox "Sparklines created successfully."

End Sub

5. Slicer
Explanation:
Slicers are visual filters that make it easier to filter data in Pivot Tables and Pivot Charts.
Example Code:
Objective: Add a Slicer to a Pivot Table.
Code:

Sub AddSlicer()
Dim ws As Worksheet
Dim pivotTable As PivotTable

' Set the worksheet and Pivot Table

Set ws = ThisWorkbook.Sheets("Sheet1")
Set pivotTable = ws.PivotTables("PivotTable1")

' Add Slicer

ws.Shapes.AddSlicer(SlicerCache:=pivotTable.PivotCache.Slicers.Ad
Caption:="Category", Top:=150, Left:=10, Height:=200

MsgBox "Slicer added successfully."

End Sub

6. Apply Conditional Formatting:

Objective: Apply conditional formatting to a range of cells.
Code:

Sub ApplyConditionalFormatting()
Dim ws As Worksheet

' Set the worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

' Apply conditional formatting

With ws.Range("A1:A10").FormatConditions.Add(Type:=xlCellValue,
.Interior.Color = RGB(255, 0, 0) ' Red color for cells greater than 1
End With

MsgBox "Conditional formatting applied successfully."

End Sub
Results:
You will see new Pivot Tables, Charts, and conditional formatting applied to the specified data rang

Code Explanation:
CreatePivotTable: Creates a Pivot Table from a specified data range and adds fields to it.
CreateChart: Creates a Chart from a specified data range and sets its type and title.
ApplyConditionalFormatting: Applies conditional formatting to a specified range based on a condi

7. VBS (Visual Basic Script)

Explanation:
VBS is a scripting language used to automate tasks in Windows. It is often used for simple tasks such
Example Code:
Objective: Create and run a simple VBS script.
Code:

Sub CreateAndRunVBScript()
Dim fso As Object
Dim filePath As String
Dim vbScript As String

' Define the file path

filePath = "C:\ExampleScript.vbs"

' Create the VBScript content

vbScript = "MsgBox ""Hello, this is a VBScript!"""

' Create FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")

' Create and write to the VBS file

With fso.CreateTextFile(filePath, True)
.Write vbScript
.Close
End With

' Run the VBS script

Shell "wscript.exe " & filePath, vbNormalFocus

MsgBox "VBScript created and run successfully."

End Sub

8. User Forms
Explanation:

User Forms in VBA provide a graphical interface for user interaction. You can create forms to gather
interactive tasks.
Example Code:
Objective: Create a simple User Form to collect user input.
Steps:
 1. Open the VBA editor (ALT + F11).
2. Insert a User Form (Insert > UserForm).
3. Add a TextBox and a CommandButton to the User Form.
4. Double-click the CommandButton to open the code window and add the following code:
Private Sub CommandButton1_Click()
MsgBox "Hello, " & TextBox1.Value & "!"
End Sub

5. Add the following code in a module to show the User Form:

Sub ShowUserForm()
UserForm1.Show
End Sub

9. Class Module
Explanation:

Class Modules in VBA allow you to create custom objects and define their properties, methods, and
components.
Example Code:
Objective: Create a simple class to manage student information.
Steps:
1. Open the VBA editor (ALT + F11).
2. Insert a Class Module (Insert > Class Module) and name it "Student".
3. Add the following code in the Class Module:
' Class Module: Student
Public Name As String
Public Age As Integer

Public Function GetDetails() As String

GetDetails = "Name: " & Name & ", Age: " & Age
End Function

4. Add the following code in a standard module to use the class:

Sub TestStudentClass()
Dim stud As Student
Set stud = New Student
stud.Name = "John Doe"
stud.Age = 21
MsgBox stud.GetDetails
End Sub

10. Protect Sheet

Explanation:
Protecting a sheet in Excel prevents users from making changes to the contents of the sheet. You ca
Example Code:
Objective: Protect a worksheet with a password.
 Code:
Sub ProtectSheet()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Protect Password:="password123"
MsgBox "Sheet protected successfully."
End Sub

11. Protect Workbook

Explanation:
Protecting a workbook restricts access to the entire workbook, preventing users from adding, deletin
Example Code:
Objective: Protect a workbook with a password.
Code:
Sub ProtectWorkbook()
ThisWorkbook.Protect Password:="password123"
MsgBox "Workbook protected successfully."
End Sub

Steps for All Codes:

1. Open the VBA editor (ALT + F11).
2. Insert a new module (Insert > Module) or the specified type (e.g., UserForm, Class Module).
3. Copy and paste the code into the module or specified type.
4. Run the macro (F5).
Results:
Pivot Charts: A Pivot Chart is created based on a Pivot Table.
Sparklines: Sparklines are added to a specified range.
Slicer: A Slicer is added to filter data in a Pivot Table.
VBS: A VBScript is created and executed.
User Forms: A User Form is displayed, collecting user input.
Class Module: A custom class is created and used to manage student information.
Protect Sheet: A worksheet is protected with a password.
Protect Workbook: The entire workbook is protected with a password.
Code Explanation:
Pivot Charts: Creates a graphical representation of Pivot Table data.
Sparklines: Adds small charts within cells to show data trends.
Slicer: Adds interactive filters to Pivot Tables.
VBS: Demonstrates creating and running a VBScript.
User Forms: Provides a graphical interface for user interaction.
Class Module: Demonstrates creating and using custom classes in VBA.
Protect Sheet: Prevents changes to a worksheet.
Protect Workbook: Restricts access to the entire workbook.

4. Consolidation (Worksheet To Worksheet, Worksheet to Another

Explanation:
Consolidation involves combining data from multiple worksheets or workbooks into a single workshe
Example Codes:
1. Consolidate Worksheet to Worksheet:
Objective: Copy data from one worksheet to another within the same workbook.
Code:
Sub ConsolidateWorksheetToWorksheet()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
' Set the worksheets
Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
Set targetSheet = ThisWorkbook.Sheets("Sheet2")
' Copy data from source to target
sourceSheet.Range("A1:D10").Copy Destination:=targetSheet.Rang
MsgBox "Data consolidated from Sheet1 to Sheet2."
End Sub

2. Consolidate Worksheet to Another Workbook/Worksheet:

Objective: Copy data from a worksheet in one workbook to a worksheet in another workbook.
Code:
Sub ConsolidateWorksheetToAnotherWorkbook()
Dim sourceWorkbook As Workbook
Dim targetWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
' Open the source workbook
Set sourceWorkbook = Workbooks.Open("C:\SourceWorkbook.xlsx")
Set sourceSheet = sourceWorkbook.Sheets("Sheet1")
' Set the target workbook and worksheet
Set targetWorkbook = ThisWorkbook
Set targetSheet = targetWorkbook.Sheets("Sheet1")
' Copy data from source to target
sourceSheet.Range("A1:D10").Copy Destination:=targetSheet.Rang
' Close the source workbook
sourceWorkbook.Close False
MsgBox "Data consolidated from SourceWorkbook to current workb
End Sub

3. Consolidate Workbook to Workbook:

Objective: Combine data from multiple workbooks into a single workbook.
Code:
Sub ConsolidateWorkbookToWorkbook()
Dim sourceWorkbook1 As Workbook
Dim sourceWorkbook2 As Workbook
 Dim targetWorkbook As Workbook
Dim sourceSheet1 As Worksheet
Dim sourceSheet2 As Worksheet
Dim targetSheet As Worksheet
' Open the source workbooks
Set sourceWorkbook1 = Workbooks.Open("C:\SourceWorkbook1.xls
Set sourceWorkbook2 = Workbooks.Open("C:\SourceWorkbook2.xls
Set sourceSheet1 = sourceWorkbook1.Sheets("Sheet1")
Set sourceSheet2 = sourceWorkbook2.Sheets("Sheet1")
' Set the target workbook and worksheet
Set targetWorkbook = ThisWorkbook
Set targetSheet = targetWorkbook.Sheets("Sheet1")
' Copy data from source workbooks to target workbook
sourceSheet1.Range("A1:D10").Copy Destination:=targetSheet.Ran
sourceSheet2.Range("A1:D10").Copy Destination:=targetSheet.Ran
' Close the source workbooks
sourceWorkbook1.Close False
sourceWorkbook2.Close False
MsgBox "Data consolidated from SourceWorkbook1 and SourceWork
End Sub
Results:
Data from one worksheet is copied to another within the same workbook, or from one workbook t
Code Explanation:
ConsolidateWorksheetToWorksheet: Copies data from one worksheet to another within the same
ConsolidateWorksheetToAnotherWorkbook: Copies data from a worksheet in one workbook to a
ConsolidateWorkbookToWorkbook: Combines data from multiple workbooks into a single workbo
ced Concepts

ulate files and folders. You can create, delete, copy folders, and check if a folder exists,
sourceFolder & " to " & destinationFolder

sourceFolder
xtFilePath

textFilePath
nd existence of folders and text files as per the code executed.

se operators within functions to perform calculations or manipulate data. You can also
se operators within functions to perform calculations or manipulate data. You can also

As Double

ons, such as the sum of numbers or the size of a file.

exists, and returns the file size.

d using VBA.

rmat. You can create Pivot Charts to analyze and compare data more effectively.
 Width:=375, Top:=50, Height:=225)

Width:=375, Top:=50, Height:=225)

ntation of data trends over a range.

parkLine, SourceData:=ws.Range("A1:A10")

Pivot Charts.

ivotCache.Slicers.Add, SourceName:="Category", _
ft:=10, Height:=200, Width:=100)

d(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=10")

r cells greater than 10
 to the specified data ranges.

adds fields to it.

d range based on a condition.

used for simple tasks such as file manipulation, registry editing, and automation.

an create forms to gather user input, display information, and perform other
 the following code:

properties, methods, and events. This helps in organizing code and creating reusable

tents of the sheet. You can set a password to restrict access.

users from adding, deleting, or renaming sheets.

rForm, Class Module).

sheet to Another Workbook/Worksheet, Workbook To Workbook)

ooks into a single worksheet or workbook.

:=targetSheet.Range("A1")

et in another workbook.

urceWorkbook.xlsx")

:=targetSheet.Range("A1")

ok to current workbook."
ourceWorkbook1.xlsx")
ourceWorkbook2.xlsx")

n:=targetSheet.Range("A1")
n:=targetSheet.Range("E1")

ok1 and SourceWorkbook2 to current workbook."

k, or from one workbook to another workbook.

o another within the same workbook.

eet in one workbook to a worksheet in another workbook.
books into a single workbook.