Possible to reference range established as part of different sub?

[largeselection]

As you may already know...I've gotten to the point where I have established 12 different ranges based on cells values.

All of the cells in the ranges are in column B.

So my ranges look something like this after the code (lets call it routineA) has run:

Rng1 = B2, B5, B9, B50, B60
Rng2 = B1, B3, B6, B8
Rng3 = B4, B20, B31
etc
etc
etc


Is it possible to refer to these ranges in a different sub-routine without having to define them again?

IE- So I run routineA and get all these nice, organized ranges. Now I want to run another routine which says "sum all the values for the cells within Rng1, then do it for Rng2, then Rng3, etc."

As it stands now if I try to do something like that it doesn't work and I'm assuming that's because they are separate routines. So even if I run routineA, if I try and run a different one that says sum(rng1) it doesn't know what is in rng1 because I haven't defined it in routineB.

Is it possible to refer to ranges established in a different sub? If so, how?

Any links/insight/books you can point me to would be much appreciated.

 

[AlphaFrog]

Can you show your code where you create the series ranges e.g. series1.XValues? How you created them will determine how you reference or offset from those ranges.


Alternatively, here's another approach...

Working with ranges used in a chart is tricky because VBA doesn't give you a range object of say the series1.XValues. You can parse the range from a text string of a "Series" formula. Fortunately, someone has done that code for us.

A Class Module to Manipulate a Chart SERIES

Put this code in a Class module called ChartSeries

Using the above ChartSeries class, this code will read the series a-1 point b and offset -2 columns and read that value.

Sub ExampleUsage()

    a = 4
    b = 5

    Dim MySeries As New ChartSeries
    With MySeries
        .Chart = ActiveChart
        .ChartSeries = a - 1
         If .ValuesType = "Range" Then
            Txt = "Series " & .SeriesName
            Txt = Txt & " point " & b
            Txt = Txt & " (" & .Values(b).Text & ")"
            Txt = Txt & " - " & .Values(b).Offset(, -2).Value
            MsgBox Txt
        End If
    End With
    
End Sub

 

[pgc01]

Interesting. Why do you prefer that over defining ranges as listed above? Is it just personal preference or is there a specific reason why you think that is better to use.

I'm following an old programming rule that states that a variable should always be declared with the smallest scope possible.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
This limits the visibility and accessibility of the variable, minimising thus the risk of modifying or using the variable in other parts of the solution in an incorrect way.<o></o>
<o> </o>
For ex. in the code I posted the value of Rgn1 is always defined inside the function with the same name. There’s no way that any other part of the code can change its value.<o></o>
<o> </o>
Remark: this is the type of rule that is mandatory in a project where several teams write different parts of code. In a case like the one in this thread, with 1 person writing 1 page of code, you don't really need rules.<o></o>
<o> </o>
I still think that even in a simple case like this one it makes sense to follow the rule. Besides the security reasons explained before, why use a third party (the module), where you store and access the variable, when you can exchange the information directly between the 2 procedures?<o></o>
<o> </o>
An example of an error difficult to debug that could happen in a project if you would not follow this rule was due to a syntax problem of an undeclared variable. The problem was the following: in another procedure (several pages down, maybe written by another person) you would use another variable Rng1, but you forgot to declare it in the procedure. If you had defined a Rgn1 variable at the module level you would not get the missing variable warning, because the variable was defined (although not where you wanted). You can imagine the time one would waste sometimes with an error like this one, when the solution would work ok for some time and then suddenly something would go wrong with no apparent reason.<o></o>
<o> </o>
Conclusion: as I said before this is a rule that you would use in a big project, you don’t really need to follow it if you are just writing some lines of code, but it’s good programming practice and like to follow it even in simple cases.<o></o>

 

[largeselection]

Thanks! Interesting to know and it makes a lot of sense.

AlphaFrog- Here is the code I am using to create the ranges:

Dim xrng0 As Range
Dim xrng1 As Range
Dim xrng2 As Range
...etc...
Dim xrng10 As Range
Dim yrng0 As Range
Dim yrng1 As Range
Dim yrng2 As Range
...etc...
Dim yrng10 As Range
 
Application.ScreenUpdating = False
 
Set xrng0 = Nothing
Set xrng1 = Nothing
Set xrng2 = Nothing
...etc...
Set xrng10 = Nothing
Set yrng0 = Nothing
Set yrng1 = Nothing
Set yrng2 = Nothing
...etc...
Set yrng10 = Nothing
 
Dim LastRow As Long
Dim i As Long
Dim CaseID As String
 
LastRow = Cells(Rows.Count, "G").End(xlUp).Row
For i = 28 To LastRow
CaseID = Cells(i, "G").Value
 
Select Case CaseID
 
Case Is = "0"
    If xrng0 Is Nothing Then
        Set xrng0 = Cells(i, "D")
    Else
        Set xrng0 = Union(xrng0, Cells(i, "D"))
    End If
    If yrng0 Is Nothing Then
        Set yrng0 = Cells(i, "E")
    Else
        Set yrng0 = Union(yrng0, Cells(i, "E"))
    End If
    
Case Is = "1"
    If xrng1 Is Nothing Then
        Set xrng1 = Cells(i, "D")
    Else
        Set xrng1 = Union(xrng1, Cells(i, "D"))
    End If
    If yrng1 Is Nothing Then
        Set yrng1 = Cells(i, "E")
    Else
        Set yrng1 = Union(yrng1, Cells(i, "E"))
    End If 
Case Is = "2"
 
...etc...
 
End Select
 
