I encountered a strange bug in Excel (both 2010 and 2016) such that if one bases a conditional format on the following VBA code:

Public Function uf_TEST_STATUS(r_range As Range, s_string As String) As Boolean
If r_range.Value2 <> “” Then
uf_TEST_STATUS = Application.ThisCell.Value2 <> s_string
Else
uf_TEST_STATUS = False
End If
End Function

the workbook won’t execute the Workbook_Open() event when the workbook is opened. Simply basing a conditional format on a user-defined function isn’t sufficient to disable the Workbook_Open() event though I have yet to figure out precisely what it is about the user-defined function that disables the Workbook_Open() event. However, I do know that it is what the user-defined function does internally, and not just the interface it presents to caller.

Hi KJW

UDFs are supposed to return something to the calling cell (or range, if it’s an array function), but yours seems to be trying to write to another cell (the argument this_cell), which isn’t allowed.

So unless I’m missing something, it doesn’t seem to be a bug.

Is there a reason it has to be a function, rather than a sub?

The Rev Dodgson said:

Hi KJW

UDFs are supposed to return something to the calling cell (or range, if it’s an array function), but yours seems to be trying to write to another cell (the argument this_cell), which isn’t allowed.

So unless I’m missing something, it doesn’t seem to be a bug.

Is there a reason it has to be a function, rather than a sub?

The function is the condition formula for a conditional format (Use a formula to determine which cells to format). The formula itself works when placed in a conditional format formula (but gives a circular reference when placed in a cell).

KJW said:

The Rev Dodgson said:

Hi KJW

UDFs are supposed to return something to the calling cell (or range, if it’s an array function), but yours seems to be trying to write to another cell (the argument this_cell), which isn’t allowed.

So unless I’m missing something, it doesn’t seem to be a bug.

Is there a reason it has to be a function, rather than a sub?

The function is the condition formula for a conditional format (Use a formula to determine which cells to format). The formula itself works when placed in a conditional format formula (but gives a circular reference when placed in a cell).

Have you tried it as a sub rather than function?

The Rev Dodgson said:

Have you tried it as a sub rather than function?

If it’s a sub, it can’t return a Boolean need for the conditional format.

The function itself works fine… it does exactly what I intended it to do. But it turns out (I was exploring this further) that a reference to Application.ThisCell (even an offset from it or simply looking at the row value) causes to Workbook_Open event to not run when the workbook is opened.

The Rev Dodgson said:

UDFs are supposed to return something to the calling cell (or range, if it’s an array function), but yours seems to be trying to write to another cell (the argument this_cell), which isn’t allowed.

I don’t understand why you think my code is trying to write to a cell. I assume you are referring to the line:

uf_TEST_STATUS = Application.ThisCell.Value2 <> s_string

Note that the expression Application.ThisCell.Value2 <> s_string is a truth-value and that uf_TEST_STATUS is a Boolean type.

The purpose of the function is to apply a conditional format to cells that do not contain the text specified by the second argument, but only if the cell specified by the first argument does not contain an empty string (or blank).

KJW said:

The Rev Dodgson said:

UDFs are supposed to return something to the calling cell (or range, if it’s an array function), but yours seems to be trying to write to another cell (the argument this_cell), which isn’t allowed.

I don’t understand why you think my code is trying to write to a cell. I assume you are referring to the line:

uf_TEST_STATUS = Application.ThisCell.Value2 <> s_string

Note that the expression Application.ThisCell.Value2 <> s_string is a truth-value and that uf_TEST_STATUS is a Boolean type.

The purpose of the function is to apply a conditional format to cells that do not contain the text specified by the second argument, but only if the cell specified by the first argument does not contain an empty string (or blank).

It looks like I just mis-read the code. I tried it, and I see how it works now. I don’t use conditional formatting much, and I haven’t used it with VBA, so I can’t be much help. The fact it gives a circular reference when entered in a cell may be the root of the problem, but I don’t know how you can get round it.

The Rev Dodgson said:

The fact it gives a circular reference when entered in a cell may be the root of the problem, but I don’t know how you can get round it.

The code is not intended to be entered into a cell, but a conditional format.

Actually, the problem stated is part of a bigger problem with conditional formatting. So far, I have not found any way to have a conditional format of a cell that is based on the value in the cell itself as well as the value in another cell. Either the conditional format doesn’t work or the Workbook_Open event is disabled. One can get around this by using an additional cell or an additional conditional format, but that is not the point, which is for the conditional format to be directly based on more than one condition.

Note that a conditional format using the formula:

=INDIRECT,COLUMN))<>”“

does work in spite of the circular reference that would occur if the formula were placed in a cell.

KJW said:

Note that a conditional format using the formula:

=INDIRECT,COLUMN))<>”“

does work in spite of the circular reference that would occur if the formula were placed in a cell.

Sorry, the post is not displaying correctly.

KJW said:

KJW said:

Note that a conditional format using the formula:

=INDIRECT,COLUMN))<>”“

does work in spite of the circular reference that would occur if the formula were placed in a cell.

Sorry, the post is not displaying correctly.

=INDIRECT(ADDRESS(ROW(),COLUMN()))<>""

Not very helpful, but:

Adding On Error Resume Next

to the Workbook_Open() sub does not help.

Adding an On Error to the conditional format UDF is always fired, so:

Public Function uf_TEST_STATUS(r_range As Range, s_string As String) As Boolean
On Error GoTo rtntrue:
If r_range.Value2 <> "" Then
uf_TEST_STATUS = Application.ThisCell.Value2 <> s_string
Else
uf_TEST_STATUS = False
Exit Function
End If
rtntrue:
uf_TEST_STATUS = True
End Function

always returns True.

If I find anything more useful, I’ll let you know.

Forget the second one.

The Exit Function should have been after the End if. If you do that, the conditional format with the UDF then works, but the Workbook_Open sub still doesn’t, even with the Resume Next added.

I haven’t read through the link below yet, but it looks like it might be useful:

Charles Williams on Conditional Formats