Pass array of worksheets to ActiveX DLL (VB6)
 

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! *

Pass array of worksheets to ActiveX DLL (VB6)
 

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.

Pass array of worksheets to ActiveX DLL (VB6)
 

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! *

Pass array of worksheets to ActiveX DLL (VB6)
 

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! *

Pass array of worksheets to ActiveX DLL (VB6)
 

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.

Pass array of worksheets to ActiveX DLL (VB6)
 

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! *

Pass array of worksheets to ActiveX DLL (VB6)
 

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.)

Pass array of worksheets to ActiveX DLL (VB6)
 

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

Pass array of worksheets to ActiveX DLL (VB6)
 

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.

Pass array of worksheets to ActiveX DLL (VB6)
 

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.

I have no desire to killfile you... when you decide to buckle down and
actually answer questions, I find them interesting and illuminating to
read. It's just when you get into these "the hell with helping people
moods" (you are bored, I presume), you sometimes tend to go overboard.
Calling people names seems childish (maybe you **are** a lot younger
than I give you credit for<g). As for usenet filters, I'm not too sure
about them, but the f***ing word came through unmodified in your post.

Rick

Pass array of worksheets to ActiveX DLL (VB6)
 

On Thu, 17 Jun 2004 20:07:22 +0100, "Beeeeeves" <beeeeeeeeev@ves
wrote:

(remember, younger programmers come here for help also)

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

Believe me, that came through in your original post.

Tip - if you want to killfile me, notice that I only ever have two different
names,

Thanks for the tip. I've now bozo binned this one as well.


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

Pass array of worksheets to ActiveX DLL (VB6)
 

So, presumably you can killfile on name aswell as email address?
In that case I'll have to put a few more "e"s in now and again...

"Hank Scorpio" wrote:

On Thu, 17 Jun 2004 20:07:22 +0100, "Beeeeeves" <beeeeeeeeev@ves
wrote:

(remember, younger programmers come here for help also)

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

Believe me, that came through in your original post.

Tip - if you want to killfile me, notice that I only ever have two different
names,

Thanks for the tip. I've now bozo binned this one as well.


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

Pass array of worksheets to ActiveX DLL (VB6)
 

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.

--

Pass array of worksheets to ActiveX DLL (VB6)
 

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.

--

Pass array of worksheets to ActiveX DLL (VB6)
 

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

Pass array of worksheets to ActiveX DLL (VB6)
 

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.

--

Pass array of worksheets to ActiveX DLL (VB6)
 

Frank, not sure if you're interested but we actually
employ people like this. Combination of quite bright but
no sense at all and very immature. Over the years we have
developed a strategy - something like this:

Keep separated from others and allow them to plod away at
the clever stuff, steering where necessary in such a way
that whatever becomes their own idea.
Listen to what they say in case amidst the nonsense
something useful emerges. If nothing does just humour them
politely without rising, control constructive argument but
avoid futile debate. This is all rather a black art.

We tend to find most begin to catch on and we can all look
back and laugh at their former ways. The first tangible
sign is when they start learning how to listen (can be a
new concept for them), the transformation is satisfying to
watch. But some never will, for these and similar who
cross our path, we give a gesture of dismissal - of the
ancient kind.

Paul

-----Original Message-----
On Thu, 17 Jun 2004 20:07:22 +0100, "Beeeeeves"
<beeeeeeeeev@ves
wrote:

(remember, younger programmers come here for help also)

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

Believe me, that came through in your original post.

Tip - if you want to killfile me, notice that I only
ever have two different
names,

Thanks for the tip. I've now bozo binned this one as well.


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

Pass array of worksheets to ActiveX DLL (VB6)
 

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

Pass array of worksheets to ActiveX DLL (VB6)
 

As for usenet filters, I'm not too sure
about them, but the f***ing word came through unmodified in your post.

YES! Because I outwitted them!

Pass array of worksheets to ActiveX DLL (VB6)
 

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

Pass array of worksheets to ActiveX DLL (VB6)
 

You must be really, really clever! I am awed.

--

Vasant

"Beeeeeves" <beeeeeeeeev@ves wrote in message
...
YES! Because I outwitted them!

Pass array of worksheets to ActiveX DLL (VB6)
 

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! *

Pass array of worksheets to ActiveX DLL (VB6)
 

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! *

Pass array of worksheets to ActiveX DLL (VB6)
 

On Fri, 18 Jun 2004 09:33:25 -0700, "PaulS"
wrote:

Hi Paul,

Frank,

Umm, it's Hank, actually 8^

not sure if you're interested but we actually
employ people like this. Combination of quite bright but
no sense at all and very immature.

Over the years we have
developed a strategy - something like this:
Keep separated from others and allow them to plod away at
the clever stuff, steering where necessary in such a way
that whatever becomes their own idea.
Listen to what they say in case amidst the nonsense
something useful emerges. If nothing does just humour them
politely without rising, control constructive argument but
avoid futile debate. This is all rather a black art.

