Function doesn't work - Boolean, Array, Select

[Philip Mark Hunt]

Can anyone see what is wrong with this function? All I get is a VALUE error.
I have tried some Debug.Print statements at various places but they don't
appear to be even arrived at. In effect, the function doesn't even seem to
be entered.

The function is called, for example, with the statement:
=Country_Held(C2,TRUE)

C2 is a VLOOKUP statement which returns, for example, the string value CG

***********************************************

Function Country_Held(fHeld_String As String, _
UK_Call As Boolean) As Boolean

Application.Volatile

Country_Held = False

Dim fLen_Held_String As Long
Dim tVal As String
Dim I As Integer

fLen_Held_String = Application.WorksheetFunction.Len(fHeld_String)

Dim fHeld_Array(12)

For I = 1 To fLen_Held_String
fHeld_Array(I) = Application.WorksheetFunction.Mid(fHeld_String, I, 1)
Next I

If UK_Call = True Then
For I = 1 To fLen_Held_String
tVal = fHeld_Array(I)
Select Case tVal
Case "A": Country_Held = True
Case "B": Country_Held = True
Case "C": Country_Held = True
Case "D": Country_Held = True
End Select
If Country_Held = True Then
Exit For
End If
Next I
Else
For I = 1 To fLen_Held_String
tVal = fHeld_Array(I)
Select Case tVal
Case "E": Country_Held = True
Case "F": Country_Held = True
Case "G": Country_Held = True
Case "H": Country_Held = True
Case "I": Country_Held = True
Case "J": Country_Held = True
End Select
If Country_Held = True Then
Exit For
End If
Next I
End If

End Function

***********************************************

I look forward to some construtive comments,as usual.

 

[Dave Peterson]

I can get that error if C2 shows an error (#n/a) or if the length of the string
in C2 is longer than 12.

ps. VBA has its own Len() function and Mid() function. You don't need to go
back to the worksheet for these.

pps. I don't see any reason for making this volatile, either.

 

[AltaEgo]

VBA has Mid and Len functions so, delete relevant instances of
'Application.WorksheetFunction.'

 

[Per Jessen]

Hi

I'm not sure what the function should do, but don't use
worksheetfunctions for LEN and MID, as VBA has the functions to.

This seems to work:

Function Country_Held(fHeld_String As String, _
UK_Call As Boolean) As Boolean

Application.Volatile

Country_Held = False

Dim fLen_Held_String As Long
Dim tVal As String
Dim I As Integer

fLen_Held_String = Len(fHeld_String)

Dim fHeld_Array(12)

For I = 1 To fLen_Held_String
fHeld_Array(I) = Mid(fHeld_String, I, 1)
Next I

If UK_Call = True Then
For I = 1 To fLen_Held_String
tVal = fHeld_Array(I)
Select Case tVal
Case "A": Country_Held = True
Case "B": Country_Held = True
Case "C": Country_Held = True
Case "D": Country_Held = True
End Select
If Country_Held = True Then
Exit For
End If
Next I
Else
For I = 1 To fLen_Held_String
tVal = fHeld_Array(I)
Select Case tVal
Case "E": Country_Held = True
Case "F": Country_Held = True
Case "G": Country_Held = True
Case "H": Country_Held = True
Case "I": Country_Held = True
Case "J": Country_Held = True
End Select
If Country_Held = True Then
Exit For
End If
Next I
End If

End Function

Hopes this helps.

 

[Philip Mark Hunt]

Thank you Dave and Steve. The changing of Mid and Len did the trick.

 

[Dana DeLouis]

Just to add... would any ideas here help?

Select Case tVal
Case "E" to "J" : Country_Held = True
End Select
If Country_Held Then Exit For

= = =
HTH
Dana DeLouis