Microsoft Excel Webinar

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 23

Thread: Compile error: Variable not defined

  1. #1

    Question Compile error: Variable not defined

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

    VB:
    Public SumXCollection As Collection 
    Public artikal As String 
    ... 
    
    
    Formatting tags added by mark007
    VB:
    Private Sub Sub1() 
        ... 
        artikal = Product.Text 
        ... 
    End Sub 
    
    
    Formatting tags added by mark007
    VB:
    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 
    
    
    Formatting tags added by mark007
    VB:
     '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 
    
    
    Formatting tags added by mark007
    and i get :
    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?

    Thanks a lot in advance
    Last edited by SMC; 01-23-2007 at 04:06 PM.

  2. #2
    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

    VB:
     
    Public Property Let TotalXX(ByVal vNewValue As String) 
        pTotalXX = vNewValue 
    End Property 
    
    
    Formatting tags added by mark007
    3) where do you define the collection SumXCollection? OR is this part of the code that in in the ...


    Tony

  3. #3

    Thumbs up

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

    Public zx As SumX

    But you gave me the solution for the next issue :

    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.

    Many thanks acw, that was an exceptional observation.


    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

  4. #4
    Administrator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,000
    Location
    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.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  5. #5
    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

  6. #6
    Administrator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,000
    Location
    Quote Originally Posted by acw
    Somewhere I presume you have an option explicit.
    For what it's worth, VERY GOOD HABIT!
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  7. #7
    Hmm, something's unclear here to me.

    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

  8. #8
    Administrator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,000
    Location
    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. )
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  9. #9
    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

  10. #10
    Yes, i have 'option explicit'.

    Well :
    Public x as Integer - is a declaration

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

    correct?

    Thanks

  11. #11
    Another (i hope the last) problem with this program:

    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:
    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 ?

  12. #12
    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

  13. #13
    Administrator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,000
    Location
    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.

    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  14. #14

    Red face

    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.

  15. #15
    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

  16. #16

    Thumbs up

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

    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.


    Thanks again

  17. #17

    Lightbulb

    A stupid mistake founded (at last).

    VB:
    For t = 0 To n - 1 
         
        st = artic & arr_color(0) 'instead of (t)
        SumXCollection.Remove st 
    Next t 
    
    
    Formatting tags added by mark007
    Can you believe it?


  18. #18
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    12,503
    Location
    Quote Originally Posted by SMC
    A stupid mistake founded (at last).

    VB:
    For t = 0 To n - 1 
         
        st = artic & arr_color(0) 'instead of (t)
        SumXCollection.Remove st 
    Next t 
    
    
    Formatting tags added by mark007
    Can you believe it?

    I've not tried to follow all the previous posts, but this one just appears to repeat the same action n times.
    MVP (Excel 2008-2010)

    "Provide sample data and layout if you want a quicker solution." - MD


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  19. #19

  20. #20
    Administrator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,000
    Location
    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.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •