Compile error: Variable not defined

I'm close to the end of this code, but there is a problem with it.:think:

[vba]Public SumXCollection As Collection
Public artikal As String
...[/vba] [vba]Private Sub Sub1()
...
artikal = Product.Text
...
End Sub[/vba] [vba]Private Sub Sub2()
...

If (some item exists) Then
...
...
'update the corresponding object of the SumX collection !!!!!!!!!
Set zx = SumXCollection(artikal)
zx.ColorXX = Color.Text
zx.TotalXX = ActiveSheet.Cells(begin1 + i, 11)

Else
...
...
'create a new object of the SumX coolection
Set zx = New SumX
zx.CodeXX = artikal
zx.ColorXX = Color.Text
zx.TotalXX = ActiveSheet.Cells(stepx, 11)

SumXCollection.Add zx, zx.CodeXX
...
End If

...
End Sub[/vba] [vba]'Class Module named 'SumX'
Option Explicit

Private pCodeXX As String
Private pColorXX As String
Private pTotalXX As String

Public Property Get CodeXX() As String
CodeXX = pCodeXX
End Property

Public Property Let CodeXX(ByVal vNewValue As String)
pCodeXX = vNewValue
End Property

Public Property Get ColorXX() As String
ColorXX = pColorXX
End Property

Public Property Let ColorXX(ByVal vNewValue As String)
pColorXX = vNewValue
End Property

Public Property Get TotalXX() As String
TotalXX = pTotalXX
End Property

Public Property Let TotalXX(ByVal vNewValue As String)
TotalXX = vNewValue
End Property[/vba]
and i get :

Quote:

---

Compile error:
Variable not defined

---

on 'Set zx = SumXCollection(artikal)' in Sub2().

What is particularly odd is that the code shouldn't enter the first part of the If statement immediately, because in the first pass the item (from the condition) doesn't exist, so the code must drop to Else part of the If statement, where a zx object needs to be created.

So that, every new zx object is created in the second (Else) part of the If st., and every existing one is updated in the first part.

What's wrong with this?:eek:

Thanks a lot in advance

Hi

Couple of things I can think of.

1) Has sub1 been run to set the variable artikal
2) I think the Get TotalXX should be

[VBA]
Public Property Let TotalXX(ByVal vNewValue As String)
pTotalXX = vNewValue
End Property
[/VBA]

3) where do you define the collection SumXCollection? OR is this part of the code that in in the ...


Tony

I've resolved "Variable not defined" problem with:

Public zx As SumX :banghead:

But you gave me the solution for the next issue :

Quote:

---

Run-time error '28':
Out of stack space

---

on zx.TotalXX = ActiveSheet.Cells(stepx, 11)
(in the Else part, by creation a new SumX /zx/ object)

I corrected pTotalXX in the class module, and now the code works fine. :clap:

Many thanks acw, that was an exceptional observation. :bow:


Could you clarify something to me:

Public SumXCollection As Collection is the declaration
and
Set SumXCollection = New Collection is the definition

and i need the both, in order to use my SumX collection, correct?


Thanks again

Quote:

---

Originally Posted by SMC

Public SumXCollection As Collection is the declaration
and
Set SumXCollection = New Collection is the definition

and i need the both, in order to use my SumX collection, correct?

---

Yeppers. The first creates an (empty) global variable to hold your collection. The second actually initializes it. :)

HI

Yep that is so. Somewhere I presume you have an option explicit. This means that you have to define the variable SumXCollection. However, just defining it doesn't mean that you have done anything with it. So you need to set it. This is what I really meant by my comment about defining the collection - wrong terminology.


Tony

Quote:

---

Originally Posted by acw

Somewhere I presume you have an option explicit.

---

For what it's worth, VERY GOOD HABIT! :thumb

Hmm, something's unclear here to me. :think:

I said:
Set SumXCollection = New Collection -is that a definition?

Ken and Tony said:
Yep that is so.

and subsequently, Tony says:
However, just defining it doesn't mean that you have done anything with it. So you need to set it. (??)

So, I'd ask again, is:
Set SumXCollection = New Collection - the definition or not ?

Thanks again

I don't know if this would make an official definition but...

Public SumXCollection As Collection

Is a variable, or container of a specific type. This would be the definition... You're basically saying "If I want to use this, it will be a collection". It is empty, until you tell the program to use it.

Set SumXCollection = New Collection is the definition

This is where you are saying "I really want to use this object. Create me a new one and put it in this variable." At this point the variable becomes "live", and has a scope.

(Just to clarify one point... I didn't address those questions above, sorry. I focussed on "Do I need both". The answer there is still yes. :) )

Hi

From the excel help file

Dim: Declares variables and allocates storage space

Set: Assigns an object reference to a variable or property

If you don't have option explicit, then you can use a set statement without using a dim statement, but if you have option explicit, then you must have a dim before you can set.

Tony

Yes, i have 'option explicit'.

Well :
Public x as Integer - is a declaration

x = Range("B3") - is a definition

correct? :whistle:

Thanks

Another (i hope the last) problem with this program: :eek:

In my SumX collection i need to have objects with 2 keys, CodeXX and ColorXX.

What i need is to have some objects with the same key (CodeXX) and diferent ColorXX property, but compiler refuses that:

Quote:

---

Run-time error '457':
This key is already associated with an element of this collection

---

I assume i should declare ColorXX property as a second key, but don't know is it possible, and how to do it ? :wot

Hi

1) Looks like semantics is getting in the road. The dim delcares the variable. Once it is declared, you then assign something to it. In the case of a range, you set the variable to assign the range. Otherwise you assign a value to it.

