<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?