VBA will not always assign value to variable

mike703 · #1 (**permalink**) 12-30-10, 14:36

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

MikeTheBike · #2 (**permalink**) 01-04-11, 08:19

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

norie · #3 (**permalink**) 01-04-11, 13:37

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech