V
Vasant Nanavati
You must be really, really clever! I am awed.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
H
Hank Scorpio
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!
H
Hank Scorpio
On 18 Jun 2004 05:01:20 -0700, (e-mail address removed) (Jamie Collins)
wrote:
Hi Jamie,
Heh. I like what you did with my munged e-mail address...
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!
wrote:
Hi Jamie,
Hank Scorpio <[email protected]> wrote ...
Heh. I like what you did with my munged e-mail address...
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!
H
Hank Scorpio
On Fri, 18 Jun 2004 09:33:25 -0700, "PaulS"
Hi Paul,
Umm, it's Hank, actually 8^>
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.
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.
P
PaulS
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
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
<snip>-----Original Message-----
On Fri, 18 Jun 2004 09:33:25 -0700, "PaulS"
Hi Paul,
Umm, it's Hank, actually 8^>
S
Stephen Bullen
Hi Hank,
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
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
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.