Transcript Visual Basic for Applications

Visual Basic for Applications
• Introduction
• The Control Toolbox
View►Toolboxs► Control Toolbox
• Command Buttons
View►Toolboxs► Command Buttons
Visual Basic for Applications
• Visual Basic Editor (VBE)
Menu Bar
Toolbar
Project explorer window
Code window
Properties window
• A Simple Program
Loan Amortization Table Using PMT Function (Lecture 8)
Create your first Macro
Visual Basic for Applications
•
Running a Program
–
–
–
–
–
–
Press the shortcut key, if there is one.
Select Tools►Macro►Macros and click the name of
macros in the macro dialogue box.
Press Alt+F8 and click the name of macros in the macro
dialogue box.
Click the Run button, ►, press [F5], or go to Run..Run
Sub/UserForm to run the program
Create a control bottom (or a command bottom)
Enter the function name and arguments
Visual Basic for Applications
• Modules and Procedures
A module is a container for procedures.
A procedure is a unit of code enclosed either
between the Sub and End Sub statement or
between the Function and End Function
statements.
Visual Basic for Applications
• Module Functions
Sub Statement (NB: Subs do not return values,
Functions do)
–
–
–
–
–
Sub name [(arguments list)]
[statements]
[Exit Sub]
[statements]
End Sub
Visual Basic for Applications
• Module Functions
Function Statement
–
–
–
–
–
–
–
Function name [(arglist)] [As type]
[statements]
[name = expression]
[Exit Function]
[statements]
[name = expression]
End Function
Visual Basic for Applications
• Module Functions
Call Sub Procedure
– Call Sub name [(argument list)]
Call Function Procedure
– Call Function name (argument list)
– Object.Value = Function name (argument list)
Sometime you have to add Worksheet name or
Modules name before the procedure.
Visual Basic for Applications
• Passing Argument by Reference
The procedure access to the actual variable in
memory. As a result, the variable's value can be
changed by the procedure. Passing by reference
is the default in VBA.
Visual Basic for Applications
• Passing Argument by Value
It will pass the value to the corresponding
variable, not the memory address.
Visual Basic for Applications
• Scope of Modules and Procedures
Procedures in Visual Basic can have either
private or public scope.
Procedure with private scope
A procedure with private scope is only accessible to the
other procedures in the same module
Visual Basic for Applications
• Scope of Modules and Procedures
Procedure with public scope
A procedure with public scope is accessible to all
procedures in in every module in the workbook in which
the procedure is declared, and in all workbooks that contain
a reference to that workbook.
Visual Basic for Applications
• Objects and Collections
Objects are the fundamental building blocks of
Visual Basic.
An object is a special type of variable that contains
both data and codes.
A collection is a group of objects of the same class.
Visual Basic for Applications
• Objects and Collections
The most used Excel objects in VBA programming
are Workbook, Worksheet, Sheet, and Range.
Workbooks is a collection of all Workbook objects.
Worksheets is a collection of Worksheet objects.
Visual Basic for Applications
• Objects and Collections
The Workbook object represents a workbook.
The Worksheet object represents a worksheet.
The Sheet object represents a worksheet or
chartsheet.
The Range object represents a range of cells
Visual Basic for Applications
• Workbook and Worksheet Object
A workbook is the same as an Excel file.
The Workbook collection contains all the workbooks
that are currently opened.
Inside of a workbook contains at least one
worksheet.
Visual Basic for Applications
• Workbook and Worksheet Object
In VBA, a worksheet can be referenced as followed:
Worksheets("Sheet1")
 Worksheets("Sheet1") is the worksheet that named
"Sheet1."
Another way to refer to a worksheet is to use
number index like the following:
Worksheets(1)
The above refers to the first worksheet in the
collection.
Visual Basic for Applications
• Workbook and Worksheet Object
To refer sheets (or other objects) with the same
name, you have to qualify the object. For
example:
Workbooks("Book1").Worksheets("Sheet1")
Workbooks("Book2").Worksheets("Sheet1")
Visual Basic for Applications
• Range Object and Cells Property
Range represents a cell, a row, a column, a
selection of cells containing one or more
contiguous blocks of cells, or a 3-D range
Visual Basic for Applications
• Range Object and Cells Property
Range object has a Cells property.
The Cells property takes one or two indexes as
its parameters
Cells(index) or Cells(row, column)
Visual Basic for Applications
• Range Object and Cells Property
ActiveSheet.Range.Cells(1,1)
Range.Cells(1,1)
Cells(1,1)
Range("A1") = 123 and Cells(1,1) = 123
Cells(12) = "XYZ"
puts "XYZ" on Cells(1,12) or Range("L1") assume
cell A1 is the current cell
Range("B1:F5").cells(12) = "ZYZ"
Visual Basic for Applications
• Methods and Properties
A Property represents a built-in or user-defined
characteristic of the object.
A method is an action that you perform with an
object. Below are examples of a method and a
property for the Workbook Object.
Visual Basic for Applications
• Methods and Properties
– Workbooks.Close
Close method close the active workbook
– Workbooks.Count
Count property returns the number of workbooks
that are currently opened
Visual Basic for Applications
• Methods and Properties
Methods That Take No Arguments:
Worksheets("Sheet").Column("A:B").AutoFit
Methods That Take Arguments:
Worksheets("Sheet1").Range("A1:A10").Sort _
Worksheets("Sheet1").Range("A1")
Visual Basic for Applications
• Methods and Properties
Visual Basic for Applications
• Variables
Dim x as Type
Data Types
–
–
–
–
–
–
–
–
Integer (2 bytes)
Long (long integer, 4 bytes)
Single (single-precision floating-point, 4 bytes)
Double (double-precision floating-point, 8 bytes)
Currency (scaled integer, 8 bytes)
Decimal (14 bytes)
Date (8 bytes)
String (variable-length, 10 bytes)
• Array(arglist)
Visual Basic for Applications
• Array(arglist)
Before signing values to an array, the array needs
to be created. You can declare the array by
using the Dim statement.
Dim Array_name(Begin to End, Begin to End)
Visual Basic for Applications
• Comments
‘ This is a comment, not part of the program
Visual Basic for Applications
• Input and Output (I/O)
Input
– From Cells
• Set x = Range(“A1”)
– From Screen
• x = InputBox(Prompt:="Please enter a number.")
Output
– To Cells
• Range(“A1”) = x
– To Screen
• The Message Box
MsgBox “The value of x is” & Str(x)
Visual Basic for Applications
• Arithmetic Operators
Power/Exponent ^ Operator
Multiplication * Operator
Division
/ Operator
Addition
+ Operator
Subtraction
- Operator
Visual Basic for Applications
• Concatenation Operators
Concatenate Strings & Operator (String)
Visual Basic for Applications
• Type Conversion Functions
Change expression to Currency
CCur(expression)
Change expression to Date CDate(expression)
Change expression to Double CDbl(expression)
Change expression to Decimal
CDec(expression)
Visual Basic for Applications
• Type Conversion Functions
Change expression to Integer
CInt(expression)
Change expression to Long CLng(expression)
Change expression to Single CSng(expression)
Change expression to String CStr(expression)
Visual Basic for Applications
• Type Conversion Functions
Change text to number
VALUE(text)
Change value to text
TEXT(value,format_text)
Change number to String
STR(number)
Visual Basic for Applications
• Control Functions
If, Then
If expression Then statements
[Else [elsestatements]]
End If
Visual Basic for Applications
• Control Functions
Do, Until
Do Until x = y
statements
Loop
Visual Basic for Applications
• Control Functions
Do, While
Do While x = y
statements
Loop
Visual Basic for Applications
• Control Functions
For, Next
For counter = start To end [Step step]
[statements]
[Exit For]
[statements]
Next [counter]
Visual Basic for Applications
• Logical Functions
Logical AND AND(logical1,logical2, ...)
Logical OR OR(logical1,logical2,...)
Visual Basic for Applications
• Mathematical Functions
Count
COUNT(value1,value2,...)
Natural Logarithm
LN(number)
Maximum of a Series MAX(number1,number2,...)
Minimum of a Series MIN(number1,number2,...)
Power/Exponent
POWER(number,power)
Product of a Series
PRODUCT(number1,number2,...)
Round
ROUND(number,num_digits)
Sum of a Series SUM(number1,number2, ...)
Visual Basic for Applications
• Statistical Functions
Correlation Coefficient
CORREL(array1,array2)
Covariance COVAR(array1,array2)
Kurtosis
KURT(number1,number2,...)
Lognormal Distribution
LOGNORMDIST(x,mean,standard_dev)
Mean
AVERAGE(number1,number2,...)
Median
MEDIAN(number1,number2,...)
Mode
MODE(number1,number2,...)
Normal Distribution
NORMDIST(x,mean,standard_dev,cumulative)
Visual Basic for Applications
• Statistical Functions
Percentile Rank
PERCENTRANK(array,x,significance)
Percentile PERCENTILE(array,k)
Quartile
QUARTILE(array,quart)
Rank
RANK(number,ref,order)
Skewness SKEW(number1,number2,...)
Slope
SLOPE(known_y's,known_x's)
Standard Deviation STDEV(number1,number2,...)
Standard Normal Distribution
NORMSDIST(z)
Variance
VAR(number1,number2,...)
Visual Basic for Applications
• Financial Functions
Depreciation
DDB(cost, salvage, life, period[,
factor])
Depreciation, Straight Line SLN(cost, salvage, life)
Depreciation, Sum-Of-Years' Digits SYD(cost,
salvage,life, period)
Future Value
FV(rate, nper, pmt[, pv[, type]])
Interest Payment IPmt(rate, per, nper, pv[, fv[, type]])
Interest Rate
Rate(nper, pmt, pv[, fv[, type[,
guess]]])
Visual Basic for Applications
• Financial Functions
Internal Rate of Return IRR(values()[, guess])
Modified Internal Rate of Return
MIRR(values(),
finance_rate, reinvest_rate)
Net Present Value
NPV(rate, values())
Number of Periods (Annuity) NPer(rate, pmt, pv[, fv[,
type]])
Present Value PV(rate, nper, pmt[, fv[, type]])
Principle Payment
PPmt(rate, per, nper, pv[, fv[,
type]])