What is the syntax to call procedures from another module?

Call <module name>.<sub procedure's name>
#example - project has a userform, and a module called Commands
Private Sub btnRefresh_Click() 'located in a userform
 Call Commands.output
End Sub


Sub output() 'located in Commands module
     MsgBox "This code is in a separate module"
End Sub

 

What is the syntax to run a procedure based on a timer?

Sub <procedure name>()
   'code to be run
   Application.OnTime Now + TimeValue("00:00:00"), "<procedure name>"
End Sub
'example
Sub runsEveryTenSeconds()
    If formClock.Visible = False Then Exit Sub

    Call updateClock
    Application.OnTime Now + TimeValue("00:00:10"), "runsEveryTenSeconds"
End Sub

 

What is the syntax to exit a procedure?

Exit Sub
#example - exits procedure if something error description happens
Private Sub btnAddNew_Click()
errorStr = errorCheck 'checks for errors ie. missing gaps in textfields

If Not (errorStr = "") Then 'if something wrong with wat user entered
     Exit Sub
End If

End Sub

What is the syntax to run code every time a cell value changes?

'place code in a sheet under Microsoft Excel Objects
Private Sub Worksheet_Change(ByVal Target As Range)
     If target.Row = Range("<range of cell>").Row And target.Column = Range("<range of cells>").Column Then
        <code to be run here>
     End If
End Sub
'example
Private Sub Worksheet_Change(ByVal Target As Range)
     If target.Row = Range("A1").Row And target.Column = Range("A1").Column Then
        MsgBox("The value of A1 has changed")
     End If
End Sub

 

 

What is the syntax for a function to return a value?

Function <function name> (<parameter name> As <data type>)
     'parameters are optional
     <function name> = <your code/calculations here>
End Function
'examples
Function TotalPay (hrs As Integer)
     TotalPay = hrs*12
End Function

Public Function returnOne() As Integer
     returnOne = 1
End Function

 

What is a procedure?

  • Grouped statements that relate to a specific task
  • Used for complex tasks that can be further broken down into smaller tasks
  • Two types of procedures: sub and function
Function <name>(<parameters>)
‘callable from anywhere in program
‘can return values
‘put function declarations in a module sheet
End Function
Sub <name>(<parameters>)
‘do not return values
End Sub