Cyberdude
10-19-2006, 08:19 PM
The value of the VBA function "CellVal" (shown below) is for some reason sensisitve to the format of the number it is compared to. The purpose of the UDF is to extract the value of a cell that is offset from the cell that the UDF executes in. So if I want to compare a value in ?B1? to the value in ?C1?, I would write the following formula in "A1":
=IF(CellVal(1,0) = C1, ?They are the same?, ?They are not the same?) The worksheet looks something like this:
Cell ?A1?................................... Cell ?B1?....... Cell ?C1?
IF(CellVal(1, 0) = C1 then ? ......$5.43............. 5.43
Assume that the value of ?B1? is 5.43 formatted as a number with 2 fractional digits..
If the value of ?C1? is 5.43, then the comparison is TRUE. However, if the value in ?B1? is unchanged, but formatted to display as $5.43, then the comparison is FALSE. Here?s the function code:
Function CellVal(Optional ColOffset& = 0, Optional RowOffset& = 0) As Variant
Application.Volatile
On Error GoTo ErrRef
CellVal = Cells(Application.ThisCell.Row + RowOffset, Application.ThisCell.Column + ColOffset).Value
On Error GoTo 0
Exit Function
ErrRef:
On Error GoTo 0
MsgBox "Invalid offset argument ('" & ColOffset & "' or '" & RowOffset & "') in function 'CellVal'"
CellVal = CVErr(xlErrRef)
End Function 'CellVal' This function has been working very well, except it is failing in the compare part of an IF statement. Why??
=IF(CellVal(1,0) = C1, ?They are the same?, ?They are not the same?) The worksheet looks something like this:
Cell ?A1?................................... Cell ?B1?....... Cell ?C1?
IF(CellVal(1, 0) = C1 then ? ......$5.43............. 5.43
Assume that the value of ?B1? is 5.43 formatted as a number with 2 fractional digits..
If the value of ?C1? is 5.43, then the comparison is TRUE. However, if the value in ?B1? is unchanged, but formatted to display as $5.43, then the comparison is FALSE. Here?s the function code:
Function CellVal(Optional ColOffset& = 0, Optional RowOffset& = 0) As Variant
Application.Volatile
On Error GoTo ErrRef
CellVal = Cells(Application.ThisCell.Row + RowOffset, Application.ThisCell.Column + ColOffset).Value
On Error GoTo 0
Exit Function
ErrRef:
On Error GoTo 0
MsgBox "Invalid offset argument ('" & ColOffset & "' or '" & RowOffset & "') in function 'CellVal'"
CellVal = CVErr(xlErrRef)
End Function 'CellVal' This function has been working very well, except it is failing in the compare part of an IF statement. Why??