Determined to eliminate .select

  • Thread starter Thread starter Bruce Roberson
  • Start date Start date
B

Bruce Roberson

When Bob and others say "Rarely if ever" should I use
select, then I am determined to eliminate it somehow.

I have tried EVERWAY I know in this situation and I just
can't get it.

Ok, the situation is this:

I'm in another worksheet, and I need to do this code:

Sub Upload_MJE_to_As400()
Range("Start").Select
Range(ActiveCell.End(xlDown),
ActiveCell.End(xlToRight)).Select

How do I get there and eliminate this select line in this
case when I'm somewhere else?

Bruce
 
Bruce,

Try something like

Dim Rng As Range
Set Rng = Range("Start")(1,1)
Range(Rng.End(xlDown),Rng.End(xlToRight)).Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Hey Chip:

That worked, now help me understand the (1,1) part? Is
that the same as cells(1,1) except that you don't use
Cells since you're working with an object? Probably a dumb
question, but I'm still not comfortable with working with
objects and their syntax yet.

And I had misstated when I put
Range(ActiveCell.end(Xldown),
activecell.end(xltoright)).select

I changed that to ".copy" which is what I meant and that
worked fine that way.

So, selects are gone from that sub now. I only have 50,000
more to do away with that I have used while I've been
learning VBA.

Now, help me take this object to a different example, ok?

Sub CurrValues()
Sheets("MJE").Select
Range("Start").offset(2,.).Select
Selection.CurrentRegion.Clear

I tried the following replacement for the selects, but I
got a run time error 1004, application-defined or object
defined error.

The reason I keep selecting the sheet first and then the
range is because it doesn't always work unless I do it
that way. I think it has something to do with what sheet
is active at the time I run this sub. But it drives me
crazy when I see those error messages like that.

With Select:
Sub CurrValues()
Sheets("MJE").Select
Range("Start").Offset(2, 0).Select
Selection.CurrentRegion.Clear

Without Select:
Sub CurrValues()
Dim Rng as Range
Set Rng = Sheets("MJE").Range("MJE")(3,1)
Range(rng).Selection.CurrentRegion.Clear
 
Alan, I tried testing your solution below with .select
instead of .clear, just to make sure it worked if the
worksheet mje is not the active sheet, and at first, that
bombed with the error message 1004.

Then, just for kicks, I changed the .select back to .clear
just to make sure it didn't work that way.

That evil .select really does cause those run time 1004
errors, because lo and behold, it worked with .clear when
I was in a sheet other than mje and then executed the sub.

Sometimes I just need a way to test my subs with .select
first before I do something like clear something I didn't
intend to clear. Is there a suggestion on how to test
something you're not sure of in this case, before you run
the risk of wiping something out you didn't intend to wipe
out? I guess I could just save the workbook before trying
something I'm not sure of. But then I'd be saving the
workbook pretty darned often <LOL>

Thanks,


Bruce
 
Bruce Roberson wrote:

<I guess I could just save the workbook before trying
something I'm not sure of. But then I'd be saving the
workbook pretty darned often <LOL> . . . .[snip]
Click to expand...

As a matter of fact, I save my test workbooks pretty darned often! It's
just a couple of clicks.

Alan Beban
 
If the variable is only going to be referred to once, I would usually
recommend against it. You have to code in the range once in order to
assign it to the variable--might as well let that once be the time it's
going to be used.

Alan Beban
 
Bruce...

I see so many of your questions here recently

They suggest to me that you've recently started with VBA
Fine.. we all had to start someday.

But would you go swim in a lake, if you didn't know how to swim?
Then.. on the point of drowning.. You ask the lifeguard to please
explain what a backstroke is?

We'll keep helping you. That's not the issue here. Finally you'll learn
how to swim. But YOU will get frustrated by this trial and error
method, cause you lack an understanding of some BASICS.


Step back from what your currently trying to do ....
Close the workbook. Forget it for 1 day.


Buy a book on VBA (preferably for Excel)

Read the first few chapters...
Really you'll spend maybe 1/2 day
including your trip to the bookstore:)

Then do a few exercises from the book.
and take time to understand what your doing.
That'll take another 1/2 day.

Then go back to what you're working on now.


I promise you that day reading and learning will be a day well spent


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
I see so many of your questions here recently
They suggest to me that you've recently started with VBA
Fine.. we all had to start someday.
Click to expand...


Not sure I should touch this post, however....

Yes, I have recently started working with VBA. I'm about 6 or 7 weeks into
Excel and VBA transferring work files from Quattro Pro for Windows. And yes
I've worked through a VBA book: Step By Step, Microsoft Excel 97 Visual
Basic by Reed Jacobson.

I didn't learn near as much from that book as what I've been able to learn
by converting my own QPW spreadsheets over to Excel 2000. Some of my
spreadsheets in QPW are very involved, and yea, some of the things I am
trying to do now may be more of an intermediate to advanced stage of VBA.

