VBA/Excel/Access/Word/Language Basics/Sub

an Exit Sub just before the error label, which forces the subroutine to exit immediately, without erroneously running the error code.

 
Public Sub Foo()
   On Error Goto Foo_Err
   " some code goes here
   Exit Sub
Foo_Err:
   " Error handling code goes here
End Sub

Calling Functions and Sub Procedures

 
     Sub Master()
           SalesData = GetInput("Enter Sales Data")
           If SalesData = False Then Exit Sub
           PostInput SalesData, "B3"
     End Sub
     Function GetInput(Message)
           Data = InputBox(Message)
           If Data = "" Then GetInput = False Else GetInput = Data
     End Function
     Sub PostInput(InputData, Target)
           Range(Target).Value = InputData
     End Sub

Calling the function from a Sub procedure

 
Sub CallerSub()
      Ans = CubeRoot(125)
      MsgBox Ans
End Sub
Function CubeRoot(number)
      CubeRoot = number ^ (1 / 3)
End Function

Creating Procedures

 
Option Compare Database
Option Explicit
Sub addNumbers()
   "Declare the variables
End Sub

Declare sub

 
Sub declareSub()
    Dim DateOfBirth As Date
    Dim age As Integer
    Dim FullName As String
End Sub

Only place parentheses around the arguments when calling a function and making use of the return value from the function procedure

 
Sub main()
     SalesData = GetInput("Enter Sales Data")
End Sub
Function GetInput(Message)
    Data = InputBox(Message)
    If Data = "" Then GetInput = False Else GetInput = Data
End Function

Optional parameters

 
Sub increment(x, Optional y)
  If IsMissing(y) Then
    x = x + 1
  Else
    x = x + y
  End If
End Sub

Pass double to sub module

 
Sub CubeRoot(ByVal dblNumber As Double)
    dblNumber = dblNumber ^ (1 / 3)
End Sub
Sub CubeRootWrapper()
    Dim dblVariable As Double
    dblVariable = 8
    Debug.Print "Before: " & dblVariable
    CubeRoot dblVariable
    Debug.Print "After: " & dblVariable
End Sub

Passing Arguments to Subroutines and Functions

 
Sub ThreeNumbers()
    Dim num1 As Integer, num2 As Integer, num3 As Integer
    num1 = 10
    num2 = 20
    num3 = 30
    MsgBox MyAverage(num1, num2, num3)
    MsgBox num1
    MsgBox num2
    MsgBox num3
End Sub
Function MyAverage(ByVal num1, ByVal num2, ByVal num3)
    num1 = num1 + 1
    MyAverage = (num1 + num2 + num3) / 3
End Function

Passing Elements of an Array to Another Procedure

 
"Option Base 1
Sub CityOperator()
    Dim cities(6) As String
    cities(1) = "Baltimore"
    cities(2) = "Atlanta"
    cities(3) = "Boston"
    cities(4) = "Washington"
    cities(5) = "New York"
    cities(6) = "Trenton"
    Hello cities()
End Sub
Sub Hello(cities() As String)
    Dim counter As Integer
    For counter = 1 To 6
    MsgBox "Hello, " & cities(counter) & "!"
    Next
End Sub

Private (module-level) variables can be seen by any routine in the module they were declared in, but not from other modules.

 
Option Explicit
Private mintAge As Integer
Private Sub cmd()
  mintAge = mintAge + 1
  Debug.Print mintAge
End Sub

The Call Statement

 
     Sub Master()
         SalesData = GetInput("Enter Sales Data")
         If SalesData = False Then Exit Sub
         Call PostInput(SalesData, "B3")
     End Sub
     Function GetInput(Message)
           Data = InputBox(Message)
           If Data = "" Then GetInput = False Else GetInput = Data
     End Function
     Sub PostInput(InputData, Target)
           range(Target).value = InputData
     End Sub

Use If and ElseIf to check the parameter

 
Sub MultipleIfs(intNumber As Integer)
   If intNumber = 1 Then
      MsgBox "You entered a one"
   ElseIf intNumber = 2 Then
      MsgBox "You entered a two"
   ElseIf intNumber >= 3 And intNumber <= 10 Then
      MsgBox "You entered a number between 3 and 10"
   Else
      MsgBox "You entered some other number"
   End If
End Sub