VBA HOME PAGE

Menu

Recording macros

Looking at the code

Ways of running macros

Where macros are stored

Reasons to write macros

Writing macros

Procedure types

Visual Basic editor (VBE)

Rules & conventions

Excel objects

Range/Selection objects

Object hierarchy

Object browser

Chart objects

Pivot Table objects

Formulas

Visual Basic Functions

Creating Add-Ins

Variables & constants

Object variables

Arrays

Collections

Message Box

VBA Input Box

Excel Input Box

Making decisions (If)

Making decisions (Case)

Looping (Do...Loop)

Looping (For...Loop)

With...End With blocks

User defined functions

Event handling

Error handling

Debugging

Creating User Forms

DAO/ADO Objects

Input/Output Files

 

Other links

Example code snippets

Userform input example

 

 

 

 

 


With...End With Blocks

The With…End With block instruction enables you to perform multiple operations on a single object.  This is another way to make the code execute more quickly and code styles more efficient.

 

The following procedure will format the selected cells with the font ‘Times New Roman’, font size 12, Bold, Italic and the colour Blue.

 

Sub ChangeFont()
    Selection.Font.Name = "Times New Roman"
    Selection.Font.Size = 12
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.ColorIndex = 5
End Sub

The above procedure can be rewritten using a With…End With block as follows:

Sub ChangeFont()
    With Selection.Font
        .Name = "Times New Roman"
        .Size = 12
        .Bold = True
        .Italic = True
        .ColorIndex = 5
    End With
End Sub

 

Using the this type of block, your code is cleaner and easier to maintain. The With...End With block encapsulates the object and member without the need to repeat unnecessary (duplicate) code.

 

If fact, when you record a macro and you navigate through the dialog boxes making various changes before choosing the OK button you in fact capture the code using the above structure. Try the Font Dialog box whilst recording the macro.

 

 


Next Topic: User Defined Functions (UDF's)

 

Want to teach yourself Access? Free online guide at About Access Databases

VBA Keywords: Selection, Name, Font, Bold, Italic, ColorIndex and Size..
Home | Terms of Use | Privacy Policy | Contact

© copyright 2010 TP Development & Consultancy Ltd, All Rights Reserved.

All trademarks are copyrighted by their respective owners. Please read our terms of use and privacy policy.