Short Code Versions

[dcwinter]

HI,

I thought I'd start a thread to help people with shorter versions of code. Don't know how this will work, but I know that I as well as many others would like to see how we can write our code in a shorter and easier format.

So, for example, what are your top code simplifying tips, and most importantly for us beginers, why? How does it work? What does it mean?

Looking forward to some examples....

A good start might be how you condense something like this:

Range("D4:D14").Select
    Selection.ClearContents

****JUST TO CLARIFY, THIS IS SUPPOSED TO BE A DISCUSSION, NOT A TECHNICAL QUERY ON MY PART****

 

[SydneyGeek]

One pointer is that you don't need to select something to operate on it.

So,

Range("D4:D14").Select
Selection.ClearContents

can be shortened to

Range("D4:D14").ClearContents

... and that approach can be used pretty much wherever the recorder has Select followed by Selection.

Of course, every rule has an exception. If you do need to switch to another worksheet and then select a range, a one-liner will generally fail:

Sheets("Sheet3").Range("C4").Select

Instead use:

Sheets("Sheet3").Activate
Range("C4").Select

Similar when switching drives and directories.

Something else that can save heaps of time is to use the code equivalents of Edit>GoTo>Special (the SpecialCells method) and Paste Special (the PasteSpecial method).

Just for starters...

Denis

 

[RoryA]

Not sure if this is the sort of thing you had in mind but:
1. Always use Option Explicit at the top of each module. (under Tools-Options in the VBEditor, on the editor tab, check the 'Require variable declaration' option and this will add Option Explicit to any new modules from that point on)
Reason: it will help you to avoid errors due to typos in your code, which are extremely common, by requiring you to have a declaration (Dim statement) for any variable used in your code.
2. If you repeatedly refer to anything in code, you should probably be using a variable. That way, if your code needs to be altered, you only alter the variable in one place, not several. For example:

Sheets("Sheet1").Range("A1").Value = "test"
Sheets("Sheet1").Range("A2").Value = "test"
Sheets("Sheet1").Range("A3").Value = "test"
Sheets("Sheet1").Range("A4").Value = "test"
Sheets("Sheet1").Range("A5").Value = "test"

If your sheet name gets changed, you have to change every instance of the name in that code. (and of course the references might be scattered throughout your code). If you use a variable:

Dim wks as Worksheet
Set wks = Sheets("Sheet1")
wks.Range("A1").Value = "test"
wks.Range("A2").Value = "test"
wks.Range("A3").Value = "test"
wks.Range("A4").Value = "test"
wks.Range("A5").Value = "test"

Now you would only have to change one line if the sheet name changes.

3. You will also note there are repeated references to the same object (the worksheet) in that code - this can be simplified with a With...End With block:

Dim wks as Worksheet
Set wks = Sheets("Sheet1")
With wks
   .Range("A1").Value = "test"
   .Range("A2").Value = "test"
   .Range("A3").Value = "test"
    .Range("A4").Value = "test"
    .Range("A5").Value = "test"
End With

 

[Norie]

If you want to put the same value in contiguous cells you can just use something like this.

Worksheets("Sheet1").Range("A1:A5") = "Same Value"

I would totally agree with Denis, there is hardly ever, if ever, the need to use Select.

But there is also normally no need to use Activate either.

If you want to move to a cell on another sheet use Application.Goto.

Application.Goto Sheets("Sheet3").Range("C4"). Scroll:=True

 

[dcwinter]

WOW!

I'm really pleased with how this is going, and I'm sure that I can improve my code using these comments!

Thanks everyone...

Lets keep going, and if there are any people learning about code, you could always post some code to see if there are any suggestions as to how it could be improved....