#NAME? error running Excel 2003 VBA in Excel 2007

October 24th, 2009, 08:47 AM

Hi,

I made a spreadsheet in XL03 but the VBA functions will not calculate in XL07!

I have saved it as macro enabled, the module exists, the functions appear in the fx dropdown list as I type!

I have set the "Trust Center" options in various ways but can not get rid of the error.

One of my functions:-

Code:

Function timval(tim_str As String) As Single

Dim hrh As Single
Dim minh As Single

hrh = 1 / 24
minh = hrh / 60

Dim hr_st As String
Dim min_st As String
Dim hr As Single
Dim min As Single

' extract hours and minutes from string
hr_st = Mid(tim_str, 1, 2)
min_st = Mid(tim_str, 4, 2)

' convert to numeric
If IsNumeric(hr_st) Then
    hr = hr_st
Else
    hr = 0
End If
    
If IsNumeric(min_st) Then
    min = min_st
Else
    min = 0
End If

' calculate time value in excel format
timval = (hr * hrh) + (min * minh)

End Function

Hope someone can help.

Thanks
Steve