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

--
Jacob


"Michael Arch" wrote:


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.

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



On Tue, 16 Mar 2010 07:33:01 -0700, Michael Arch
wrote:


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.

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



--

HTH

Bob

"Michael Arch" wrote in message
...

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.