Using ROW function to pass row number to conditional formatting UDF

[danners430]

Hey folks,

I've been having a rather "interesting" issue lately writing a UDF for conditional formatting. It's designed to highlight overdue tasks, according to the duration assigned to each task (Dur1-3). It returns TRUE when the task is overdue, and FALSE otherwise.

Public Function Deadline(Row As Variant) As Boolean


With Sheet1


If DeadlineCanc = True Then Exit Function


Select Case .Cells(Row, "D")
Case Dur1:
    
    If DateDiff("Y", .Cells(Row, "G"), Now) > 7 Then Deadline = True Else Deadline = False
    
Case Dur2:
    
    If DateDiff("Y", .Cells(Row, "G"), Now) > 31 Then Deadline = True Else Deadline = False
    
Case Dur3:
    
    If DateDiff("Y", .Cells(Row, "G"), Now) > 90 Then Deadline = True Else Deadline = False
    
End Select


End With


End Function

In the conditional formatting I simply had the formula =Deadline(ROW($G1))

In order to capture the error, I added an error handler that displayed the error code, and details about the variable Row. The error was a type mismatch, and the variable details returned False to isNumeric, isDate, isEmpty, isNull etc. and varType = 8204...

Anyone know what sort of number format =ROW returns? it seems to not be a straightforward number...

 

[Joe4]

A few things:

1. What is "DeadlineCanc"? You seem to be referencing it in your UDF, but you are not calculating it or passing a value in for it. Is it some sort of global variable?

2. You should never used reserved words (names of functions, properties, methods, etc) as the name of your variables, procedures, or functions. This can cause ambiguity, errors, and unexpected results.
So I would recommend changing your variable name from "Row" to something like "myRow".

3. Your case statement does not look structured correctly. What exactly is in column D of your sheet? If they are text values like "Dur1", your Case statements should look like:

Case "Dur1"

Otherwise, it is looking for variables named Dur1, etc.

4. I would highly recommend using "Option Explicit" at the beginning of your modules. This forces you to declare all your variables, and will help catch errors resulting from typos or not using variables correctly.
See: http://www.excel-easy.com/vba/examples/option-explicit.html

 

[danners430]

Hi joe,

I'll quickly go through the points

1. DeadlineCanc was simply a method for me to cancel the calculation temporarily while other procedures ran - it's a Public variable, now actually obsolete and unused

2. I've tried umpteen variations of Row, eg. iRow or Rowf, and they all yielded identical results

3. Column D is where the duration for that job is stored. It is a text value, which is contained within the Public variables Dur1-3, which are set when the workbook opens. When testing the code, these variables retained their values.
The reason for using Dur1-3 is so that by simply changing the header in a summary table, the program automatically updates all of the durations to the new value

4. I do use Option Explicit at the top of all of my procedures, it's just not included above The function is halfway down my Functions module

Hope this helps

 

[Joe4]

Regarding point 2, I am not saying that is the cause of this particular error. I am saying that is important advice to heed to avoid problems or unexpected results (more of a "best practices" thing).

I am unable to reproduce your error. Can you lay out an example for me where you are getting an error?
I will need to see the other parts of your code (like the ones that declare and set your Public Variables) and some data that is resulting in errors.

 

[danners430]

Currently I can't reproduce the problem exactly as it was - A solution to this problem was found by writing the code completely differently, and avoiding the need to use the =ROW formula in the conditional formatting window.
However, from the testing that I did I believe the issue lies with the way the =ROW formula passes values in a UDF argument within conditional formatting - the issue did not exist when I copied the exact same formula into a cell, only within conditional formatting. If I could find out what format (integer, long etc.) the return value of =ROW takes I would probably know the answer

 

[Joe4]

If I could find out what format (integer, long etc.) the return value of =ROW takes I would probably know the answer

If I had to guess, I would probably say "Long", as "Integer" isn't big enough to hold row numbers over 32,767.

 

[danners430]

OK... odd... I tried declaring the variable as a long (even double at one point), and it simply didn't call at all, didn't even go into the error handler.
When I finally managed to get it to show me details, it said the VarType was 8204, which as far as I can tell refers to an array of variants...???

 

[Joe4]

If it was me, I would probably rewrite the UDF so that instead of feeding the row number into it, I would feed the range into it (and get the Row number from that right in the UDF).

 

[danners430]

Aye, that's what i ended up doing - The whole situation piqued my curiosity though, and I wanted to get to the bottom of it

 

[Joe4]

Aye, that's what i ended up doing - The whole situation piqued my curiosity though, and I wanted to get to the bottom of it

I understand. I am a curious person too.

If you ever get to the bottom of it, be sure to post back here!