# Thread: How do I convert this code to Function

1. Registered User
Join Date
Apr 2008
Location
All over, Now in Missouri
Posts
78

## Unanswered: How do I convert this code to Function

I call this Public Sub from an exit of a control. How do I convert to a function? (novice on functions)

Public Sub CalcDwell()
' control to evaluate is Dwell_St_Dt
' Check if the control is empty
If IsNull(Dwell_St_Dt) Or Me.Dwell_St_Dt = "" Then Exit Sub

' Assign values to variables
Dim vmonths, vdays As Integer
vmonths = DateDiff("m", Dwell_St_Dt, Date)

vdays = DateDiff("d", DateAdd("m", vmonths, Dwell_St_Dt), Date)

' Assign calculated variable to txtfield
If Dwell_St_Dt > Date Then
txtDTim = 0 & " Mo " & 0 & " Days"
Else
txtDTim = vmonths & " Mo " & vdays & " Days"
End If
End Sub

2. Registered User
Join Date
Jan 2009
Location
Kerala, India
Posts
188
Originally Posted by PJHAction
I call this Public Sub from an exit of a control. How do I convert to a function? (novice on functions)

Public Sub CalcDwell()
' control to evaluate is Dwell_St_Dt
' Check if the control is empty
If IsNull(Dwell_St_Dt) Or Me.Dwell_St_Dt = "" Then Exit Sub

' Assign values to variables
Dim vmonths, vdays As Integer
vmonths = DateDiff("m", Dwell_St_Dt, Date)

vdays = DateDiff("d", DateAdd("m", vmonths, Dwell_St_Dt), Date)

' Assign calculated variable to txtfield
If Dwell_St_Dt > Date Then
txtDTim = 0 & " Mo " & 0 & " Days"
Else
txtDTim = vmonths & " Mo " & vdays & " Days"
End If
End Sub
Code:
```Public Function Calc_Dwell(ByVal DwellStDt As Date) As String
' control to evaluate is Dwell_St_Dt
' Check if the control is empty

If IsNull(DwellStDt) Or DwellStDt = "" Then Exit Function

' Assign values to variables
Dim vmonths, vdays As Integer
vmonths = DateDiff("m", DwellStDt, Date)

vdays = DateDiff("d", DateAdd("m", vmonths, DwellStDt), Date)

' Assign calculated variable to txtfield
If DwellStDt > Date Then
txtDTim = 0 & " Mo " & 0 & " Days"
Else
txtDTim = vmonths & " Mo " & vdays & " Days"
End If

Calc_Dwell = txtDTim

End Function```
Copy and paste the Calc_Dwell() Function code into a Standard VBA Module and save the module.

Syntax for calling the Function from a Form Control:

Code:
`=Calc_Dwell(Me!Dwell_St_Dt)`
From the Form VBA Module:
Code:
`X = Calc_Dwell(Me!Dwell_St_Dt)`
Last edited by apr pillai; 07-22-15 at 12:59. Reason: to add calling methods

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
strictly speaking in VB/VBA a function returns a value, a SUB routine doesn't, but its fairly arbitrary.
you don't say where you are suing the function. In an ideal world a function should have all arguments passed into it, and return a value (that returned value could be a single value or an array

Code:
```public function myfunction(Dwell_St_Dt as datatypeB*) as datatypeA*
' control to evaluate is Dwell_St_Dt
' Check if the control is empty
If Not IsNull(Dwell_St_Dt) and Me.Dwell_St_Dt != "" Then
' Assign values to variables
Dim vmonths, vdays As Integer  'what datatype should vmonths be? prob shoudl be declared on a separate line
vmonths = DateDiff("m", Dwell_St_Dt, Date)
vdays = DateDiff("d", DateAdd("m", vmonths, Dwell_St_Dt), Date)

' Assign calculated variable to txtfield //instead of assigning the value directly to a text field, return the value
'purists would argue the variable to be returned should have a more descriptive name and that value be exprressly RETURNed
'its one of the quirks of VB/VBA that you can assign a value to a variable of the same name as the function and it will be RETURNed without expressly doing so
If Dwell_St_Dt > Date Then
myfunction = 0 & " Mo " & 0 & " Days"
Else
myfunction = vmonths & " Mo " & vdays & " Days"
End If
else
myfunction = null
endif
return myfunction 'no need for this if your working variable is called the same as the function itself in VB/VBA. however I'd argue its good practice to explicitly return a value form a function
end function```
datatypeA, datatypeB. in an ideal world you shoudl alwasy expressly state what datatype each parameter and the retuyrn shoudl be. VB/VBA will default to variant if not expressly declared. but even if the datattype shoudl be variant I'd strongly recommend that your declare it as such

the basic goal for a function is that it should only work on values expressly passed to it as part of the call
AND should only return values likewise.
So try to avoid reading or setting control properties or variables that are not within the function EVEN if the function is only being used within a single form/report

arguably you should separate the formatting from the value
Code:
```public function myfunction(Dwell_St_Dt as Date) as variant
dim ReturnValue as integer[5] 'not sure this will work as Ive not tested it ;)
'setup some default return values
ReturnValue[0] = 0 'equates to years
ReturnValue[1] = 0 'equates to months
ReturnValue[2] = 0 'equates to days
ReturnValue[3] = 0 'equates to hours
ReturnValue[4] = 0 'equates to minutes
ReturnValue[5] = 0 'equates to seconds
If Not IsNull(Dwell_St_Dt) and Me.Dwell_St_Dt != "" Then
' Assign values to variables
ReturnValue[1] = DateDiff("m", Dwell_St_Dt, Date)
ReturnValue[2] = DateDiff("d", DateAdd("m", vmonths, Dwell_St_Dt), Date)
endif
return ReturnValue
end function
//add ano9ther function to handle the formatting
//give the fucntion a meanign full name instread of MyFucntion ;)```

4. Registered User
Join Date
Apr 2008
Location
All over, Now in Missouri
Posts
78
Thanks for the response. Back at it.

Peter

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•