Folks

I'm having a rather weird (to me at last) problem with error handling
in an Excel application. Here is the idea...

I have a class module with a few properties, say property aaa, bbb and
ccc, all accessed with property "get routines".

There is also a refresh method, where I actually load those properties
from an SQL server through ADO.

Every procedure has a specific error handler, which is systematically
invoked by an "on error goto myErrHdl" statement

Everything seems to be working fine except for the following sort
routine that sometimes (pretty often actually, but not always) fails in
a rather strange way...

<code

Public Sub MySuperSort (a() As myClass) ' --- a is an array of
myClass

Dim i As Integer, j As Integer
Dim Low As Integer, Hi As Integer
Dim PushPop As New myClass
Dim perfTimer As Date
Dim sortValue1 As Double, sortValue2 As Double


On Error GoTo errHdl

perfTimer = Now()

Low = LBound(a)
Hi = UBound(a)

If Hi = 0 Then Exit Sub

j = (Hi - Low + 1) \ 2

Do While j 0
For i = Low To Hi - j
sortValue1 = a(i).aaa ' <--- These are the
sortValue2 = a(i + j).aaa ' <--- offending statements
If sortValue1 < sortValue2 Then
Set PushPop = a(i)
Set a(i) = a(i + j)
Set a(i + j) = PushPop
End If
Next i
For i = Hi - j To Low Step -1
sortValue1 = a(i).aaa ' <--- These are the
sortValue2 = a(i + j).aaa ' <--- offending statements
If sortValue1 < sortValue2 Then
Set PushPop = a(i)
Set a(i) = a(i + j)
Set a(i + j) = PushPop
End If
Next i
j = j \ 2
Loop

Call addPerfEntry("SORT", CDbl(Now() - perfTimer) / Hi)

Exit Sub

' === ERROR HANDLER ===

errHdl:

' --- Setup error message
Dim errMsg As String
errMsg = "Error in proc [MySuperSort]: " & Err.Description
addLogEntry prmEntry:=errMsg, prmClass:="ERR" ' logs the message

' --- Warns the user
MsgBox errMsg

' --- Some more stuff here

</code


At some point (I can't really have it 100% replicated), this statement

sortValue1 = a(i).aaa

raises the following error *within* the sort proc (i.e it is not caught
in the property get, although there is an error handler).

Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available. No work was done.

(btw the connectionObject.Errors.Count method returns 0)

Even stranger is that there is absolutely NO ADO activity whatsoever in
the properties. The whole code is a simple logical test (invoking one
other property) and a variable assignment from a private local variable
within the class module to the property:

Property Get aaa() As double

On Error GoTo errHdl

If me.bbb = 0 then

aaa = locAAAStorage

else

aaa = locAAAStorage

end if

exit property

errHdl:
stop

End Property


The local variable being initialized (correctly) by the refresh method.

This is really puzzling... All I can say is that the problem is not
machine dependent... and pretty annoying !
Any idea ?!

Thanks & regards

--alexT

You might look at:
http://support.microsoft.com/kb/269495

I have known this to happen when declared variables are not compatible with
the context in which they are used.

What happens when you declare SortValue1 & SortValue2 as variants?

"AlexT" wrote:

Folks

I'm having a rather weird (to me at last) problem with error handling
in an Excel application. Here is the idea...

I have a class module with a few properties, say property aaa, bbb and
ccc, all accessed with property "get routines".

There is also a refresh method, where I actually load those properties
from an SQL server through ADO.

Every procedure has a specific error handler, which is systematically
invoked by an "on error goto myErrHdl" statement

Everything seems to be working fine except for the following sort
routine that sometimes (pretty often actually, but not always) fails in
a rather strange way...

<code

Public Sub MySuperSort (a() As myClass) ' --- a is an array of
myClass

Dim i As Integer, j As Integer
Dim Low As Integer, Hi As Integer
Dim PushPop As New myClass
Dim perfTimer As Date
Dim sortValue1 As Double, sortValue2 As Double


On Error GoTo errHdl

perfTimer = Now()

Low = LBound(a)
Hi = UBound(a)

If Hi = 0 Then Exit Sub

j = (Hi - Low + 1) \ 2

Do While j 0
For i = Low To Hi - j
sortValue1 = a(i).aaa ' <--- These are the
sortValue2 = a(i + j).aaa ' <--- offending statements
If sortValue1 < sortValue2 Then
Set PushPop = a(i)
Set a(i) = a(i + j)
Set a(i + j) = PushPop
End If
Next i
For i = Hi - j To Low Step -1
sortValue1 = a(i).aaa ' <--- These are the
sortValue2 = a(i + j).aaa ' <--- offending statements
If sortValue1 < sortValue2 Then
Set PushPop = a(i)
Set a(i) = a(i + j)
Set a(i + j) = PushPop
End If
Next i
j = j \ 2
Loop

Call addPerfEntry("SORT", CDbl(Now() - perfTimer) / Hi)

Exit Sub

' === ERROR HANDLER ===

errHdl:

' --- Setup error message
Dim errMsg As String
errMsg = "Error in proc [MySuperSort]: " & Err.Description
addLogEntry prmEntry:=errMsg, prmClass:="ERR" ' logs the message

' --- Warns the user
MsgBox errMsg

' --- Some more stuff here

</code


At some point (I can't really have it 100% replicated), this statement

sortValue1 = a(i).aaa

raises the following error *within* the sort proc (i.e it is not caught
in the property get, although there is an error handler).

Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available. No work was done.

(btw the connectionObject.Errors.Count method returns 0)

Even stranger is that there is absolutely NO ADO activity whatsoever in
the properties. The whole code is a simple logical test (invoking one
other property) and a variable assignment from a private local variable
within the class module to the property:

Property Get aaa() As double

On Error GoTo errHdl

If me.bbb = 0 then

aaa = locAAAStorage

else

aaa = locAAAStorage

end if

exit property

errHdl:
stop

End Property


The local variable being initialized (correctly) by the refresh method.

This is really puzzling... All I can say is that the problem is not
machine dependent... and pretty annoying !
Any idea ?!

Thanks & regards

--alexT

What happens when you declare SortValue1 & SortValue2 as variants?

Well, it helps somewhat...

The error is still raised, but less often... I know it's not scientific
but that's what I see...

Still digging

--alexT

AlexT wrote:
I have a class module with a few properties, say property aaa, bbb
and
ccc, all accessed with property "get routines".

There is also a refresh method, where I actually load those
properties
from an SQL server through ADO.

Every procedure has a specific error handler, which is systematically
invoked by an "on error goto myErrHdl" statement

At some point (I can't really have it 100% replicated), this
statement

sortValue1 = a(i).aaa

raises the following error *within* the sort proc (i.e it is not
caught
in the property get, although there is an error handler).

Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available. No work was done.

Even stranger is that there is absolutely NO ADO activity whatsoever
in
the properties. The whole code is a simple logical test (invoking one
other property) and a variable assignment from a private local
variable
within the class module to the property:

Property Get aaa() As double

On Error GoTo errHdl

If me.bbb = 0 then

aaa = locAAAStorage

else

aaa = locAAAStorage

end if

exit property

errHdl:
stop

End Property


I usually see this error when trying to assign a value to an ADO
recordset field but there is a 'type mismatch' e.g. trying to assign a
null string to a numeric field. Does your class have an object variable
pointing at an open recordset and is something in locAAAStorage
attempting to write to the recordset? It could even be something
'external' e.g. value assigned to an activex control which is bound to
the recordset.

As the error is mysteriously falling back to your 'top' level error
handler, some debug.print lines or writing to a log file could help
track the execution path.

Jamie.

--