Another invalid or unqualified reference

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

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

Sorry about missing your final paragraph.

 

[davegb]

One by one -


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.


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.


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]

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]

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