Declare your function like returning integer:
Function ExtractYear(MyFileName1 As String) As Integer


"Marc" wrote:

Hi,

I am having some big troubles with a quit simple VBA code.
It extracts 4 digit year number out of a text string.

The problem is that it returns the year as a text value no matter what
I do.
I have tried to use Val, Cdbl, multiply with 1 etc. but nothing helps.

I dont want to use the worhsheetfunction value in excel and it is not
valid in the VBE.
Nor either to mutiply with 1 in excel, which also works. This should
work with just this UDF!

Please help me!! See code below.

Function ExtractYear(MyFileName1 As String) As String
Dim AntalTegn, j As Integer

AntalTegn = Len(MyFileName1)
If AntalTegn = 0 Then
Exit Function
Else

For j = 1 To AntalTegn
If IsNumeric(Mid(MyFileName1, j, 1)) = True Then
If IsNumeric(Mid(MyFileName1, j, 4)) = True Then
ExtractYear = Mid(MyFileName1, j, 4)
'ExtractYear = ExtractYear.Value
Exit For
End If
End If
Next j
End If
End Function


Hope to hear from someone soon :-)

Cheers

Marc