Using User Define Formula in a Formula gives #Value! error

[daneyuleb]

The below function works fine when called from a command button. But, if used a cell's formula: =ShowValues("name")
the cell shows the dreaded #Value! error.

I can't figure out what's wrong--what in the function is causing it not to work as a formula?

The function simply takes some text data held in a Global Variable (the data is an array containing individual lines from a text file) - and searches for a certain text label -- and returns it if it finds it.

Am I missing something obvious--Is this just the kind of thing you just can't do in a formula?

Function ShowValues(ByVal label As Variant) As Variant
   
    Dim count As Integer
    Dim Found_it As Integer
    Dim Value As String
    ' Go through each line in our split data
    For x = LBound(Split_Data_Global) To UBound(Split_Data_Global)
        If Len(Trim(Split_Data_Global(count))) <> 0 Then
            ' Non-empty line--check for our value (label)
            Found_it = InStr(1, Split_Data_Global(count), label, vbBinaryCompare)
            If Found_it Then
                ' Return the 2nd part of the split (everything after the colon)
                ShowValues = Split(Split_Data_Global(count), ":")(1)
                Exit Function
            End If
        End If
        ' Increment our line counter
        count = count + 1
        ' Next line
    Next x
End Function

 

[daneyuleb]

Never mind!!!

I removed the global references and that seemed to fix it!