|
User Defined Function Causing Problems
I'm trying to convert an old GW Basic program into a more user friendly and
more updateable spreadsheet. I was getting on fine but now that I have created and implemented a function into the spreadsheet it crashes everytime data is changed. The conversion was fine until I came to a repeating loop that calculated the value of one variable needed to generate a particular value in another. I have managed to create a working function that does the job. It requires three values to be called in which come from named cells [i.e. =X1CALC(IE, JC, JD) ]. Now elsewhere in the spreadsheet are some pull down menus that alter some of the formulae being used. Every time that the data changes that is called into the function Excel crashes. I presume that its because the function will be trying to update itself and can't for some reason. I have tried only calculating the values on a press of F9 but it still has no effect. Excel still crashes. I had a quick try at re writing the function as a Sub and assigning the values from the cells IE, JC and JD to variables within the Sub but I couldn't get that to work let alone asigning the output to a cell. Can anyone help? |
User Defined Function Causing Problems
This is very interesting.
Please post the UDF and we will take a look at it. -- Gary's Student "Steve" wrote: I'm trying to convert an old GW Basic program into a more user friendly and more updateable spreadsheet. I was getting on fine but now that I have created and implemented a function into the spreadsheet it crashes everytime data is changed. The conversion was fine until I came to a repeating loop that calculated the value of one variable needed to generate a particular value in another. I have managed to create a working function that does the job. It requires three values to be called in which come from named cells [i.e. =X1CALC(IE, JC, JD) ]. Now elsewhere in the spreadsheet are some pull down menus that alter some of the formulae being used. Every time that the data changes that is called into the function Excel crashes. I presume that its because the function will be trying to update itself and can't for some reason. I have tried only calculating the values on a press of F9 but it still has no effect. Excel still crashes. I had a quick try at re writing the function as a Sub and assigning the values from the cells IE, JC and JD to variables within the Sub but I couldn't get that to work let alone asigning the output to a cell. Can anyone help? |
User Defined Function Causing Problems
Function X1CALC(IE, JC, JD)
Dim ZZ(9) As Single Dim V As Single Dim I As Integer X1 = 0 I = 1 ZZ(1) = 10000 ZZ(2) = 1000 ZZ(3) = 100 ZZ(4) = 10 ZZ(5) = 1 ZZ(6) = 0.1 ZZ(7) = 0.01 ZZ(8) = 0.001 ZZ(9) = 0.0001 For I = 1 To 9 X1 = X1 + ZZ(I) V = (IE * (X1 ^ 3)) + (JD * (X1 ^ 2)) If V = JC Then I = 11 End If If V < JC Then If V - JC = 0 Then If V - JC 0.001 Then X1 = X1 - ZZ(I) End If If V = JC Then I = 10 End If Else I = I - 1 End If End If Next I X1CALC = X1 End Function "Gary''s Student" wrote: This is very interesting. Please post the UDF and we will take a look at it. -- Gary's Student "Steve" wrote: I'm trying to convert an old GW Basic program into a more user friendly and more updateable spreadsheet. I was getting on fine but now that I have created and implemented a function into the spreadsheet it crashes everytime data is changed. The conversion was fine until I came to a repeating loop that calculated the value of one variable needed to generate a particular value in another. I have managed to create a working function that does the job. It requires three values to be called in which come from named cells [i.e. =X1CALC(IE, JC, JD) ]. Now elsewhere in the spreadsheet are some pull down menus that alter some of the formulae being used. Every time that the data changes that is called into the function Excel crashes. I presume that its because the function will be trying to update itself and can't for some reason. I have tried only calculating the values on a press of F9 but it still has no effect. Excel still crashes. I had a quick try at re writing the function as a Sub and assigning the values from the cells IE, JC and JD to variables within the Sub but I couldn't get that to work let alone asigning the output to a cell. Can anyone help? |
User Defined Function Causing Problems
After Dim-ing X1 (as Double) and adding "End Function" it worked for me.
With what values do you call the function? How does Excel "crash"? -- Kind regards, Niek Otten Microsoft MVP - Excel "Steve" wrote in message ... | Function X1CALC(IE, JC, JD) | | Dim ZZ(9) As Single | Dim V As Single | Dim I As Integer | | X1 = 0 | I = 1 | | ZZ(1) = 10000 | ZZ(2) = 1000 | ZZ(3) = 100 | ZZ(4) = 10 | ZZ(5) = 1 | ZZ(6) = 0.1 | ZZ(7) = 0.01 | ZZ(8) = 0.001 | ZZ(9) = 0.0001 | | For I = 1 To 9 | | X1 = X1 + ZZ(I) | V = (IE * (X1 ^ 3)) + (JD * (X1 ^ 2)) | | If V = JC Then | I = 11 | End If | | If V < JC Then | If V - JC = 0 Then | | If V - JC 0.001 Then | X1 = X1 - ZZ(I) | End If | | If V = JC Then | I = 10 | End If | Else | I = I - 1 | | End If | End If | | Next I | | X1CALC = X1 | | End Function | | "Gary''s Student" wrote: | | This is very interesting. | | Please post the UDF and we will take a look at it. | | | -- | Gary's Student | | | "Steve" wrote: | | I'm trying to convert an old GW Basic program into a more user friendly and | more updateable spreadsheet. I was getting on fine but now that I have | created and implemented a function into the spreadsheet it crashes everytime | data is changed. | | The conversion was fine until I came to a repeating loop that calculated the | value of one variable needed to generate a particular value in another. I | have managed to create a working function that does the job. It requires | three values to be called in which come from named cells [i.e. =X1CALC(IE, | JC, JD) ]. Now elsewhere in the spreadsheet are some pull down menus that | alter some of the formulae being used. Every time that the data changes that | is called into the function Excel crashes. I presume that its because the | function will be trying to update itself and can't for some reason. I have | tried only calculating the values on a press of F9 but it still has no | effect. Excel still crashes. | | I had a quick try at re writing the function as a Sub and assigning the | values from the cells IE, JC and JD to variables within the Sub but I | couldn't get that to work let alone asigning the output to a cell. | | Can anyone help? |
User Defined Function Causing Problems
I can't make it crash... for example:
=x1calc(0.1,0.2,0.3) yields 0.733099997043609 =x1calc(0.4,0.2,0.3) yields 0.608099937438964 =x1calc(0.1,0.2,0.7) yields 0.51609992980957 I suspect that the problem may be related to your changing the value of I within the For Loop. Is it possible that the value of I can be getting to 0 or 11? This would cause a crash. -- Gary''s Student "Steve" wrote: Function X1CALC(IE, JC, JD) Dim ZZ(9) As Single Dim V As Single Dim I As Integer X1 = 0 I = 1 ZZ(1) = 10000 ZZ(2) = 1000 ZZ(3) = 100 ZZ(4) = 10 ZZ(5) = 1 ZZ(6) = 0.1 ZZ(7) = 0.01 ZZ(8) = 0.001 ZZ(9) = 0.0001 For I = 1 To 9 X1 = X1 + ZZ(I) V = (IE * (X1 ^ 3)) + (JD * (X1 ^ 2)) If V = JC Then I = 11 End If If V < JC Then If V - JC = 0 Then If V - JC 0.001 Then X1 = X1 - ZZ(I) End If If V = JC Then I = 10 End If Else I = I - 1 End If End If Next I X1CALC = X1 End Function "Gary''s Student" wrote: This is very interesting. Please post the UDF and we will take a look at it. -- Gary's Student "Steve" wrote: I'm trying to convert an old GW Basic program into a more user friendly and more updateable spreadsheet. I was getting on fine but now that I have created and implemented a function into the spreadsheet it crashes everytime data is changed. The conversion was fine until I came to a repeating loop that calculated the value of one variable needed to generate a particular value in another. I have managed to create a working function that does the job. It requires three values to be called in which come from named cells [i.e. =X1CALC(IE, JC, JD) ]. Now elsewhere in the spreadsheet are some pull down menus that alter some of the formulae being used. Every time that the data changes that is called into the function Excel crashes. I presume that its because the function will be trying to update itself and can't for some reason. I have tried only calculating the values on a press of F9 but it still has no effect. Excel still crashes. I had a quick try at re writing the function as a Sub and assigning the values from the cells IE, JC and JD to variables within the Sub but I couldn't get that to work let alone asigning the output to a cell. Can anyone help? |
User Defined Function Causing Problems
Why does X1 need to be defined as Double? I wasn't quite sure what all the
various types were and there as an End function on my one just coppied it wrong! The values called into the function a IE = 0.010477442 JC = 230929464 JD = 7760.7996 After the changes that cause it to crash the values a IE = Same JC = Same JD = 4226.792 Excel crashes as in it does nothing for a time then when you try click within the window the 'Not Responding' appears in the title bar. Have tried waiting for a fair while and wouldn't have thought it would have taken that long to recalculate. Thanks for the interest so far! "Niek Otten" wrote: After Dim-ing X1 (as Double) and adding "End Function" it worked for me. With what values do you call the function? How does Excel "crash"? -- Kind regards, Niek Otten Microsoft MVP - Excel "Steve" wrote in message ... | Function X1CALC(IE, JC, JD) | | Dim ZZ(9) As Single | Dim V As Single | Dim I As Integer | | X1 = 0 | I = 1 | | ZZ(1) = 10000 | ZZ(2) = 1000 | ZZ(3) = 100 | ZZ(4) = 10 | ZZ(5) = 1 | ZZ(6) = 0.1 | ZZ(7) = 0.01 | ZZ(8) = 0.001 | ZZ(9) = 0.0001 | | For I = 1 To 9 | | X1 = X1 + ZZ(I) | V = (IE * (X1 ^ 3)) + (JD * (X1 ^ 2)) | | If V = JC Then | I = 11 | End If | | If V < JC Then | If V - JC = 0 Then | | If V - JC 0.001 Then | X1 = X1 - ZZ(I) | End If | | If V = JC Then | I = 10 | End If | Else | I = I - 1 | | End If | End If | | Next I | | X1CALC = X1 | | End Function | | "Gary''s Student" wrote: | | This is very interesting. | | Please post the UDF and we will take a look at it. | | | -- | Gary's Student | | | "Steve" wrote: | | I'm trying to convert an old GW Basic program into a more user friendly and | more updateable spreadsheet. I was getting on fine but now that I have | created and implemented a function into the spreadsheet it crashes everytime | data is changed. | | The conversion was fine until I came to a repeating loop that calculated the | value of one variable needed to generate a particular value in another. I | have managed to create a working function that does the job. It requires | three values to be called in which come from named cells [i.e. =X1CALC(IE, | JC, JD) ]. Now elsewhere in the spreadsheet are some pull down menus that | alter some of the formulae being used. Every time that the data changes that | is called into the function Excel crashes. I presume that its because the | function will be trying to update itself and can't for some reason. I have | tried only calculating the values on a press of F9 but it still has no | effect. Excel still crashes. | | I had a quick try at re writing the function as a Sub and assigning the | values from the cells IE, JC and JD to variables within the Sub but I | couldn't get that to work let alone asigning the output to a cell. | | Can anyone help? |
User Defined Function Causing Problems
It could well be I suppose. By altering the value the I value will alter at
different times so could well be. The strange thing is that when I recalculate the formula using the altered values it works. It just seems to be the transition. I take it that there shouldn't be a problem with it reclaculating automatically. I'll try rewriting the program, though only got it working this morning after puzzling over it for some of yesterday! "Gary''s Student" wrote: I can't make it crash... for example: =x1calc(0.1,0.2,0.3) yields 0.733099997043609 =x1calc(0.4,0.2,0.3) yields 0.608099937438964 =x1calc(0.1,0.2,0.7) yields 0.51609992980957 I suspect that the problem may be related to your changing the value of I within the For Loop. Is it possible that the value of I can be getting to 0 or 11? This would cause a crash. -- Gary''s Student "Steve" wrote: Function X1CALC(IE, JC, JD) Dim ZZ(9) As Single Dim V As Single Dim I As Integer X1 = 0 I = 1 ZZ(1) = 10000 ZZ(2) = 1000 ZZ(3) = 100 ZZ(4) = 10 ZZ(5) = 1 ZZ(6) = 0.1 ZZ(7) = 0.01 ZZ(8) = 0.001 ZZ(9) = 0.0001 For I = 1 To 9 X1 = X1 + ZZ(I) V = (IE * (X1 ^ 3)) + (JD * (X1 ^ 2)) If V = JC Then I = 11 End If If V < JC Then If V - JC = 0 Then If V - JC 0.001 Then X1 = X1 - ZZ(I) End If If V = JC Then I = 10 End If Else I = I - 1 End If End If Next I X1CALC = X1 End Function "Gary''s Student" wrote: This is very interesting. Please post the UDF and we will take a look at it. -- Gary's Student "Steve" wrote: I'm trying to convert an old GW Basic program into a more user friendly and more updateable spreadsheet. I was getting on fine but now that I have created and implemented a function into the spreadsheet it crashes everytime data is changed. The conversion was fine until I came to a repeating loop that calculated the value of one variable needed to generate a particular value in another. I have managed to create a working function that does the job. It requires three values to be called in which come from named cells [i.e. =X1CALC(IE, JC, JD) ]. Now elsewhere in the spreadsheet are some pull down menus that alter some of the formulae being used. Every time that the data changes that is called into the function Excel crashes. I presume that its because the function will be trying to update itself and can't for some reason. I have tried only calculating the values on a press of F9 but it still has no effect. Excel still crashes. I had a quick try at re writing the function as a Sub and assigning the values from the cells IE, JC and JD to variables within the Sub but I couldn't get that to work let alone asigning the output to a cell. Can anyone help? |
User Defined Function Causing Problems
With your data:
=x1calc(0.010477442, 230929464, 7760.7996) yields 172.478790283203 =x1calc(0.010477442, 230929464, 4226.792) yields 233.672973632812 Make sure that you are not going into an infinite loop by setting i=i-1 -- Gary's Student "Steve" wrote: It could well be I suppose. By altering the value the I value will alter at different times so could well be. The strange thing is that when I recalculate the formula using the altered values it works. It just seems to be the transition. I take it that there shouldn't be a problem with it reclaculating automatically. I'll try rewriting the program, though only got it working this morning after puzzling over it for some of yesterday! "Gary''s Student" wrote: I can't make it crash... for example: =x1calc(0.1,0.2,0.3) yields 0.733099997043609 =x1calc(0.4,0.2,0.3) yields 0.608099937438964 =x1calc(0.1,0.2,0.7) yields 0.51609992980957 I suspect that the problem may be related to your changing the value of I within the For Loop. Is it possible that the value of I can be getting to 0 or 11? This would cause a crash. -- Gary''s Student "Steve" wrote: Function X1CALC(IE, JC, JD) Dim ZZ(9) As Single Dim V As Single Dim I As Integer X1 = 0 I = 1 ZZ(1) = 10000 ZZ(2) = 1000 ZZ(3) = 100 ZZ(4) = 10 ZZ(5) = 1 ZZ(6) = 0.1 ZZ(7) = 0.01 ZZ(8) = 0.001 ZZ(9) = 0.0001 For I = 1 To 9 X1 = X1 + ZZ(I) V = (IE * (X1 ^ 3)) + (JD * (X1 ^ 2)) If V = JC Then I = 11 End If If V < JC Then If V - JC = 0 Then If V - JC 0.001 Then X1 = X1 - ZZ(I) End If If V = JC Then I = 10 End If Else I = I - 1 End If End If Next I X1CALC = X1 End Function "Gary''s Student" wrote: This is very interesting. Please post the UDF and we will take a look at it. -- Gary's Student "Steve" wrote: I'm trying to convert an old GW Basic program into a more user friendly and more updateable spreadsheet. I was getting on fine but now that I have created and implemented a function into the spreadsheet it crashes everytime data is changed. The conversion was fine until I came to a repeating loop that calculated the value of one variable needed to generate a particular value in another. I have managed to create a working function that does the job. It requires three values to be called in which come from named cells [i.e. =X1CALC(IE, JC, JD) ]. Now elsewhere in the spreadsheet are some pull down menus that alter some of the formulae being used. Every time that the data changes that is called into the function Excel crashes. I presume that its because the function will be trying to update itself and can't for some reason. I have tried only calculating the values on a press of F9 but it still has no effect. Excel still crashes. I had a quick try at re writing the function as a Sub and assigning the values from the cells IE, JC and JD to variables within the Sub but I couldn't get that to work let alone asigning the output to a cell. Can anyone help? |
User Defined Function Causing Problems
Well I tried taking out the I = I - 1 and replacing that by including a Do
While Loop. This does look better and give the corerct answer still but the problem remains unfortunately. Excel crashed when I changes the pull down menu. Would it be an idea to import the cell values into the function and redo it as a sub? If so how do I extract cell values and how do I place the results into another cell? The updated function: Function X1CALC(IE, JC, JD) Dim ZZ(9) As Single Dim V As Single Dim I As Integer Dim X1 As Single X1 = 0 I = 1 ZZ(1) = 10000 ZZ(2) = 1000 ZZ(3) = 100 ZZ(4) = 10 ZZ(5) = 1 ZZ(6) = 0.1 ZZ(7) = 0.01 ZZ(8) = 0.001 ZZ(9) = 0.0001 For I = 1 To 9 Do X1 = X1 + ZZ(I) V = (IE * (X1 ^ 3)) + (JD * (X1 ^ 2)) If V = JC Then I = 10 End If Loop While V - JC < 0 If V < JC Then If V - JC 0.001 Then X1 = X1 - ZZ(I) End If If V = JC Then I = 10 End If End If Next I X1CALC = X1 End Function "Gary''s Student" wrote: I can't make it crash... for example: =x1calc(0.1,0.2,0.3) yields 0.733099997043609 =x1calc(0.4,0.2,0.3) yields 0.608099937438964 =x1calc(0.1,0.2,0.7) yields 0.51609992980957 I suspect that the problem may be related to your changing the value of I within the For Loop. Is it possible that the value of I can be getting to 0 or 11? This would cause a crash. -- Gary''s Student "Steve" wrote: Function X1CALC(IE, JC, JD) Dim ZZ(9) As Single Dim V As Single Dim I As Integer X1 = 0 I = 1 ZZ(1) = 10000 ZZ(2) = 1000 ZZ(3) = 100 ZZ(4) = 10 ZZ(5) = 1 ZZ(6) = 0.1 ZZ(7) = 0.01 ZZ(8) = 0.001 ZZ(9) = 0.0001 For I = 1 To 9 X1 = X1 + ZZ(I) V = (IE * (X1 ^ 3)) + (JD * (X1 ^ 2)) If V = JC Then I = 11 End If If V < JC Then If V - JC = 0 Then If V - JC 0.001 Then X1 = X1 - ZZ(I) End If If V = JC Then I = 10 End If Else I = I - 1 End If End If Next I X1CALC = X1 End Function "Gary''s Student" wrote: This is very interesting. Please post the UDF and we will take a look at it. -- Gary's Student "Steve" wrote: I'm trying to convert an old GW Basic program into a more user friendly and more updateable spreadsheet. I was getting on fine but now that I have created and implemented a function into the spreadsheet it crashes everytime data is changed. The conversion was fine until I came to a repeating loop that calculated the value of one variable needed to generate a particular value in another. I have managed to create a working function that does the job. It requires three values to be called in which come from named cells [i.e. =X1CALC(IE, JC, JD) ]. Now elsewhere in the spreadsheet are some pull down menus that alter some of the formulae being used. Every time that the data changes that is called into the function Excel crashes. I presume that its because the function will be trying to update itself and can't for some reason. I have tried only calculating the values on a press of F9 but it still has no effect. Excel still crashes. I had a quick try at re writing the function as a Sub and assigning the values from the cells IE, JC and JD to variables within the Sub but I couldn't get that to work let alone asigning the output to a cell. Can anyone help? |
User Defined Function Causing Problems
Yeh thats the results that I should be getting. I can get them if I delete
the cell with the function and retype it each time. I'm thinking that it might be to do with the rest of the spreadsheet. I retried it and it worked once (didn't the subsequent 2 tries) and decied that it had a name error on cell IE. Unsure what might cause this yet will redo the formula and related cells. I've started going through each of the formulae (theres quite a lot) and redoing them. "Gary''s Student" wrote: With your data: =x1calc(0.010477442, 230929464, 7760.7996) yields 172.478790283203 =x1calc(0.010477442, 230929464, 4226.792) yields 233.672973632812 Make sure that you are not going into an infinite loop by setting i=i-1 -- Gary's Student "Steve" wrote: It could well be I suppose. By altering the value the I value will alter at different times so could well be. The strange thing is that when I recalculate the formula using the altered values it works. It just seems to be the transition. I take it that there shouldn't be a problem with it reclaculating automatically. I'll try rewriting the program, though only got it working this morning after puzzling over it for some of yesterday! "Gary''s Student" wrote: I can't make it crash... for example: =x1calc(0.1,0.2,0.3) yields 0.733099997043609 =x1calc(0.4,0.2,0.3) yields 0.608099937438964 =x1calc(0.1,0.2,0.7) yields 0.51609992980957 I suspect that the problem may be related to your changing the value of I within the For Loop. Is it possible that the value of I can be getting to 0 or 11? This would cause a crash. -- Gary''s Student "Steve" wrote: Function X1CALC(IE, JC, JD) Dim ZZ(9) As Single Dim V As Single Dim I As Integer X1 = 0 I = 1 ZZ(1) = 10000 ZZ(2) = 1000 ZZ(3) = 100 ZZ(4) = 10 ZZ(5) = 1 ZZ(6) = 0.1 ZZ(7) = 0.01 ZZ(8) = 0.001 ZZ(9) = 0.0001 For I = 1 To 9 X1 = X1 + ZZ(I) V = (IE * (X1 ^ 3)) + (JD * (X1 ^ 2)) If V = JC Then I = 11 End If If V < JC Then If V - JC = 0 Then If V - JC 0.001 Then X1 = X1 - ZZ(I) End If If V = JC Then I = 10 End If Else I = I - 1 End If End If Next I X1CALC = X1 End Function "Gary''s Student" wrote: This is very interesting. Please post the UDF and we will take a look at it. -- Gary's Student "Steve" wrote: I'm trying to convert an old GW Basic program into a more user friendly and more updateable spreadsheet. I was getting on fine but now that I have created and implemented a function into the spreadsheet it crashes everytime data is changed. The conversion was fine until I came to a repeating loop that calculated the value of one variable needed to generate a particular value in another. I have managed to create a working function that does the job. It requires three values to be called in which come from named cells [i.e. =X1CALC(IE, JC, JD) ]. Now elsewhere in the spreadsheet are some pull down menus that alter some of the formulae being used. Every time that the data changes that is called into the function Excel crashes. I presume that its because the function will be trying to update itself and can't for some reason. I have tried only calculating the values on a press of F9 but it still has no effect. Excel still crashes. I had a quick try at re writing the function as a Sub and assigning the values from the cells IE, JC and JD to variables within the Sub but I couldn't get that to work let alone asigning the output to a cell. Can anyone help? |
User Defined Function Causing Problems
Ok I've found the problem.
After doing another line by line break mode while looking at all the variables I've found that despite having all the correct data under the heading its not picking up the value for JD. When I expanded the variable info in the Watch list it has all the corect info including the correct value under 'Text' but hasn' put this into function memory for JD for some reason. Have I not defined it correctly? "Gary''s Student" wrote: With your data: =x1calc(0.010477442, 230929464, 7760.7996) yields 172.478790283203 =x1calc(0.010477442, 230929464, 4226.792) yields 233.672973632812 Make sure that you are not going into an infinite loop by setting i=i-1 -- Gary's Student "Steve" wrote: It could well be I suppose. By altering the value the I value will alter at different times so could well be. The strange thing is that when I recalculate the formula using the altered values it works. It just seems to be the transition. I take it that there shouldn't be a problem with it reclaculating automatically. I'll try rewriting the program, though only got it working this morning after puzzling over it for some of yesterday! "Gary''s Student" wrote: I can't make it crash... for example: =x1calc(0.1,0.2,0.3) yields 0.733099997043609 =x1calc(0.4,0.2,0.3) yields 0.608099937438964 =x1calc(0.1,0.2,0.7) yields 0.51609992980957 I suspect that the problem may be related to your changing the value of I within the For Loop. Is it possible that the value of I can be getting to 0 or 11? This would cause a crash. -- Gary''s Student "Steve" wrote: Function X1CALC(IE, JC, JD) Dim ZZ(9) As Single Dim V As Single Dim I As Integer X1 = 0 I = 1 ZZ(1) = 10000 ZZ(2) = 1000 ZZ(3) = 100 ZZ(4) = 10 ZZ(5) = 1 ZZ(6) = 0.1 ZZ(7) = 0.01 ZZ(8) = 0.001 ZZ(9) = 0.0001 For I = 1 To 9 X1 = X1 + ZZ(I) V = (IE * (X1 ^ 3)) + (JD * (X1 ^ 2)) If V = JC Then I = 11 End If If V < JC Then If V - JC = 0 Then If V - JC 0.001 Then X1 = X1 - ZZ(I) End If If V = JC Then I = 10 End If Else I = I - 1 End If End If Next I X1CALC = X1 End Function "Gary''s Student" wrote: This is very interesting. Please post the UDF and we will take a look at it. -- Gary's Student "Steve" wrote: I'm trying to convert an old GW Basic program into a more user friendly and more updateable spreadsheet. I was getting on fine but now that I have created and implemented a function into the spreadsheet it crashes everytime data is changed. The conversion was fine until I came to a repeating loop that calculated the value of one variable needed to generate a particular value in another. I have managed to create a working function that does the job. It requires three values to be called in which come from named cells [i.e. =X1CALC(IE, JC, JD) ]. Now elsewhere in the spreadsheet are some pull down menus that alter some of the formulae being used. Every time that the data changes that is called into the function Excel crashes. I presume that its because the function will be trying to update itself and can't for some reason. I have tried only calculating the values on a press of F9 but it still has no effect. Excel still crashes. I had a quick try at re writing the function as a Sub and assigning the values from the cells IE, JC and JD to variables within the Sub but I couldn't get that to work let alone asigning the output to a cell. Can anyone help? |
User Defined Function Causing Problems
<Would it be an idea to import the cell values into the function and redo it as a sub?
I wouldn't do that. A function is much more elegant and should work. If I were you I'd make all variables either Long (instead of integer) or Double (instead of Single). That increases capacity and precision. I agree with Gary's Student that changing the controlling variable of a do loop is dangerous. Use Exit Do if you want to break out. I can't reproduce your problem. -- Kind regards, Niek Otten Microsoft MVP - Excel "Steve" wrote in message ... | Well I tried taking out the I = I - 1 and replacing that by including a Do | While Loop. This does look better and give the corerct answer still but the | problem remains unfortunately. Excel crashed when I changes the pull down | menu. | | Would it be an idea to import the cell values into the function and redo it | as a sub? If so how do I extract cell values and how do I place the results | into another cell? | | The updated function: | | Function X1CALC(IE, JC, JD) | | Dim ZZ(9) As Single | Dim V As Single | Dim I As Integer | Dim X1 As Single | | X1 = 0 | I = 1 | | ZZ(1) = 10000 | ZZ(2) = 1000 | ZZ(3) = 100 | ZZ(4) = 10 | ZZ(5) = 1 | ZZ(6) = 0.1 | ZZ(7) = 0.01 | ZZ(8) = 0.001 | ZZ(9) = 0.0001 | | For I = 1 To 9 | | Do | | X1 = X1 + ZZ(I) | V = (IE * (X1 ^ 3)) + (JD * (X1 ^ 2)) | | If V = JC Then | I = 10 | End If | | Loop While V - JC < 0 | | If V < JC Then | | If V - JC 0.001 Then | X1 = X1 - ZZ(I) | End If | | If V = JC Then | I = 10 | End If | | End If | | Next I | | X1CALC = X1 | | End Function | | | "Gary''s Student" wrote: | | I can't make it crash... for example: | | =x1calc(0.1,0.2,0.3) yields 0.733099997043609 | =x1calc(0.4,0.2,0.3) yields 0.608099937438964 | =x1calc(0.1,0.2,0.7) yields 0.51609992980957 | | I suspect that the problem may be related to your changing the value of I | within the For Loop. | | Is it possible that the value of I can be getting to 0 or 11? This would | cause a crash. | | -- | Gary''s Student | | | "Steve" wrote: | | Function X1CALC(IE, JC, JD) | | Dim ZZ(9) As Single | Dim V As Single | Dim I As Integer | | X1 = 0 | I = 1 | | ZZ(1) = 10000 | ZZ(2) = 1000 | ZZ(3) = 100 | ZZ(4) = 10 | ZZ(5) = 1 | ZZ(6) = 0.1 | ZZ(7) = 0.01 | ZZ(8) = 0.001 | ZZ(9) = 0.0001 | | For I = 1 To 9 | | X1 = X1 + ZZ(I) | V = (IE * (X1 ^ 3)) + (JD * (X1 ^ 2)) | | If V = JC Then | I = 11 | End If | | If V < JC Then | If V - JC = 0 Then | | If V - JC 0.001 Then | X1 = X1 - ZZ(I) | End If | | If V = JC Then | I = 10 | End If | Else | I = I - 1 | | End If | End If | | Next I | | X1CALC = X1 | | End Function | | "Gary''s Student" wrote: | | This is very interesting. | | Please post the UDF and we will take a look at it. | | | -- | Gary's Student | | | "Steve" wrote: | | I'm trying to convert an old GW Basic program into a more user friendly and | more updateable spreadsheet. I was getting on fine but now that I have | created and implemented a function into the spreadsheet it crashes everytime | data is changed. | | The conversion was fine until I came to a repeating loop that calculated the | value of one variable needed to generate a particular value in another. I | have managed to create a working function that does the job. It requires | three values to be called in which come from named cells [i.e. =X1CALC(IE, | JC, JD) ]. Now elsewhere in the spreadsheet are some pull down menus that | alter some of the formulae being used. Every time that the data changes that | is called into the function Excel crashes. I presume that its because the | function will be trying to update itself and can't for some reason. I have | tried only calculating the values on a press of F9 but it still has no | effect. Excel still crashes. | | I had a quick try at re writing the function as a Sub and assigning the | values from the cells IE, JC and JD to variables within the Sub but I | couldn't get that to work let alone asigning the output to a cell. | | Can anyone help? |
| All times are GMT +1. The time now is 02:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com