Range calls cause my custom function to not return

[atucker]

Hi all,


This is my first time posting, but I read a few stickies so hopefully my syntax isn't terrible. I can't post the actual sheet I'm working on, as it contains sensitive information. However, I've described my problem and posted my code. If needed, I can probably make a dupe spreadsheet to post here.


I'm having trouble with a custom function that calculates a coefficient of variation for a given range, and compares it to a max acceptable value, then colors the cell accordingly.


Basically, as soon as the function executes any line of code using ranges, the all the values in Watch go "<out of context>", and the function aborts (i've set a breakpoint on End Function that never gets hit). The only times I can make references to ranges is when using MsgBox (the two lines below I made for debugging). All of this is kind of perplexing me.


I'm afraid I'm just making a stupid mistake somewhere. I do know how to debug, and have been stepping through the code and watching variables.


Here's the function code:


[pre]<br />
<pre><code>Function RequirementD96(data As Range, Optional MaxCOV As Double = 5) As Double

'actual calculation part of function; works fine:
RequirementD96 = ((Application.WorksheetFunction.StDev(data)) / (Application.WorksheetFunction.Average(data)))

'code I made to debug
Range("B1:B6&#34.Interior.Color = RGB(200, 160, 35)
MsgBox Application.Caller.Address
MsgBox Application.Range("G3&#34.Address

'' this is code that I want to do, but was ran into the range problems, so I put it on hold.
'Application.Caller.DisplayFormat.NumberFormat = "Percentage"
'If (RequirementD96 <= (MaxCOV / 100)) Then 'the data passes the requirement
'Application.ActiveCell.Interior.Color = vbGreen
'Else
'Application.ActiveCell.Interior.Color = vbRed
'End If

End Function</code></pre>
<p> [/pre]


Thanks for any insight you might be able to provide. I think I've combed through 15 other forums looking for help, but the closest thing to an answer was on here.

 

[Colin Legg]

Welcome to the forum and rest easy, you're not making a stupid mistake.


When a VBA function is called from a range, a golden rule is that the function is not allowed to change the Excel environment: it can only return a value (or set of values) back to the range. Changing a cell's colour would be a change to the Excel environment, so it's not allowed. That's why it's erroring out. Have you looked at using conditional formatting to do the cell colouring instead?

 

[atucker]

Thanks for the input. I'm only calling from a single cell (but the function accepts a range as an argument). I'm trying to format the caller cell (using application.caller).


I would use conditional formatting, but the main point of the custom function is to do this on its own, regardless of the spreadsheet. The calculation itself would be easy to do with formulas, so that's not where the worth of the function is.


Is there any other way a function can format a cell? I've recently found more info on using Selection.

Or, maybe I can make a sub that formats, and call that from the function?

 

[Colin Legg]

Is there any other way a function can format a cell? I've recently found more info on using Selection.

Or, maybe I can make a sub that formats, and call that from the function?

Sorry, no - if the originating call is made from a range then it all gets locked down. Calling a sub from your function won't help.


Using a simple formula of

=STDEV(data)/AVERAGE(data)

and then tuning some conditional formatting to highlight the cell as necessary will be more efficient in calculation terms than using a UDF anyway. I really think that's your best option so that's what I recommend you do.


If your question is, "In theory, is there a way to bypass the environment lockdown?", then the answer is yes, there is. But the two ways I know of aren't a good option in this case.

 

[atucker]

Well, as much as I hate to hear that, it is what it is. Thanks for explaining this to me, Colin. You've saved me a few hours of combing the web before I gave up.

 

[NARAYANK991]

Hi Colin ,


Have you gone through this link ?


http://stackoverflow.com/questions/8520732/i-dont-want-my-excel-add-in-to-return-an-array-instead-i-need-a-udf-to-change


Narayan

 

[Colin Legg]

Hi Narayan,


The windows timer workaround on that link is one of the two 'theoretical' options that I referred to in my previous post. I had a feeling someone might bring one of them up so I covered myself!

If your question is, "In theory, is there a way to bypass the environment lockdown?", then the answer is yes, there is. But the two ways I know of aren't a good option in this case.

It can make Excel extremely unstable (crash Excel) unless handled very delicately, which I expect Kevin has.


The other option I referred to would be to use the WorkSheet_Calculate() event handler to colour the cells: the problem with this is that it's inefficient (calculate event is for all cells on the sheet, not just 1 cell) and the colouring could go out of sync with the formula result if Application.EnableEvents is set to False.


I think both of these options add far more complexity to the workbook than is necessary: Excel's built-in tools do this task quite adequately.


EDIT: I should add that many years ago I posted a windows timer UDF on my old website which lists distinct/unique values from a given range. I'm planning to post a mini series on the technique on my blog in the coming months, so check in over there if you're interested in reading more about it.

 

[SirJB7]

@All

Hi!


More info about using windows timer API calls from Excel:

http://www.mrexcel.com/forum/excel-questions/179683-excel-crashes-using-api-timer.html

http://www.bigresource.com/VB-Timer-API-crashing-Excel-26XtsZWoZt.html


But the key article appears to be this:

http://www.cpearson.com/excel/OnTime.aspx

from which I quote, bold is mine:

"Using Windows Timers

...

These procedures require Excel 2000 or later, since we use the AddressOf operator. The code will not work in Excel 97 or earlier versions.


A NOTE OF CAUTION: If the code executed by the timer changes a cell value, and you are presently in edit mode in Excel (e.g., entering data in a cell), Excel will likely crash completely and you will lose all unsaved work. Use Windows timers with caution."


So my advice is to absolutely avoid this method unless anything else could do the job, and if needed try with OnTime Application method.


You may want to give a look to this recent topic:

http://chandoo.org/forums/topic/help-with-macro-for-dynamically-track-elapsed-time-with-5minute-visual-alarms

from which I quote myself:

"I was wondering about one cell only as the Application.OnTime method would be used. Neither VBA (nor VB too) never felt fully comfortable with timers (VBA doesn't have)"

But in the last comment I had to retract myself:

http://chandoo.org/forums/topic/help-with-macro-for-dynamically-track-elapsed-time-with-5minute-visual-alarms#post-105859


Regards!