Hi, I have a macro that chokes on overflow errors, even though I have
error-trapping invoked. Can anyone help me to trap these errors (they
occur when z=0, or x*x is larger than a double allows, etc.)? I've
tried various placements of the on error goto statement, but none do
the trick. Thanks for helping! This is how the entire code looks at
present:

Public Sub LS()
Dim i, j, n As Integer
Dim fk As Double, x As Double, y As Double, z As Double, p As Double,
K0 As Double
Dim xy As Double, x2 As Double, yz As Double, xz As Double
Dim Sxy As Double, Sx2 As Double, Syz As Double, Sxz As Double
Dim fk1 As Double, x1 As Double, z1 As Double, p1 As Double
Dim xy1 As Double, x12 As Double, yz1 As Double, xz1 As Double
Dim Sxy1 As Double, Sx12 As Double, Syz1 As Double, Sxz1 As Double
Dim k(1000) As Double
Dim Converged As Boolean
Dim mult As Single
Dim bDone As Boolean

'find out how many rows contain data
Sheets("Fit Data").Select
'determine number of rows containing data
Range("a10").Select
Selection.End(xlDown).Select
n = ActiveCell.Row
mult = 0.1
K0 = Range("$B$7").Value


Do Until Converged

k(1) = Range("$B$7").Value
k(0) = k(1) * 0.9
For j = 1 To 1000

Sxy = 0
Sx2 = 0
Syz = 0
Sxz = 0
Sxy1 = 0
Sx12 = 0
Syz1 = 0
Sxz1 = 0

For i = 10 To n
On Error GoTo Err_Do
'populate all variables
p = k(j) * -1 * Range("$A" & i).Value
p1 = k(j - 1) * -1 * Range("$A" & i).Value
x = 1 - Exp(p)
x1 = 1 - Exp(p1)
y = Range("$B" & i).Value
z = Range("$A" & i).Value * Exp(p)
z1 = Range("$A" & i).Value * Exp(p1)
xy = x * y
xy1 = x1 * y
x2 = x * x
x12 = x1 * x1
xz = x * z
xz1 = x1 * z1
yz = y * z
yz1 = y * z1
Sxy = xy + Sxy
Sxy1 = xy1 + Sxy1
Sx2 = x2 + Sx2
Sx12 = x12 + Sx12
Syz = yz + Syz
Syz1 = yz1 + Syz1
Sxz = xz + Sxz
Sxz1 = xz1 + Sxz1
Next i
On Error GoTo Err_Do
fk = Sxy / Sx2 - Syz / Sxz
fk1 = Sxy1 / Sx12 - Syz1 / Sxz1

k(j + 1) = k(j) - fk * (k(j) - k(j - 1)) / (fk - fk1)

If Abs((k(j + 1) - k(j)) / k(j)) < 0.00000001 Then
If k(j + 1) 0 Then
Range("$B$7").Select
ActiveCell.Value = k(j + 1)
Range("$d$7").Select
ActiveCell.Value = Syz1 / Sxz1
Converged = True
Exit Sub
End If
End If
Next j

Err_Do:
If Not Converged Then
If bDone Then
MsgBox "Couldn't converge with a starting k of " & Round(K0
/ 10, 4) & " or " & Round(K0, 4) & " or " & Round(K0 * 10, 2)
Exit Sub
End If
'multiply original guess (K0) by 0.1, then by 10
Range("$B$7").Select
ActiveCell.Value = K0 * mult
If mult = 0.1 Then
mult = 10
Else
bDone = True
End If
End If
Loop
End Sub

You need to understand how error handling works. When an error is
raised, your code goes to err_do, and VBA is running in "error
mode". No other errors can be trapped until you exit error mode
and go back in to normal mode. This is done with a Resume, Resume
Next, Exit Sub, or End Sub statement.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Kate" wrote in message
...
Hi, I have a macro that chokes on overflow errors, even though
I have error-trapping invoked. Can anyone help me to trap
these errors (they occur when z=0, or x*x is larger than a
double allows, etc.)? I've tried various placements of the on
error goto statement, but none do the trick. Thanks for
helping! This is how the entire code looks at present:

