Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns #NUM! with F9 or Application.CalculateFull if sheet notactive
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns #NUM! with F9 or Application.CalculateFull if sheet not active
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns #NUM! with F9 or Application.CalculateFull if sheet no
#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. "Greg" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns #NUM! with F9 or Application.CalculateFull if sheetno
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns #NUM! with F9 or Application.CalculateFull if sheet not active
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 "Greg" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns #NUM! with F9 or Application.CalculateFull if shee
Cell is not a reserved word in VBA, it is cells with an S.
"Charles Williams" wrote: 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 "Greg" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns #NUM! with F9 or Application.CalculateFull if shee
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. "Greg" wrote: 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns #NUM! with F9 or Application.CalculateFull if shee
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 "joel" wrote in message ... Cell is not a reserved word in VBA, it is cells with an S. "Charles Williams" wrote: 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 "Greg" 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns #NUM! with F9 or Application.CalculateFull if shee
I like cell because it is very descriptive. Haven't found a better term.
"Charles Williams" wrote: 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 "joel" wrote in message ... Cell is not a reserved word in VBA, it is cells with an S. "Charles Williams" wrote: 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 "Greg" 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns #NUM! with F9 or Application.CalculateFull if shee
I use Dim oCell as range or Dim oRng as range
Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "joel" wrote in message ... I like cell because it is very descriptive. Haven't found a better term. "Charles Williams" wrote: 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 "joel" wrote in message ... Cell is not a reserved word in VBA, it is cells with an S. "Charles Williams" wrote: 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 "Greg" 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns #NUM! with F9 or Application.CalculateFull if sheet no
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 "Greg" wrote in message ... 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? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF returns #NUM! with F9 or Application.CalculateFull if sheet no
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? 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) On Tue, 7 Apr 2009 21:40:00 -0700 (PDT), Greg wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA calling a web application that returns XML | Excel Programming | |||
Application.CalculateFull | Excel Programming | |||
Is it necessary to use CalculateFull command when in automatic mod | Excel Discussion (Misc queries) | |||
application.match Returns what | Excel Programming | |||
Application.Calculatefull function | Excel Programming |