Ranges in code

B

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
Why does this fail?
Code: 
Dim QueryRange As Range
  Set QueryRange = SAP02.Range("SAPBEXq0001")

but this not?
Code: 
Dim QueryRange As Range
  Set QueryRange = Range("SAPBEXqueries!SAPBEXq0001")

Yes, SAP02 is the codenae for worksheet "SAPBEXqueries"
 
Sort by date Sort by votes
Or go straight for the name, e.g.,

<font face=Courier New><SPAN style="color:darkblue">Dim</SPAN> QueryRange <SPAN style="color:darkblue">As</SPAN> Range
<SPAN style="color:darkblue">Set</SPAN> QueryRange = [SAPBEXq0001]
MsgBox QueryRange.Parent.CodeName</FONT>

:)
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Gentlemen, thanks for your interest!

This produces no results (as expected since the names are not GLOBAL)
Code: 
Sub listnames()
    Dim n As Name
    For Each n In Names
        Debug.Print n.Name, n.Parent.Name, n.Parent.CodeName
    Next n
End Sub

This produces results (since CODENAME is specified)
Code: 
Sub listnames()
    Dim n As Name
    For Each n In SAP02.Names
        Debug.Print n.Name, n.Parent.Name, n.Parent.CodeName
    Next n
End Sub
Perhaps a mute point but bceause I explicetedly refer to SAP02 in my code, I suppose it goes without saying that the code is NOT in the SAP02 code behind sheet.

Here's the output
Code: 
SAPBEXqueries!SAPBEXq0001				SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001f0CALMONTH     		SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001f3SFBZ1ATIP6F3WO3WKZA96RTH     SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001fZGOAOPDIF       		SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001fZGOCPRTY       		SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001fZGOCTCONT       		SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001fZGONGPOSN__ZGONGDLTP          SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001fZGONGPOSN__ZGOSLPRTP          SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001fZGOPINDEX       		SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001fZGOPNPOSN__ZGOPNTRDD          SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001fZGOPNPOSN__ZGOPNTYPE          SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001fZGOPTPNT        		SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001fZGOPTPNT__ZGOAOMKRG           SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001fZGOTDTRDE       		SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001fZGOTPRPTP       		SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001tFILTER_0CALMONTH              SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001tFILTER_0SOURSYSTEM            SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001tFILTER_ZGODTAOGN              SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001tFILTER_ZGOPNPOSN__ZGOPNTYPE   SAPBEXqueries SAP02
SAPBEXqueries!SAPBEXq0001tREPTXTLG        		SAPBEXqueries SAP02
 
Upvote 0
Hi, I did some basic tests and heres my findings:-

A globally defined name will work except where there is a conflict between the sheet qualifier in the range and the sheet refered to in the reference.

A sheet level name will fail if the active sheet is not the same as the sheet in the reference (presuming sheet name doesnt preceed the range). It will also fail if the sheet qualifier in the range cell referecne is different to the sheet in the reference.

Code: 
Sub GlobalName()
Dim QueryRange As Range

Sheets(1).Activate
ActiveWorkbook.Names.Add Name:="GlobalName", RefersToR1C1:= _
        "=Sheet1!R1C1:R3C1"

Sheets(2).Activate

'this works
Set QueryRange = Range("GlobalName")
MsgBox "Msg1" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
  "Address: " & QueryRange.Address
Set QueryRange = Nothing

'this works even though active sheet is sheet2 and reference is on
'sheet1 plus I am including a sheet name.
Set QueryRange = Range("Sheet2!GlobalName")
MsgBox "Msg2" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
  "Address: " & QueryRange.Address
Set QueryRange = Nothing

'this works even though active sheet is sheet2
Set QueryRange = Range("Sheet1!GlobalName")
MsgBox "Msg3" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
  "Address: " & QueryRange.Address
Set QueryRange = Nothing

'this works where I express the same sheet as the reference
Set QueryRange = Sheet1.Range("Sheet3!GlobalName")
MsgBox "Msg4" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
  "Address: " & QueryRange.Address
Set QueryRange = Nothing

'this fails
On Error GoTo ErrH
Set QueryRange = Sheet3.Range("GlobalName")
MsgBox "Msg5" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
  "Address: " & QueryRange.Address
Set QueryRange = Nothing


ErrH:
MsgBox "Error #: " & Err.Number & vbLf & "Description: " & Err.Description
ActiveWorkbook.Names("GlobalName").Delete
Set QueryRange = Nothing
End Sub

Code: 
Sub SheetLevelName()
Sheets(3).Activate
ActiveWorkbook.Names.Add Name:="Sheet3!SheetLevelName", RefersToR1C1:= _
        "=Sheet3!R4C3:R6C3"
Dim QueryRange As Range


Sheets(3).Activate
'this works where both active sheet and reference are the same sheet
Set QueryRange = Range("SheetLevelName")
MsgBox "Msg1" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
  "Address: " & QueryRange.Address
