Hi Marc,

Just declare the function a Double:

Function ExtractYear(MyFileName1 As String) As Double

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Marc" wrote in message ups.com...
| 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
|