Another invalid or unqualified reference

[Peter T]

"davegb" <davegb@safebrowse.com> wrote in message
news:1124836141.416754.102520@f14g2000cwb.googlegroups.com...
> 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

"davegb" <davegb@safebrowse.com> wrote in message
news:1124836141.416754.102520@f14g2000cwb.googlegroups.com...
> 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.
>

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

[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)


"Peter T" <peter_t@discussions> wrote in message
news:%23FMgURDqFHA.820@TK2MSFTNGP09.phx.gbl...
> 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
>
> "davegb" <davegb@safebrowse.com> wrote in message
> news:1124836141.416754.102520@f14g2000cwb.googlegroups.com...
> > 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.
> >
>
>

[davegb]

Peter T wrote:
> 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

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:#p9aofDqFHA.2628@TK2MSFTNGP10.phx.gbl...
> 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)
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%23FMgURDqFHA.820@TK2MSFTNGP09.phx.gbl...
> > 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
> >
> > "davegb" <davegb@safebrowse.com> wrote in message
> > news:1124836141.416754.102520@f14g2000cwb.googlegroups.com...
> > > 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]

"davegb" <davegb@safebrowse.com> wrote in message
news:1124839642.454267.232320@z14g2000cwz.googlegroups.com...
>
> Peter T wrote:
> > 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?

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 wrote:
>
> 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
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:#p9aofDqFHA.2628@TK2MSFTNGP10.phx.gbl...
> > 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)
> >
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:%23FMgURDqFHA.820@TK2MSFTNGP09.phx.gbl...
> > > 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
> > >
> > > "davegb" <davegb@safebrowse.com> wrote in message
> > > news:1124836141.416754.102520@f14g2000cwb.googlegroups.com...
> > > > 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.
> > > >
> > >
> > >
> >
> >

--

Dave Peterson

[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


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:430BBC1B.B43CE2D2@verizonXSPAM.net...
> 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 wrote:
> >
> > 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
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:#p9aofDqFHA.2628@TK2MSFTNGP10.phx.gbl...
> > > 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)
> > >
> > >
> > > "Peter T" <peter_t@discussions> wrote in message
> > > news:%23FMgURDqFHA.820@TK2MSFTNGP09.phx.gbl...
> > > > 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
> > > >
> > > > "davegb" <davegb@safebrowse.com> wrote in message
> > > > news:1124836141.416754.102520@f14g2000cwb.googlegroups.com...
> > > > > 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.
> > > > >
> > > >
> > > >
> > >
> > >
>
> --
>
> Dave Peterson

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

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions> wrote in message
news:%23GmTgvDqFHA.3752@TK2MSFTNGP10.phx.gbl...
> 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
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:#p9aofDqFHA.2628@TK2MSFTNGP10.phx.gbl...
> > 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)
> >
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:%23FMgURDqFHA.820@TK2MSFTNGP09.phx.gbl...
> > > 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
> > >
> > > "davegb" <davegb@safebrowse.com> wrote in message
> > > news:1124836141.416754.102520@f14g2000cwb.googlegroups.com...
> > > > 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]

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" <twogilvy@msn.com> wrote in message
news:uhSvwNEqFHA.272@TK2MSFTNGP15.phx.gbl...
> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%23GmTgvDqFHA.3752@TK2MSFTNGP10.phx.gbl...
> > 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
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:#p9aofDqFHA.2628@TK2MSFTNGP10.phx.gbl...
> > > 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)
> > >
> > >
> > > "Peter T" <peter_t@discussions> wrote in message
> > > news:%23FMgURDqFHA.820@TK2MSFTNGP09.phx.gbl...
> > > > 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
> > > >
> > > > "davegb" <davegb@safebrowse.com> wrote in message
> > > > news:1124836141.416754.102520@f14g2000cwb.googlegroups.com...
> > > > > 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.
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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



--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions> wrote in message
news:uKG6QeIqFHA.2240@tk2msftngp13.phx.gbl...
> 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" <twogilvy@msn.com> wrote in message
> news:uhSvwNEqFHA.272@TK2MSFTNGP15.phx.gbl...
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:%23GmTgvDqFHA.3752@TK2MSFTNGP10.phx.gbl...
> > > 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
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > news:#p9aofDqFHA.2628@TK2MSFTNGP10.phx.gbl...
> > > > 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)
> > > >
> > > >
> > > > "Peter T" <peter_t@discussions> wrote in message
> > > > news:%23FMgURDqFHA.820@TK2MSFTNGP09.phx.gbl...
> > > > > 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
> > > > >
> > > > > "davegb" <davegb@safebrowse.com> wrote in message
> > > > > news:1124836141.416754.102520@f14g2000cwb.googlegroups.com...
> > > > > > 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]

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
> 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 wrote:
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> > 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

