This is an esoteric one which may or may not get any responses, but
even random thoughts would be useful. (I hope that Don will forgive me
for lapsing into a cross post here, but I think that both groups are
relevant. I haven't been able to find anything on this after Googling
both the VB and Excel groups, nor anything in the KnowledgeBase.)

I'm writing a VB6 (SP6) DLL which is intended to be called from /
automate all versions of Office from 97 onwards. Objects are early
bound. In view of KB articles 244167 and 245115 (regarding the
automation of multiple versions of Office), the DLL contains a
reference to the earliest supported version of Excel; that is,
Excel8.olb. However I ran into a compile error in Excel (argument type
mismatch, described below) when I tried calling one of the library's
methods.

I created a second dummy .dll with the same Excel library reference
just to ensure that it wasn't a problem specific to the first project.
(It wasn't.) The dummy contains one class called DLLTestClass, which
has one (very simplified) method:

Function TestMethodDLL( _
xlApp As Excel.Application, _
SourceWorksheet As Excel.Worksheet, _
ByRef TargetWorksheets() As Excel.Worksheet _
) As Boolean

TestMethodDLL = False
On Error GoTo ErrorHandler

MsgBox UBound(TargetWorksheets)

TestMethodDLL = True

ExitPoint:

Exit Function

ErrorHandler:
Select Case Err.Number
Case Else
MsgBox Err.Number & vbCrLf & Err.Description

End Select

Resume ExitPoint

End Function

I compiled the dummy dll without any problem, then opened an Excel
2002 workbook, set a reference to the dll, and included the following
in a standard module:

Sub TestDLLVersion()

Dim obj As New DLLTestClass

Dim obja_wks() As Excel.Worksheet

ReDim obja_wks(1)

Set obja_wks(0) = Sheet2
Set obja_wks(1) = Sheet3

obj.TestMethodDLL Application, Sheet1, obja_wks

Set obj = Nothing

End Sub

When I run it, I get a compile error relating to the obja_wks variable
in the method call line; specifically, "ByRef Argument Type Mismatch".

If I put an identical class into the Excel workbook itself, and create
a second procedure which sets the obj variable to THAT class instead
of the dll's one, no such error occurs. This set me t'thinkin. I
therefore saved the workbook, and re-opened it in Excel 97. This time
there was no compile error in the procedure which calls the dll, and
the procedure ran perfectly.

Just to prove the point (and yes, I do have one), I changed the
reference in the dummy DLL to the Excel 10 library, recompiled it,
opened the workbook in Excel 2002, re-set the reference to the dummy
dll and... no compile error.

Conclusion: Using a reference to the oldest Office library will
generally allow you to automate that and subsequent versions... UNLESS
you're passing an array of objects from that library (or maybe just
SOME objects?), in which case you're toast unless you've specified the
library for the EXACT Office version that you're using. I've already
written a stack of methods which use all manner of individual objects;
the application object, a workbook, a worksheet, a range, a Word
document... and despite calling them from 97, 2000 AND 2002, this is
the first time I've had a problem.

Which means that I'll presumably have to kludge some unsatisfactory
workaround. (I haven't decided what yet, but I'm not crazy about the
idea of using collections, variants or generic late bound objects
since in all cases I'd need to somehow check that the passed argument
is indeed a worksheet. Which is what I was hoping to avoid, but
probably won't be able to...)

Anyone else come across this?


---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

Hi.

I compiled the dummy dll without any problem, then opened an Excel
2002 workbook, set a reference to the dll, and included the following
in a standard module:

If I understand the above correctly, you are calling TestDLLVersion
from a standard module in an Excel 2002 workbook. Is that right?

It is quite correct that, when automating Office products from VB, you
should early bind:

Dim MyXL As Excel.Application
Set MyXL = New .Excel.Application
or
Set MyXL = CreateObject("Excel.Application")

On your development machine, you should have, and set a reference to,
the earliest version of the Office product that you want your app to
work with.

However, that is Automation from VB. Passing a parameter from another
version of Excel, I suspect, is a different kettle of fish.
TestDLLVersion's arguments are probably being set up to expect an
Excel 8 object, hence the failure when passing in a Workbook reference
from Excel 2002.

You could try declaring TestDLLVersion's arguments as Object instead
of Excel.Worksheet.

You can still use early binding within the Sub:

Dim MySheet As Excel.Worksheet
Set MySheet = TargetWorksheets(n)
....
Set MySheet = Nothing

You may get a more useful response from an Excel newsgroup.

Gale.

On Thu, 17 Jun 2004 11:38:40 +0100, Gale Green
wrote:

I compiled the dummy dll without any problem, then opened an Excel
2002 workbook, set a reference to the dll, and included the following
in a standard module:
If I understand the above correctly, you are calling TestDLLVersion
from a standard module in an Excel 2002 workbook. Is that right?

Correct.

[Snip]
On your development machine, you should have, and set a reference to,
the earliest version of the Office product that you want your app to
work with.

However, that is Automation from VB. Passing a parameter from another
version of Excel, I suspect, is a different kettle of fish.
TestDLLVersion's arguments are probably being set up to expect an
Excel 8 object, hence the failure when passing in a Workbook reference
from Excel 2002.

That's not the problem. As I mentioned later in the original post, I
have no problems passing individual workbook objects, worksheet
objects, Word documents or any other single objects from an Office
application to the library, regardless of which version of Office I'm
passing them from. The problem only occurs when I'm attempting to pass
an ARRAY of objects, specifically (in this case) worksheets.

You could try declaring TestDLLVersion's arguments as Object instead
of Excel.Worksheet.

Thanks for the suggestion; I may end up needing to do that. As I
mentioned later in my original post though, that's a matter of last
resort because I don't want to have to also test each member of the
array to ensure that it's the right type of object.

You can still use early binding within the Sub:

Dim MySheet As Excel.Worksheet
Set MySheet = TargetWorksheets(n)
...
Set MySheet = Nothing

This is still dealing with only a single sheet at a time, though, and
that's something that I have no problem with.

You may get a more useful response from an Excel newsgroup.

That's why this has been cross-posted to Excel.Programming. (The Excel
groups are my "home territories" (even if I've been a little lax of
late), but this problem seems to fall into both camps.)

I appreciate your time.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

On Thu, 17 Jun 2004 20:57:43 +1000, Hank Scorpio
wrote:

That's not the problem. As I mentioned later in the original post, I
have no problems passing individual workbook objects, worksheet
objects, Word documents or any other single objects from an Office
application to the library, regardless of which version of Office I'm
passing them from. The problem only occurs when I'm attempting to pass
an ARRAY of objects, specifically (in this case) worksheets.

I did realise that. It is still quite likely that array arguments are
set up differently from scalar arguments.

You can still use early binding within the Sub:

Dim MySheet As Excel.Worksheet
Set MySheet = TargetWorksheets(n)
...
Set MySheet = Nothing

This is still dealing with only a single sheet at a time, though, and
that's something that I have no problem with.

I was presuming that you were processing the array one element at a
time:

Function TestMethodDLL(... TargetWorksheets() As Object)...

Dim MySheet As Excel.Worksheet

For n = LBound(TargetWorksheets) To UBound(TargetWorksheets)

Set MySheet = TargetWorksheets(n)
' Process MySheet
Set MySheet = Nothing

Next

Sorry I can't be more helpful.

Gale.

Objects are early
bound.

Why?
I compiled the dummy dll without any problem, then opened an Excel
2002 workbook, set a reference to the dll, and included the following
in a standard module:

Sub TestDLLVersion()

Dim obj As New DLLTestClass

Dim obja_wks() As Excel.Worksheet

ReDim obja_wks(1)

Set obja_wks(0) = Sheet2
Set obja_wks(1) = Sheet3

obj.TestMethodDLL Application, Sheet1, obja_wks

Set obj = Nothing

End Sub

When I run it, I get a compile error relating to the obja_wks variable
in the method call line; specifically, "ByRef Argument Type Mismatch".

I wouldn't be surprised.


