Very Simple Code Hanging????

[Frustrated]

Sub Sort().......
Dim rRge4 As Range
Dim rRge5 As Range
Dim rRge6 As Range

Set rRge4 = Range("A4:EE500")
Set rRge5 = Range("B7:K46")
Set rRge6 = Range("A1:K47")

Sheets("Data").Select
ActiveSheet.Unprotect
rRge4.Select
Selection.Sort Key1:=Range("Ba4"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("A4").Select
ActiveSheet.Protect

End Sub

I didn't post the entire code but this is the very first
of it and it hangs on the step "rRge4.Select" Can someone
point out why??

Can provide more info if necessary.

TIA

 

[Vasant Nanavati]

Try:

Set rRge4 = Sheets("Data").Range("A4:EE500")

etc.

 

[Frustrated....maybe not..:\)]

Thanks Vasant......trying that now, will post the results.

Guess I'll find out the answer to this as I play with your
solution, but is it proper to list all the "Dim" and "Set"
statements at the first of the module, as I show above,
even though they relate to different sheets, and in one
case a different workbook (created by the macro)? Or,
shoould I insert those "Dim" and "Set" statements after
those sheets are selected?

I've been reading here for a few months and am slowly
catching on, but starting from ground zero it's a slow
process. Thanks for all the help you and others on here
have been to a complete novice.

Don

 

[Harlan Grove]

Guess I'll find out the answer to this as I play with your
solution, but is it proper to list all the "Dim" and "Set"
statements at the first of the module, as I show above,
even though they relate to different sheets, and in one
case a different workbook (created by the macro)? Or,
shoould I insert those "Dim" and "Set" statements after
those sheets are selected?

....

Dim and Set statements are fundamentally different. Dim statements are only
compiled, not run. They declare and allocate memory for variables. Set
statements assign object references to object type variables, and they
execute as the procedure runs.

Placement of Dim statements is a matter of taste similar to how you should
name variables. All at the beginning is similar to languages like Pascal and
C which (historically if not currently) required all variable declarations
at the beginning of procedures. Interspersed, declared when needed is a more
modern approach that has its advantages and drawbacks (the drawbacks would
be greater in single pass compiled languages like VBA, but not killer unless
you have dozens of separate declarations, which would be an indicator your
procedure was too large).

Placement of Set statement is more a matter of engineering. They should
appear near where the object variables assigned are first used.

 

[Don]

Thanks for the input Harlan.

I've been experimenting with this a bit this morning and
think I now have a handle on it.....at least until the
next error occurs...... A long time ago (actually a
very long time ago), I had one quarter of Fortran (card-
reader type) and this is so much more user friendly. But,
if nothing else, that did teach me the benefits of
organization when writing code.

What I've found is that using Vasants suggested code, all
the Dim and Set codes can be placed right at the first
part of the macro, as I did. I sort of like this as it
seems, for me at least, much easier to keep track of them
all. I also read recommendations in several places to
use "Option Explicit", which I've now made automatic in
the tools/option/editor window.

I really do appreciate all the help I've got on here from
you and all the others. Some really good people on here,
lots of knowledge and very friendly.

Thank you all again very much,

Don