But again, I do believe I'm learning quickly from the information I've
received from the responses to the posts I have been doing. Many many people
here have taken the time and have been patient with me in their responses
even when I didn't quite get it the first time around.

But would you go swim in a lake, if you didn't know how to swim?
Then.. on the point of drowning.. You ask the lifeguard to please
explain what a backstroke is?

We'll keep helping you. That's not the issue here. Finally you'll learn
how to swim. But YOU will get frustrated by this trial and error
method, cause you lack an understanding of some BASICS.
Click to expand...
Not quiet sure how to take this. Yea sure I get frustrated with myself
because I expect of myself to become the leader in Excel that I have been
for years in my company with Quattro Pro. But it certainly isn't all trial
and error on here.
I try new things and then if I can't figure them out of my own, I post for
help, and I usually get a timely response.
I am reusing the examples I pick up on here. Only in the past day or so have
I intensified my efforts towards not just functional coding that gets the
tasks done, but to strive to learn the most efficient means of coding... ie,
not using .select as much in my coding. I'm still trying to grasp how to use
objects as I'm sure you can see.

I don't think I'm in any danger or drowning at all. I would of been perhaps
if my only source had of been the books you think I should read before
continuing on.

Step back from what your currently trying to do ....
Close the workbook. Forget it for 1 day.
Click to expand...

I'm almost done with my most challenging workbook in Excel, and so no way am
I going to slack off now.

I suppose if you get tired of reading my posts, then you have the option of
not reading or responding to them...


Like I said, maybe I should of just ignored this post, and maybe it wasn't
intended to be caddy in any way... But at first glance, I kind of took it
that way.

Bruce
 
Bruce Roberson wrote:>
. . . [snip]
Not sure I should touch this post, however....[snip]
Click to expand...

That was probably the better choice. But for what it's worth, I
wouldn't internalize that kind of negative criticism until I saw it
coming from more than one source. Until then, you can't really tell
whether the comments are more about you or more about the commentor.

Alan Beban
 
What he was saying is that you are missing some of the basic concepts and
this is causing many of your problems.

For instance, your post on Clear versus select.

A basic fact is you can't select a range on a sheet unless it is the active
sheet. It would be hard to recognize this is the problem. Another problem
is that when code is placed in a sheet module, unqualified references refer
to the sheet associated with the sheet module, not the active sheet. The
same code in a general module would refer to the active sheet.

Assume Sheet2 is active and you have code in the Sheet2 module

Worksheets("Sheet1").Activate
Range("A1").Select

A1 on sheet2 is attempted to be selected - although the intent was probably
to select A1 on Sheet1. Since Sheet2 is not the active sheet, an error
would be raised. If the same code is put in a general module, A1 on Sheet1
*is* selected.

These type anomolies are difficult to pick up through experimentation where
a book might cover them.

I believe that is the type of thought KeepitCool was trying to convey. Not
criticizing, but saying that a good book that spells out some of the above
type basic concepts might be a multiplier in terms of productivity and
growth in ability.

---

If you wanted to test a range before you take action on that range (rather
than clear, see if the reference is what you want), then use address

Sub CurrValues()
msgbox
Sheets("MJE").Range("Start")(3,1).CurrentRegion.Address(0,0,xlA1,True)

or

Sub CurrValues()
debug.print
Sheets("MJE").Range("Start")(3,1).CurrentRegion.Address(0,0,xlA1,True)

--
Regards,
Tom Ogilvy
 
Keep:

You're right on that I haven't made the step from scripting to programming.
When you consider my background in 1-2-3 and QPW it is easy to see why that
would be true, macros in 1-2-3 and QPW at least in early versions where I
began are more scripting than programming. Then, if you couple that with the
fact of my work experience being an accounting person rather than a
programmer, you can see at least in part why I am the way I am.

I continually have to remind myself about Excel in general, to quit thinking
about the methods I used in QPW and just look to solve the problem in Excel,
however it needs to be done.

I am intent on identifying those weaknesses I have with regard to learning
to be an expert in VBA. And, then I am intent on fixing those weaknesses,
because I think that this stuff is cool when I get the results I want. I get
excited when I figure out stuff in spreadsheets to the point I can't sleep
at night thinking what I'm going to do next.

Now that I know more about who you are and that you didn't mean anything
"harsh" I can more readily accept what you have to say.
Maybe Bruce skipped the theory and went straight to the examples. That's
not so strange... he needs HIS workbook translated from QPW to Excel and
he wants results.. NOW!
Click to expand...

Good observation again I think. Of course some people learn better by doing
than by reading and studying. I am more of a visual learner than a book
learner. Probably explains why I made so many C's in college , because I
wanted results and not theory. <LOL>

Once I'm done with this last major workbook and then once I've gone back and
tried to clean up some of the "functional" code of the first few weeks, then
I'll try and look at others posts on here. I think Bob mentioned on here
that the point of all this was to get each one to where they could start
being a helper of others rather than just coming here for their own
solutions (not his exact words, but the gist I got out of it).

Later,


Bruce
 
Back
Top