If I put an identical class into the Excel workbook itself, and create
a second procedure which sets the obj variable to THAT class instead
of the dll's one, no such error occurs. This set me t'thinkin. I
therefore saved the workbook, and re-opened it in Excel 97. This time
there was no compile error in the procedure which calls the dll, and
the procedure ran perfectly.

Don't waste your time on this early binding confusion.


Just to prove the point (and yes, I do have one), I changed the
reference in the dummy DLL to the Excel 10 library, recompiled it,
opened the workbook in Excel 2002, re-set the reference to the dummy
dll and... no compile error.

Conclusion: Using a reference to the oldest Office library will
generally allow you to automate that and subsequent versions... UNLESS
you're passing an array of objects from that library (or maybe just
SOME objects?), in which case you're toast unless you've specified the
library for the EXACT Office version that you're using. I've already
written a stack of methods which use all manner of individual objects;
the application object, a workbook, a worksheet, a range, a Word
document... and despite calling them from 97, 2000 AND 2002, this is
the first time I've had a problem.

Which means that I'll presumably have to kludge some unsatisfactory
workaround. (I haven't decided what yet, but I'm not crazy about the
idea of using collections, variants or generic late bound objects
since in all cases I'd need to somehow check that the passed argument
is indeed a worksheet.

Why would you NEED to check it's a worksheet? If anyone passes it an object that isn't a worksheet (and to be honest, you can probably stop flattering yourself that everyone in your office is going to be clamouring for a copy of this DLL - in all probability you'll be the only one that uses it) they'll just get a slightly different error a bit further down the line ( a few lines of code on)....

Which is what I was hoping to avoid, but
probably won't be able to...)

No, you probably won't be able to avoid it. This IS what late binding was invented for.



Anyone else come across this?


---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

On Thu, 17 Jun 2004 04:40:01 -0700, "Beeeeeeeeeeeeves"
wrote:

Objects are early
bound.

Why?

Because it makes development easier and less error-prone for starters.
Because it allows you to specify the types of objects that properties
and methods require, thus making it easier for people using your
objects to detect, at compilation time, when they've passed an
invalid argument type for seconds. For thirds... never mind. You'll
find the other reasons in most programming textbooks. On on MSDN. Or
in other places that require some reading.

[Snip the code, since you had nothing worthwhile to say about it.]

Don't waste your time on this early binding confusion.

Gee thanks. That's useful. You really must sign on as a consultant to
the many and varied textbook writers who point out the benefits of
early binding when you're working with *known* object types.

Which means that I'll presumably have to kludge some unsatisfactory
workaround. (I haven't decided what yet, but I'm not crazy about the
idea of using collections, variants or generic late bound objects
since in all cases I'd need to somehow check that the passed argument
is indeed a worksheet.

Why would you NEED to check it's a worksheet?

Good point. There's no need for all this error checking and
interception. I think I'll just go and redefine all of my variables as
Variant too. Better still, I'll just rip out the Option Explicit
statements and use implicit declarations. Let the errors fall where
they may!

If anyone passes it an object
that isn't a worksheet (and to be honest, you can probably stop flattering yourself
that everyone in your office is going to be clamouring for a copy of this DLL

Gee, thanks again, that's just so constructive. You don't think that
possibly, just maybe, I'll have a better idea than you of the 247 end
users in our various branches who will have the .dll installed when
it's finished than you will, hmm?

Yes, I concur with anyone else reading this; I don't know why I
bothered replying to it either. I think the expression I'm looking for
is [Plonk].

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

Because it makes development easier and less error-prone for starters.

Development isn't supposed to be orienteated towards making other development easier, the ultimate aim is supposed to be to produce a good, final application for the end user. That's the *END* user, not just the person who you consider to be your customer.

Because it allows you to specify the types of objects that properties
and methods require, thus making it easier for people using your
objects to detect, at compilation time, when they've passed an
invalid argument type for seconds.

I agree that this is a good advantage to make use of, but it shouldn't EVER be at the expense of cross-version incompatibility.
A developer that is using a DLL which takes an object as a parameter should know to use the DLL's documentation to know what type of object that should take. If you're so thick you're too scared of losing intellisense, maybe you should get a job as a bank teller.


Don't waste your time on this early binding confusion.

Gee thanks. That's useful. You really must sign on as a consultant to
the many and varied textbook writers who point out the benefits of
early binding when you're working with *known* object types.

But as you've said yourself, they're not known when you're using multiple different versions of the applicaiton.... (which in this case is Excel), but you blindly carry on doing it regardless... "I MUST have intellisense!" you fuucking cretin!

Why would you NEED to check it's a worksheet?

Good point. There's no need for all this error checking and
interception. I think I'll just go and redefine all of my variables as
Variant too.

Don't be sarcy, you know exactly what I mean (and what's more you're riled because you know I'm right - you just don't want to lose the buzz you get when intellisense works on your own DLL) - a long is always going to be a long no matter how many new versions of Excel are brought out. But an Excel.Workbook array won't be. It will have a different guid, but the same type name ("excel.workbook") as an Excel.Workbook.9. And you know it.



statements and use implicit declarations. Let the errors fall where
they may!

Like i say if you're so thick that you blindly go ahead and insist on the need for type checking even when it breaks version compatability then you shouldn't be a programmer.


Gee, thanks again, that's just so constructive. You don't think that
possibly, just maybe, I'll have a better idea than you of the 247 end
users in our various branches who will have the .dll installed when
it's finished than you will, hmm?

Maybe you do. Do you have a sense of doom and gloom that they're even more stupid than you are? After all what sort of a company can it be, when YOU of all people have attained the heady hights of "senior DLL writer"?
HAVEN'T YOU HEARD OF DOCUMENTATION?

(Tit.)

But as you've said yourself, they're not known when you're using
multiple different versions of the applicaiton.... (which in this case
is Excel), but you blindly carry on doing it regardless... "I MUST have
intellisense!" you f***ing cretin!

Maybe you do. Do you have a sense of doom and gloom that they're even
more stupid than you are? After all what sort of a company can it be,
when YOU of all people have attained the heady hights of "senior DLL
writer"?
HAVEN'T YOU HEARD OF DOCUMENTATION?

Ease up there Ben... the vulgar word is completely out of place here
(remember, younger programmers come here for help also) and the name
calling is not real cool either. I guess we could say you are not having
a good day today. Maybe you might want to take a break for a day or two
until you get your bearings back.

Rick

Ease up there Ben... the vulgar word is completely out of place here

Usenet filters out swear words, so there shouldn't be any reason for
something I type to get through. If they want to outwit me, they'll have to
use a regex then won't they - possibly <regex type="air" tested="false
"f+\s*u+\s*c+\s*k" </regex ?
If a post matches that then it's vulgar. I'll leave it up to you to
construct a function which takes a string array of defined swear words and
come up with a load of regexes (or a single one ;-) ) to eliminate any
variation on them. And I expect optimization.

(remember, younger programmers come here for help also)

I AM one of the younger programmers - how old did you think I was??!! 40?

Tip - if you want to killfile me, notice that I only ever have two different
names, Beeeeeeeves probably has a different amount of "e"s in it when I'm at
work to when I'm at home. I do change the fake mail address from time to
time, basically because it's easy and it ****es Bob O'Bob off.

I haven't felt "cool" since my mind was mullered in summer 2002, so there's
no love lost there I'm afraid.

Hank Scorpio wrote ...

I'm not crazy about the
idea of using collections, variants or generic
late bound objects since in all cases I'd need
to somehow check that the passed argument
is indeed a worksheet.

How about using your own custom class collection class that wraps the
collection object and only allows Excel.Worksheet objects in? e.g.

' --- Revised code in DLLTestClass
Option Explicit

Public Function TestMethodDLL( _
xlApp As Excel.Application, _
SourceWorksheet As Excel.Worksheet, _
ByRef TargetWorksheets As CWorksheets _
) As Boolean

TestMethodDLL = False
On Error GoTo ErrorHandler

MsgBox TargetWorksheets.Count

TestMethodDLL = True

ExitPoint:

Exit Function

ErrorHandler:
Select Case Err.Number
Case Else
MsgBox Err.Number & vbCrLf & Err.Description

End Select

Resume ExitPoint

End Function


' --- Code in new CWorksheets class (in VB6 project)
Option Explicit

Private m_colWorksheets As Collection

Public Property Get Item(ByVal Index As Variant) As Excel.Worksheet
Set Item = m_colWorksheets.Item(Index)
End Property

Public Property Get NewEnum() As IUnknown
Set NewEnum = m_colWorksheets.[_NewEnum]
End Property

Public Sub Add(ByVal ExcelWorksheet As Excel.Worksheet)
m_colWorksheets.Add ExcelWorksheet, ExcelWorksheet.Name
End Sub

Public Property Get Count() As Long
Count = m_colWorksheets.Count
End Property

Public Function Remove(ByVal Index As Variant) As Boolean
On Error Resume Next
m_colWorksheets.Remove Index
Remove = CBool(Err.Number = 0)
End Function

Private Sub Class_Initialize()
Set m_colWorksheets = New Collection
End Sub


' --- Code in Excel standard module
Option Explicit

Sub TestDLLVersion()

Dim obj As New DLLTestClass

Dim obja_wks As CWorksheets
Set obja_wks = New CWorksheets

obja_wks.Add Sheet3
obja_wks.Add Sheet2

obj.TestMethodDLL Application, Sheet1, obja_wks

Set obj = Nothing

End Sub

Jamie.

--

Because it doesn't solve his problem. It's still early bound, and he'd need to recompile the DLL that contained the custom collection for each version of Excel, etc.. which is what the point of the question was.

Mine solves that, he just doesn't want to admit it.


"Jamie Collins" wrote:

Hank Scorpio wrote ...

I'm not crazy about the
idea of using collections, variants or generic
late bound objects since in all cases I'd need
to somehow check that the passed argument
is indeed a worksheet.

How about using your own custom class collection class that wraps the
collection object and only allows Excel.Worksheet objects in? e.g.

' --- Revised code in DLLTestClass
Option Explicit

Public Function TestMethodDLL( _
xlApp As Excel.Application, _
SourceWorksheet As Excel.Worksheet, _
ByRef TargetWorksheets As CWorksheets _
) As Boolean

TestMethodDLL = False
On Error GoTo ErrorHandler

MsgBox TargetWorksheets.Count

TestMethodDLL = True

ExitPoint:

Exit Function

ErrorHandler:
Select Case Err.Number
Case Else
MsgBox Err.Number & vbCrLf & Err.Description

End Select

Resume ExitPoint

End Function


' --- Code in new CWorksheets class (in VB6 project)
Option Explicit

Private m_colWorksheets As Collection

Public Property Get Item(ByVal Index As Variant) As Excel.Worksheet
Set Item = m_colWorksheets.Item(Index)
End Property

Public Property Get NewEnum() As IUnknown
Set NewEnum = m_colWorksheets.[_NewEnum]
End Property

Public Sub Add(ByVal ExcelWorksheet As Excel.Worksheet)
m_colWorksheets.Add ExcelWorksheet, ExcelWorksheet.Name
End Sub

Public Property Get Count() As Long
Count = m_colWorksheets.Count
End Property

Public Function Remove(ByVal Index As Variant) As Boolean
On Error Resume Next
m_colWorksheets.Remove Index
Remove = CBool(Err.Number = 0)
End Function

Private Sub Class_Initialize()
Set m_colWorksheets = New Collection
End Sub


' --- Code in Excel standard module
Option Explicit

Sub TestDLLVersion()

Dim obj As New DLLTestClass

Dim obja_wks As CWorksheets
Set obja_wks = New CWorksheets

obja_wks.Add Sheet3
obja_wks.Add Sheet2

obj.TestMethodDLL Application, Sheet1, obja_wks

Set obj = Nothing

End Sub

Jamie.

--

Hi Beeeeeeeeeeeeves,

Because it doesn't solve his problem. It's still early bound, and he'd need to recompile the DLL that contained the custom collection for each
version of Excel, etc.. which is what the point of the question was.

Actually, no. I presume you haven't tried Jamie's suggestion. The Excel object library is extraordinarily good at backwards-compatibility. In
general, one can reference the earliest version of Excel in the DLL and it will 'just work' when used from later versions, without recompiling and
all early bound. Passing arrays of worksheets seems to be one of the extremely few exceptions to the 'just works' rule, for which there are a
number of workarounds - and creating your own Worksheets collection class is certainly one of them.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

Wrong.

"Stephen Bullen" wrote in message
...
Hi Beeeeeeeeeeeeves,

Because it doesn't solve his problem. It's still early bound, and he'd
need to recompile the DLL that contained the custom collection for each
version of Excel, etc.. which is what the point of the question was.

Actually, no. I presume you haven't tried Jamie's suggestion. The Excel
object library is extraordinarily good at backwards-compatibility. In
general, one can reference the earliest version of Excel in the DLL and it
will 'just work' when used from later versions, without recompiling and
all early bound. Passing arrays of worksheets seems to be one of the
extremely few exceptions to the 'just works' rule, for which there are a
number of workarounds - and creating your own Worksheets collection class
is certainly one of them.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

Hi Hank,

However I ran into a compile error in Excel (argument type
mismatch, described below) when I tried calling one of the library's
methods.

I've been burnt passing arrays of non-simple types between Excel and
VB6 dlls too and always assumed it's COM's marshalling getting in the
way. My suggestion would be to make it easy on the users of the DLL by
defining the object As Variant and allowing them to pass a single
sheet, array of sheets, collection of sheets or an Excel Sheets object:

Function TestMethodDLL( _
xlApp As Excel.Application, _
SourceWorksheet As Excel.Worksheet, _
ByRef TargetWorksheets As Variant _
) As Boolean

Dim vItem As Variant
Dim oSht As Excel.Worksheet

TestMethodDLL = False
On Error GoTo ErrorHandler

'Just one sheet?
If TypeName(TargetWorksheets) = "Worksheet" Then
Set oSht = TargetWorksheets
MsgBox oSht.Name

'An array, Collection or Sheets object?
ElseIf IsArray(TargetWorksheets) Or _
TypeName(TargetWorksheets) = "Collection" Or _
TypeName(TargetWorksheets) = "Sheets" Then

'Loop through them, checking for worksheets
For Each vItem In TargetWorksheets
If TypeName(vItem) = "Worksheet" Then
Set oSht = vItem
MsgBox oSht.Name
End If
Next
End If

TestMethodDLL = True

ExitPoint:

Exit Function

ErrorHandler:
Select Case Err.Number
Case Else
MsgBox Err.Number & vbCrLf & Err.Description

End Select

Resume ExitPoint

End Function


Tested using the following Excel code:

Sub TestDLLVersion()

Dim obj As New class1
Dim col As Collection
Dim obja_wks() As Excel.Worksheet

'A single sheet
obj.TestMethodDLL Application, Sheet1, Sheet2

'An array of sheets
ReDim obja_wks(1)
Set obja_wks(0) = Sheet2
Set obja_wks(1) = Sheet3
obj.TestMethodDLL Application, Sheet1, obja_wks

'A collection of sheets
Set col = New Collection
col.Add Sheet2
col.Add Sheet3
obj.TestMethodDLL Application, Sheet1, obja_wks

'An Excel Sheets collection
obj.TestMethodDLL Application, Sheet1, ActiveWindow.SelectedSheets

Set obj = Nothing

End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

On Fri, 18 Jun 2004 12:53:29 GMT, Stephen Bullen
wrote:
However I ran into a compile error in Excel (argument type
mismatch, described below) when I tried calling one of the library's
methods.

I've been burnt passing arrays of non-simple types between Excel and
VB6 dlls too and always assumed it's COM's marshalling getting in the
way. My suggestion would be to make it easy on the users of the DLL by
defining the object As Variant and allowing them to pass a single
sheet, array of sheets, collection of sheets or an Excel Sheets object:

Thanks for your input Stephen; it's nice to know that it's not just me
who's been bitten by this. What I HAD done before reading Jamie's
suggestion was to have the argument as a generic object array, which I
then tested the members of to ensure that they were indeed worksheets.
(This particular method isn't applicable to non-worksheet sheets like
chart sheets.) I have to admit that I never felt comfortable with it,
though. Your solution would have worked just as well as my one, though
I've ended up going with Jamie's suggestion. (In part because I'm
trying to avoid variants whenever possible given the approach taken in
VB.Net, but more importantly it will allow other (future) methods to
use the same collection object without the need for more validation
coding.)

Thanks for your time!

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

Hi Hank,

Your solution would have worked just as well as my one, though
I've ended up going with Jamie's suggestion. (In part because I'm
trying to avoid variants whenever possible given the approach taken in
VB.Net, but more importantly it will allow other (future) methods to
use the same collection object without the need for more validation
coding.)

Fine - they're both equally good alternatives. If implementing the
custom collection bit, you might like to modify the Add method, so it
accepts a single sheet, or an array of sheets, collection of sheets,
Excel Sheets collection etc. (as in my example code), to make it as easy
as possible for your users to get their existing data structures into
your custom collection.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

Hank Scorpio wrote ...

I'm not crazy about the
idea of using collections, variants or generic
late bound objects since in all cases I'd need
to somehow check that the passed argument
is indeed a worksheet.

How about using your own custom class collection class that wraps the
collection object and only allows Excel.Worksheet objects in? e.g.

' --- Revised code in DLLTestClass
Option Explicit

Public Function TestMethodDLL( _
xlApp As Excel.Application, _
SourceWorksheet As Excel.Worksheet, _
ByRef TargetWorksheets As CWorksheets _
) As Boolean

TestMethodDLL = False
On Error GoTo ErrorHandler

MsgBox TargetWorksheets.Count

TestMethodDLL = True

ExitPoint:

Exit Function

ErrorHandler:
Select Case Err.Number
Case Else
MsgBox Err.Number & vbCrLf & Err.Description

End Select

Resume ExitPoint

End Function


' --- Code in new CWorksheets class (in VB6 project)
Option Explicit

Private m_colWorksheets As Collection

Public Property Get Item(ByVal Index As Variant) As Excel.Worksheet
Set Item = m_colWorksheets.Item(Index)
End Property

Public Property Get NewEnum() As IUnknown
Set NewEnum = m_colWorksheets.[_NewEnum]
End Property

Public Sub Add(ByVal ExcelWorksheet As Excel.Worksheet)
m_colWorksheets.Add ExcelWorksheet, ExcelWorksheet.Name
End Sub

Public Property Get Count() As Long
Count = m_colWorksheets.Count
End Property

Public Function Remove(ByVal Index As Variant) As Boolean
On Error Resume Next
m_colWorksheets.Remove Index
Remove = CBool(Err.Number = 0)
End Function

Private Sub Class_Initialize()
Set m_colWorksheets = New Collection
End Sub


' --- Code in Excel standard module
Option Explicit

Sub TestDLLVersion()

Dim obj As New DLLTestClass

Dim obja_wks As CWorksheets
Set obja_wks = New CWorksheets

obja_wks.Add Sheet3
obja_wks.Add Sheet2

obj.TestMethodDLL Application, Sheet1, obja_wks

Set obj = Nothing

End Sub

Jamie.

--

On 18 Jun 2004 05:01:20 -0700, (Jamie Collins)
wrote:

Hi Jamie,

Hank Scorpio wrote ...

Heh. I like what you did with my munged e-mail address...

I'm not crazy about the
idea of using collections, variants or generic
late bound objects since in all cases I'd need
to somehow check that the passed argument
is indeed a worksheet.

How about using your own custom class collection class that wraps the
collection object and only allows Excel.Worksheet objects in? e.g.

The library *does* contain some of its own collection classes, and the
idea of using another one to pass the worksheets did drift across my
mind... but for some reason I didn't like it much at the time. (Maybe
because there would then be two different collection objects for
worksheets floating around, though upon further reflection that's not
really much of an issue since they won't have the same name anyway.)

The more I looked at your suggestion, the more I could see what an
elegant solution it is. (This is why it's always useful to get another
perspective; it's possible to be too close to something to see it
objectively!) I've implemented it and tested it in all supported
versions of Excel, and it solves the problem exactly. Thank you
greatly!


---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *