Re: Function doesn't work - Boolean, Array, Select

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

---
Per


On 2 Maj, 00:39, Philip Mark Hunt
<PhilipMarkH...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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.
--
Best regards

Philip
Kwinana, Perth, Western Australia

.

Relevant Pages

  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)
  • Re: Save a File from a Text Box
    ... Function DriveExists(ByVal sDrive As String) As Boolean ... 'get the string that contains all drives ... Public Function FileExistAs Boolean ... Dim blnResult As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: Save a File from a Text Box
    ... Dim sSource as string ... Public Function FileExistAs Boolean ... Dim intRet As Integer ...
    (microsoft.public.access.modulesdaovba)
  • Re: Save a File from a Text Box
    ... The sSource is passing the verification with no problems, however, the sDest ... Dim sSource as string ... Public Function FileExistAs Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: Save a File from a Text Box
    ... how do I get it to verify the path&file found in my text box; ... Function DriveExists(ByVal sDrive As String) As Boolean ... Public Function FileExistAs Boolean ... Dim blnResult As Boolean ...
    (microsoft.public.access.modulesdaovba)