VBA Range.SpecialCells( xlCellTypeVisible ) not working as a UDF

Hello,

I've just joined the forum in the last few days. While I have cracked numerous Excel apps over the decades, I can't say I don't need more practice. I am much more proficient in the likes of HTML, Javascript, SQL, C, and numerous other scripting languages. Though I could have this app done in no time with those skills, my current client is
desirous of an single workbook Excel 2010 solution. The problem I am experiencing comes down to what I would call a scope issue. The calcFedTax UDF functions just fine when executed in the debugger context either as an F8 step thru or in the immediate window. The autofilter on the "taxes" worksheet filters down correctly to the desired single row, and
the msgbox displays that single correct fedFormulas row address. However, when this function is used on the "paychecks" table as a UDF in the appropriate cell, the msgbox displays the range address for the entire "fedForumlas" table on the "taxes" worksheet, even though the autofilter functioned just fine. Obviously, I cannot use a multirow result. Furthermore, no errors are being tripped. From the way my code is laid out, it looks like the .SpecialCells method is misbehaving when used in this latter context.

Anybody that can offer me suggestions on this issue would be greatly appreciated. I have no clue whats going on here.


---dave k

Please Login or Register  to view this content.

Please Login or Register  to view this content.

UDF's can only return a value to the calling cell. Any* code that manipulates other objects will terminate.

*There are some exceptions to that rule, such as the special cells working incorrectly.

No, you were clear, obviously I was not

The issue is not the arguments but rather where the function was called from.
Your function will work when called from the immediate window or when used in a running macro. It will not work when called from the worksheet.

Normally functions called from the worksheet that manipulate the worksheet or other objects will result in an error.
Some objects, such as SpecialCells, do not error BUT they also do not work correctly.

Take the example in the attached file. It simply returns the sum of formula cells.
When called from the immediate window it works correctly

Please Login or Register  to view this content.

Immediate window

Please Login or Register  to view this content.

When called from the worksheet it returns this incorrect result

Please Login or Register  to view this content.

So again the problem is not your code but where you are using it.