Conflict with variables with the same value?

[kingofaces]

I'm working on writing a function for growing degree days (sine wave if anyone is familiar) and I have just one little snag that's been bothering me. The sheet is set up with two columns, minimum and max temperatures and the formula SinehalfDD is used for each row. The problem I have is that when the min and max temps are equal, the cell returns #VALUE! for that row, otherwise everything else is working fine. My VBA knowledge is just pieced together from scratch, so I'm guessing this has something to do with how I set my variables up? This example from the start of the whole thing shows the problem I'm running into.

Code:

Function SinhalfDD(Min, Max, Base, Ceiling) As Double

Dim Taverage As Double
Dim W As Double
Dim PTheta1 As Double
Dim PTheta2 As Double

Taverage = (Max + Min) / 2
W = (Max - Min) / 2
PTheta1 = (Base - Taverage) / W
PTheta2 = (Ceiling - Taverage) / W

....
....
End Function

I've narrowed it down to this where say I input 30 for both the min and max values. I could create another variable from something simple like = Max + Min and it would return 60 just fine. However, once I ask for the value of Taverage to be returned, that's when I get the VALUE error. Obviously (30+30)/2 should be 30. This is only for identical values though. If I had 60 for a max and 30 for a min all of the variables listed above would have regular numbers and the rest of the formula would work just fine. I have also tried just setting up an If statement to set SinehalfDD = 0 outright if the max and min are equal, but that still returns the error, so I figure it has to be something outside the formula I've made and rather an issue with the variables themselves. Any pointers are greatly appreciated as this is the only special case left I need to work out before I can use it.

Windows XP/ Excel 2003

 

[sulakvea]

hi, and welcome to the board

in your code below you divide a number by zero when Min = Max (i,.e,. when that happens, W = 0, hence everything that is divided by W returns an error).

you can put a condition before the division (depending on what you want the desired outcome to be in Max = Min situation)

W = (Max - Min) / 2
If W = 0 Then 
 "Desired ouctome, like SinhalfDD = 0"
Else
 PTheta1 = (Base - Taverage) / W
 PTheta2 = (Ceiling - Taverage) / W
End if

 

[JoeMo]

If Max and Min are equal, then W = 0. In that case, PTheta1 and PTheta2 will generate errors (divide by zero). This seems more likely to be your error source than TAverage.

 

[Norie]

I doubt it's an issue with the variables, more likely what you are actually doing with them.

I've never heard of such a thing as a conflict between values.

Variable names yes, but that' usually to do with names and/or scope/visibility.

You do realise that if Max=Min then W=0 and you will be trying to divide by zero.

Don't know why but I don't think you can do that without error.

 

[kingofaces]

I know about the errors that would come from dividing by W = 0. That one doesn't concern me too much since I can work with that further on down the line. The main issue is that something like TAverage = (Max + Min)/2 returns an error. Any number for max/min should return value in a numerical sense at least.

The start of the whole issue is with the TAverage and W variables as each of those formulas shouldn't have any problems. If you use the max = 30 and min = 30 you should have 30 and 0 respectively, yet, I still end up with #VALUE! for both. Here's the way the formula is entered into the cell (base and ceiling aren't entirely relevant at this point of the formulas) =SinhalfDD(30,30,50,86)

 

[Norie]

You should deal with the division problem at the start.

It's an error - the function will stop as soon as it's encountered.

 

[kingofaces]

It's an error - the function will stop as soon as it's encountered.

And I knew it was going to be a problem due to absent mindedness, but I didn't think it would be that bad. :P The moment you said that pretty much resulted in a palm to the face. Once I took the thetas out everything worked just fine, so it won't take much to get things back in order again. I had included If statements later on to try to tackle that since it fit well with everything going on in those sections before more major calculations. Apparently getting sloppy on sequence was the cause. Thanks a bunch.

 

[Norie]

No problem.

Functions can sometimes be harder than subs to debug.

That's mainly because when an error is encountered they just stop, no error messages, no debug option...

One way I've found that helps is to set up a breakpoint on the function header.