User-defined functions not calculating/recalculating
 

Created several user-defined functions that work well, but now each
time I open Excel the aren't calculating. They will also show errors
from time to time while I am working in the workbook. Is this
something quirky with Excel or am I doing something wrong.

User-defined functions not calculating/recalculating
 

Can we see an example of a problematic UDF and the errors generated?

Mike

"Jim" wrote:

Created several user-defined functions that work well, but now each
time I open Excel the aren't calculating. They will also show errors
from time to time while I am working in the workbook. Is this
something quirky with Excel or am I doing something wrong.

User-defined functions not calculating/recalculating
 

for UDF to update all input cells must be passed through the parameter list.
You can't do something like this


Function MyUDF()

MYUDF = Range("A1")

end function



The UDF will nmot be called when Range "A1" is modified. Instead

Function MyUDF(Target as Range)

MYUDF = Target.Value

end function


"Jim" wrote:

Created several user-defined functions that work well, but now each
time I open Excel the aren't calculating. They will also show errors
from time to time while I am working in the workbook. Is this
something quirky with Excel or am I doing something wrong.

User-defined functions not calculating/recalculating
 

Mike -

Here is one of the functions:

Function AWGCVAR(Avg_G)
' Calulates the variance in AWGC as determined by the appropiate
tier. YTD
Dim Tier1 As Double, Tier2 As Double
Dim Tier3 As Double, tier4 As Double
Dim tier5 As Double
Tier1 = Range("F$743")
Tier2 = Range("F$744")
Tier3 = Range("F$745")
tier4 = Range("F$746")
tier5 = Range("F$747")
Select Case Avg_G
Case Is <= 3000: AWGCVAR = -Tier1 + Avg_G
Case Is <= 4000: AWGCVAR = -Tier2 + Avg_G
Case Is <= 5000: AWGCVAR = -Tier3 + Avg_G
Case Is <= 6000: AWGCVAR = -tier4 + Avg_G
Case Is 6000: AWGCVAR = -tier5 + Avg_G
End Select
End Function

This function works well for me, but when I open Excel now it just
returns Avg_G as the function's result. When I redo the function it
works fine and then I AutoFill the rest of the cells. It is just a
pain to do it everytime I open up the file. Not sure if it has to do
with the fact I have several array functions and several user-defined
functions on this worksheet. Also, I am trying to write a macro for
this file and it could be causing some problems, maybe when I am
debugging?

User-defined functions not calculating/recalculating
 

