UDF returns #NUM! with F9 or Application.CalculateFull if sheet notactive

G

Greg

I made a User Defined Function that generates a random number from a
cumulative frequency distribution (see below). The function works
properly if the workbook is recalculated when the sheet that contains
the function is active. However, the function returns a #NUM! if a
different sheet is active when the workbook is recalculated. Does
anyone know how to fix this? I get the same error whether manually
recalculating with F9 or using Application.CalculateFull from VBA.

- Greg


Function GenCFD(InRange) As Variant
'randomly select values from an input of a cumulative frequency
distribution
'The input range (InRange) should be two contiguous columns of data
'with probability values (from 0 to 1) in the first column
'and X values corresponding to each probability in the second column
Application.Volatile (True) 'recalculate this cell on pressing F9
Dim SubSetRange, Cell
Dim ir As Long, ic As Long, irprev As Long, icprev As Long, icount
As Long
Dim X As Double, Y As Double, xprev As Double, yprev As Double
Dim PRandom As Double
Dim found As Boolean
'The Set statement uses the Intersect function to create a new
range object
'that consists of the intersection of the UsedRange and the input
range,
'to minimize the loop through all cells in the range
'limited to exclude those cells that are beyond the worksheet's
"used range."
Set SubSetRange = _
Intersect(InRange.Parent.UsedRange, InRange)
ir = 0: ic = 0
X = -999: Y = -999
PRandom = YRandom
found = False
For Each Cell In SubSetRange
irprev = ir: icprev = ic
xprev = X: yprev = Y
ir = Cell.Row: ic = Cell.Column
If ir > irprev Then
X = Cells(ir, ic).value
If X < xprev Or X < 0 Or X > 1 Then
MsgBox "Check that first column for range of gencfd is
sorted values from 0 to 1"
GenCFD = CVErr(xlErrNum) 'check that first column is
sorted values from 0 to 1
End If
Y = Cells(ir, ic + 1).value
If xprev <> -999 And PRandom >= xprev And PRandom <= X Then
GenCFD = linearinterpolate(PRandom, xprev, yprev, X, Y)
found = True
Exit For
End If
End If
Next Cell
If found = False Then
GenCFD = CVErr(xlErrNum) 'didn't find a value - check that
first column is sorted values from 0 to 1
'MsgBox "Didn't find a value in GENCFD for PRandom=" & PRandom &
" - check that first column is sorted values from 0 to 1."
'End
End If
End Function

Function YRandom() As Double
Application.Volatile (True)
If (s10 = 0 And s11 = 0 And s12 = 0) And (s20 = 0 And s21 = 0 And
s22 = 0) Then
s10 = 64785
s11 = 3546
s12 = 123456
s20 = 658478
s21 = 73575
s22 = 234567
End If
Dim k As Long
Dim p1, p2 As Double
p1 = a12 * s11 - a13n * s10
k = p1 / m1
p1 = p1 - (k * m1)
If (p1 < 0) Then
p1 = p1 + m1
End If
s10 = s11
s11 = s12
s12 = p1
p2 = a21 * s22 - a23n * s20
k = p2 / m2
p2 = p2 - (k * m2)
If (p2 < 0) Then
p2 = p2 + m2
End If
s20 = s21
s21 = s22
s22 = p2
If (p1 <= p2) Then
YRandom = ((p1 - p2 + m1) * norm)
Else
YRandom = ((p1 - p2) * norm)
End If
End Function
 
J

Jim Cone

Your YRandom function looks like trouble.
Are a12 and a21 typos?
Where and how are all of the variables declared?
Are you using Option Explicit?
--
Jim Cone
Portland, Oregon USA



"Greg" <[email protected]>
wrote in message
I made a User Defined Function that generates a random number from a
cumulative frequency distribution (see below). The function works
properly if the workbook is recalculated when the sheet that contains
the function is active. However, the function returns a #NUM! if a
different sheet is active when the workbook is recalculated. Does
anyone know how to fix this? I get the same error whether manually
recalculating with F9 or using Application.CalculateFull from VBA.
- Greg


Function GenCFD(InRange) As Variant
'randomly select values from an input of a cumulative frequency
distribution
'The input range (InRange) should be two contiguous columns of data
'with probability values (from 0 to 1) in the first column
'and X values corresponding to each probability in the second column
Application.Volatile (True) 'recalculate this cell on pressing F9
Dim SubSetRange, Cell
Dim ir As Long, ic As Long, irprev As Long, icprev As Long, icount
As Long
Dim X As Double, Y As Double, xprev As Double, yprev As Double
Dim PRandom As Double
Dim found As Boolean
'The Set statement uses the Intersect function to create a new
range object
'that consists of the intersection of the UsedRange and the input
range,
'to minimize the loop through all cells in the range
'limited to exclude those cells that are beyond the worksheet's
"used range."
Set SubSetRange = _
Intersect(InRange.Parent.UsedRange, InRange)
ir = 0: ic = 0
X = -999: Y = -999
PRandom = YRandom
found = False
For Each Cell In SubSetRange
irprev = ir: icprev = ic
xprev = X: yprev = Y
ir = Cell.Row: ic = Cell.Column
If ir > irprev Then
X = Cells(ir, ic).value
If X < xprev Or X < 0 Or X > 1 Then
MsgBox "Check that first column for range of gencfd is
sorted values from 0 to 1"
GenCFD = CVErr(xlErrNum) 'check that first column is
sorted values from 0 to 1
End If
Y = Cells(ir, ic + 1).value
If xprev <> -999 And PRandom >= xprev And PRandom <= X Then
GenCFD = linearinterpolate(PRandom, xprev, yprev, X, Y)
found = True
Exit For
End If
End If
Next Cell
If found = False Then
GenCFD = CVErr(xlErrNum) 'didn't find a value - check that
first column is sorted values from 0 to 1
'MsgBox "Didn't find a value in GENCFD for PRandom=" & PRandom &
" - check that first column is sorted values from 0 to 1."
'End
End If
End Function

