VBA Code Explained — EUI Mid-Term 2

Basic syntax • Every QB code explained • Experiment 3 code explained • Macros

PART 1 — What is VBA and What are Macros?

What is VBA?
VBA stands for Visual Basic for Applications. It is a programming language built into Microsoft Office apps like Excel,
Word, and Access. It lets you write code to automate tasks, build forms, manipulate data, and control the app — all from
inside Excel itself.
VB6 (what your EUI course covers) and VBA are almost identical in syntax. The only difference is VBA runs inside
Excel/Office, while VB6 runs as a standalone desktop app. So everything you learn in EUI applies directly to VBA too.

What is a Macro?
A Macro is simply a saved piece of VBA code that performs a task automatically. Instead of doing something manually
step by step, you record or write a macro and run it with one click.
Simple example: Instead of manually formatting 500 rows every Monday, you write one macro that does it in 1
second.
Where macros live: Inside the VBA Editor (Alt + F11 in Excel). They are stored in Modules, UserForms, or Sheet
code.
How to run a macro: Developer tab → Macros → Select name → Run. Or assign it to a button.
.xlsm files: The .xlsm extension means 'Excel Macro-Enabled Workbook'. Regular .xlsx files cannot store macros.
Your experiment file E041_EUI_Exp3 is .xlsm because it has VBA code inside.

Where does VBA code live in Excel?

Module: A plain code file for general Sub and Function procedures. Like a .bas module in VB6.
UserForm: A form you design visually — with buttons, text boxes, dropdowns etc. Your experiment is a UserForm.
Sheet code: Code attached to a specific worksheet — runs when something happens on that sheet.
ThisWorkbook: Code attached to the entire workbook — runs on open, close etc.

Key VBA-specific things not in VB6

Worksheet: An Excel sheet. You access it as an object: Dim sh As Worksheet
ThisWorkbook: Refers to the Excel file that contains this VBA code.
Sheets("Name"): Access a specific sheet by its name tab.
Range("A1"): A cell or group of cells in a sheet.
Cells(row, col): A specific cell by row number and column number or letter.
[Link]: Total number of rows in the sheet (used to find the last filled row).
.End(xlUp).Row: Excel trick — start from the bottom, go up until you hit data. Gives you the last used row number.
Me: Refers to the current UserForm itself. [Link] = the txtName control on this form.
 PART 2 — VBA Basic Syntax (Same as VB6)

1. Variables — Declaring and Assigning

In C++ you write: int x = 5; → In VB/VBA you write it in two steps:
VB / VBA Code What it means (plain English)
Dim x As Integer Dim = declare a variable. x = name. As Integer = data
type (whole number). Like: int x; in C++
x=5 Assign value 5 to x. Same as x = 5 in C++.

Dim name As String String = text data type. Like: string name; in C++
name = "Prerak" Strings go in double quotes. Same as C++.

Dim sh As Worksheet Worksheet is a VBA object type. Like declaring an

object in C++. sh is just the variable name.
Set sh = [Link]("DataSheet") Set = used when assigning OBJECTS (not simple
values). Attach the DataSheet worksheet to variable sh.
Now sh refers to that sheet.

Dim lr As Long Long = bigger whole number. Like long in C++. lr will
store the last row number.

2. If / ElseIf / Else — no curly braces

VB / VBA Code What it means (plain English)
If score >= 90 Then If condition is true, run the block below. Then replaces
{ in C++
MsgBox "A Grade" Show a popup message. MsgBox = cout + popup in VB.
ElseIf score >= 60 Then If first condition false, check this. Like else if in C++
Else If none of the above matched. Like else in C++
End If End of the If block. Like closing } in C++

3. With block — shortcuts for repeated object access

In VBA you often set multiple properties of the same object. Instead of writing the object name every time, use With:
VB / VBA Code What it means (plain English)
With sh Start a With block for object sh (the worksheet).
Everything inside uses sh automatically.
.Cells(1, "A").Value = "Hello" The dot . means [Link](1,"A").Value = "Hello". The
With saves you from typing sh every line.
.Cells(2, "B").Value = "World" Again, dot = sh. Setting value of cell B2.
End With End of With block.

4. IsNumeric — checking if input is a number

VB / VBA Code What it means (plain English)
IsNumeric([Link]) Built-in VBA function. Returns True if the value is a
number, False if it contains letters or symbols.
 If IsNumeric([Link]) = False Then If the roll no entered is NOT a number, run the block
below.
MsgBox "Please enter Numeric Roll No!" Show error popup telling user to enter a number.
End If End of If block.

5. Call — running another Sub

VB / VBA Code What it means (plain English)
Call refresh_data Run (call) another Sub named refresh_data. Like calling
a function in C++. The word Call is optional — just
writing refresh_data works too.

6. Array() — creating a list of items

VB / VBA Code What it means (plain English)
[Link] = Array("[Link]", "[Link]", Array() creates a list of values. This fills the dropdown
"BTI") cmbCourse with three options: [Link], [Link], BTI.
[Link] = Array("A+", "A-", "B+", "B-") Fills the blood group dropdown with these options.

7. .Value — reading what user typed

VB / VBA Code What it means (plain English)
[Link] Gets whatever text the user typed into the txtName text
box on the form.
[Link] = "" Sets the text box back to empty (clears it). Like resetting
a field after saving.
[Link] For a radio button, Value = True means it is selected.
Value = False means not selected.
[Link] = False Unselect the Male radio button (deselect it).
PART 3 — Experiment 3 Code: Every Line Explained

Your experiment built a Student Data Entry UserForm in Excel. It has: a Name text box (txtName), Roll No text box
(txtRollNo), Course dropdown (cmbCourse), Gender radio buttons (rdbMale, rdbfemale), Blood Group dropdown
(cmbBG), a Save button (cmdSave), and a ListBox (lbData) to display saved records. Data is saved to a sheet called
DataSheet.

Sub 1 — cmbBG_Change() : Runs when blood group dropdown changes

VB / VBA Code What it means (plain English)
Private Sub cmbBG_Change() This Sub runs automatically whenever the user changes
the selection in the cmbBG dropdown. Private = only this
form can use it.
End Sub Empty — no code inside. Sir created the event but left it
blank (placeholder for future use).

Sub 2 — cmdSave_Click() : Runs when Save button is clicked

VB / VBA Code What it means (plain English)
Private Sub cmdSave_Click() This Sub runs automatically when user clicks the
cmdSave button. This is the main data saving logic.

Dim sh As Worksheet Declare sh as a Worksheet object variable. sh will refer

to the DataSheet.
Set sh = [Link]("DataSheet") Attach the sheet named "DataSheet" from this workbook
to variable sh. Now sh IS that sheet.

Dim le As Long Declare le as a Long number. (Note: sir declared 'le' but
actually uses 'lr' below — lr was never declared with
Dim but VB allows it. lr will hold the last row number.)
lr = Sheets("DataSheet").Range("A" & Find the last row that has data in column A. How it
[Link]).End(xlUp).Row works: [Link] = total rows in sheet (1048576). "A"
& [Link] = "A1048576" = bottom of column
A. .End(xlUp) = go UP from the bottom until you find
data. .Row = get that row number. So lr = last filled row
in column A.

If ([Link] = "") Then Check if the Name text box is empty. "" = empty string.
If user left it blank, show error.
MsgBox "Please Enter Name!", vbCritical Show error popup with red X icon. vbCritical = the
style/icon of the message box (shows a critical error
icon).
End If End of Name validation check.

If ([Link] = "") Then Check if Roll No is empty.

MsgBox "Please Enter Roll No!", vbCritical Show error popup if empty.
End If End of Roll No empty check.

If IsNumeric([Link]) = False Then Check if Roll No contains non-numeric characters.

 IsNumeric returns False if user typed letters like 'abc'
instead of a number.
MsgBox "Please enter Numeric Roll No!", Tell user Roll No must be a number.
vbCritical
End If End of numeric check.

