# Excel VBA: Named Range - Across multiple worksheets, same workbook



## MRdNk (Apr 7, 2007)

I have an Excel workbook with a named range that I want to extend across multiple sheets, and let VBA refer to them all.

At the moment, as soon as I add a reference in my named range to another worksheet the VBA code stops working.

The whole code is based on wanting to be able to change a value in anyone of the cells in the Named Range, and it change all the other cell values to be the same. I want to achieve this through VBA, it works on a single sheet, but not multiple sheets.

Currently I'm just trying to get sheet1 to generate the update on other sheets for now, instead of having the sheet code on the other sheet; but maybe this is the wrong way round, please tell me.

Workbook attached.
Note: When the code crashes, you can't get the code to work again, even if you fix it; and have to exit Excel completely, then reopen the workbook - any help on how to fix this would be good too.

Although I do use classes, I haven't so far in this, and any suggestions along this root welcome.

*What I have so far:*
Worksheet Code (sheet1):

```
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim Cell As Variant

    Application.EnableEvents = False
    If Macro3(Target.Value, Target) = True Then
    End If
    Application.EnableEvents = True

End Sub
```
Macro Code:

```
Option Explicit

Public Function Macro3(newValue, rng As Range)
' Macro3 Macro
'
Dim Cell As Variant
Dim CellClass As Variant
Dim ws As Worksheet

For Each CellClass In Sheets("classids").Range(Sheets("classids").Range("A1").Offset(1, 0), Sheets("classids").Range("A1").End(xlDown))
    For Each Cell In Range(CellClass.Value)
        If Cell.Address = rng.Address Then
            Sheets("RawData").Range(CellClass.Offset(0, 1).Value).Value = newValue
        End If
    Next Cell
    For Each Cell In Range(CellClass.Value)
        Cell.Value = Sheets("RawData").Range(CellClass.Offset(0, 1).Value).Value
    Next Cell
Next CellClass

End Function
```
All help, gratefully appreciated.


----------



## MRdNk (Apr 7, 2007)

Starting to wonder if there is a solution that doesn't end up being ridiculously over complicated. Although if achieved via classes and add-ins, this could make it worth while.


----------



## bomb #21 (Jul 1, 2005)

"At the moment, as soon as I add a reference in *my named range* ..."

Can you explain to me in layman's terms where that is, exactly?

OK, maybe not.

First thought is does "multiple sheets" = "all sheets"? If yes, then you want to use Workbook_SheetChange in the ThisWorkbook module, rather than Worksheet_Change in (all) the Worksheet modules.

Anyway, this worked for me:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim x, y As String
Dim ws As Worksheet
x = Target.Address
y = Target.Value
Application.EnableEvents = False
For Each ws In ActiveWorkbook.Sheets
ws.Range(x) = Target.Value
Next ws
Application.EnableEvents = True
End Sub

meaning that I tested by making entries in A6 in any *one* of your sheets and it entered across *all 3* sheets.

Which just leaves ... where's the range that Target needs to intersect? (per sheet?)

Note: I've no idea if *Dim x, y As String* is correct since I tend not to get involved with declaring variables. That said, it doesn't seem to cause a problem.

HTH


----------



## MRdNk (Apr 7, 2007)

It's closer.

Sorry the named ranges, are listed in the named ranges list. Insert>Names (or something like that in 2003), 2007 is Formulas>Named Manager.

The class_name range is being used wherever you see the cell value "bob" on sheet1 these are included in the named range "class_name". Changing one of these will change all the others, and on sheet "rawdata", a named range exists called id_name - this has been separated from the rest so that I can use the range name to pickup values via Access.


----------



## MRdNk (Apr 7, 2007)

Your post seems to be a potential way around, however, how can I make the values show for values in a named range. 
For Each Cell In Range(CellClass.Value),

Is really:

*For Each Cell In Range("class_name")*

This errors as:
*Method 'Range' of object '_GLOBAL' failed*

When the range "class_name", includes a reference to another sheet.


----------



## Zack Barresse (Jul 25, 2004)

Is the named range defined on each sheet? It must be, _and_ you must reference each worksheet when referencing a named range. I.e. just using "Range("named range")" will not work, but "Sheets("sheet name").Range("named range")" is going to work. One way would be to loop through each worksheet, the other would be to select each worksheet in a selection array, enter the value, then de-select them (or select one sheet instead, as each action will carry onto all sheets at that point).

Here is an example of using one named range against all worksheets in the workbook (since you can only have one unique defined name per workbook, not worksheet)...


```
Function testingTSG()

    Dim ws As Worksheet, rngAddy As Range, c As Range, sTemp As String

    Set rngAddy = Sheets("Sheet1").Range("class_name")

    For Each ws In ThisWorkbook.Worksheets
        For Each c In ws.Range(rngAddy.Address).Cells
            'perform action here, for example...
            If Len(Trim(c.Value)) > 0 Then sTemp = sTemp & c.Value & vbNewLine
        Next c
    Next ws

    MsgBox Trim(sTemp), vbOKOnly
    
End Function
```
Not sure if this will help or not, but hope it does.


----------



## MRdNk (Apr 7, 2007)

Thanks for your input.

I think what I'm really needing, is a Does Range Exist function:

I've found the code below on OzGrid.com, but it errors out with:

Run-time error '1004':
Method 'Range' of object '_GLOBAL' failed

Code (OzGrid.com):

```
Sub DoesRangeExist()

''''''''''''''''''''''''''''''''''''''''

'Written by www.ozgrid.com



'Test to see if a named range exists.

''''''''''''''''''''''''''''''''''''''''

Dim rRange As Range

On Error Resume Next
	Set rRange = Range("MyRange")
		If rRange Is Nothing Then 'Doesn't exist
			MsgBox "The named range does not exist", _
                                vbCritical,"OzGrid.com"
			Set rRange = Nothing
			On Error GoTo 0
		Else 'Does exist
			MsgBox "The named range does exist", _
				 vbInformation,"OzGrid.com"
			Set rRange = Nothing
			On Error GoTo 0
		End If
End Sub
```
Link: http://www.ozgrid.com/VBA/IsWorkbookOpen.htm


----------



## Zack Barresse (Jul 25, 2004)

This function will check if a named range exists. You can call with a specific worksheet or not (which would assume the active sheet)...


```
Public Function DOESRANGEEXIST(strRngName As String, Optional wks As Worksheet) As Boolean
     If wks Is Nothing Then
        If ActiveSheet Is Nothing Then Exit Function
        Set wks = ActiveSheet
    End If
    On Error Resume Next
    DOESRANGEEXIST = Len(wks.Range(strRngName).Name)
End Function
```
Call like so...


```
doesrangeexist("range name")
doesrangeexist("range name", sheets("Sheet1"))
```
HTH


----------



## MRdNk (Apr 7, 2007)

Errors out for me; am I missing something, need to turn something on? Add a VBA reference?


----------



## Zack Barresse (Jul 25, 2004)

Nope. It's a function though. Are you running it like a Sub routine perhaps??


----------



## MRdNk (Apr 7, 2007)

Sorry being dumb!

How do I use it?

Have this in a module:

```
Sub test()
If (DOESRANGEEXIST("range name")) = True Then
    MsgBox "True"
Else
    MsgBox "False"
End If
End Sub

Public Function DOESRANGEEXIST(strRngName As String, Optional wks As Worksheet) As Boolean
     If wks Is Nothing Then
        If ActiveSheet Is Nothing Then Exit Function
        Set wks = ActiveSheet
    End If
    On Error Resume Next
    DOESRANGEEXIST = Len(wks.Range(strRngName).Name)
End Function
```
Pressing F5 on the sub test.


----------



## MRdNk (Apr 7, 2007)

Excel 2007 issue?

I have it at home, but developing stuff for Excel 2003 (work version) - bit of a pain.


----------



## Zack Barresse (Jul 25, 2004)

I tested in 2007, works ok. Just tested again in 2003, works ok there too. What kind of error are you getting? A debug error? If so, what line does it debug to? Do you get erroneous results? I'm assuming you're testing on a valid range name as well, i.e. no spaces?


----------



## MRdNk (Apr 7, 2007)

I get:

*Run-time error '1004':
Method 'Range' of object '_GLOBAL' failed*

On line:
DOESRANGEEXIST = Len(wks.Range(strRngName).Name)

Tried it without spaces, not sure why that one didn't, but yes still doesn't work, unless I have a range name that does exist, in which case it works, and returns TRUE.


----------



## Zack Barresse (Jul 25, 2004)

Do you have some other global variable somewhere? If you specify the worksheet, does it happen then? You might be looking at a bad sheet, or chart sheet perhaps. Prior to you running any code, and while in the VBE, does hitting Alt + D, then L, do anything?

Edit: perhaps I'm hitting the wrong area. Maybe it is the error handling. Let's try to skip and exit on any error. Try this...

```
Public Function DOESRANGEEXIST(strRngName As String, Optional wks As Worksheet) As Boolean
    DOESRANGEEXIST = False
    On Error GoTo ErrExit
     If wks Is Nothing Then
        If ActiveSheet Is Nothing Then Exit Function
        Set wks = ActiveSheet
    End If
    DOESRANGEEXIST = Len(wks.Range(strRngName).Name)
ErrExit:
End Function
```


----------



## MRdNk (Apr 7, 2007)

