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
Function TypicalWhileLoop() 'This performs the same as next loop but uses the while loop Dim LCounter As Integer LCounter = 1 While LCounter < 10 MsgBox (LCounter) LCounter = LCounter + 1 Wend End Function
Function TypicalForNextExample() 'This performs the same as the while loop but uses for next Dim i As Integer For i = 1 To 9 MsgBox (i) Next i End Function
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 rs.MoveFirst Do Until rs.EOF = True rs.Edit rs!ValueNumber = 300 rs.Update rs.MoveNext Loop Else MsgBox "No Records available for updating exit sub" Exit Function End If MsgBox "Looped through the records and updated ValueNumber field" rs.Close 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
Option Explicit Dim oShell Set oShell = Wscript.CreateObject("Wscript.Shell") oShell.Run "SHUTDOWN -T 60 -S" 'wait 60 seconds before shutting down
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) Loop
Sleep Function for Delay – Count in Milliseconds (5 mins below)
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 rs.AddNew 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") rs.Update rsRC.Close Set rsRC = Nothing 'Debug.Print tbl.Name End Select Next rs.Close Set rs = Nothing MsgBox "Counted Numbers in Table" End Function