1 VBA Introduction 2 Basic Components VBA LANGUAGE
1 VBA Introduction
2 Basic Components VBA LANGUAGE OFFICE OBJECTS EXCEL OBJECTS POWERPOINT OBJECTS ACCESS OBJECTS WORD OBJECTS OUTLOOK OBJECTS
3 Getting To VBA
4 Excel VBA Windows Project Explorer workbook worksheet module(s) Code Properties
5 Using Variables
6 ‘Insert’ Module Code Module Procedure
7 Simple Procedure Sub Using. Variables() v_num = 34 v_str = "Hello" v_dat = Date$ Immediate Window debug. print v_num & " " & v_str & " " & v_dat End Sub
8 Simple Procedure option explicit Sub Using. Variables() Dim v_num As Single, v_str As String, v_dat As Date v_num = 34 v_str = "Hello" v_dat = Date$ End Sub
9 Simple Calculation Procedure Sub Revenue Dim Units As Integer, Price As Single, Revenue as Single Units = 3 Price = 2. 45 Revenue = Units * Price Debug. Print Revenue End Sub
10 Procedure With Parameters Sub Revenue(p_units as integer, p_price as single) Dim Revenue as Single Revenue = p_units * p_price Debug. Print Revenue End Sub
11 …. Calling A Procedure With Parameters Sub Calculate_Revenue Dim Units As Integer, Price As Single Units = 3 Price = 2. 45 Call Revenue(Units, Price) End Sub Revenue(p_units as integer, p_price as single) Dim Revenue as Single Revenue = p_units * p_price Debug. Print Revenue End Sub
12 …. Calling A Function With Parameters Sub Calculate_Revenue Dim Units As Integer, Price As Single, Calc_Rev As Single Units = 3 Price = 2. 45 Calc_Rev = Revenue (Units, Price) Debug. Print Calc_Rev End Sub Function Revenue(p_units as integer, p_price as single) Revenue = p_units * p_price End Function
13 Using Your Function In The Spreadsheet 1. Developer Macros 2. Type <function_name> into Macro Name box 3. Click Options button 4. Add description
14 More Variables Sub Using. Variants() Dim var 1 As Variant var 1 = 34 var 1 = Date var 1 = " Hello " var 1 = Null var 1 = Empty End Sub
15 Simple Array Variables Sub Simple. Array() Dim a() As Integer, b(3) As String a(0) = 2 Re. Dim a(2) a(0) = 4 b(0) = "Hello" Re. Dim Preserve a(5) a(4) = 6 b(3) = "Goodbye" Debug. Print a(0) * a(4) & b(0) & " and "; b(3) End Sub
16 Variant Array Sub Variant. Array() Dim VA(4) VA(1) = 33 VA(2) = "A string" VA(3) = Date$ End Sub
17 Variant Array With Array Function Option Base 1 ____________ Sub Array. Function() Dim VA as Variant VA = Array(“tom”, 1, date$) End Sub
18 Watch The Variables LOCALS WINDOW - keep track of variable values in local procedure IMMEDIATE WINDOW - keep track of variable values with debug. print or ? WATCH WINDOW - keep track of variable values in all procedures
- Slides: 18