Public Sub LS()
Dim i, j, n As Integer
Dim fk As Double, x As Double, y As Double, z As Double, p As
Double, K0 As Double
Dim xy As Double, x2 As Double, yz As Double, xz As Double
Dim Sxy As Double, Sx2 As Double, Syz As Double, Sxz As Double
Dim fk1 As Double, x1 As Double, z1 As Double, p1 As Double
Dim xy1 As Double, x12 As Double, yz1 As Double, xz1 As Double
Dim Sxy1 As Double, Sx12 As Double, Syz1 As Double, Sxz1 As
Double
Dim k(1000) As Double
Dim Converged As Boolean
Dim mult As Single
Dim bDone As Boolean

'find out how many rows contain data
Sheets("Fit Data").Select
'determine number of rows containing data
Range("a10").Select
Selection.End(xlDown).Select
n = ActiveCell.Row
mult = 0.1
K0 = Range("$B$7").Value


Do Until Converged

k(1) = Range("$B$7").Value
k(0) = k(1) * 0.9
For j = 1 To 1000

Sxy = 0
Sx2 = 0
Syz = 0
Sxz = 0
Sxy1 = 0
Sx12 = 0
Syz1 = 0
Sxz1 = 0

For i = 10 To n
On Error GoTo Err_Do
'populate all variables
p = k(j) * -1 * Range("$A" & i).Value
p1 = k(j - 1) * -1 * Range("$A" & i).Value
x = 1 - Exp(p)
x1 = 1 - Exp(p1)
y = Range("$B" & i).Value
z = Range("$A" & i).Value * Exp(p)
z1 = Range("$A" & i).Value * Exp(p1)
xy = x * y
xy1 = x1 * y
x2 = x * x
x12 = x1 * x1
xz = x * z
xz1 = x1 * z1
yz = y * z
yz1 = y * z1
Sxy = xy + Sxy
Sxy1 = xy1 + Sxy1
Sx2 = x2 + Sx2
Sx12 = x12 + Sx12
Syz = yz + Syz
Syz1 = yz1 + Syz1
Sxz = xz + Sxz
Sxz1 = xz1 + Sxz1
Next i
On Error GoTo Err_Do
fk = Sxy / Sx2 - Syz / Sxz
fk1 = Sxy1 / Sx12 - Syz1 / Sxz1

k(j + 1) = k(j) - fk * (k(j) - k(j - 1)) / (fk - fk1)

If Abs((k(j + 1) - k(j)) / k(j)) < 0.00000001 Then
If k(j + 1) 0 Then
Range("$B$7").Select
ActiveCell.Value = k(j + 1)
Range("$d$7").Select
ActiveCell.Value = Syz1 / Sxz1
Converged = True
Exit Sub
End If
End If
Next j

Err_Do:
If Not Converged Then
If bDone Then
MsgBox "Couldn't converge with a starting k of " &
Round(K0 / 10, 4) & " or " & Round(K0, 4) & " or " & Round(K0 *
10, 2)
Exit Sub
End If
'multiply original guess (K0) by 0.1, then by 10
Range("$B$7").Select
ActiveCell.Value = K0 * mult
If mult = 0.1 Then
mult = 10
Else
bDone = True
End If
End If
Loop
End Sub

Thank you, I didn't understand that. Now it works!

Chip Pearson wrote:
You need to understand how error handling works. When an error is
raised, your code goes to err_do, and VBA is running in "error
mode". No other errors can be trapped until you exit error mode
and go back in to normal mode. This is done with a Resume, Resume
Next, Exit Sub, or End Sub statement.

The other thing I noticed was that your error handler was inside your Do/Loop
loop. It has to be outside of that loop, and the Resume statement should send
it back into a statement inside the loop.


On Wed, 11 May 2005 13:26:35 -0400, Kate wrote:

Thank you, I didn't understand that. Now it works!

Chip Pearson wrote:
You need to understand how error handling works. When an error is
raised, your code goes to err_do, and VBA is running in "error
mode". No other errors can be trapped until you exit error mode
and go back in to normal mode. This is done with a Resume, Resume
Next, Exit Sub, or End Sub statement.

