Named Ranges - Same code, but one is cast as Variant and the other a Range?

[zredbaron]

Hi.

I'm attempting to refer to three named ranges, and for whatever reason, one is successfully cast as a Range object, and the other two are cast as a Variant. I'm trying to cast them all as a Range!

Using the Name manager and not VBA, I have set up three named Ranges highlighted below.

named ranges.jpg

Under ThisWorkbook in VBA, I have the following code:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim RngRoster, RngCategory, RngCategoryList As Range
    Dim RefRoster, RefCategory, RefCategoryList As String
    
    On Error Resume Next
        Set RngRoster = ThisWorkbook.Names("RangeRoster").RefersToRange
        Set RngCategory = ThisWorkbook.Names("RangeCategory").RefersToRange
        Set RngCategoryList = ThisWorkbook.Names("RangeCategoryList").RefersToRange
    On Error GoTo 0
    
    RefRoster = ActiveWorkbook.Names("RangeRoster")
    RefCategory = ActiveWorkbook.Names("RangeCategory")
    RefCategoryList = ActiveWorkbook.Names("RangeCategoryList")
  
    If Not Intersect(Target, RngRoster) Is Nothing Then
        MsgBox "A name was added or changed!"
    ElseIf Not Intersect(Target, RngCategory) Is Nothing Then
        MsgBox "A category was added or changed!"
    ElseIf Not Intersect(Target, RngCategoryList) Is Nothing Then
        MsgBox "A category LIST entry was added or changed!"
    End If
    
End Sub

For whatever reason, the three Ranges are cast differently. The only difference I can see is that they are all referring to different tabs, one of which refers to a column of a named table.

Oddly, one of the plain ranges works and the other does not. I would have thought the table would be the odd man out, but I guess not.

Regardless, my behavior is that I get a run-time error 1004: Method 'Intersect' failed. It will break when the Intersect() function is passed either the RngRoster or RngCategory variables.

Here is my Watch List, which shows that I am casting these as different types.
Watches.png


I did my best to provide detail on my issue. Thank you so much for your time!

[jaslake]

Hi zredbaron

If you're not explicit in the Dim Statement, Variant is assumed

Dim RngRoster As Range, RngCategory As Range, RngCategoryList As Range
   Dim RefRoster As String, RefCategory As String, RefCategoryList As String

[zredbaron]

Oh wow, I had no idea. It's difficult going from object oriented programming to VBA.... THANKS!!!

[jaslake]

Your welcome...

[zredbaron]

I'm still having runtime 1004 errors:

  
Option Explicit

Dim RngRoster As Range, RngCategory As Range, RngCategoryList As Range
Dim RefRoster As String, RefCategory As String, RefCategoryList As String

Public Sub Initialize_Variables()
  On Error Resume Next
    Set RngRoster = ThisWorkbook.Names("RangeRoster").RefersToRange
    Set RngCategory = ThisWorkbook.Names("RangeCategory").RefersToRange
    Set RngCategoryList = ThisWorkbook.Names("CategoryList").RefersToRange
    
    RefRoster = ActiveWorkbook.Names("RangeRoster")
    RefCategory = ActiveWorkbook.Names("RangeCategory")
    RefCategoryList = ActiveWorkbook.Names("CategoryList")
    
  On Error GoTo 0
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      
    Call Initialize_Variables
    
    MsgBox "content changed!  " & Target.Address & ", " & Sh.Name
    
    'This will print the formula if you need it:
    'MsgBox "named: " & ActiveWorkbook.Names("RngCategoryList")
    'MsgBox "named: " & ActiveWorkbook.Names("RngCategory")
    'MsgBox "named: " & ActiveWorkbook.Names("RngRoster")
      
    If Not Intersect(Target, RngRoster) Is Nothing Then
        MsgBox "A name was added or changed!"
    ElseIf Not Intersect(Target, RngCategory) Is Nothing Then
        MsgBox "A category was added or changed!"
    ElseIf Not Intersect(Target, RngCategoryList) Is Nothing Then
        MsgBox "A category LIST entry was added or changed!"
    End If
    
End Sub

I suspect I am not using my variables correctly....

Capture.jpg

[zredbaron]

Thanks in advance!

[Paul_Hossler]

VBA is OOP (sort of a poor version)

I think the problem is that the named ranges are not on the sheet being changed so Intersect doesn't work

Also I don't think you want to use the workbook event, but instead maybe use the worksheet event for the worksheet where the named ranges are

The Init variables sub would normally be in a standard module (IMO anyway)

Try to avoid On Error Resume Next unless really necessary


1. Your original with tests for the sheet that was changed

Option Explicit
 
Dim RngRoster As Range, RngCategory As Range, RngCategoryList As Range
Dim RefRoster As String, RefCategory As String, RefCategoryList As String
 
Public Sub Initialize_Variables()
'    On Error Resume Next
    Set RngRoster = ThisWorkbook.Names("RangeRoster").RefersToRange
    Set RngCategory = ThisWorkbook.Names("RangeCategory").RefersToRange
    Set RngCategoryList = ThisWorkbook.Names("CategoryList").RefersToRange  '   iconsistant name?
    
     
    RefRoster = ActiveWorkbook.Names("RangeRoster")
    RefCategory = ActiveWorkbook.Names("RangeCategory")
    RefCategoryList = ActiveWorkbook.Names("CategoryList")
     
'    On Error GoTo 0
End Sub
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     
    Call Initialize_Variables
     
    MsgBox "content changed!  " & Target.Address & ", " & Sh.Name
     
     'This will print the formula if you need it:
     'MsgBox "named: " & ActiveWorkbook.Names("RngCategoryList")
     'MsgBox "named: " & ActiveWorkbook.Names("RngCategory")
     'MsgBox "named: " & ActiveWorkbook.Names("RngRoster")
    
    If RngRoster.Parent Is Sh Then
        If Not Intersect(Target, RngRoster) Is Nothing Then
            MsgBox "A name was added or changed!"
            Exit Sub
        End If
    End If
    
    If RngCategory.Parent Is Sh Then
        If Not Intersect(Target, RngCategory) Is Nothing Then
            MsgBox "A name was added or changed!"
            Exit Sub
        End If
    End If
    
    If RngCategoryList.Parent Is Sh Then
        If Not Intersect(Target, RngCategoryList) Is Nothing Then
            MsgBox "A name was added or changed!"
            Exit Sub
        End If
    End If
    
     
End Sub

2. Standard module (note the Public scope) and no On Error

Option Explicit
Public RngRoster As Range, RngCategory As Range, RngCategoryList As Range
Public RefRoster As String, RefCategory As String, RefCategoryList As String
 
Public Sub Initialize_Variables()
'    On Error Resume Next
    Set RngRoster = ThisWorkbook.Names("RangeRoster").RefersToRange
    Set RngCategory = ThisWorkbook.Names("RangeCategory").RefersToRange
    Set RngCategoryList = ThisWorkbook.Names("CategoryList").RefersToRange  '   iconsistant name?
    
     
    RefRoster = ActiveWorkbook.Names("RangeRoster")
    RefCategory = ActiveWorkbook.Names("RangeCategory")
    RefCategoryList = ActiveWorkbook.Names("CategoryList")
     
'    On Error GoTo 0
End Sub

3. The worksheet change event on the sheet where the named ranges are

This only fires is a cell on this sheet is changed

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Call Initialize_Variables
     
    MsgBox "content changed!  " & Target.Address
     
     'This will print the formula if you need it:
     'MsgBox "named: " & ActiveWorkbook.Names("RngCategoryList")
     'MsgBox "named: " & ActiveWorkbook.Names("RngCategory")
     'MsgBox "named: " & ActiveWorkbook.Names("RngRoster")
    
    If Not Intersect(Target, RngRoster) Is Nothing Then
        MsgBox "A name was added or changed!"
        Exit Sub
    End If
    
    If Not Intersect(Target, RngCategory) Is Nothing Then
        MsgBox "A name was added or changed!"
        Exit Sub
    End If
    
    If Not Intersect(Target, RngCategoryList) Is Nothing Then
        MsgBox "A name was added or changed!"
        Exit Sub
    End If
End Sub

If you decide to try 2 and 3, remember to delete/comment out the ThisWorkbook event handler

[zredbaron]

Awesome, thanks so much for the detailed reply, Paul! I appreciated looking at your workbook and seeing how you placed the various bits of code in the various parts of the worksheet. Thanks again for your time!