What is the syntax to call a local procedure?

Call <procedure name>
'example
Call loadSheet

Sub loadSheet()
    'code here
End Sub

 

Advertisements

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