Organizing Code into Procedures
Procedures are named blocks of reusable code. VBA has two kinds: Sub procedures, which perform actions but return no value, and Function procedures, which compute and return a value. Breaking a macro into small, well-named procedures makes code easier to read, test, and reuse.
Cricket analogy: A bowler and a batter are specialized roles you call on repeatedly; a Sub is like a fielding drill that does a job, while a Function is like a batter who returns a value—runs—to the scoreboard.
Sub Procedures
A Sub is declared with Sub name(args) ... End Sub and is invoked either bare (MyMacro) or with the Call keyword. Subs can take arguments in parentheses, and a Sub with no arguments and Public scope appears in Excel's Macro dialog so users can run it. Subs are the entry points bound to buttons and events.
Cricket analogy: A pre-match anthem ceremony is a fixed routine triggered before play, like a Public Sub that shows up in the macro list and kicks off when someone presses the button.
Sub FormatReport(sheetName As String)
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(sheetName)
ws.Rows(1).Font.Bold = True
ws.Columns("A:D").AutoFit
ws.Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous
End Sub
Sub RunReports()
Call FormatReport("Sales")
FormatReport "Expenses" ' Call keyword is optional
End SubFunction Procedures and Return Values
A Function returns a value through its own name: Function Area(w, h) ... Area = w * h ... End Function. You assign the result to the function's name before End Function. Functions can be called from other VBA code or, if Public in a standard module, used directly in worksheet cells as custom UDFs like =Area(3, 4).
Cricket analogy: A run-rate calculator takes runs and overs and returns a number, like a Function that computes and hands back a value you can plug into the scoreboard.
Function CommissionDue(sales As Double, Optional rate As Double = 0.05) As Double
If sales <= 0 Then
CommissionDue = 0
Else
CommissionDue = sales * rate
End If
End Function
' Usable in a cell as =CommissionDue(A2) or =CommissionDue(A2, 0.1)Declaring a return type—Function CommissionDue(...) As Double—and typing every argument helps VBA catch mistakes and avoids slow, ambiguous Variants. If you omit As Type, the procedure or argument defaults to Variant, which is flexible but less safe and slower.
Scope, Arguments, and Optional Parameters
Arguments pass ByRef by default, meaning the procedure can change the caller's variable; use ByVal to pass a copy instead. Optional parameters (with defaults) and ParamArray allow flexible signatures. Procedures declared Private are visible only within their module, while Public ones are callable across the project—control scope to avoid name clashes and to hide helper routines.
Cricket analogy: ByRef is like lending your actual bat so changes come back to you; ByVal is handing over a spare so your original stays untouched, and a Private player is one only your club can select.
Because arguments are ByRef by default, a procedure can unintentionally overwrite the caller's variables. If a helper only needs to read a value, pass it ByVal to prevent surprising side effects. Also, a Function used as a worksheet UDF must not try to modify other cells or the workbook—Excel blocks that and the cell shows #VALUE!.
- Subs perform actions and return no value; Functions compute and return a value via the function name.
- Public argument-less Subs appear in the Macro dialog and can be bound to buttons and events.
- Public Functions in standard modules can be used as custom worksheet functions (UDFs).
- Arguments pass ByRef by default; use ByVal to pass a copy and avoid side effects.
- Optional parameters with defaults and ParamArray enable flexible signatures.
- Private limits a procedure to its module; Public exposes it project-wide, so use scope to hide helpers.
Practice what you learned
1. How does a VBA Function return a value?
2. By default, how are arguments passed to a VBA procedure?
3. Which can be used directly in a worksheet cell?
4. What does ByVal do to an argument?
5. If you omit 'As Type' on an argument or return value, what type is used?
Was this page helpful?
You May Also Like
Conditionals in VBA
Learn how VBA evaluates Boolean expressions to branch code using If...Then...Else, ElseIf chains, and Select Case.
Loops in VBA
Master repetition in VBA with counter-driven For...Next, collection-driven For Each, and condition-driven Do loops.
Error Handling with On Error
Intercept run-time errors gracefully in VBA using On Error statements, the Err object, and disciplined cleanup patterns.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics