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.

18284

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. :(
18288


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

[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.... :(

18357

[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! :friends: