Another invalid or unqualified reference

[davegb]

Still working on this code:

Sub CtyMatch()
Dim strOrig, strOutcomes As String
Dim rCell, rTOCtyLst As Range
Dim iOrigCityNo, iEndRow As Integer

strOrig = ActiveSheet.Range("A2")
iOrigCityNo = Left(strOrig, 2)
iEndRow = ActiveSheet.Range.Cells(Rows.Count, "B").End(xlUp).Row
Set rTOCtyLst = Range(.Cells(1, 1), .Cells(1, iEndRow))

But am getting an "invalid or unqualified reference" error on the last
line, .Cells being highlighted. I copied this line from another thread
here so I'm stumped as to why it doesn't recognize ".Cells" here but
does elsewhere in a nearly identical situation.
Any suggestions?
Thanks.

 

[Peter T]

Still working on this code:

Sub CtyMatch()
Dim strOrig, strOutcomes As String
Dim rCell, rTOCtyLst As Range
Dim iOrigCityNo, iEndRow As Integer

strOrig = ActiveSheet.Range("A2")
iOrigCityNo = Left(strOrig, 2)
iEndRow = ActiveSheet.Range.Cells(Rows.Count, "B").End(xlUp).Row
Set rTOCtyLst = Range(.Cells(1, 1), .Cells(1, iEndRow))

But am getting an "invalid or unqualified reference" error on the last
line, .Cells being highlighted. I copied this line from another thread
here so I'm stumped as to why it doesn't recognize ".Cells" here but
does elsewhere in a nearly identical situation.
Any suggestions?
Thanks.

 

[Peter T]

I've only glanced at your code but try

with activesheet
Set rTOCtyLst = Range(.Cells(1, 1), .Cells(1, iEndRow))
end with

this qualifies the dot that precedes your Cells to the sheet. Alternatively
just remove those dots if, and only if you are working with the active
sheet.

It would be a good idea to qualify your variables
Dim strOrig as string, strOutcomes As String
Dim rCell as range, rTOCtyLst As Range
Dim iOrigCityNo as long, iEndRow As Long ' not Integer with rows

Regards,
Peter T

 

[Bob Phillips]

The range should be dot qualified also

with activesheet
Set rTOCtyLst = .Range(.Cells(1, 1), .Cells(1, iEndRow))
end with

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[davegb]

I've only glanced at your code but try

with activesheet
Set rTOCtyLst = Range(.Cells(1, 1), .Cells(1, iEndRow))
end with

this qualifies the dot that precedes your Cells to the sheet. Alternatively
just remove those dots if, and only if you are working with the active
sheet.

It would be a good idea to qualify your variables
Dim strOrig as string, strOutcomes As String
Dim rCell as range, rTOCtyLst As Range
Dim iOrigCityNo as long, iEndRow As Long ' not Integer with rows

Why not interger with rows if there's never going to be more than 80 of
them?

 

[Peter T]

Hi Bob,

Is it strictly necessary to qualify the Range with a dot?

Sub test()
Dim ws As Worksheet
Dim r1 As Range, r2 As Range
Set ws = Worksheets("Sheet3")

Worksheets("Sheet1").Activate

With ws
Set r1 = Range(.Cells(1, 1), .Cells(2, 2))
Set r2 = .Range(.Cells(1, 1), .Cells(2, 2))
End With

MsgBox r1.Parent.Name & vbCr & _
r2.Parent.Name

End Sub

Regards,
Peter T

 

[Peter T]

Why not interger with rows if there's never going to be more than 80 of
them?

You didn't say there would never be more than 80 rows, you said you were
having problems and that might have been another reason, apart from the
obvious. If you are 100% certain you will never refer to a row over 32k then
yes you could use Integer. But one day it might bite you.

In 32 bit vba there's no advantage to using an Integer vs a Long.

Regards,
Peter T

 

[Dave Peterson]

Not always. But it doesn't hurt when you do and will save debugging time when
you actually need it.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rTOCtyLst As Range
With Worksheets("sheet2")
Set rTOCtyLst = Range(.Cells(1, 1), .Cells(1, 2))
End With
End Sub

Try putting this code behind Sheet1's module.

The version without the dot (range()) will fail. When you add the dot
(.range(), it'll work ok.

Unqualified ranges in that worksheet module will refer to the worksheet that
owns the code.

In xl2003's help, it says:

When used without an object qualifier, this property is a shortcut for
ActiveSheet.Range (it returns a range from the active sheet; if the active sheet
isn’t a worksheet, the property fails).

But that doesn't look true to me.

I think that excel treats this unqualified Range as application.range.

All these examples have sheet1 the activesheet:

Because this in a general module works fine:

With Worksheets("sheet2")
Set rTOCtyLst = Range(.Cells(1, 1), .Cells(1, 2))
End With

But this fails:

With Worksheets("sheet2")
Set rTOCtyLst = activesheet.Range(.Cells(1, 1), .Cells(1, 2))
End With

But this works ok:

With Worksheets("sheet2")
Set rTOCtyLst = application.Range(.Cells(1, 1), .Cells(1, 2))
End With

=======
And for all the extra time it takes to type that dot, I think it's money well
spent!

 

[Peter T]

Hi Dave,

Good point about use in worksheet module.

And for all the extra time it takes to type that dot, I think it's money well
spent!

My resources can just about stretch to an extra dot, as you say might repay
if ever copying code from a normal module to a worksheet module !!

Regards,
Peter T

 

[Tom Ogilvy]

No, it's not essential, until you get in a sheet module:

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim r1 As Range, r2 As Range
Set ws = Worksheets("Sheet3")

Worksheets("Sheet1").Activate

With ws
Set r1 = Range(.Cells(1, 1), .Cells(2, 2))
Set r2 = .Range(.Cells(1, 1), .Cells(2, 2))
End With

MsgBox r1.Parent.Name & vbCr & _
r2.Parent.Name

End Sub

So for continuity, it would make sense to get in the habit.

 

[Peter T]

Hi Tom,

I guess Dave's comments and my follow-up were not visible when you posted,
but thanks also.

Dave mentioned that if Range is not qualified, either with a dot to some
sheet or by default to the sheet if in a worksheet module, it defaults to
the Application.

So in a sheet module could use like this:

' in Sheet1 module
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim r1 As Range, r2 As Range
Set ws = Worksheets("Sheet3")

Worksheets("Sheet1").Activate

With ws
Set r1 = Application.Range(.Cells(1, 1), .Cells(2, 2))
Set r2 = .Range(.Cells(1, 1), .Cells(2, 2))
End With

MsgBox r1.Parent.Name & vbCr & _
r2.Parent.Name

End Sub

This makes sense. In VB6 both Range & Cells always need to be qualified,
even if working on the active sheet (& even if a ref to Excel has been set
in the vb6 project ref's)

Set xlApp = the current Excel instance
Set ws1 = .ActiveSheet ' ref'd back to wb & app

Set rng = xlApp.Range(ws1.Cells(1, 1), ws1.Cells(2, 2))
or
Set rng = ws1.Range(ws1.Cells(1, 1), ws1.Cells(2, 2))

Regards,
Peter T

 

[Tom Ogilvy]

Why introduce application in the middle of a With statement. While it may
work, it doesn't make sense to me when the simple inclusion of the period
would suffice.

Obviously, if Daves contribution were visible, I wouldn't have bothered.

 

[Peter T]

Why introduce application in the middle of a With statement. While it may
work, it doesn't make sense to me when the simple inclusion of the period
would suffice.

It was highly contrived but based on the previous example to further
re-enforce what you and Dave had noted, particularly with regards to what
Range defaults to can be changed. Whilst very familiar to you perhaps not
to everyone. I wouldn't use that particular scenario.

Obviously, if Daves contribution were visible, I wouldn't have bothered.

I always appreciate anyone having taken the trouble to respond to any
question I have raised.

Regards,
Peter T

 

[Dave Peterson]

And I didn't mean to give the impression that application.range is something I
generally use.

 

[Peter T]

And I didn't mean to give the impression that application.range is
something I generally use.

I didn't take it that way but your comments were interesting nevertheless.

My first reply to Tom probably expanded on the topic beyond the point of
being useful !

Regards,
Peter T

 

[davegb]

You didn't say there would never be more than 80 rows, you said you were
having problems and that might have been another reason, apart from the
obvious. If you are 100% certain you will never refer to a row over 32k then
yes you could use Integer. But one day it might bite you.

In 32 bit vba there's no advantage to using an Integer vs a Long.

Regards,
Peter T

Thanks for the reply! I doubt that my state will ever have 32,000+
counties!
Seriously, does this mean that "integer" as a data type is obsolete? Is
there any use for it anymore?

 

[Peter T]

<snip

Thanks for the reply! I doubt that my state will ever have 32,000+
counties!
Seriously, does this mean that "integer" as a data type is obsolete? Is
there any use for it anymore?

It's a good point and fears have been expressed about the Integer data type
becoming obsolete. Though I doubt it will in the foreseeable future.

My understanding, gleaned from others, is in 32 bit vba Integer types are
internally converted to Long's before any work is done. In other words an
additional process and overhead.

That being the case there seems no point ever to use an Integer, except
perhaps when a particular inbuilt function specifically expects an Integer.
But even these functions still seem to work fine if a Long is received.

Regards,
Peter T

 

[davegb]

<snip

It's a good point and fears have been expressed about the Integer data type
becoming obsolete. Though I doubt it will in the foreseeable future.

My understanding, gleaned from others, is in 32 bit vba Integer types are
internally converted to Long's before any work is done. In other words an
additional process and overhead.

That being the case there seems no point ever to use an Integer, except
perhaps when a particular inbuilt function specifically expects an Integer.
But even these functions still seem to work fine if a Long is received.

Regards,
Peter T

Interesting series of replies to my original message. I have some
beginner questions.
I gather that "qualifying" means to show what object is the parent of
the current object? If I'm working with a range, is it's parent the
activesheet or the application? Is that what it means?
What is the difference between "Range" and ".Range"? I don't understand
why one works and the other doesn't in different situations.

Dave P. wrote:
Try putting this code behind Sheet1's module.

What does this mean? There are other references above to "Sheet
modules" and "regular modules". What are they? What's the difference?
Why would a ".range" be required in one but a "range" be ok in another?

And why does

with activesheet
Set rTOCtyLst = .Range(.Cells(1, 1), .Cells(1, iEndRow))
end with

work better than

set rTOCtyLst = Activesheet.range(.Cells(1,1), .Cells(1, iEndRow))

?

Got lots of questions here, but am trying to figure out if there are
discernable patterns to VBA or if it's just memorizing thousands of
rules that aren't in the books!
I appreciate all the help.

 

[Peter T]

Interesting series of replies to my original message. I have some
beginner questions.

One by one -

I gather that "qualifying" means to show what object is the parent of
the current object? If I'm working with a range, is it's parent the
activesheet or the application? Is that what it means?

Application.Workbooks("myBook").WorkSheets("mySheet").Range("A1")

This tree-like path always exists. Assuming code is in a normal module (see
later), if no reference is made back to parents, VBA works with the
activesheet in the active workbook. So, if you want to work with Range("A1")
on the activesheet, you don't need to reference (qualify) to it's parent
sheet or workbook. Similarly with

Set rng = Range(Cells(1,1),Cells(2,2))

in this line, assuming code is in a normal module, the important implied
reference is to "Cells" in the active sheet. Here, Cells do not and should
not have a preceding dot unless the line is embraced with "With
mySheet...End With". That was the problem in your OP. Why - because the
preceding dot is expected to link to a written reference to the Cells'
parent sheet.

What is the difference between "Range" and ".Range"? I don't understand
why one works and the other doesn't in different situations.

Again, if you include a preceding dot it links to a ref, that you need to
write, to whatever parent you want the range to be "in".
But -
With mySheet
set rng = Range(.Cells(1,1),.Cells(2,2))
End With

Range does not need the preceeding dot as the reference to the parent sheet
is linked with the dots that precede Cells. But I agree with all the
recommendations to include it.

Dave P. wrote:
Try putting this code behind Sheet1's module.

What does this mean? There are other references above to "Sheet
modules" and "regular modules". What are they? What's the difference?
Why would a ".range" be required in one but a "range" be ok in another?

Right-click a sheet-tab, view-code and you wll go straight into a sheet
module. Typical code in a sheet module are sheet events and worksheet
ActiveX control's code. But you can also write your own routines there (but
don't until you understand what you are doing).

Unlike unqualified references in normal modules that default to the
activesheet & workbook, any unqualified code refers to the Worksheet of that
sheet module (whether or not it is active). Therefore if code is not
intended to refer to that sheet you need to explicitly refer whatever other
sheet.

'in a sheet module
With mySheet
set rng = .Range(.Cells(1,1),.Cells(2,2))
End With

Unlike the similar code higher up, the dot preceeding Range is definately
required. Otherwise there is a conflict between Range (referring to the
sheet module) and Cells (referring) to mySheet.

Hope this takes you a bit further,
Peter T

 

[Dave Peterson]

Just this portion:

With mySheet
set rng = Range(.Cells(1,1),.Cells(2,2))
End With

Range does not need the preceeding dot as the reference to the parent sheet
is linked with the dots that precede Cells. But I agree with all the
recommendations to include it.

======
It still depends on where that code is located.

======
And for me, Integer as a variable type is gone.


<<snipped>>