User Defined Function (1 Viewer)

ismailr

Registered User.
Local time
Yesterday, 18:12
Joined
Oct 16, 2015
Messages
31
Hello,

Writing VBA and reducing repeating code by defining user defined functions is very new for me, So need help to understand the basic structure of UDF and how it should be structured In correct way.

I created my codes like;

Private Sub cbxEmployeeName_AfterUpdate()
Dim condition As String
Dim a, b, c, d, e, f, g, h, i As String

a = cbxEmployeeCode.Value
b = cbxEmployeeName.Value
c = cbxEmployeeDepartment.Value
d = cbxEmployeeDesignation.Value
e = cbxEmployeeReportingManagerDesignation.Value
f = cbxEmployeeReportingManagerName.Value
g = cbxCompanyCode.Value
h = cbxCompanyGrade.Value
i = cbxEmployeeStatus.Value

condition = SearchEmployee(a, b, c, d, e, f, g, h, i)

End Sub

The code for UDF is;
Option Compare Database
Option Explicit

Public Function SearchEmployee(a, b, c, d, e, f, g, h, i As String)

Dim flag As String
Dim a1, b1, c1, d1, e1, f1, g1, h1, i1 As String
Dim vCondition As String
a1 = "EmployeeCode like '*" & a & "*'"
b1 = "EmployeeName like '*" & b & "*'"
c1 = "EmployeeDepartment like '*" & c & "*'"
d1 = "EmployeeDesignation like '*" & d & "*'"
e1 = "EmployeeReportingManagerDesignation like '*" & e & "*'"
f1 = "EmployeeReportingManagerName like '*" & f & "*'"
g1 = "CompanyCode like '*" & g & "*'"
h1 = "CompanyGlobalGrade like '*" & h & "*'"
i1 = "EmployeeStatus like '*" & i & "*'"

flag = "F"
If Not IsNull(UCase(a)) Then
If flag = "T" Then
vCondition = a1 & " And "
Else
vCondition = a1
flag = "T"
End If
End If
.
.
.
.

If Not IsNull(i) Then
If flag = "T" Then
vCondition = vCondition & " And " & i1
Else
vCondition = i1
flag = "T"
End If
End If
'condition = vCondition

End Function

Issue is that once the function run and return back to original code I unable to get the value of vCondition into condition. How I can get the value of concatenationconcatenated concatenation concatenationcondition value.

thanks
Ismail
 

plog

Banishment Pending
Local time
Yesterday, 20:12
Joined
May 11, 2011
Messages
11,613
First some general notes about your coding:

1. Use comments. Not only does your post not contain any information about what your code is trying to accomplish, your code itself lends no insights. Use comments to tell me what the purpose of the function/sub is and then help me with tips along the way.

2. Poorly named variables. Similar to #1, you should use good variable names to help me know what a variable represents. No idea what your code is for, nor how you are trying to accomplish it.

As for your issue, I believe it has to do with your improper function declaration:

Code:
Public Function SearchEmployee(a, b, c, d, e, f, g, h, i As String)

A function returns a value, and to do so, you need to tell it what type of data it returns. After the closing parenthesis you should have 'As String' if you want to return a string. Because your 'As String' is inside your parenthesis you are declaring the variable i as an input string.

Also, to return a variable from a function you set the function name equal to what you want to return on the last line. So to return the string "Success" you would do this:


Code:
...
...
SearchEmployee = "Success" 
End Function
 

MarkK

bit cruncher
Local time
Yesterday, 18:12
Joined
Mar 17, 2004
Messages
8,178
The function name is like a variable, so within the function you must assign it a value, like . . .
Code:
Function [B][COLOR="DarkRed"]Add[/COLOR][/B](n1 as single, n2 as single) as Single
[COLOR="Green"]   'this function returns a value[/COLOR]
   [B][COLOR="DarkRed"]Add[/COLOR][/B] = n1 + n2
End Function
Then you can call the function like . . .
Code:
   dim result as single
   result = Add(12, 15.5)
   msgbox result
 

Users who are viewing this thread

Top Bottom