Possible to reference range established as part of different sub?

largeselection

Active Member
Joined
Aug 4, 2008
Messages
358
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
If you define the variable at the top of the module, each procedure within the module will have access to it.

Example: Run Macro1 then run Macro2
Code:
Dim rng1 As Range

Sub Macro1()
    Set rng1 = Range("A1:A10")
    MsgBox "rng1 is set"
End Sub

Sub Macro2()
    MsgBox "rng1 address is: " & rng1.Address
End Sub

If you define the variable using the Public key word, all modules will have access to that variable.
Code:
Public rng1 as Range
 
Upvote 0
Hi

I try to avoid module variables and even more public variables.

In the case you describe, if you want to define the range in one procedure and use it in another one you could for ex. use a function:

Code:
Function Rng1() As Range
    ' execute some code and define the range
    Set Rng1 = Range("A1:A10")
End Function
 
Sub Macro2()
    MsgBox "rng1 address is: " & Rng1.Address
End Sub
 
Upvote 0
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.
 
Upvote 0
Also,

I know this is basic, but I'm a beginner...

I have two examples:

If I have the following two subs
Code:
Sub Rng1()
 
Set Rang1 = Range("A1:A4")
Set Rang2 = Range("B1:B4")
 
End Sub
 
Sub Rng2()
 
Msgbox Rang1.Address
 
End Sub

Everything works and the messagebox at the end of Rng2 displays the address.

However I can't seem to get it to work if I change the "1" in Rang1 to something else (like a variable).

So something like these:

Code:
Sub Rng2 ()
 
Msgbox Rang(3-1).address 
 
end sub
Does not return the same as Rang2.address. I'm sure it is because it doesn't know what to do with 3-1 and I've tried different combinations of " & " etc, to no avail.

Also something like this:
Code:
Sub Rng2 ()
 
Dim a as Integer
Dim b as Integer
 
Set a = 1
Set b = 3
 
Msgbox Rang(a)(b).Address

does not work, but

Code:
Msgbox Rang1(3).Address
does work so I don't know why the above doesn't work.
 
Upvote 0
Code:
Dim Rang(1 To 2) As Range

Sub Macro1()

    Set Rang(1) = Range("A1:D5")
    Set Rang(2) = Range("E10:H20")

End Sub

Sub Macro2()

    Dim i As Integer, r As Integer, c As Integer
    
    r = 2
    c = 3
    
    For i = 1 To 2
    
        MsgBox "Address for Rang" & i & ": " & Rang(i).Address & vbCr & _
               "Address for the 2nd row, 3rd column in Rang" & i & " is: " & Rang(i).Cells(r, c).Address
                        
    Next i
    
End Sub
 
Upvote 0
Thanks!

But for some reason I can't even get just the rang(i) part to work.

So if I do:

Code:
Sub Macro2 ()
 
dim i as integer
 
Set i = 2
 
msgbox rang(i).Address 
 
end sub

does not work vs msgbox rang2.Address which does.
 
Upvote 0
Did you dimension Rang as an array?
Code:
Dim Rang(1 To 2) As Range

Did you set Rang(2) to some range?
Code:
Sub Macro1()

    Set Rang(1) = Range("A1:D5")
    [COLOR="Red"]Set Rang(2) = Range("E10:H20")[/COLOR]

End Sub
 
Upvote 0
Perhaps it would help if I elaborated more on the end goal:

Basically I have a scatterplot which is created based on 15 different series.

The ranges that make up the series' are built based on the values in a column: IE - if the value = 1 in cell G5, then add cell D5 to series1.XValues, and add cell E5 to series1.Values, if the value = 2 in cell G5, then ... to series2.XValues, etc, etc.

So I end up with a bunch of different ranges which are not arranged in row order- Series1 may be row 5, 15, 16, 28, 50, so they are not together.

I'm trying to adjust a Ch_Mousedown event and the text that pops up when I click on a datapoint in the chart. So in the code for that I can retrieve that it's in CollectionSeries(3) and it's Point(5) of that.

I want to alter it so that I can pull the Name of that point (which would be in the same row, but column B.

So ultimately I need to be able to reference the ranges that are created when building the chart so that I can say the 5th point within that range.

The code that I am altering is:
Code:
If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
             .HasDataLabel = True
            Txt = "Series " & .Parent.Name
            Txt = Txt & " point " & b
            Txt = Txt & " (" & .DataLabel.Text & ")"
            Txt = Txt & " - " &

but I can't figure out what to put after the & in the last row. Ideally I would want to say something like & Xrng(a-1)(b).offset(0,-2)<because I have named the ranges Xrng0-15 so the seriescollection(3) would correspond to the Xrng2>.


So that ideally I would say to add the text in the cell that is two columns to the left of the b'th point in the (a-1)'th Xrng. where b = the point index from the clicked point and a = the series index.

I don't know if that helped or made it more confusing, but maybe now you can see what I'm driving at.

Thank you for your help!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top