Hi All
I have a wee problem with an Exel UDF.
The function is used in a cell in the spreadsheet typically just
= payrate(C8,E8,$E$5 - $D8)
and generally works fine
But when I edit the cell with the function (in the simplest case, F2 and enter) the function code is then evaluated twice.
For the first evaluation, one of the parameters (a calcuated value) is 0 despite having a valid value visible in the cell - the other two are correct but they are constants not formulae
.
I presume the second evaluation happens because, at some stage after the first evaluation, the calculated parameter is recalculated (no idea why) and this triggers a second calculation of the UDF
But why does excel use 0 for the first calculation when there is a perfectly good value visible in the cell? it wouldn't matter greatly except that it triggers a bunch of msgbox errors
And a minor irritation: Towards the end, the function is set with Payrate = Round(mres,2)
But when the result is displayed on the worksheet it can be something like 8.909999847
whereas I thought round should make it 8.91
Thanks for any suggestions
With apologies for the poor quality, the UDF follows:
Function PayRate(AorS As String, MAge As Single, DasA As Single) As Single
Dim Mkey As String ' key to search table
Dim DasAYN As String
Dim PayTable As Range
Dim age As Single
Set PayTable = Worksheets("DataTables").Range("B6:F32")
If MAge > 24 Then
MAge = 24
End If
If MAge < 16 Then
MsgBox ("Ages less than 16 are set to 16")
End If
If AorS = "A" And MAge > 18 Then
If DasA < 365 Then
DasAYN = "Y"
Else
DasAYN = "N"
End If
Else
DasYn = ""
End If
Mkey = AorS + CStr(MAge) + DasAYN
mres = Application.VLookup(Mkey, PayTable, 5, 0)
If IsError(mres) Then
MsgBox ("No Pay Rate found for key " + Mkey)
PayRate = 0
Else
PayRate = round(mres,2)
End If
End Function
Bookmarks