Myrna, thanks for adding that info. And why is that the case
(error-handler must be outside of loop)? Do you mean the on error
goto statement? I originally did have it out, but then when it didn't
work (because I wasn't setting the program back to normal mode), I
moved it inside. But there is no processing outside the loop, so why
does it matter?

Thanks!

Myrna Larson wrote:
The other thing I noticed was that your error handler was inside your Do/Loop
loop. It has to be outside of that loop, and the Resume statement should send
it back into a statement inside the loop.


On Wed, 11 May 2005 13:26:35 -0400, Kate wrote:


Thank you, I didn't understand that. Now it works!

Chip Pearson wrote:

You need to understand how error handling works. When an error is
raised, your code goes to err_do, and VBA is running in "error
mode". No other errors can be trapped until you exit error mode
and go back in to normal mode. This is done with a Resume, Resume
Next, Exit Sub, or End Sub statement.

If you put it inside the loop, then you also need, inside the loop, a
statement that jumps over the error handler code when there is no error. If
you don't do that, and you "fall into" the error handler and the latter
contains a Resume statement (which it always should), you'll get a "Resume
Without Error" error.

Assuming that after an error you want to continue with the next iteration of
the loop, the correct structure should look like this:

Sub ....
'
'
'
'
On Error Goto Handler
Do
'your code here
ResumeHe
Loop

Handler:
'do what you need to do to process the error
Resume ResumeHere

End Sub


On Thu, 12 May 2005 12:03:38 -0400, Kate wrote:

Myrna, thanks for adding that info. And why is that the case
(error-handler must be outside of loop)? Do you mean the on error
goto statement? I originally did have it out, but then when it didn't
work (because I wasn't setting the program back to normal mode), I
moved it inside. But there is no processing outside the loop, so why
does it matter?

Thanks!

Myrna Larson wrote:
The other thing I noticed was that your error handler was inside your
Do/Loop
loop. It has to be outside of that loop, and the Resume statement should
send
it back into a statement inside the loop.


On Wed, 11 May 2005 13:26:35 -0400, Kate wrote:


Thank you, I didn't understand that. Now it works!

Chip Pearson wrote:

You need to understand how error handling works. When an error is
raised, your code goes to err_do, and VBA is running in "error
mode". No other errors can be trapped until you exit error mode
and go back in to normal mode. This is done with a Resume, Resume
Next, Exit Sub, or End Sub statement.

Thanks again, Myrna. Got it working fine now; and I think I
understand error-handling at last.

Myrna Larson wrote:
If you put it inside the loop, then you also need, inside the loop, a
statement that jumps over the error handler code when there is no error. If
you don't do that, and you "fall into" the error handler and the latter
contains a Resume statement (which it always should), you'll get a "Resume
Without Error" error.

Assuming that after an error you want to continue with the next iteration of
the loop, the correct structure should look like this:

Sub ....
'
'
'
'
On Error Goto Handler
Do
'your code here
ResumeHe
Loop

Handler:
'do what you need to do to process the error
Resume ResumeHere

End Sub


On Thu, 12 May 2005 12:03:38 -0400, Kate wrote:


Myrna, thanks for adding that info. And why is that the case
(error-handler must be outside of loop)? Do you mean the on error
goto statement? I originally did have it out, but then when it didn't
work (because I wasn't setting the program back to normal mode), I
moved it inside. But there is no processing outside the loop, so why
does it matter?

Thanks!

Myrna Larson wrote:

The other thing I noticed was that your error handler was inside your

Do/Loop

loop. It has to be outside of that loop, and the Resume statement should

send

it back into a statement inside the loop.


On Wed, 11 May 2005 13:26:35 -0400, Kate wrote:



Thank you, I didn't understand that. Now it works!

Chip Pearson wrote:


You need to understand how error handling works. When an error is
raised, your code goes to err_do, and VBA is running in "error
mode". No other errors can be trapped until you exit error mode
and go back in to normal mode. This is done with a Resume, Resume
Next, Exit Sub, or End Sub statement.