' Data Entry Comment — the actual saving to sheet starts below.
With sh Start With block for sh (DataSheet). Every . below
means sh.
.Cells(lr + 1, "A").Value = [Link] Save name to column A, next empty row (lr+1). Me =
this UserForm. [Link] = what user typed in
name field.
.Cells(lr + 1, "B").Value = Save roll number to column B, same row.
[Link]
.Cells(lr + 1, "C").Value = Save selected course from dropdown to column C.
[Link]

If ([Link]) = True Then Check if Male radio button is selected (True = selected).
.Cells(lr + 1, "D").Value = "Male" Save text "Male" to column D.
Else If Male is not selected, assume Female.
.Cells(lr + 1, "D").Value = "Female" Save "Female" to column D.
End If End of gender check.

.Cells(lr + 1, "E").Value = [Link] Save selected blood group from dropdown to column E.
End With End of With block. All 5 columns for this student are
now saved.

MsgBox "Data Entry Successful" Show success popup to tell user data was saved.

[Link] = "" Clear the Name field after saving — ready for next entry.
[Link] = "" Clear Roll No field.
[Link] = "" Reset course dropdown to blank.
[Link] = False Unselect Male radio button.
[Link] = False Unselect Female radio button.
[Link] = "" Reset blood group dropdown.

Call refresh_data Call the refresh_data Sub to update the ListBox with the
newly saved record.
End Sub End of cmdSave_Click.

Sub 3 — UserForm_Activate() : Runs when form first opens

VB / VBA Code What it means (plain English)
Private Sub UserForm_Activate() This runs automatically when the UserForm is
opened/activated. Like Form_Load in VB6. Used to set
 up dropdowns and load existing data.

[Link] = Array("[Link]", "[Link]", Fill the Course dropdown with these 3 options when
"BTI") form opens.
[Link] = Array("A+", "A-", "B+", "B-", Fill Blood Group dropdown with all 8 blood type
"AB+", "AB-", "O+", "O-") options.

Call refresh_data Run refresh_data to load any existing records into the
ListBox immediately when form opens.
End Sub End of UserForm_Activate.

Sub 4 — refresh_data() : Updates the ListBox with all saved records

VB / VBA Code What it means (plain English)
Sub refresh_data() A regular Sub (not Private, so can be called from
anywhere). Its job is to reload the ListBox with all data
from DataSheet.

Dim sh As Worksheet Declare sh as a Worksheet variable.

Set sh = [Link]("DataSheet") Point sh to the DataSheet.

Dim le As Long Declare le as Long. (Again, lr is actually used below —

same note as before.)
lr = Sheets("DataSheet").Range("A" & Find last filled row in column A of DataSheet. Same
[Link]).End(xlUp).Row technique as in cmdSave — go from bottom up to find
last data row.

With [Link] Start With block for lbData — the ListBox control on
this form that shows all saved records.
.ColumnCount = 5 Tell the ListBox to display 5 columns (Name, RollNo,
Course, Gender, BloodGroup).
.ColumnHeads = False Don't show column headers in the ListBox. False = no
headers shown.
.ColumnWidths = "100,100,100,100,100" Set each column 100 units wide. 5 columns, all equal
width.
.RowSource = "DataSheet!A1:E" & lr Tell ListBox where to get its data from. "DataSheet!
A1:E" & lr builds a range like "DataSheet!A1:E5" if
lr=5. This means: show all data from A1 to E(last row) of
DataSheet. ListBox updates automatically.
End With End of With block.
End Sub End of refresh_data. ListBox now shows all saved
student records.
 PART 4 — What the Experiment Does: Big Picture

Flow of the entire program

VB / VBA Code What it means (plain English)
Step 1: Form Opens UserForm_Activate fires. Dropdowns get filled. Existing
records load into ListBox.
Step 2: User fills form Types name, roll no, picks course, selects gender radio
button, picks blood group.
Step 3: Clicks Save cmdSave_Click fires. Validates inputs (empty check,
numeric check). Saves data to next empty row in
DataSheet.
Step 4: Form resets All fields cleared. refresh_data called — ListBox updates
to show the new record.
Step 5: Repeat User can enter another student. Data keeps appending to
new rows.

DataSheet structure (what gets saved)

