Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collections and Keys for Ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collections and Keys for Ranges
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. -- regards, Tom Ogilvy "Ronald Dodge" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collections and Keys for Ranges
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 "Tom Ogilvy" wrote in message ... 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. -- regards, Tom Ogilvy "Ronald Dodge" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collections and Keys for Ranges
Yes, I am using Option Explicit as that is done by design.
-- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "Tom Ogilvy" wrote in message ... 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. -- regards, Tom Ogilvy "Ronald Dodge" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collections and Keys for Ranges
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 "Ronald Dodge" wrote in message ... 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 "Tom Ogilvy" wrote in message ... 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. -- regards, Tom Ogilvy "Ronald Dodge" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For Each and Collections | Excel Programming | |||
How to use Alt keys + symbol keys to invoke a macro? | Excel Programming | |||
Collections of Collections | Excel Programming | |||
Help with collections | Excel Programming | |||
Using Collections | Excel Programming |