[go: up one dir, main page]
Scribd
Upload
35 views26 pages

Excel Macros 2 Upload 22

Uploaded by

h3034
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
35 views26 pages

Excel Macros 2 Upload 22

Uploaded by

h3034
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 26

Excel Macros

LE V E L 1

Jose K Puthur
An Introduction to VBA
- What is VBA
▪An abbreviation for Visual Basic for Applications
▪Official name is "Visual Basic, Applications Edition."
▪VBA is Microsoft's common application programming (macro) language for Word,
Excel, Access, etc.
▪VBA and VB have a lot in common, but they are different. VB is a programming
language that lets you create standalone executable programs.
▪VBA is a part of Excel
▪VBA can not work with out excel
▪Visual Basic (VB) is a stand alone program that runs independently
▪VBS is a variant of Visual basic Language used for INTERNET based applications.
Macros
Macros are set of codes
Set of instruction
Macros are written in VBA (Visual Basic for Application)
To save time
It is for repeat task or task are done again and again
Write your own function
Or formula
Subroutine
Procedure
program
object
Object oriented program
Every thing is object
Work book sheet chart
Excel
Work book
sheet
An Introduction to VBA
- What Can You Do With VBA

▪Write Macros to Automate Labor-Intensive and Repetitive Tasks

▪Create User-Defined Functions to Achieve Complicated Functionality

▪Create Standard Windows Menu/Tool Bars for Interface

▪Interact with Other Windows Programs (like Matlab)

▪I/O with External Files

▪Database Operation ….
An Introduction to VBA
- VBA- Object Based Programming Language

▪C++, Java, etc. are OOP (Object Oriented Programming) Language

▪VBA is an Object Based Programming Language

▪What is Object?
An Introduction to VBA
- VBA Object Based Programming Language
 Concepts – Containers or Collections
◼ A Group of Similar Objects Share Common Properties, Methods and Events
◼ Such as Workbooks, Worksheets, etc.
◼ Worksheets is a collection of all the Worksheet objects in the specified or
active workbook.
◼ Worksheets(1) refers to the 1st worksheet of current active workbook.
◼ Worksheets (“inflore”) refers to the worksheet named “inflore”.
An Introduction to VBA
- VBA Object Based Programming Language

 Concepts – Objects
◼ The term Excel Objects (collectively referred to as the Excel
Object Model) refers to the entities that make up an Excel
workbook, such as Worksheets, Rows, Columns, Cell
Ranges, and the Excel Workbook itself.
◼ Such as Workbook, Worksheet, Range, Cell, Chart, Name,
etc.
◼ Worksheets(1) is an Object Referring to the First Sheet
◼ Range("A1:B15") is an Object Referring to a Range
◼ Cells(1,1) or Range(“A1”) is an Object Referring to Range
“A1”
To record a macro
An Introduction to VBA
- VBA Object Based Programming Language

 Concepts – Properties
◼ Properties are the Physical Characteristics of Objects
and Can be Measured or Quantified.
◼ Properties for Collections
◼ - Worksheets.Count (Read Only)
◼ - Worksheets.Visible = True (Read and Write)
◼ Properties for Object
◼ - Range("A1:B15").Rows.Count (Read Only)
◼ - Range("A1:B15").Font.Bold = True (Read and Write)
An Introduction to VBA
- VBA Object Based Programming Language

 Concepts – Methods
◼ Methods are the Actions that Can be Performed by
Objects or on Objects
◼ Methods for Collections
◼ - Worksheets.Add
◼ - Worksheets.Delete
◼ Methods for Objects
◼ - Range("A1:B15").ClearContents
◼ - ActiveCell.Copy
An Introduction to VBA
- VBA Object Based Programming Language

 Concepts – Events
◼ Objects Can Respond to Events, Such as Mouse Click,
Double Click on a Cell, Active a Worksheet,
Open/Close/Save a Workbook, etc.
◼ Worksheet Events –
 Such as Activate, Deactivate, Change, Selection
Change, Before Double Click, etc.
◼ Workbook Events-
 Such as Activate, Deactivate, Open, Before Close,
Before Saving, Before Print, New Sheet.
An Introduction to VBA
- VBA Object Based Programming Language

 Concepts – Referring To
◼ Use brackets () to refer to member object
 Worksheets(“inflore”)
◼ Use dot . to refer to child object or object’s
properties and methods
 Worksheets(“inflore”).Range(“A1:B3”).Font.Bold
An Introduction to VBA
- VBA Object Based Programming Language

 Concept Collections
(worksheets)
Summary

Objects
(worksheet)

Properties Method
name Add

Event
Activate
Variables
▪Variables are programmer-defined names.
▪Variables can store values that can change while the application is
running.
▪Variables store values in computer memory.
▪A program code statement can change the value at any time.
▪A value for a variable exists only while a program is running.
Constants

▪Constants store values that are specified at design time.


▪ Constants remain the same while the application is running.
▪Programmers can change a constant’s value at design time.
▪Types of constants include:
▪ Intrinsic—specific to an application
▪ User-defined—created by a programmer
▪ Conditional compiler—defined in the host application
Data Type
▪Computer cannot differentiate between the numbers (1,2,3..) and strings (a,b,c,..). To make this differentiation,
we use Data Types. VBA data types can be segregated into two types
▪ Numeric Data Types
▪ Non-numeric Data Types

Numeric Data Types


Data Types
String Data Types
Decision Making
▪Decision making allows the programmers to
control the execution flow of a script or one of
its sections. The execution is governed by one or
more conditional statements.
▪Following is the general form of a typical
decision making structure found in most of the
programming languages.
VBA Decision making Structures
Loops
▪There may be a situation when you need to
execute a block of code several number of times.
In general, statements are executed sequentially:
The first statement in a function is executed first,
followed by the second, and so on.
▪Programming languages provide various control
structures that allow for more complicated
execution paths.
▪A loop statement allows us to execute a statement
or group of statements multiple times. Following is
the general form of a loop statement in VBA.
Loops in VBA
Loop Control Statements
▪Loop control statements change execution from its normal sequence. When
execution leaves a scope, all the remaining statements in the loop are NOT
executed.
▪VBA supports the following control statements. Click the following links to check
their detail.
Variable Declaration
Dim num As Integer
num = 1234

Dim password As String


password = "Admin#1"

You might also like