User Defined Function Causing Problems

[Guest]

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?

 

[Niek Otten]

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

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

 

[Guest]

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.

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

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!

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


:

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

:

This is very interesting.

Please post the UDF and we will take a look at it.


--
Gary's Student


:

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?

 

[Guest]

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?

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

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!

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


:

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

:

This is very interesting.

Please post the UDF and we will take a look at it.


--
Gary's Student


:

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?

 

[Niek Otten]

<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


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