Set QueryRange = Nothing

'this works where the wrong sheet is active sheet
' but the range includes the correct sheet for the reference
Sheets(2).Activate
Set QueryRange = Range("Sheet3!SheetLevelName")
MsgBox "Msg2" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
  "Address: " & QueryRange.Address
Set QueryRange = Nothing

'this works even though Im on the wrong sheet
Set QueryRange = Sheet3.Range("SheetLevelName")
MsgBox "Msg3" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
  "Address: " & QueryRange.Address
Set QueryRange = Nothing

'this fails
Sheets(2).Activate
On Error GoTo ErrH1
Set QueryRange = Range("SheetLevelName")
MsgBox "Msg4" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
  "Address: " & QueryRange.Address
Set QueryRange = Nothing
ActiveWorkbook.Names("Sheet2!SheetLevelName").Delete

ErrH1:
MsgBox "Error #: " & Err.Number & vbLf & "Description: " & Err.Description
ActiveWorkbook.Names("Sheet3!SheetLevelName").Delete

'this fails where the wrong sheet is referenced in the name
Sheets(2).Activate
On Error GoTo ErrH2
Set QueryRange = Range("Sheet2!SheetLevelName")
MsgBox "Msg2" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
  "Address: " & QueryRange.Address
Set QueryRange = Nothing

ErrH2:
MsgBox "Error #: " & Err.Number & vbLf & "Description: " & Err.Description
ActiveWorkbook.Names("Sheet3!SheetLevelName").Delete
Set QueryRange = Nothing

End Sub
 
Upvote 0
So does this work?

Code: 
Dim QueryRange As Range
  Set QueryRange = SAP02.Range("SAPBEXqueries!SAPBEXq0001")

EDIT : Hopefully Parry's findings will identify the problem. It's annoying me now!
 
Upvote 0
dk said:
So does this work?

Code: 
Dim QueryRange As Range
  Set QueryRange = SAP02.Range("SAPBEXqueries!SAPBEXq0001")

EDIT : Hopefully Parry's findings will identify the problem. It's annoying me now!
Click to expand...

Depends :)

If the sheet reference for SAPBEXq0001 was =SAP02!A1:A5 then yes because SAP02 is the same sheet as where the reference is held.

If the sheet reference for SAPBEXq0001 was =SAP01!A1:A5 then it would fail because the range needs to be a reference on sheet SAP01.

hth
 
Upvote 0
dk said:
So does this work?

Code: 
Dim QueryRange As Range
  Set QueryRange = SAP02.Range("SAPBEXqueries!SAPBEXq0001")
Click to expand...

No, it does not work for me in my situation.
 
Upvote 0
Bill, what is the reference in the name SAPBEXq0001? Plus is this a workbook level or sheet level name?
 
Upvote 0
parry said:
Hi, I did some basic tests and heres my findings:-

A globally defined name will work except where there is a conflict between the sheet qualifier in the range and the sheet refered to in the reference.

A sheet level name will fail if the active sheet is not the same as the sheet in the reference (presuming sheet name doesnt preceed the range). It will also fail if the sheet qualifier in the range cell referecne is different to the sheet in the reference.
Click to expand...

Not really sure where you were going.
However, I've very appreciative that you took the time to work towards a solution.

I believe its fair to say that the ranges are encapsulated within SAP02.
There are NO global names

Based on ouput of the two SUB's, what is your position:
1) that the original code will not work as expected
2) that the original code is sound.
3) that the original code worx for some but not others.
 
Upvote 0
parry said:
Bill, what is the reference in the name SAPBEXq0001? Plus is this a workbook level or sheet level name?
Click to expand...

This is a sheet level name and refers to:
=RawData!$BA$22:$BT$247
 
Upvote 0
Bill,
Does anything happen when you run this code?


Code: 
Sub listnames()
    Dim n As Name
    Dim QueryRange As Range

    For Each n In SAP02.Names

        If n.Name Like "*SAPBEXq0001" Then
            Set QueryRange = n.RefersToRange
            
            MsgBox "QueryRange has been set to " & n.Name
            
            
            Exit Sub
        End If

    Next n
        
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
VBA ActiveSheet vs declared worksheet
Replies
4
Views
226
OxfordCurmudgeon
O
E
  • Solved
VBA fails when worksheet is protected?
Replies
1
Views
190
EssKayKay
E
K
  • Solved
Question Only? why one vba code works and another wont, even though should have same results.
Replies
3
Views
101
MARK858
MARK858
franklin_m
  • Solved
Learning arrays ... error when trying to read from range using cells property
Replies
4
Views
90
Fluff
Fluff
T
  • Question
VBA compare values on different sheets.
Replies
5
Views
77
rlv01
R

Forum statistics

Threads
1,215,616
Messages
6,125,862
Members
449,266
Latest member
davinroach

Share this page

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