It's funny, I was thinking about this very thing a little after making
my previous post in this thread.

After reading what you had to say I Googled some of his posts
(including a couple of the ones that I had killfiled in this thread).
Clearly the guy isn't clueless, but nor is he the computing god that
he thinks he is. And in this *particular* case, the advice that he was
giving was just plain dumb. (OK, I'd NORMALLY say "inadvisable" rather
than "dumb"; it's the d00d-'tood that brought it into the latter
category.)

Don't bother checking that a worksheet object has been passed because
it'll just error a few lines further down anyway? Uh-huh. Technically
accurate, but not a good idea in real life. It means that you give up
control over what error gets passed back. I prefer to pass back
specific errors about what went wrong, why, and what's needed to fix
it rather than let the code spew back a generic "Type Mismatch" or
"Object does not support this property or method" or the gods know
what else at some unpredictable point in the code. You can't predict
EVERY possible error, but I don't think it's a good idea to abrogate
responsibility for intercepting obvious ones. It makes it easier for
people using your object to pick up their own errors, which makes it
faster for them to develop their own applications, which then allows
them to focus on making those applications better for users further
down the line. It's the difference between what's OK for Mr. Smith's
CompSci classes, and what's expected in a real workplace.

Then there's the setting up of "straw man" arguments when I declined
to bow down before his dismissive, 3leet haxor d00d skills. Right,
declare that my principal reason for using early binding was so that
I could use Intellisense during development. And hope that no-one
reading his rant will realise that if that WAS the case then I could
have developed early bound, then removed the library references and
done a global search and replace to change all arguments to Objects.
No, it's far better to set up transparently absurd arguments and spit
and cuss like an 8 year old who's been denied cookies.

Documentation? Well yes, obviously the DLL *IS* being documented...
but I'm not arrogant enough to assume that anyone using it is going to
reach for the documentation every couple of minutes to find out "so
what type of object does THIS method require" when it would be far
easier for them to be able to just SEE the type of object needed in
the autocomplete statement.

And this doesn't even BEGIN to address the difference in speed between
early and late binding, which may not be an issue in some cases but
when you're extracting output from hundreds of thousands of records
(as some methods will), every cycle that you can save counts.

But here's the thing; the world is changing. The workplace is
changing. Innovation, skill and talent isn't the prerogative of prima
donnas and the socially challenged. These days, if someone's going to
act like an adolescent then while there may be SOME places which will
employ them in the manner that you suggest, far more won't because
they have plenty of choice. People who behave like that will more
often find themselves increasingly marginalised and eventually
"downsized" or "outsourced" at the first opportunity. I've seen it
happen more than once. If it comes down to a chioce between someone
with talent who can work and play well with others and someone who
won't, then...

You only have to look at this thread; Stephen Bullen and Jamie Collins
both gave intelligent, useful suggestions and comments. B[insert
e's]ves gave profanity and belligerence. In the circumstances, what do
I gain by reading his posts? Not a lot, so I see no reason to take him
out of the bit bucket.

JMHO, and I appreciate your alternative point of view.

We tend to find most begin to catch on and we can all look
back and laugh at their former ways. The first tangible
sign is when they start learning how to listen (can be a
new concept for them), the transformation is satisfying to
watch. But some never will, for these and similar who
cross our path, we give a gesture of dismissal - of the
ancient kind.

-----Original Message-----
On Thu, 17 Jun 2004 20:07:22 +0100, "Beeeeeves"
<beeeeeeeeev@ves
wrote:

(remember, younger programmers come here for help also)

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

Believe me, that came through in your original post.

Tip - if you want to killfile me, notice that I only
ever have two different
names,

Thanks for the tip. I've now bozo binned this one as well.


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

Pass array of worksheets to ActiveX DLL (VB6)
 

Hi Hank,

My apologies for missing your name, please put this down
to the foibles of age!

I hope you would agree one can still appreciate anyone
taking the trouble to reply to a question of this nature,
even if the suggestion proves not useful or inadvisable.
Your Q. relates to an advanced topic, for other readers
(eg me) it can be instructive to learn why a suggestion
is, or is not appropriate and if not why not.

Just a shame B's was interlaced with profanity and
arrogance, with similar in follow-ups highlighting the
difference between constructive and futile debate. Quite a
contrast to Jamie Collins' "How about..." and Stephen
Bullens' "My suggestion would be to...", irrespective of
which approach ultimately proves the more useful.

Notwithstanding, you posted an interesting question
producing equally interesting suggestions from JC & SB.

Paul

-----Original Message-----
On Fri, 18 Jun 2004 09:33:25 -0700, "PaulS"
wrote:

Hi Paul,

Frank,

Umm, it's Hank, actually 8^

<snip

Pass array of worksheets to ActiveX DLL (VB6)
 

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