Write Your Own Functions Callahan Chapter 5 User Defined Functions Demo mdb Recap of Callahan Chapters 1 4 Written VBA event procedures to handle events several form events and control events BeforeUpdate LostFocus AfterUpdate KeyDown If Then Else Now MsgBox Dim Used VBA functions Current Filter Have used VBA statements Click Enter IsNull Date Comments non executable remarks included to clarify can use to temporarily disable a statement 1 Branching Conditionally Controlling the Execution Flow Change the flow of execution by using IF statements IF THEN select whether or not to execute a specified set of statements IF THEN ELSE select which of two mutually exclusive sets of statements to execute When writing branching code remember End If is two words to indent the conditionally executed statements for clarity 3 IF THEN If the condition is TRUE the THEN statements are executed If the condition is FALSE or NULL the THEN statements are passed over In either case control resumes at the first statement following End If TRUE FALSE IF condition THEN statements If bytChoice vbCancel Then PostalCode SetFocus Cancel True End If 2 IF THEN ELSE If the condition is TRUE the THEN statements are executed If the condition is FALSE or NULL the ELSE statements are executed In either case control resumes at the first statement following End If TRUE FALSE IF condition THEN statements ELSE statements If bytChoice vbOK Then Confirm True Else Confirm False 5 End If Understanding Modules and Procedures Module an object that stores executable VBA procedures two types of modules Standard module Form Report module Procedure contains a series of statements that performs processing or calculation two types of procedures Function procedure Sub procedure performs processing and returns a value performs processing but does not return a value 3 Understanding Modules and Procedures Event Procedures aka Event Handlers Access automatically calls executes them whenever the event occurs are always Sub procedures since they don t return a value General Procedures can be called by your VBA code or by expression in a query form report either a sub or function can appear in either Standard module Form Report module place a procedure in a standard module when it applies to more than 1 form report e g DisplayMessage Confirm place a procedure in a form report module when it applies to a single form report e g FullName FullAddress some benefits of using general procedures perform complex operations that can t be done in an expression reuse code instead of duplicating it in several different locations break up programming tasks into more manageable units Creating General Procedures in a Standard Module Declarations Section Callahan 2 Option Explicit statement Callahan 2 used to declare module options variables constants user defined types and external procedures requires each variable used in the module be declared before it is used Declaring Constants a named storage location that holds a value that does not change have used intrinsic constants eg vbCancel vbKeyPageDown can define own constant then refer to the symbolic name eg Public Const vbGray 12632256 helps make code more understandable and maintainable a constant declared in a module s Declarations section is visible only to procedures within the module add Public to make it global Demo use conAppName in the Debug Window 4 Procedure Lifetime How long the procedure is loaded into memory Standard Module a procedure must be in memory to be executed called the entire is module is automatically loaded the first time any of its procedures is called Form Report s module is loaded callable only when the form report is open its procedures are not callable if form report is not loaded Procedure Scope a k a Visibility Where the procedure can be called from 2 main procedure scopes Public procedures can only be called by another procedure within its own module Procedures in a Standard module are Public by default callable from anywhere in the application Private procedures use Private keyword to make a procedure available only to other procedures in the same module Procedures in a Form Report module event handlers are Private general functions subs are Public by default but the form report must be open for the call to succeed use Private to make the proc available only to procs in that form report module 5 The Messages Module A Standard module that contains two procedures that display messages to user Confirm a function DisplayMessage a sub Public Sub DisplayMessage strMessage As String Display an important message to the user MsgBox strMessage vbExclamation conAppName End Sub strMessage is an argument since they are in a Standard module and Public DisplayMessage and Confirm can each be called from anywhere in the Contacts database a constant variable or expression passed to a procedure an argument acts as a local variable Demo Use DisplayMessage in the Immediate Window The Messages Module declares the return value s datatype Public Function Confirm strMessage As String As Boolean Ask the user to confirm an action returning True or False Dim bytChoice As Byte bytChoice MsgBox strMessage vbQuestion vbOKCancel conAppName If bytChoice vbOK Then Confirm True Else Confirm False End If End Function A function returns a value of a specified datatype our Confirm function returns a Boolean result a function s return value is determined by the last value assigned to the function name note that Confirm is assigned a value in 2 places Demo Use Confirm in the Immediate Window 6 Using General Procedures on a Form Modified the Contacts Form s Form BeforeUpdate event handler to use the Confirm function Private Sub Form BeforeUpdate Cancel As Integer if the user entered an address check for a postal code Dim blnOK As Boolean If Not IsNull Address And IsNull PostalCode Then blnOK Confirm You didn t enter a postal code Save anyway If Not blnOK Then PostalCode SetFocus Cancel True End If End If user chose Cancel go back to PostalCode field cancel saving the record End Sub Call Stack one procedure calls another which executes then flow returns to caller Demo step through BeforeUpdate which calls Confirm Creating General Procedures in a Form Module To perform processing that is relevant only to a specific form report Private Function FullName As String FullName FirstName LastName End Function This procedure has no arguments Where does the data come from Since FullName was placed in the Contacts form s