UDF Not working

  • Thread starter Thread starter Michael Arch
  • Start date Start date
M

Michael Arch

This function should return the new string, but it returns Blank can anyone
explain why, and also tell me how to fix it and avoid similar issues in the
future. I tried using Select Case statement in my first attempt and it did
not work, so I figure I switch to If statements, but the result is the same a
blank.
Thanks in advance

Global strField As String


Sub Raul()

strField = "AP_Parts"
strField = Test(strField)

MsgBox (strField)


End Sub


Function Test(strField As String) As String

Dim FndException

strmval = UCase(strField)

If strmval = "NV_EQUIPMENT" Then
strField = "HIST_Dir"
Else
If strmval = "NV_PARTS" Then
strField = "HIST_Dir_Prt"
Else
If strmval = "AP_EQUIPMENT" Then
strField = "HIST_Dis"
Else
If strmval = "AP_PARTS" Then
strField = "HIST_Dis_Prt"
Else
If strmval = "GPL_EQUIPMENT" Then
strField = "HIST_Dis"
Else
If strmval = "STL_COMMON" Then
strField = "HIST_OTH"
End If
End If
End If
End If
End If
End If
End Function




Michael Arch.
 
Within the function the result should be assigned to Test which is declared
as string.

Try thebelow



Function Test(strField As String) As String

Select Case UCase(strField)
Case "NV_EQUIPMENT"
Test = "HIST_Dir"
Case "NV_PARTS"
Test = "HIST_Dir_Prt"
Case "AP_EQUIPMENT"
Test = "HIST_Dis"
Case "AP_PARTS"
Test = "HIST_Dis_Prt"
Case "GPL_EQUIPMENT"
Test = "HIST_Dis"
Case "STL_COMMON"
Test = "HIST_OTH"
End Select

End Function
 
You are setting the value of the parameter, not the function. Instead
of
strField = "HIST_Dir"
Click to expand...

you should use
Test = "HIST_Dir"

Make this change for all instances of strField.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
Whilst the other two suggestions should work, a far simpler change is this

Sub Raul()
Dim strField As String

strField = "AP_Parts"
Call Test(strField)

MsgBox (strField)

End Sub
 
Back
Top