Excel Made Easy

Vba with keyword in Excel

There are some occassions while programming, where one would want to refer an object repeatedly for some purpose.

For example, if there is a need to change the font,color, backcolor and so on for a range object, then the same object has to be repeated for every property setting.

But with "With..End With" statement, the object is referred only once at the beginning and the rest of the code is automatically made to refer that object only.

This feature comes handy while coding with multiple objects, wihout which, the coding will be tedious.

Also note that, When using a structure, you can only read the values of members or invoke methods, and you get an error if you try to assign values to members of a structure used in a "With End with" block.

Syntax

  1. With
  3. End With

    4. The following example demonstrates the use of with statement.

    1. Sub withendeith()
    2. Dim rng As Range
    3. Set rng = Sheets("Q238").Range("K12:K22")
    4. With rng
    5. .Interior.ColorIndex = 37
    6. End With
    7. MsgBox "K12:K22 background changed with \'With\' Statement"
    8. rng.Select
    9. With Selection.Interior
    10. .Pattern = xlSolid
    11. .PatternColorIndex = xlAutomatic
    12. .ThemeColor = xlThemeColorDark1
    13. .TintAndShade = 0
    14. .PatternTintAndShade = 0
    15. End With
    16. End Sub

    The above example changes the background color, theme color, patterncolorindex,tint and shade and so on of the range K12:K22.

    Note the preceeding .(dot) within the "With" block. Without this, the rng object has to be called at each statement.

    The Screenshot of the editor is as shown below

    excel vba with keyword

     

    You can find similar Excel Questions and Answer hereunder

    1) How can I clear cell after activating a routine when there is a change in value of a cell?

    2) Line break in vba message box in Excel

    3) Excel 2010 vba replacement of application filesearch in Excel

    4) The scenario manager in Excel VBA allows to explore various scenarios in a very easy way

    5) How to add a link in a sheet to another sheet

    6) Here some explanations about the MSXML who stands for Microsoft XML core services

    7) How can I get input from user through a prompt and assign user's input to a cell?

    8) How to read a value from a cell in vba in Excel

    9) How can I export a chart as a gif file?

    10) How can I list all files in a folder using VBA?

     

    Here the previous and next chapter

    Excel Made Easy was founded in 2002. Excel Made Easy is a service to people looking to learn Microsoft Excel or any other spreadsheet SW on the market.

    We endeavor to provide the best service and support and to respond quickly to your questions.

    Excel Made Easy is a proud sponsor of Dismantle-It.com the site that shows you the belly of your daily objects, Poupounette.com the site of cars loving cat and PowerPointMadeEasy.com the world top reference for PowerPoint Presentations questions and Pet Cemetery Online a site dedicated to your loving Pets.