VB / VBA Code What it means (plain English)
Column A Student Name — from txtName
Column B Roll Number — from txtRollNo
Column C Course — from cmbCourse dropdown
Column D Gender — "Male" or "Female" based on radio button
Column E Blood Group — from cmbBG dropdown

Key concepts your sir might ask about

UserForm vs Worksheet: UserForm = the form the user sees and fills. Worksheet = the sheet where data is actually
stored. They are separate — form collects, sheet stores.
lr + 1 logic: lr = last row with data. lr+1 = the next empty row. Every new student gets saved one row below the last.
This is how data keeps appending without overwriting.
End(xlUp).Row: This is the standard VBA trick to find the last used row. Start from absolute bottom, go up until data
is found.
Me keyword: Me refers to the UserForm itself. [Link] = the txtName control on this form. Avoids ambiguity
when there are multiple forms.
With block purpose: Avoids repeating sh. every line. [Link](1,A), [Link](1,B)... becomes .Cells(1,A), .Cells(1,B)...
inside With sh. Cleaner code.
Call refresh_data: Called twice — once on form open (load existing data) and once after save (show new record).
Keeps ListBox always up to date.
vbCritical: A constant for MsgBox that shows a red X error icon. Makes the error message look more serious. Other
options: vbInformation (blue i), vbQuestion (?)
PART 5 — Every QB Code Explained Line by Line

Every code block from the Question Bank is below. Left = code, Right = what it means in plain English.

Q1 — Basic Sub procedure

VB / VBA Code What it means (plain English)
Sub ShowGreeting(name As String) Define a Sub called ShowGreeting. It takes one input
called name which must be text (String).
MsgBox "Hello, " & name Show a popup. & joins "Hello, " with the name passed. If
name="Alice" → Hello, Alice
End Sub End of Sub.

Call ShowGreeting("Alice") Run the Sub and pass "Alice" as the name. Output:
Hello, Alice

Q2 — Sub vs Function
VB / VBA Code What it means (plain English)
Sub ShowMsg(msg As String) Sub — performs action, no return value.
MsgBox msg Show whatever msg is in a popup.
End Sub End of Sub.

Function Square(n As Integer) As Integer Function — takes Integer input, returns an Integer. 'As
Integer' at end = return type.
Square = n * n Return n*n by assigning to function name. VB rule:
return by assigning to own name.
End Function End of Function.

result = Square(5) Call function with 5. Gets back 25. result is now 25.

Q3 — ByVal vs ByRef
VB / VBA Code What it means (plain English)
Sub TestVal(ByVal x As Integer) ByVal = x is a COPY. Changes inside stay inside —
original safe.
x = x + 10 Change the copy only.
End Sub End. Original unchanged.

Sub TestRef(ByRef x As Integer) ByRef = x IS the original. Changes here affect the
original outside.
x = x + 10 Original variable also becomes x+10.
End Sub End.

Dim n As Integer : n = 5 Declare n = 5. Colon : = two statements on one line.

TestVal n n=5 passed as copy. Inside becomes 15. n outside still =
5.
TestRef n n=5 passed as original. Inside becomes 15. n outside = 15
now.

Q4 — Scope: Public Function in Module

VB / VBA Code What it means (plain English)
' In [Link] — any form can call this Comment: placed in a module so all forms can use it.
Public Function FullName(f As String, l As Public = accessible from anywhere. Takes first name f
String) As String and last name l. Returns a String.
FullName = f & " " & l Join: first + space + last. E.g., "Raj" & " " & "Shah" =
"Raj Shah". This is returned.
End Function End.

[Link] = FullName([Link], Call function with values from text boxes. Set result as
[Link]) the label's displayed text.

Q5 — Optional Parameters
VB / VBA Code What it means (plain English)
Function Greet(name As String, Optional msg msg is optional. If not provided, default "Hello" is used
As String = "Hello") As String automatically.
Greet = msg & ", " & name Join msg + ", " + name. E.g., "Hello, Prerak"
End Function End.

MsgBox Greet("Prerak") msg not given → uses default "Hello". Output: Hello,
Prerak
MsgBox Greet("Prerak", "Hi") msg="Hi" overrides default. Output: Hi, Prerak

