PDA

View Full Version : Solved: My UDF Doesn?t Work in Compares



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??

Ken Puls
10-19-2006, 11:10 PM
Sorry, Sid, I can't replicate the issue.

I was able to get a successful compare using $5.43 and even 543%.

Are you sure that the number in the cell is actually rounded off? 5.431 would not match 5.43

Just a thought...

acw
10-19-2006, 11:15 PM
Cyberdude

I agree with Ken - I couldn't replicate your problem.

Perhaps a small file that shows your problem.....


Tony

Bob Phillips
10-20-2006, 02:29 AM
Me neither Dude.

A couple of points, the standard argument order is row, column, and you could make good use of Offset here



Function CellVal(Optional RowOffset& = 0, Optional ColOffset& = 0) As Variant
Application.Volatile
On Error GoTo ErrRef
CellVal = Application.ThisCell.Offset(RowOffset, 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'

Cyberdude
10-20-2006, 05:06 PM
the standard argument order is row, column That bothered me too, Bob, but I'm so accustomed to the column - row ordering of the A1 (Range) format, that I decided to use that, since I'm the only one who will ever use this.

Ken and acw, I had a feeling the rest of the world would not be able to duplicate this. It's a "shouldn't happen" type problem, so no doubt it's something I'm doing wrong. Regarding the rounding issue, I tested that and got the same results. But it's gotta be something like that. I'll do some more testing.


you could make good use of Offset here I'm not sure what you mean, Bob. I thought I am using Offset. That's the whole object of the function. In fact the main reason for a function instead of just formulas is that I wanted to use something that could accept arguments for the offset numbers. I've been using formulas with defined names, and they work well, but I need a different defined name for each offset amount.

Hey, thanks for your time and thoughts, guys!

Bob Phillips
10-21-2006, 05:49 AM
I'm not sure what you mean, Bob. I thought I am using Offset. That's the whole object of the function.

You are using offset as a technique, I meant the Offset method in VBA. I did actually post what I meant.

You used



CellVal = Cells(Application.ThisCell.Row + RowOffset, Application.ThisCell.Column + ColOffset).Value


I suggested



CellVal = Application.ThisCell.Offset(RowOffset, ColOffset).Value


somehat clearer methinks.

Cyberdude
10-21-2006, 01:46 PM
I apologize. The difference between my statement and yours escaped my attention as I read through your suggestion. My bad!

SId