Volatile Functions causing issues

How about short macro in Thisworksheet code:

Please Login or Register  to view this content.

Try adding...
Application.Volatile
...to the top of your UDF procedure.

Otherwise, can you post the UDF code here?


Application.Volatile Method

Your first function is suffering from the same error discussed here: http://www.excelforum.com/excel-prog...ml#post3839103 You are using an unqualified reference to a range -- which usually means activesheet.range(...). When you step away from the sheet containing the UDF to another sheet, activesheet obviously changes, and the data in activesheet is likely incompatible with what the function wants -- causing the error.

As before, I would suggest that you pass all information needed by the UDF to the UDF through the argument list. This should alleviate the errors you are getting, and will it also avoid any need to make the function volatile, because Excel will be able to fit the function into its calculation tree.

Not just that - all the Range statements need qualifying too - like this one:

Please Login or Register  to view this content.

Originally Posted by penfold1992

is there any other way I can get around that? The range will always be constant to the workbook and macro.
Is it possible to do something like...

Please Login or Register  to view this content.

would that work better or no different?
(I assume you are talking about the "ListObject" table.)

Yes it is possible and it is syntactically correct. Only you can know if the data and reference are truly static. I find that, when I have done this sort of thing in the past thinking that it would always be static, when I come back to it, I tend to wish that I had coded it as an argument rather than hard coding it into the body of the function. Maybe think of it as a useful "best practices rule" (for which there will occasionally be exceptions). Many of us on here believe that passing data through the argument list is the best way to do it -- if you think your situation warrants an exception to this "rule", then you can certainly do it the way you are proposing.

One way or another, it seems that the solution to your problem is to make sure that Excel/VBA cannot accidentally pull data from somewhere other than the intended range.

I strongly suggest you go back to the original suggestion of passing the ranges to the function as arguments.

IMO, "Here's my code, it doesn't work" is one of the least useful "error statements" a debugger or an OP can give. What do you mean by "doesn't work"? Does it hang up? Does it give an error message? Does it run to completion, but the results are incorrect?

I don't offhand see anything syntactically wrong with the worksheetfunction.countifs() call, so if there is an error here, it is related to the data in those ranges. You will need to look at the data in those ranges and the parameters sent to the countif function to see if there is some "incompatibility" that is causing an error/incorrect result.

I know that one of the most used debugging strategies that I use is to set a breakpoint early in the function and step through the code one line at a time: http://www.cpearson.com/excel/DebuggingVBA.aspx If a statement errors, I will know which statement causes the error and can focus my debugging on that statement (through the locals window and various watches). If I am getting incorrect results, I can follow the values of the different variables to identify where in the calculation an unexpected result is occurring. It sounds like you have an added difficulty in that the error only occurs some of the time, which can mean looking at different contexts to understand when the errors are occurring.