I have something of the same problem with a function I wrote years ago, back
when I was a newbie. (Actually I'm still a newbie, just less of one.) It's
pretty simple: In a sheet with a few hundred cities I have lots of numbers
representing different types of expenses: hotel, apartment, meals, air fare,
general cost-of-living index and so on. I have no direct data in many cases,
so for example I just estimate the cost of an apartment in Tucson by
comparing it to some other city: In Greensboro the cost of an apartment is
65% that of a hotel, so call Tucson's apartment figure 65% of Tucson's hotel
figure.

For this AdjCol needs to know what other city and what other column it's
working with. I hand it the name of the other city ("Greensboro"), and
AdjCol looks it up, and I hand it the column number of the hotel figure (12),
and thus AdjCol can calculate what it needs, like this:

Function AdjCoL(CityName, Optional BaseCol)
If IsMissing(BaseCol) Then BaseCol = 4
SrchRow = Application.Match(CityName, Columns(1), 0) 'Find the city's row
AdjCoL = Cells(SrchRow, Application.Caller.Column).Value _
* Cells(Application.Caller.Row, BaseCol).Value _
/ Cells(SrchRow, BaseCol).Value
End Function

But from time to time certain calls to this function -- never all of them,
only some of them -- suddenly display the #VALUE! error, and I have to get it
to recalculate them by putting the cursor on that cell and hitting <Enter.
Sometimes one recalculation fixes all the #VALUE! errors; other times I have
to do it to each one. I keep thinking I'll find out one day how to fix that.

But if I understand what Joel is saying, maybe I won't. If the problem is
that I'm feeding AdjCol references to a row and column rather than the row
and column themselves -- to "Greensboro" and 4 rather than R51 and C4 -- then
maybe I'm stuck with the occasional problem, since much of the point of the
function is to save me having to find them manually.

--- "Jim" wrote:
Created several user-defined functions that work well, but now each
time I open Excel the aren't calculating. They will also show errors
from time to time while I am working in the workbook. Is this
something quirky with Excel or am I doing something wrong.

User-defined functions not calculating/recalculating
 

=AWGCVAR(A1,F$743:F$747)

Function AWGCVAR(Avg_G,Tier as Range)
' Calulates the variance in AWGC as determined by the appropiate
tier. YTD
Select Case Avg_G
Case Is <= 3000: AWGCVAR = -Tier(1) + Avg_G
Case Is <= 4000: AWGCVAR = -Tier(2) + Avg_G
Case Is <= 5000: AWGCVAR = -Tier(3) + Avg_G
Case Is <= 6000: AWGCVAR = -tier(4) + Avg_G
Case Is 6000: AWGCVAR = -tier(5) + Avg_G
End Select
End Function


"Jim" wrote:

Mike -

Here is one of the functions:

Function AWGCVAR(Avg_G)
' Calulates the variance in AWGC as determined by the appropiate
tier. YTD
Dim Tier1 As Double, Tier2 As Double
Dim Tier3 As Double, tier4 As Double
Dim tier5 As Double
Tier1 = Range("F$743")
Tier2 = Range("F$744")
Tier3 = Range("F$745")
tier4 = Range("F$746")
tier5 = Range("F$747")
Select Case Avg_G
Case Is <= 3000: AWGCVAR = -Tier1 + Avg_G
Case Is <= 4000: AWGCVAR = -Tier2 + Avg_G
Case Is <= 5000: AWGCVAR = -Tier3 + Avg_G
Case Is <= 6000: AWGCVAR = -tier4 + Avg_G
Case Is 6000: AWGCVAR = -tier5 + Avg_G
End Select
End Function

This function works well for me, but when I open Excel now it just
returns Avg_G as the function's result. When I redo the function it
works fine and then I AutoFill the rest of the cells. It is just a
pain to do it everytime I open up the file. Not sure if it has to do
with the fact I have several array functions and several user-defined
functions on this worksheet. Also, I am trying to write a macro for
this file and it could be causing some problems, maybe when I am
debugging?

User-defined functions not calculating/recalculating
 

Hi Jim,

try something like this

Function AWGCVAR(Avg_G,TierRange as range)
' Calulates the variance in AWGC as determined by the appropiate
tier. YTD
' TierRange is the address of the tier F743:F747

Dim vTier as variant

vTier=TierRange.value

Select Case Avg_G
Case Is <= 3000: AWGCVAR = -Vtier(1,1) + Avg_G
Case Is <= 4000: AWGCVAR = -Vtier(2,1) + Avg_G
Case Is <= 5000: AWGCVAR = -Vtier(3,1) + Avg_G
Case Is <= 6000: AWGCVAR = -Vtier(4,1) + Avg_G
Case Is 6000: AWGCVAR = -Vtier(5,1) + Avg_G
End Select
End Function

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Jim" wrote in message
...
Mike -

Here is one of the functions:

Function AWGCVAR(Avg_G)
' Calulates the variance in AWGC as determined by the appropiate
tier. YTD
Dim Tier1 As Double, Tier2 As Double
Dim Tier3 As Double, tier4 As Double
Dim tier5 As Double
Tier1 = Range("F$743")
Tier2 = Range("F$744")
Tier3 = Range("F$745")
tier4 = Range("F$746")
tier5 = Range("F$747")
Select Case Avg_G
Case Is <= 3000: AWGCVAR = -Tier1 + Avg_G
Case Is <= 4000: AWGCVAR = -Tier2 + Avg_G
Case Is <= 5000: AWGCVAR = -Tier3 + Avg_G
Case Is <= 6000: AWGCVAR = -tier4 + Avg_G
Case Is 6000: AWGCVAR = -tier5 + Avg_G
End Select
End Function

This function works well for me, but when I open Excel now it just
returns Avg_G as the function's result. When I redo the function it
works fine and then I AutoFill the rest of the cells. It is just a
pain to do it everytime I open up the file. Not sure if it has to do
with the fact I have several array functions and several user-defined
functions on this worksheet. Also, I am trying to write a macro for
this file and it could be causing some problems, maybe when I am
debugging?

User-defined functions not calculating/recalculating
 

Thanks for everyone's input. This should really help me get started.

User-defined functions not calculating/recalculating
 

I'm not sure about this, but have you considered the "Application.Volatile"
statement at the beginning of your function?

Tom

"Jim" wrote:

Created several user-defined functions that work well, but now each
time I open Excel the aren't calculating. They will also show errors
from time to time while I am working in the workbook. Is this
something quirky with Excel or am I doing something wrong.

User-defined functions not calculating/recalculating
 

Actually, the formula works quite well now Joel. I still have some
work to do, but I have eliminated a lot of unnecessary code. Thanks,

Jim