2) Collections (and dictionary) have only one key. How about concatenating both the items to make one key?


Tony

I agree on the semantics. I'd prefer to use the term "assignment" instead of "definition". To me, declaration and definition are almost interchangeable terms.

:)

Maybe you could help me out with this if i give you some more details about my SumX class.

It contains 2 important (key) properties, CodeXX (product code, actual collection key) and ColorXX (product color - needs to be a second key), and TotalXX -plain amount sum holder.

In this code i process one by one object(CodeXX) to find out an amount of products (TotalXX) in certain color (ColorXX) (in all sizes together)

In my If statement i check if some product has a record (Color) already existing in a table. If not, the code puts a new record in the table, and creates a new SumX object by adding product code in its CodeXX property, product color in ColorXX, and its amount (in certain size) in TotalXX property.

But if this color (of the same product) already exists in the table, it should just update (increment) the TotalXX property of the object.

So the problem is, if some color doesn't exist in the table, its (corresponding) object needs to be created, but it is possible only for first object, any subsequent one encounters existing CodeXX key, which belongs to the first object /color/.

Thus i need some kind of 'second key' for ColorXX property.

I think of using 3dimensional array, but i need to call its elements by string value, not indexes.

Also i don't know how could i use concatenation of these 2 keys (how and when to merge and unmerge?)


If someone has an idea about this issue, it would be greatly appreciated. : pray2:

Hi

Still think a concatenation of the product code and color will work. If it exists in the collection then do the update, if it doesn't then add the details.


Tony

Thanks Tony, i accepted your suggestion and made a complex key through a concatenation. It really works. :thumb

One more thing is left.

I made an abort button as well, which should undo mistaken records.

For that i need a command to remove (delete) a member of a collection by its key. Something like:

SumXCollection.Remove 65301blue

but this syntax (i found in Excel Help), doesn't work to me. :think:


Thanks again

A stupid mistake founded (at last).

[vba] For t = 0 To n - 1

st = artic & arr_color(0) 'instead of (t)
SumXCollection.Remove st
Next t [/vba] Can you believe it?

:banghead:

Quote:

---

Originally Posted by SMC

A stupid mistake founded (at last).

[vba] For t = 0 To n - 1

st = artic & arr_color(0) 'instead of (t)
SumXCollection.Remove st
Next t [/vba] Can you believe it?

:banghead:

---

I've not tried to follow all the previous posts, but this one just appears to repeat the same action n times.

:banghead:

Quote:

---

Originally Posted by mdmackillop

I've not tried to follow all the previous posts, but this one just appears to repeat the same action n times.

---

Been away, myself, but would tend to agree with this.

Huh, there is one more issue (again).:motz2:

I need to update a SumXCollection member, with a new value of one of its properties (TotalXX):

[vba]Public SumXCollection As Collection
Public zx As SumX
Public begin1 As Integer
Public article As String
...
Dim i As Integer

For i=0 To ...
...

Set zx = SumXCollection(article & Color.Text)
zx.TotalXX = ActiveSheet.Cells(begin1 + i, 11) 'begin1 and 'article' are set in previous procedure
SumXCollection(article & Color.Text) = zx ' THIS LINE YELLOW MARKED
...

Next i[/vba] getting:

Quote:

---

Run-time error '438':
Object doesn't support this property or method

---

What to do now? :help


edit:

SumX Class[vba]Option Explicit

Private pKeyXX As String
Private pCodeXX As String
Private pColorXX As String
Private pTotalXX As String

Public Property Get KeyXX() As String
KeyXX = pKeyXX
End Property

Public Property Let KeyXX(ByVal vNewValue As String)
pKeyXX = vNewValue
End Property

Public Property Get CodeXX() As String
CodeXX = pCodeXX
End Property

Public Property Let CodeXX(ByVal vNewValue As String)
pCodeXX = vNewValue
End Property

Public Property Get ColorXX() As String
ColorXX = pColorXX
End Property

Public Property Let ColorXX(ByVal vNewValue As String)
pColorXX = vNewValue
End Property

Public Property Get TotalXX() As String
TotalXX = pTotalXX
End Property

Public Property Let TotalXX(ByVal vNewValue As String)
pTotalXX = vNewValue
End Property[/vba]

Hi

wouldn't it be a matter of updating that item in the collection?

[vba]
SumXCollection(article & Color.Text).TotalXX = ActiveSheet.Cells(begin1 + i, 11)
[/vba]

Tony

Yep. Thanks. :clap:

I'm coming back soon with one (or two) more question(s).

:hi: