Scribd Logo
Upload

Welcome to Scribd!

  • 27 views

    VBA (Visual Basic For Applications)

    Uploaded by

    RF SOFT
    This document provides an overview of Visual Basic for Applications (VBA) and how to write macros in VBA. It discusses the Visual Basic Editor interface, recording macros, declaring variables, working with objects, properties and methods. Key points covered include using the range and cells objects to select cells, writing sub routines for macros, and defining variable types like integer, long, single and double. The document includes examples of VBA code.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    VBA (Visual Basic For Applications)

    Uploaded by

    RF SOFT
    27 views14 pages
    This document provides an overview of Visual Basic for Applications (VBA) and how to write macros in VBA. It discusses the Visual Basic Editor interface, recording macros, declaring variables, working with objects, properties and methods. Key points covered include using the range and cells objects to select cells, writing sub routines for macros, and defining variable types like integer, long, single and double. The document includes examples of VBA code.

    Original Title

    3.1 Building Blocks.pptx

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides an overview of Visual Basic for Applications (VBA) and how to write macros in VBA. It discusses the Visual Basic Editor interface, recording macros, declaring variables, working with objects, properties and methods. Key points covered include using the range and cells objects to select cells, writing sub routines for macros, and defining variable types like integer, long, single and double. The document includes examples of VBA code.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    27 views14 pages

    VBA (Visual Basic For Applications)

    Uploaded by

    RF SOFT
    This document provides an overview of Visual Basic for Applications (VBA) and how to write macros in VBA. It discusses the Visual Basic Editor interface, recording macros, declaring variables, working with objects, properties and methods. Key points covered include using the range and cells objects to select cells, writing sub routines for macros, and defining variable types like integer, long, single and double. The document includes examples of VBA code.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    of 14

    VBA

    (Visual Basic for Applications)


    Session 1
    Visual Basic Editor
    Record a Macro
    Write a VBA Macro
    Declaring a Variable
    Visual Basic Editor
    Go to ‘Visual Basic’ in ‘Developer’ tab

    When you click on it, a new Window will


    open which is the Visual Basic Editor
    Visual Basic Editor Components
    Menu and Tool Bar
    These bars consists of various commands that you can use to do things in VBA.

    Project Window/Project Explorer


    The top right section gives details like which all files are open and within those files how many sheets are present.
    For each sheet, workbook, you have different code modules. It is beneficial to write programs in Modules rather
    than individual sheets as long as the macros are not just for that particular sheet - modules can be exported
    imported to other workbooks; deleting a sheet deletes the corresponding macros.
    Write macros in Sheet modules or ‘ThisWorkbook’ in cases of event handler codes which we will study later.
    Recording a macro
    Refer cell(s)
    Using Range
    Examples:
    Range("A1:B6")
    Range("A1:B6,D5:M8")
    Range("A:E"), for entire columns
    Range("1:3"), for entire rows

    Using Cells - Cells(rownumber, columnnumber)


    Examples:
    Cells(2,4) is same as Range(“D2”)

    Ranges and Cells can also be used together


    Example:
    Range(Cells(1,1), Cells(4,4)) is same as Range(“A1:D4")
    Selecting cell(s)

    Selecting one cell:


    Range(“cellname”).Select

    Selecting multiple cells:


    Range(“cellname1:cellname2”).Select

    If the row number is dynamic:


    Range(“columnname” & rownumber).Select

    If the column number is dynamic:


    Range(columnname &“rownumber”).Select
    Procedures/VBA Macros/Sub Routines
    Sub keyword is used to start the macro program and this keyword is followed by the name of the
    macro. In the parenthesis following macro name, a list of parameters can be supplied and if there are
    no parameters to be passed, it is kept blank.

    The statements that should be executed by the macro follow the sub declaration and the macro ends
    with an End Sub statement/keyword.

    The following shows the declaration of a macro:

    Sub MacroName()
    [Statements]
    End Sub
    Objects, Properties & Methods
    VBA is an Object Oriented Language – combination of different projects like Workbook, Worksheet,
    Ranges, Charts, Pivots etc.

    Object Model
    Whenever you use Objects – you need to do so in an orderly manner.
    For ex: If there is no workbook, a worksheet cannot exist, similarly, if there is no worksheet, a pivot
    table or a graph cannot exist.

    Workbook
    Worksheet Object Model
    Ranges/Pivots/Charts

    A group of similar objects is called Collection. For ex: Collection of all workbook objects is referred
    to as Workbook collections – similarly collection of all worksheet collections is referred to as
    Worksheet collection
    Objects, Properties & Methods contd..
    All Objects have properties – a property can be thought of as a setting or an attribute. For ex:
    Chart object has properties like ChartTitle, ChartType, Legend

    Similarly, Range has properties like Value, Count –

    Value Property – Using this property, we can set a value of a particular cell or can read the value
    from a particular cell to a new variable

    Examples:
    New1 = Application.Workbooks("VBA File.xlsm").Worksheets("Sheet1"). Range("A1").Value
    Worksheets("Sheet1").Range("A2").Value = New2

    Count property: The number of cells in a range can be counted as


    Examples:
    Range(“A1:A10").Count,
    Range(“A1:A100").Rows.Count
    Range(“A1:D100").Columns.Count
    Objects, Properties & Methods contd..
    Just like all Objects have properties, they have Methods as well – a method is an action that is
    performed using an object.

    For ex: for the Range object we have Methods like ClearContents, Delete

    For Charts we have Methods like Activate, Select, BeforeDoubleClick etc..


    Declare a Variable
    Variables are declared using ‘Dim’ –
    Dim Population As Long
    Dim My_Name As String
    Dim Age As Integer

    ‘Option Explicit’ should be used as it forces the user to declare all the variables beforehand
    Rules –
    Should not exceed 40 characters
    No space
    No special characters

    Advantages –
    Good Programing Standards
    Removes chances of unexpected results by letting VBA know what a variable type is
    Makes code more readable for other users
    Type Range of Values
    Integer -32,768 to 32,767
    Long -2,147,483,648 to 2,147,483,648
    Single -3.402823E+38 to -1.401298E-45 for negative values
    1.401298E-45 to 3.402823E+38 for positive values
    Double -1.79769313486232e+308 to -4.94065645841247E-324 for negative values
    4.94065645841247E-324 to 1.79769313486232e+308 for positive values
    Currency -922,337,203,685,477.5808 to 922,337,203,685,477.5807
    Decimal +/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use
    +/- 7.9228162514264337593543950335 (28 decimal places)
    Byte 0 to 255
    String 1 to 65,400 characters
    Date January 1, 100 to December 31, 9999
    Boolean True or False
    Some codes attached

    Codes for Building Blocks session.txt

    You might also like