Function YRandom() As Double
Application.Volatile (True)
If (s10 = 0 And s11 = 0 And s12 = 0) And (s20 = 0 And s21 = 0 And
s22 = 0) Then
s10 = 64785
s11 = 3546
s12 = 123456
s20 = 658478
s21 = 73575
s22 = 234567
End If
Dim k As Long
Dim p1, p2 As Double
p1 = a12 * s11 - a13n * s10
k = p1 / m1
p1 = p1 - (k * m1)
If (p1 < 0) Then
p1 = p1 + m1
End If
s10 = s11
s11 = s12
s12 = p1
p2 = a21 * s22 - a23n * s20
k = p2 / m2
p2 = p2 - (k * m2)
If (p2 < 0) Then
p2 = p2 + m2
End If
s20 = s21
s21 = s22
s22 = p2
If (p1 <= p2) Then
YRandom = ((p1 - p2 + m1) * norm)
Else
YRandom = ((p1 - p2) * norm)
End If
End Function
 
J

joel

#Num usually means the UDF didn't finish executing or you didn't return a
value from the UDF. Try stepping throughthe code to find the problem.

1) Place break point at 1st line of UDF by clicking on line of code and
pressing F9.

2) go to worksheet where UDF is caleed and click on cell. Then go to F(x)
box on top of worksheet and click on end of formula. Then press Enter. this
will force a change to the worksheet and cause the UDF to execute. You
should stop on the break point.

3) Step through code using F8. You casn view the variables in the macro by
either moving the cursor over the variables or adding a watch. To add a
watch highligh any variable and right click mouse. Then select Add to Watch
and press OK in pop up window. You can add addional break points with F9,
or go to next break point with F5.
 
G

Greg

I did some debugging and I found out that the loop "For Each Cell in
Subset Range" is looping through the cells in the worksheet that is
active at the time the macro is run, instead of the worksheet of the
cell that contains the function. I need to find a way to loop through
the values on the sheet where the function is located instead of the
active sheet at the time when the subroutine is run regardless of
which sheet is active when the workbook is recalculated.

Does anyone know of a good tutorial on the best way to loop through
the values of a 2-D input range in a UDF?
 
C

Charles Williams

You have some unqualified references which will default to the actiove
sheet:
X = Cells(ir, ic).value

Also its not a good idea to use a reserved word like Cell as a variable.

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com
 
J

joel

don't think your analysis is correct when you said that it was loop through
the active sheets. I believe that was only the case becasue of the way you
were debugging the code. When a UDF is run all the cells default to the
worksheet where the UDF function is called from. When you step through the
code and you have a diffferent worksheet active it will use the
activeworksheet. I think you had the wrong worksheet activated when you
triggered the UDF or you changed the worksheet while you were stepping
through the UDF. Another possibilty is you are calling the UDF from more
than one worksheet. When you change a cells in the worksbook you have no
control over the order the workbook perfroms the updates. I often get
confused when debugging the UDF from which formula it is called. theis is my
solution. If the UDF is called multiple times from your workbook copy the
UDF and change the name from GenCFD to GenCFD1. Then change only one of the
locations in your workbook to the new name and add a break point in the
GenCFD1. This way you know where it was called from and can debug the
problem easier.
 
C

Charles Williams

Joel,

You are right, but IMHO its still not a good idea to use Cell as a variable
name as it makes the code harder ro read and leads to confusion.

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

Charles Williams

Usually the best way is to assign the Range to a variant and then loop
through the resulting variant array, something like

dim vArr as variant
dim j as long
dim k as long
dim dblSum as double
vArr=SubSetRange.Value2
for j=lbound(varr,1) to ubound(varr,1)
for k=lbound(varr,2) to ubound(varr,2)
if isnumeric(varr(j,k)) then dblSum=dblSum+varr(j,k)
next k
next j

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

Chip Pearson

Does anyone know of a good tutorial on the best way to loop through
the values of a 2-D input range in a UDF?
Click to expand...

See my reply to your later post. In short, you should pass a Range as
a parameter and do your calculations based on that range. In this
case, it doesn't matter which might be active when the calculation
takes place -- the Range parameter will always refer to the correct
worksheet. If for some reason you need to get the cell or worksheet on
which the UDF function was entered, you can use Application.Caller.
This will return a reference to the cell containing the formula. From
that, you can get a Worksheet reference and a Workbook reference.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

stock returns/average/formatting 1
UDF to Calculate YTM 5
VBA Linest function for polynomial regression on horizontal range 0
VBA killing my named ranges 4
Evaluate error when using 2 2
Storing and retrieving 3-D arrays in/from workbook names 2
Problem with UDF 2
Programming "yield" function in Microsoft Access 0

Top