ALT + D then L doesn't seem to do anything.

Have tried it in a completely new Excel workbook.

Still getting the same error.


----------



## Zack Barresse (Jul 25, 2004)

Can you post the new file?


----------



## MRdNk (Apr 7, 2007)

Yep; here it is.


----------



## Zack Barresse (Jul 25, 2004)

It runs just fine for me. LOL! Try some innocuous code instead, leaving the function commented out. So maybe try this...


```
'Public Function DOESRANGEEXIST(strRngName As String, Optional wks As Worksheet) As Boolean
'     If wks Is Nothing Then
'        If ActiveSheet Is Nothing Then Exit Function
'        Set wks = ActiveSheet
'    End If
'    On Error Resume Next
'    DOESRANGEEXIST = Len(wks.Range(strRngName).Name)
'End Function

Public Function DOESRANGEEXIST() As Boolean
    DOESRANGEEXIST = False
End Function
```


----------



## MRdNk (Apr 7, 2007)

Works perfectly fine.


----------



## Zack Barresse (Jul 25, 2004)

Okay, let's narrow this down a little bit. Try without an optional worksheet, and specify it..

```
Public Function DOESRANGEEXIST(strRngName As String, wks As Worksheet) As Boolean
    On Error Resume Next
    DOESRANGEEXIST = Len(wks.Range(strRngName).Name)
End Function
```
I'm thinking it's the way the range error is being handled. What are your VBE options (VBE | Tools | Options)? Are you breaking on unhandled errors?


----------



## MRdNk (Apr 7, 2007)

Run-time error '1004': 
on:
*DOESRANGEEXIST = Len(wks.Range(strRngName).Name)*


----------



## Zack Barresse (Jul 25, 2004)

Okay.... sick of me yet? 

Let's try a different way (narrowing down even more). I don't expect this to work for you either, but need to check...

```
Public Function DOESRANGEEXIST(strRngName As String, Optional wks As Worksheet) As Boolean
    Dim rngDummy As Range
    If wks Is Nothing Then
        If ActiveSheet Is Nothing Then Exit Function
        Set wks = ActiveSheet
    End If
    On Error Resume Next
    Set dummy = wks.Range(strRngName)
    DOESRANGEEXIST = Not dummy Is Nothing
End Function
```


----------



## MRdNk (Apr 7, 2007)

Still broken.

Have to change the typo - rngdummy to dummy to get it to work at all. But then still run-time 1004.
On....
*Set Dummy = wks.Range(strRngName)*


----------



## MRdNk (Apr 7, 2007)

Solved it.

VBA Environment.
Tools>Options>General>Error Trapping:
*Break on all Errors*, changed to *Break on Unhandled Errors*.


----------



## MRdNk (Apr 7, 2007)

When I say solved it, I mean the range exists issue, however the original problem still exists.


----------



## Zack Barresse (Jul 25, 2004)

So did bomb's post #3 or my post #5 not help with that issue at all?


----------



## MRdNk (Apr 7, 2007)

The problem with this is that rngAddy is just skipped, and is set to nothing 
Bomb's works, but its not addressing the issue of how to define around a named range, as far as I can see, it only works if the range address is the same.


```
Function testingTSG()

    Dim ws As Worksheet, rngAddy As Range, c As Range, sTemp As String
    On Error Resume Next
    Set rngAddy = Sheets("Sheet1").Range("class_name")

    For Each ws In ThisWorkbook.Worksheets
        For Each c In ws.Range(rngAddy.Address).Cells
            'perform action here, for example...
            If Len(Trim(c.Value)) > 0 Then sTemp = sTemp & c.Value & vbNewLine
        Next c
    Next ws

    MsgBox Trim(sTemp), vbOKOnly
    
End Function
```
What I really need is a way to access a range for the workbook: thisworkbook.range("class_name").
Not sure how possible it is, or if I have to go down another route of having range names with the same name suffixed with a number, so that a named range isn't spread across different sheets.


----------



## Zack Barresse (Jul 25, 2004)

I still don't understand what you want to do if the ranged name does not exist. Directly after the _rngAddy_ variable is set (or not) you can check if it *Is Nothing* or not and go from there. What exactly do you want to do if there is no named range? Create one? This is where you're losing me.

If you want to look at all the names, just loop through the Names collection ...

```
Sub ShowWorkbookNames()
    Dim n As Name
    For Each n In ThisWorkbook.Names
        Debug.Print n.Name & ", " & n.RefersTo
    Next n
End Sub
```
HTH


----------



## MRdNk (Apr 7, 2007)

Well that is interesting, I guess the refersTo can be broken down into the sheet name and each range it refers too.


Started playing with it; will post again, if I either get stuck again, or finish it.


----------

