Slide 1: Introduction to EXCEL VBA Part I 01/23/2008 Alexandra Chronopoulou 1
Slide 2: What is VBA?  Visual Basic Applications VBA IDE: VBA Integrated Development Environment Programming tools within Excel 2  
Slide 3: Today…   Built-in Functions in Excel Create & Modify Excel Macros   Using Macros Recorder Using Visual Basic Editor   Excel Solver VBA Programming    Introduction/ Terminology Subroutines, Functions, Variables Arrays & Loops 3
Slide 4: Built – in Functions in Excel  Summary Statistics Matrix Operations Logical Functions   4
Slide 5: Macros      How to create a simple macro. How to modify a macro using the VBA editor. Project Explorer & Properties windows. How to use a macro. Example… 5
Slide 6: EXCEL Solver  What can we do with it?   Find Local Minimum/Maximum Solve a system of Non-Linear Equations Tools -> Add Ins -> Solver Add-Ins  Where to find it:   Examples 6
Slide 7: Introduction to VBA Programming  Differences & similarities with the other members of the VB family  Interpreted language  VBA is more than a macro language  Comparison with other macro languages 7
Slide 8: VBA Terminology  Data Types       Numeric (single, double, currency) Object Boolean Date String Variant 8
Slide 9: VBA Terminology (cont’d)  Variables   Spaces are not allowed when naming variables VBA is not case-sensitive Functions Subroutines 9  Procedures  
Slide 10: VBA Terminology (cont’d)  Arrays   We can have up to 60 dimensions They can be either fixed-length or dynamic  Constants 10
Slide 11: VBA Terminology (cont’d)  Operators     Arithmetic (e.g. +, -, ^, *, Mod) Concatenation ( & ) Comparison (e.g. >=, <=, <>) Logical (And, Or, Not, Xor, Eqv,) 11
Slide 12: How to create a SUBROUTINE   Each macro in Excel is a Subroutine. 2 types:  Public & Private Private Sub TestSub()  Syntax  If it is not defined as private, the default is public.  Examples (with & without input arguments) 12
Slide 13: How to create a FUNCTION    Not called from macro dialog box 2 types: public & private Syntax Private Function TestFunc() As Integer FunctionValue = TestFunc(Param)  If it is not defined as private, the default is public.  Example (Black-Scholes, Binomial) 13
Slide 14: How to DECLARE a Variable  VBA lets you use variables that have not been declared!  To avoid confusion use: Option Explicit Option Explicit Sub CreateVariable() Dim NewVariable As Integer End Sub  other way of defining that NewVariable is an integer: Dim NewVariable% 14
Slide 15: Arrays  Specify the scope of an array as:  Dim, Public, Private Sub DeclareArray Dim NewArray(1 To 3) As String NewArray(1) = “One” … End Sub Dim MultiArray(1 To 3, 1 To 5) As Integer 15  
Slide 16: Homework  Due next week Thank you for your Attention! 16