[Peter T]

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> 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]

Peter T wrote:
> "davegb" <davegb@safebrowse.com> wrote in message
> news:1124839642.454267.232320@z14g2000cwz.googlegroups.com...
> >
> > Peter T wrote:
> > > 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?
>
> 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
> > > > It would be a good idea to qualify your variables
> > > > 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?
> >
> > 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?
>

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]

Peter T wrote:
> <snip
> > > > > It would be a good idea to qualify your variables
> > > > > 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?
> > >
> > > 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?
> >
>
> 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>>
--

Dave Peterson

[Peter T]

I had attempted to qualify that portion with -
"Assuming code is in a normal module (see later)" etc

Then later I tried to make the clear distinction that the dot ref is
definitely required in a sheet module, if Range does not pertain to that
sheet.

But it did mean scrolling down to see it <g>

Regards,
Peter T


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:430CE12A.DFEE947A@verizonXSPAM.net...
> 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>>
> --
>
> Dave Peterson

[Dave Peterson]

Hey, how did that part get added after I read it!!!! <bg>.

Sorry about missing your final paragraph.

Peter T wrote:
>
> I had attempted to qualify that portion with -
> "Assuming code is in a normal module (see later)" etc
>
> Then later I tried to make the clear distinction that the dot ref is
> definitely required in a sheet module, if Range does not pertain to that
> sheet.
>
> But it did mean scrolling down to see it <g>
>
> Regards,
> Peter T
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:430CE12A.DFEE947A@verizonXSPAM.net...
> > 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>>
> > --
> >
> > Dave Peterson

--

Dave Peterson

[davegb]

Peter T wrote:
> > 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

Thanks to everyone for your replies. It's definitely helping me to
figure out VBA. Thanks, Peter, for you detailed explanations of the
why's and how's.
One more question, for now. Are ActiveX controls for controlling other
apps?

[Peter T]

"davegb" <davegb@safebrowse.com> wrote in message
<<snip>>

> Thanks to everyone for your replies. It's definitely helping me to
> figure out VBA. Thanks, Peter, for you detailed explanations of the
> why's and how's.
> One more question, for now. Are ActiveX controls for controlling other
> apps?

Worksheet ActiveX controls are applied to a sheet from the Controls Toolbox
menu (Main menu, View, Toolbars). They respond to "Events" such as Click,
from these you can do or control what you want. The event code is in the
sheet module. Drag a button onto the sheet, while still in Design mode*
select View code* (* icons on the menu).

There are also controls from the "Forms" menu which don't respond to events,
other than OnAction when assigned to a macro.

There is often confusion between the two types. If you can't find what you
need to know about these by searching the groups or in help, it would be
better to start a new topic. In .Misc for general info and here for
specifics about programming.

There are of course 000's of other ActiveX controls, it'd be nice to find
one that makes a decent cup of coffee.

Regards,
Peter T

[davegb]

Peter T wrote:
> "davegb" <davegb@safebrowse.com> wrote in message
> <<snip>>
>
> > Thanks to everyone for your replies. It's definitely helping me to
> > figure out VBA. Thanks, Peter, for you detailed explanations of the
> > why's and how's.
> > One more question, for now. Are ActiveX controls for controlling other
> > apps?
>
> Worksheet ActiveX controls are applied to a sheet from the Controls Toolbox
> menu (Main menu, View, Toolbars). They respond to "Events" such as Click,
> from these you can do or control what you want. The event code is in the
> sheet module. Drag a button onto the sheet, while still in Design mode*
> select View code* (* icons on the menu).
>
> There are also controls from the "Forms" menu which don't respond to events,
> other than OnAction when assigned to a macro.
>
> There is often confusion between the two types. If you can't find what you
> need to know about these by searching the groups or in help, it would be
> better to start a new topic. In .Misc for general info and here for
> specifics about programming.
>
> There are of course 000's of other ActiveX controls, it'd be nice to find
> one that makes a decent cup of coffee.
>
> Regards,
> Peter T

Thanks again. As for the cup of coffee, I'm allergic to the stuff. But
a good marguerita...