Sub works sometimes and sometimes doesn't

[Barry O''Connell]

I am using Excel 2003 Pro on XP Pro.

I have a class module (ICustomSheet) as part of my VBA project (which I
have named BOC) which has the following simple definition;

Option Explicit

Public Sub Initialise() 'UK Spelling
End Sub

The ICustomSheet class instancing property is set to "2 -
PublicNotCreateable" and I am implementing the interface in some
worksheets in my workbook.

The following code works sometimes (especially when I step through in
debug mode) and I am curious why it does not work all of the time.

Dim wbk As Excel.Workbook
Dim sht As Excel.Worksheet
Dim csht As BOC.ICustomSheet

Set wbk = ThisWorkbook

For Each sht In wbk.Worksheets

'find all sheets that implement the custom sheet interface
If TypeOf sht Is BOC.ICustomSheet Then

MsgBox "found"

'cast sheets that implement the interface
Set csht = sht

'and initialise them
csht.Initialise

End If

Next sht

The MsgBox "found" is there for debugging and is most usually not
working. One of my sheets is implementing the interface. Curiously
the code does sometimes work - especially if I am stepping through the
code.

I know I could iterate through the worksheets with an object variable
using late binding but not all sheets will implement my initialise
method and I don't see handling the error as a neat way of doing what I
am trying to do.

Any help would be much appreciated.

Barry-Jon
(PS. Apologies for re-post - no reply using my general account so using MSDN
managed account in the hope of an MS reply).

 

[Peter Huang [MSFT]]

Hi Barry,

Based on my MSDN document,
PublicNotCreatable. Other applications can use objects of this class only
if your component creates the objects first. Other applications cannot use
the CreateObject function or the New operator to create objects from the
class.

So I suggest you may try to use 1- Private.

Also did the problem reproduce if we use the code below only.
Option Explicit

Public Sub Initialise() 'UK Spelling
End Sub

That is to say, we did not nothing in the Initialise function.

If you still have any concern, please feel free to post here.

Thanks!

Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Barry O''''''''Connell]

Peter,

Interfaces are generally created PublicNotCreateable as they cannot be
created in and of themselves but must be implemented by another class that
can be created. For the same reason the Sub Initialise has no implementation
because the implementation is in certain worksheets (which are objects)
within my workbook. It is almost unimportant what is in the implementation
because the worksheet is not behaving as if it implements the interface (by
use of Implements ICustomSheet at the top of the worksheets class module).
That, along with the inconsistent behaviour of the code dependent on whether
it is being run or stepped through, is the problem. I guess I am asking why
this is behaving so inconsistently and is there any reason why I cannot
implement a custom interface in a worksheet in excel?

 

[Peter Huang [MSFT]]

Hi Barry,

Thanks for your posting!
So I understand that you have an Interface class ICustomSheet and another
Implement class.
In the Implement class, you may do something in the Initialise method.

So I suggest you try to comment out the code in the method temporarily to
see if the problem persists.

Also if the problem persists, can you send a simple reproduce sample(the
xls file with the code) together with the reproduce code and send to me?
You may reach me via removing "online" from my Email address.

Thanks!

Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.