B
Barry-Jon
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
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