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.

   <source lang="vb">

Public Sub Foo()

  On Error Goto Foo_Err
  " some code goes here
  Exit Sub

Foo_Err:

  " Error handling code goes here

End Sub

</source>
   
  

Calling Functions and Sub Procedures

   <source lang="vb">

    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
</source>
   
  

Calling the function from a Sub procedure

   <source lang="vb">

Sub CallerSub()

     Ans = CubeRoot(125)
     MsgBox Ans

End Sub Function CubeRoot(number)

     CubeRoot = number ^ (1 / 3)

End Function

</source>
   
  

Creating Procedures

   <source lang="vb">

Option Compare Database Option Explicit Sub addNumbers()

  "Declare the variables

End Sub

</source>
   
  

Declare sub

   <source lang="vb">

Sub declareSub()

   Dim DateOfBirth As Date
   Dim age As Integer
   Dim FullName As String

End Sub

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

Optional parameters

   <source lang="vb">

Sub increment(x, Optional y)

 If IsMissing(y) Then
   x = x + 1
 Else
   x = x + y
 End If

End Sub

</source>
   
  

Pass double to sub module

   <source lang="vb">

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

</source>
   
  

Passing Arguments to Subroutines and Functions

   <source lang="vb">

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

</source>
   
  

Passing Elements of an Array to Another Procedure

   <source lang="vb">

"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

</source>
   
  

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

   <source lang="vb">

Option Explicit Private mintAge As Integer Private Sub cmd()

 mintAge = mintAge + 1
 Debug.Print mintAge

End Sub

</source>
   
  

The Call Statement

   <source lang="vb">

    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
</source>
   
  

Use If and ElseIf to check the parameter

   <source lang="vb">

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

</source>