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
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