Next i
 
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
If xrng0 Is Nothing Then
ActiveChart.SeriesCollection(1).XValues = 0
ActiveChart.SeriesCollection(1).Values = 0
Else
ActiveChart.SeriesCollection(1).XValues = xrng0
ActiveChart.SeriesCollection(1).Values = yrng0
End If
If xrng1 Is Nothing Then
ActiveChart.SeriesCollection(2).XValues = 0
ActiveChart.SeriesCollection(2).Values = 0
Else
ActiveChart.SeriesCollection(2).XValues = xrng1
ActiveChart.SeriesCollection(2).Values = yrng1
End If
If xrng2 Is Nothing Then...
...etc...
 
Application.ScreenUpdating = True

I know it's probably not the most elegant way to accomplish it, but it seems to be working thus far.

Does this help?

 

[largeselection]

So I have reviewed a bunch of the material basically I'm just trying to get at the parts that I need...

Using some of what you linked to I can see how to pull out the Series addresses, but where I'm having difficulty is how to set that string of addresses as a range (so that I can subsequently reference the 2nd, 3rd, nth, address in that range and use the value 2 cells to the left).

Without creating a range I don't know how I would reference the nth entry (it would have to be like mid(formula, starting from the 3rd comma, to the 4th comma) but a different comma depending on which nth entry I'm clicking on.

As of now I have this code which can pull the range of cells that I'm interested in:

Dim CloseParen As Integer
Dim FirstComma As Integer
Dim SecondComma As Integer
Dim SFormula As String
Dim Ranga as range
 
SFormula = ActiveChart.SeriesCollection(2).Formula
FirstComma = InStr(1, SFormula, ",")
CloseParen = InStr(1, SFormula, ")")

If Mid(SFormula, FirstComma + 1, 1) = "(" Then
Ranga = Range(""" & Mid(SFormula, FirstComma + 2, CloseParen - (FirstComma + 2)) & """)
Else
SecondComma = InStr(FirstComma + 1, SFormula, ",")
Ranga = Range(""" & Mid(SFormula, FirstComma + 1, SecondComma - (FirstComma + 1)) & """)
End If

This does not work though. I tried the Range("""...""") part in a msgbox and it pops up with (what appears to be) the correct range reference.

IE- if I just run that bit of code into a message box I get:
Range("Sheet1!$A$1, Sheet1!$A$7, Sheet1!$A$19, Sheet1!$A$24")

but though it looks like that it doesn't actually work like a range in anything else. So I can't then reference "Ranga" in a messagebox like "Ranga.Address" because it bombs out.

 

[largeselection]

I think I may have been overcomplicating things. It seemed to work if rather than trying to "create" a range I just used Range(Ranga).

 

[AlphaFrog]

Can the macro sort your data based on column G so that all the vales in column G are grouped together?

This is easy to do in a macro and it would mean each SeriesColletion would have one contiguous data range. It would make your macros a whole lot more simple.

I could post some example code it sorting on column G is a go.

 

[largeselection]

I might be able to have it sort column G, but I was hoping to avoid that.

I feel like it's very close though.

Here's where I'm at:

Dim SFormula As String
    Dim FirstComma As Integer
    Dim SecondComma As Integer
    Dim CloseParen As Integer
 
....
SFormula = ActiveChart.SeriesCollection(a).Formula
    FirstComma = InStr(1, SFormula, ",")
    CloseParen = InStr(1, SFormula, ")")
        If Mid(SFormula, FirstComma + 1, 1) = "(" Then
        ref = Mid(SFormula, FirstComma + 2, CloseParen - (FirstComma + 2))
        Else
        SecondComma = InStr(FirstComma + 1, SFormula, ",")
        ref = Mid(SFormula, FirstComma + 1, SecondComma - (FirstComma + 1))
        End If
 
With ActiveChart.SeriesCollection(a).Points(b)
             .HasDataLabel = True
            Txt = "Series " & .Parent.Name
            Txt = Txt & " point " & b
            Txt = Txt & " (" & .DataLabel.Text & ")"
            Txt = Txt & " - " & Range(ref).Address

Now when I do this, I get a label popup that has the addresses of the XValues stripped from the formula just like I wanted. So I tried to change it up and have it pull the bth value (in the example I tested it was Series 10 = a, point b = 5) and it did not work. So I tried changing the last part to Range(ref)(b).Address.

This seemed to work in isolated testing, but now it's not giving me the address. I also tried Range(ref).Cells(b,1).Address and that is giving me the cell 5 rows down from the starting cell, not the 5th address in the newly created range(ref).

Is that what you thought might eb the problem?

 

[AlphaFrog]

I'm trying to follow along but it's difficult.

Are you saying now you have the range of a given SeriesCollection and it's called Range(ref) ?

If yes, then if Range(ref) is a noncontiguous range of cells, it can be tricky to get say the 5th cell within that range. There isn't an index you can reference for a noncontiguous range of cells.

A sort of brute force method to get the 5th cell in a noncontiguous range (single column) is just to loop through the range until you get to the 5th one; e.g..

    Dim ref As String, Txt As String, counter As Long
    Dim b As Integer, rcell As Range
    
    ref = "A1, A10:A15, A100"
    b = 5
    
    For Each rcell In Range(ref)
        counter = counter + 1
        If counter = b Then
            Txt = rcell.Address
            Exit For
        End If
    Next rcell
       
    MsgBox Txt

This should return $A$13 which is the 5th cell in Range(ref) in this example.

I'm sure some guru may have a more elegant way to do this though.

 

[largeselection]

ah very clever brute force solution. It is non-contiguous because it is Range(ref) where ref is the range of the X Values in the SeriesCollection. Those values are included in the range based on the code I posted earlier (with the Select Case method).

I will have to try this brute force method. As you have noticed I'm sure I'm not so elegant myself ha.