UDF Not working

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

 

[Jacob Skaria]

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

 

[Chip Pearson]

You are setting the value of the parameter, not the function. Instead
of

strField = "HIST_Dir"

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

 

[Bob Phillips]

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