Q8 — Form Lifecycle: QueryUnload

VB / VBA Code What it means (plain English)
Private Sub Form_QueryUnload(Cancel As Runs automatically BEFORE form closes. Cancel and
Integer, UnloadMode As Integer) UnloadMode passed by VB.
If MsgBox("Exit?", vbYesNo) = vbNo Then Show Yes/No popup. If user clicks No, run block below.
Cancel = 1 Setting Cancel=1 stops the form from closing. Form
stays open.
End If End of If.
End Sub If Yes was clicked, Cancel stays 0 and form closes
normally.

Q9 — Load / Show / Hide / Unload

VB / VBA Code What it means (plain English)
Load Form2 Put Form2 in memory. Not visible yet. Form_Load fires.
[Link] Make visible. Loads first if not loaded. Modeless — can
switch forms.
[Link] vbModal Make visible AND lock all other forms. MUST close
Form2 first.
[Link] Make invisible. Still in memory. Data preserved.
Unload Form2 Remove from memory. Form_Unload fires. All data
gone.

Q11 — Sub Main

VB / VBA Code What it means (plain English)
Sub Main() Entry point. Runs first when app launches instead of a
form.
[Link] vbModal Show login form. App waits here until it closes.
If [Link] = True Then After login closes, check if login succeeded.
[Link] Success — open main form.
Else Login failed.
End End = quit the entire application.
End If End of If.
End Sub End of Sub Main.

Q13 — MDI Child

VB / VBA Code What it means (plain English)
Dim child As New frmDocument Create a new instance of frmDocument form. Like
creating an object.
[Link] = "Document 1" Set the title bar text of this child window.
[Link] Display it. Because MDIChild=True, opens INSIDE the
MDI parent.

Q14 — MDI Arrangements

VB / VBA Code What it means (plain English)
[Link] vbCascade Arrange children in diagonal overlapping stack. Each
title bar visible.
[Link] vbTileHorizontal Side by side as rows. No overlap. Equal space.
[Link] vbTileVertical Side by side as columns. No overlap. Equal space.

Q15 — Control Array: Shared Event

VB / VBA Code What it means (plain English)
Private Sub cmdKey_Click(Index As Integer) One Sub handles ALL cmdKey buttons. Index = which
button was clicked (0,1,2...).
[Link] = [Link] & Append the clicked button's label text to the display box.
cmdKey(Index).Caption
End Sub End.

Q16 — Control Array: Select Case

VB / VBA Code What it means (plain English)
Private Sub cmdKey_Click(Index As Integer) Shared event. Index = which button.
Select Case Index Check value of Index. Like switch(Index) in C++.
 Case 0: [Link] = "First" Index=0 → set label to "First".
Case 1: [Link] = "Second" Index=1 → set label to "Second".
End Select End of Select Case.
End Sub End.

Q17 — Dynamic Control Arrays

VB / VBA Code What it means (plain English)
Load cmdKey(2) Create new button at runtime as index 2. Copied from
base control (index 0).
cmdKey(2).Caption = "New" Set its text.
cmdKey(2).Left = 2000 Set horizontal position. MUST do this or it overlaps
index 0.
cmdKey(2).Top = 1000 Set vertical position.
cmdKey(2).Visible = True Make it visible. New controls start HIDDEN — forget
this and you won't see it.

Q20 — Menu States

VB / VBA Code What it means (plain English)
[Link] = Not Not flips the value. True→False, False→True. Toggles
[Link] the tick mark on/off each click.
[Link] = ([Link] <> "") If clipboard has text (<> "" = not empty) →
Enabled=True (clickable). If empty → Enabled=False
(grayed out).

Q21 — RichTextBox Formatting

VB / VBA Code What it means (plain English)
[Link] = True Make selected text bold. Only affects selected text.
[Link] = vbBlue Change selected text color to blue.
[Link] = rtfCenter Center align selected paragraph. Options: rtfLeft,
rtfCenter, rtfRight.
[Link] = 14 Change selected text font size to 14.

Q22 — Clipboard Operations

VB / VBA Code What it means (plain English)
Private Sub mnuCopy_Click() Runs when Copy menu item clicked.
[Link] Copy selected text to clipboard. Original stays.
End Sub End.

Private Sub mnuCut_Click() Runs when Cut clicked.

[Link] Remove selected text, put on clipboard.
End Sub End.

Private Sub mnuPaste_Click() Runs when Paste clicked.

 [Link] Insert clipboard content at cursor.
End Sub End.

Q23 — Search and Replace

VB / VBA Code What it means (plain English)
Dim pos As Integer Variable to store where the search result is found.
pos = InStr(1, [Link], [Link]) InStr = find text inside text. Start from 1. Search
[Link] for [Link]. Returns position number
or 0 if not found.
If pos > 0 Then pos > 0 means word was found. Run block below.
[Link] = pos - 1 Move cursor to start of match. pos-1 because SelStart is
0-based but InStr is 1-based.
[Link] = Len([Link]) Select exactly as many characters as the search word.
Highlights the match.
[Link] = [Link] Replace the highlighted match with the replacement text.
End If End.

Q24 — File Handling

VB / VBA Code What it means (plain English)
' WRITE Comment — writing section.
Open "[Link]" For Output As #1 Open/create [Link] for writing. As #1 = file handle
number to refer to it.
Print #1, [Link] Write all text from rtbEditor into the file.
Close #1 MUST close. Saves data, releases lock.

' READ Comment — reading section.

Dim s As String s will hold each line as we read.
Open "[Link]" For Input As #1 Open file for reading.
Do Until EOF(1) Loop until end of file. EOF(1)=True when no more lines.
Line Input #1, s Read one line from file into s.
[Link] = [Link] & s & vbCrLf Add line to text box. vbCrLf = newline character.
Loop : Close #1 Go back. When done, close file.

' APPEND Comment — appending section.

Open "[Link]" For Append As #1 Open for adding to end. Does NOT overwrite.
Print #1, "New entry added" Add this text at the end.
Close #1 Close.

If Dir$("[Link]") = "" Then MsgBox "File not Dir$() returns filename if exists, "" if not. Check before
found" opening to avoid crash.

Q25 — Common Dialogs

VB / VBA Code What it means (plain English)
[Link] = "Text Files|*.txt|Rich Set file type filter for dialog. | separates display name and
Text|*.rtf" extension.
[Link] Show Windows Open File dialog. Waits for user to pick.
If [Link] <> "" Then Check user picked a file (not cancelled). <> "" = not
empty.
[Link] Load selected file into RichTextBox.
[Link]

[Link] Show font picker dialog.

[Link] = Apply chosen font to selected text.
[Link]
[Link] = Apply chosen size to selected text.
[Link]

[Link] Show color picker dialog.

[Link] = [Link] Apply chosen color to selected text.
PART 6 — VB vs C++ Quick Reference

C++ Concept C++ Syntax VB/VBA Equivalent Key Difference

Declare variable int x = 5; Dim x As Integer x = 5 Two steps in VB
Comment // comment ' comment Single quote in VB
If block if(x>5){ } If x>5 Then End If No braces, use End If
Switch switch(x){ } Select Case x End Select Case instead of case:
For loop for(i=0;i<5;i++) For i=0 To 4 Next i Next replaces i++
While loop while(x<10){ } Do While x<10 Loop Loop replaces }
Not equal != <> Different symbol
AND / OR / NOT && / || / ! And / Or / Not Spelled out in VB
String join s = a + b; s=a&b Use & not + in VB
Return value return n*n; FunctionName = n*n Assign to own name
Print/output cout << "hi"; MsgBox "hi" MsgBox shows popup
Newline char \n vbCrLf Different constant
Declare object MyClass obj; Dim sh As Worksheet Same idea
Assign object obj = new MyClass() Set sh = Sheets("Name") Use Set keyword in VB

Study tip: Read Part 1 (macros/VBA basics) + Part 2 (syntax) first. Then for each QB answer with code, find it in Part 5
for the line-by-line explanation. For Experiment 3, Part 3 has every line covered.