What’s the difference between Sub Routines and Functions

I was curious Sub Routines and Functions appear to perform almost the same thing what is the difference and what are their relative advantages?

Functions return a value that is stored whereas subs don’t. The main difference is not only the return value, it seems that subs are faster than functions (at least in .net) because the MSIL code of subs is much shorter when no value is returned. so overall subs are faster when no value is returned.

MSIL stands for Microsoft Intermediate Language – which is the a programming language that has been standardized later as the Common Intermediate Language

Functions vs Sub Routines

Typical DAO.Recordset VBA for looping through and altering

Function TypicalDAOrecordset()

'Make sure the name of the recordset is unambiguous
'Good practice to reference the actual library
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM T001Main where T001Main.ValueNumber = 0")
        'the data source can be a Table Name a query name or an sql string
        'it would be possible to change the SQL to set to another set of records
        'Check to see if there are any records in the set
        If Not (rs.EOF And rs.BOF) Then
        'there are no records if End of File and beginning of file are both true
            Do Until rs.EOF = True
            rs!ValueNumber = 300
            MsgBox "No Records available for updating exit sub"
            Exit Function
            End If
            MsgBox "Looped through the records and updated ValueNumber field"
            Set rs = Nothing
            Set db = Nothing
            'libraries for DAO can be found on AllenBrowne site
            'remember to break an infinite loop press ctrl + break

End Function

VBS – Pieces of code

Shutdown computer

Option Explicit
Dim oShell
Set oShell = Wscript.CreateObject("Wscript.Shell")
oShell.Run "SHUTDOWN -T 60 -S" 'wait 60 seconds before shutting down

Trigger speech

Option Explicit 
Dim speechobject
set speechobject=createobject("sapi.spvoice")
speechobject.speak "Your system is setup and ready for your day"

Delay for Seconds (10)

Option Explicit
Dim dteWait
dteWait = DateAdd("s", 10, Now())
Do Until (Now() > dteWait)

Sleep Function for Delay – Count in Milliseconds (5 mins below)

WScript.Sleep 5*60*1000

MS Access VBA Function – Count Numbers of Records in Tables and list.

Not quite finished yet but place here for later correction.

Public Function CountAllTablesRows()
Dim rs As New ADODB.Recordset
Dim rsRC As New ADODB.Recordset
Dim strTbName As String
Dim lngRowCount As Long
Dim tbl As TableDef
CurrentProject.Connection.Execute "Delete from TABLE_INFO"
rs.Open "TABLE_INFO", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
For Each tbl In CurrentDb.TableDefs
  Select Case Left(tbl.Name, 4)
    Case "mSys"
    Case Else
      rsRC.Open "Select count(*) as The_Count from [" & tbl.Name & "]", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
      rs.Fields("TBL_NAME") = tbl.Name
      rs.Fields("TBL_ROWCOUNT") = rsRC.Fields("The_Count")
      Set rsRC = Nothing
      'Debug.Print tbl.Name
  End Select
Set rs = Nothing

MsgBox "Counted Numbers in Table"
End Function