Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    25

    Unanswered: VBA will not always assign value to variable

    I am currently working on a VBA project which is working for the most part. However I've noticed that, when left to run on it's own (without utilizing F8), the code does not always assign a value to the variable. Can not figure out why and thought another set of eyes could figure out the issue. Code below:

    Code:
        Case "Tact Integ"
                PivItem = Sheets(3).PivotTables(1).PivotFields("PRJTASK")
                For Each PivItem In PT.PivotFields("PRJTASK").PivotItems
                Select Case Mid(PivItem.Name, 10, 2)
                    Case "7A", "7T", "7R", "7S", "7M", "7J", "7W"
                        
                        PivItem.Visible = True
                        
                        Call TaskSelect
    
                    Case Else
                        
                        PivItem.Visible = False
                End Select
                Next PivItem
    To give some background, there is a drop down box with multiple choices. When one is chosen, the code goes into action based on the selected choice (case). Once the coorect case is selected, it is supposed to assign the value of "PivItem" as the current value of the pivot field "PRJTASK". As you may be able to tell from the code above, based on the last two digits of the pivot item, the code them decides whether to set the pivot item as visible or non-visible.

    The variable PivItem has been declared as a public variant and seems to work when I manually go line by line via F8, however, when I try to run the code from the worksheet (as intended) it does not always assign the value. Is there something that i'm doing wrong??

    Mike

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I havn't a clue what you are trying to do, but purly from a programming point of view, it would make more sense to me if the code looked somethink like this
    Code:
        Case "Tact Integ"
            Set PT = Sheets(3).PivotTables(1)
            For Each PivItem In PT.PivotFields("PRJTASK").PivotItems
                Select Case Mid(PivItem.Name, 10, 2)
                    Case "7A", "7T", "7R", "7S", "7M", "7J", "7W"
                        
                        PT.Visible = True
                        
                        Call TaskSelect
    
                    Case Else
                        
                        PT.Visible = False
                End Select
            Next PivItem
    ??

    I've never used pivote table (in a sheet or code!) so that is of the top of my head, but I think more of your code would be useful. For instance PT is not declared or assigned in you code so difficult to see what is happening (or intended?).


    MTB
    Last edited by MikeTheBike; 01-04-11 at 09:22.

  3. #3
    Join Date
    Mar 2006
    Posts
    163
    Mike

    What variable isn't being assigned a value?

    Where in the code should that be done?

    Perhaps if you but Debug.Print <varname> before and after the code that does that, and any other appropriate places, you can find out more.

    Also, how is the code is triggered and how are you actually determining that a value isn't being assigned?

Posting Permissions

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