Procedures and Subroutines:
- Procedure: A set of instructions that perform a specific task. Procedures can be subs or functions.
- Sub: Short for subroutine, a sub is a block of code that performs a specific task and doesn’t return a value.
Example:
Sub PrintMessage()
MsgBox “Hello, world!”
End Sub
- Call Statement: Used to call a sub or function in VBA.
Example:
Call PrintMessage
- Argument: Values passed to a sub or function when it’s called.
Example:
Sub AddNumbers(num1 As Integer, num2 As Integer)
MsgBox num1 + num2
End Sub
- ByRef Parameter: Passing arguments by reference, allowing the called sub to modify the original values.
Example:
Sub ModifyValue(ByRef num As Integer)
num = num * 2
End Sub
- ByVal Parameter: Passing arguments by value, preventing modifications to the original values.
Example:
Sub SquareValue(ByVal num As Double)
MsgBox num * num
End Sub
- Optional Parameter: A parameter that doesn’t need to be provided when calling a sub or function.
Example:
Sub Greet(name As String, Optional age As Integer = 0)
MsgBox "Hello, " & name & "! Your age is " & age & "."
End Sub
- Exit Sub: Used to prematurely exit a sub.
Example:
Sub CheckValue(value As Integer)
If value < 0 Then
MsgBox "Value is negative."
Exit Sub
End If
MsgBox "Value is non-negative."
End Sub
- Private Sub: A sub that can only be accessed within its module.
Example:
Private Sub CalculateTotal()
' Code to calculate total
End Sub
- Public Sub: A sub that can be accessed from other modules.
Example:
Public Sub ExportData()
' Code to export data End Sub
Functions:
Function: A block of code that performs a specific task and returns a value.
Example:
Function AddNumbers(num1 As Integer, num2 As Integer) As Integer
AddNumbers = num1 + num2 End Function
- Return Statement: Used to assign a value to a function and exit it.
Example:
Function MultiplyNumbers(num1 As Double, num2 As Double) As Double
MultiplyNumbers = num1 * num2 End Function
- Calling a Function: Using a function in an expression or statement.
Example:
total = AddNumbers(5, 7)
- Function Overloading: Defining multiple functions with the same name but different parameter lists.
Example:
Function Area(radius As Double) As Double
Area = 3.14159 * radius * radius
End Function
Function Area(length As Double, width As Double) As Double
Area = length * width
End Function
- Nested Function: Calling one function from within another function.
Example:
Function CalculateTotal(price As Double, quantity As Integer) As Double
CalculateTotal = price * quantity
End Function
Function CalculateTax(total As Double) As Double
CalculateTax = total * 0.1
End Function
- Scope of Functions: Functions can have private or public scope, similar to subs.
Example:
Private Function ComputeAverage(nums() As Double) As Double
' Code to compute average
End Function
Public Function ComputeSum(nums() As Double) As Double
' Code to compute sum
End Function
- Recursive Function: A function that calls itself as part of its execution.
Example:
Function Factorial(n As Integer) As Integer
If n <= 1 Then
Factorial = 1
Else
Factorial = n * Factorial(n - 1)
End If
End Function
- Built-in Functions: VBA provides numerous built-in functions like
Len,Left,Right,Mid, etc.
length = Len("Hello, world!")
- Custom Functions: Functions defined by the programmer for specific tasks.
Example:
Function IsEven(num As Integer) As Boolean
IsEven = (num Mod 2 = 0) End Function
- Function with Object: Functions can operate on objects like ranges, cells, etc.
Example:
Function
GetCellValue(cell As Range) As Variant
GetCellValue = cell.Value
End Function
