Collections and Keys for Ranges

[Ronald Dodge]

Why is it when I use the following code, it's giving the "Name" property of
the item within the collection as an application-defined or object-defined
error? I have it set as a new collection within the declaration statement.

---CODE---
cllEmpWrkCodes.Add wshLastWeekEmpsSummary.Cells(rngEmpWorkCodes.Row
+ 1, lngEmpWrkCode). _
Resize(sumdatLastWeekEmps.ColumnHeader.Rows.Count, 1),
wshLastWeekEmpsSummary.Cells(4, lngEmpWrkCode).Text
---CODE---

The text being returned by the worksheet object down to the text property is
returning the proper string value. I'm using the text as the ID value of
the record (row) in most of these collections, though in this example, it's
using it for summary purposes similar to crosstabs in Access, though it's
actually part of a larger report.

The data is actually housed in Access, and I'm using TransferSpreadsheet
method within Access to update the data in Excel, which then I'm using both
standard and class modules to further manipulate the data within Excel.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Guest]

Sub abc()
Dim cllEmpWrkCodes As Collection
Set cllEmpWrkCodes = New Collection
cllEmpWrkCodes.Add ActiveSheet.Range("A1:A10"), "Dog"
Set r = cllEmpWrkCodes("dog")
MsgBox r.Address
End Sub

works fine for me in Excel 2003 and appears to be equivalent to what you are
trying to do - so suspect there is a problem in your declaration (are you
using option explicit?) or in your first argument since you say the second is
fine.

 

[Ronald Dodge]

Declaration statement is as follows

Dim cllEmpWrkCodes As New Collection

Which then there is no Set statement.

The first argument also returns a range object, which is intended to be
returned as a range object.

I can also call it up by way of index number, but not by the string value of
the key, which was the whole purposes of setting up the collection object.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Ronald Dodge]

Yes, I am using Option Explicit as that is done by design.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Ronald Dodge]

Well as working my way through the code, I found the issue and have
corrected it, which initially stemmed from a rule that I already had in
place, but didn't follow through with the rule this particular time around
only to get burnt by it. Guess it's only cause I been working on the
project for so long and they needed to get the data about as soon as they
can get it so as to move away from the way they have been tracking and
reporting the data in Excel. The data is now being tracked in Access and
only additional manipulations done within Excel after so much of it is done
in Access. TransferSpreadsheet method is used to transfer the data from
Access To Excel. The only thing I don't like about the TransferSpreadsheet
method, it breaks up my naming convention.

However, in the case of this error that I ran into, and after doing some
research, I have added one more programming rule to the list of my other
rules, which is:

Don't use the key word, "New" within a declaration statement. This is in
regards to Chip Pearson's page.

I'm also using Class Modules rather than Type structures that seems to be so
common for others to use, especially in the Access environment, but given
the .NET stuff, I'm avoiding the no-nos of it to the extent reasonably
possible. The one thing I don't like about the .NET programming
environment, it has done away with the GoTo feature entirely, which makes it
much tougher to deal with in certain debugging situations as you now have to
setup up a Try...Catch...Finally block to do the debugging in those certain
cases that is so much easier to use the GoTo feature for, but yet, can't use
it in the .NET programming environment.

The type of situation I'm refering to, if a variable doesn't work out to as
expected, I would have it just go to a label to have it reset again.
However, I would only have this in place for the time duration that I'm
stepping through the code, so as I can fix the line of code that is causing
the issue to happen. Once I have the issue resolved, I would then remove
the Goto line and also the Label, so as to avoid breaking the algorythm
definition by the code being caught inside an infinite loop. Well
technically broke the definition when I put in, but it's only temporary and
it's only for stepping through, then remove it afterwards to unbreak the
definition.

I certainly understand their argument for completely doing away with it, but
I don't agree with it cause there are a very few minor cases where the GoTo
feature would be beneficial and knock out a lot of the work that would now
have to be done in the .NET environment, cause of their claim of bad
programming practices. I have seen many of such bad practices, but why
should they take it away only to punish those that does use good programming
practices, even if there are rare cases for such use?

That would be like me telling MS to get rid of the Active<Object> objects
and Select/Activate methods cause too many people use those in bad ways, but
yet, even in good programming practices, there's rare but acceptible reasons
why you would have to use Active<Object> objects and Select/Activate
methods. I highly hate using these items cause of the issues presented by
using these items, but I don't have a choice in the matter in one particular
situation as the third party tool add-in doesn't allow for me to do it the
way I would prefer to do it in. I have talked with them about it, but since
I'm one of the very few customers that even gets into the VBA side out of
the many customers they have that uses their product within Excel, chances
are pretty low that it will be addressed anytime within the next few years.

Why do I hate Active<Object>?

If the code is changed down the road, the Active<Object> could be
inadvertently be changed not meaning to.

Why do I hate the Activate method?

When I am running a program that contains the Activate method, a pretty good
majority of the time, the Excel application becomes activated, even when I'm
working in another program with the program within Excel initially running
in the background. I really hate it when I'm typing or doing something else
on the computer and that particular Excel instance takes over cause of the
Activate method being executed. In my example, it's used on the workbook
just before using the third party add-in to be able to update data into
Excel from JDE, and there's no way of getting around it as the third party
commands only works on the active workbook. For this reason, I can't
realistically do anything on the computer until all of the numbers and
reports has been updated and completed.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000