How to set up a Public variable?

[feipezi]

Hi,
I tried to set up a Public variable and make it's value available every time I call it in the same project.
But the setup below makes me run the macros every time I want the value of srow_b1, instead of running only once and srow_b1 will avail until the session is closed, which is what I want.
Here is part of the code:


Public srow_b1 As Long

Sub DeclareBeigeRows(rnum As Long, dumrowname)
Sheets("a").Activate
Range("a2").Activate
totalrows = ActiveSheet.UsedRange.Rows.Count
Do Until ActiveCell.Row > totalrows
Do While ActiveCell.Interior.ColorIndex = 36
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Interior.ColorIndex = 36 And ActiveCell.Row < rnum Then dumrowname = ActiveCell.Row
Loop
End Sub

Public Sub dummies()
DeclareBeigeRows 100, srow_b1
End Sub


Thanks in advance.

 

[combo]

So where is the problem?
The variable is declared. The procedure DeclareBeigeRows, if called from 'dummies' procedure, changes the variable every time the conditions are met ('dumrowname' declared as ByRef, the default).
So the variable srow_b1 is available all the time, initially equals 0, after executing 'dumrowname' can be changed to other value.
You can have potential problems when the project is reset, try to trace it by ticking 'Notify Before State Loss' option in the 'General' tab of Tools>Options.. dialog in VBE. The solution will be separation of code and saved workbook.
BTW, it is possible to work with range (ActiveSheet.UsedRange) without selecting cells, it's faster, more stable and less annouing for the user.

combo

 

[feipezi]

Thanks for the quick return.
Sorry I did not make myself clear. The problem is that 'srow_b1' is NOT always available after I call dummies(). It will avail if I DO NOT call other Subs. BTW, the other Subs do not have 'srow_b1' in them. So there won't be any overwriting in the calls. I suspect something missing in the declaration statements above.

I have to use Range("a2").Activate. I have 3 PivotTables on each of 3 sheets. Under each PT, there is a beige area where I have the aggregation data based on the PT above. The process on each beige area is diff. from each other.
Every month, as the PTs are updated, there is one more row and column added to the PTs (the numbers are distributed in the shape of half-rectangle (like a tri-angle)...
Anyway, it's hard to explain without seeing what the sheets look like. The point is to automate the process and keep the manual work minimum.
Thanks again.

 

[combo]

What do you mean by 'not available'? In the worst case it should return 0 (Long). Is the variable declared in standard module?
Concerning the logic of the code:
Suppose that the first beige cell in col. 'A' (or rather with colour index of interior equal 36) is 20. The code activates cell 'A21' (inner loop). Next activates cell 'A22' (ActiveCell.Offset(1, 0).Activate). If this cell is beige, then srow_b1 becomes 22, but the loop continues. Finally, the srow_b1 becomes the second row of beige pair 'A[n-2]', 'A[n]' cells where n<100. Is it your intention? If so, you can simplify this in one loop:

 srow_b1=-1
For i=100 to 3 step -1
    If Activesheet.Cells(i,1).Interior.ColorIndex)=36 And Activesheet.Cells(i-2,1).Interior.ColorIndex)=36 Then
        srow_b1=i
        Exit For
    End If
Next i
MsgBox srow_b1

combo

 

[feipezi]

To me, '0' is not available. In my code, I already showed how I defined the variable. Yes, in a standard module.

Ideally, I was going to put the data process part (aggregation) inside a Do Until or Do While Loop because that way, I don't have to specify lower bound or upper bound limits, like 100, 200. As long as Excel sees a beige row, it will process until all the consecutive beige rows are done; then it will keep looking for next beige area...
But the aggregation part is not straightforward and it might be confusing if I put it inside a loop.

Thanks again for the effort you put in.

 

[combo]

How do you try to call the 'srow_b1'? Please explain what you mean by the 'To me, '0' is not available.'
I'd go through the logic of loops again, according to the initial example, the same result can be achieved with one loop with a call of external procedure, without working with active cell. This will make debugging much easier.


combo

 

[feipezi]

Sorry, I meant '0' means 'unavailable'. It looks like the declaration is not working.

I do need the outer loop since I have 3 PTs on a worksheet, with a summary beige area under each and every PT. After the PTs are updated with new data, I want the change reflected in the beige area too, which cannot be done with Refresh since it's not part of PTs.

Here is the code giving me the row number with the specific value (H1-06) in all the beige area on the sheet:



Sub DetectValueInAllBeigeAreas()
Range("a2").Activate
Do Until ActiveCell.Row > ActiveSheet.UsedRange.Rows.Count
Do While ActiveCell.Interior.ColorIndex = 36 And ActiveCell = "H1-06"
'MsgBox ActiveCell.Address
'some processing or manipulation and/or call other macros
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Offset(1, 0).Activate
Loop
End Sub


So, with the double Do Loop, I don't need to check and set the limit for the beige areas, like the first beige area from row 100 to 150,...,the 3rd, 300 to 500. I'll leave the detection for Excel/VBA. It's clumsy to set the limit since the row number will change with time passing.

Again, since I need those row numbers for more than one procedure, I tried to make them public but like what I said before, they are not going to stay put. After I run a few macros, those row numbers will turn into zeros as I call them. But the Public constant will always be there like:

Public Const abc As Integer = 15

Question1: is there any way I can make these row numbers as stable as the constant defined above?

As I call those PTs,

Sub callPTs
For i=1 To 3
Msgbox Activesheet.PivotTables(i).Name
Next
End Sub

the first PT will be located at the lower part of the sheet, and the last PT will be found at the upper. I renamed them but I cannot change the Index of the PTs.

Question2: is there any way of changing the index of the PTs, i.e., as I call them the first PT always stays at the top of the sheet and the last one at the bottom? Of course, I don't want to delete and re-create them.

Thanks again.

 

[fumei]

Maybe I am not understanding something, but if you declare a Public variable (or a CONST) in a Worksheet module, then it is not available from other Worksheets. So presumably you are indeed declaring it in a real standard module.

In your original code:

Public srow_b1 As Long

Sub DeclareBeigeRows(rnum As Long, dumrowname)
Sheets("a").Activate
Range("a2").Activate
totalrows = ActiveSheet.UsedRange.Rows.Count
Do Until ActiveCell.Row > totalrows
 Do While ActiveCell.Interior.ColorIndex = 36
    ActiveCell.Offset(1, 0).Activate
 Loop
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Interior.ColorIndex = 36 And ActiveCell.Row < rnum Then dumrowname = ActiveCell.Row
Loop
End Sub

Public Sub dummies()
DeclareBeigeRows 100, srow_b1
End Sub

the variable srow_bl will always = 0. You never make it anything else. And again, if it is not in a standard module (NOT a Worksheet module) then it will not be available at all.

BTW: please use the TGML code tags when posting code. It makes it much easier to read.

Gerry

 

[combo]

Concerning the structure of your code:
- you can name the pivot table (or use default one) and refer to pivot table by its name instead of by index,
- the pivot table object has some useful properties as TableRange1 or TableRange2, they can be used to detect area of the table. If you add named summary range then tere will be no need to loop.

combo