Modeling using VBA Using Toolbox Using the Toolbox
Modeling using VBA
Using Toolbox Using the Toolbox select a GUI element and by mouse-click place it on the frame This is a label This is a button This is a text box
Using User Form Work with Common Button, Text Box
Using User Form Work with Common Button, Text Box
Working with Combo Box
Working with List Box
Working with Image Control, Spin Button
Work with Multipage, Option controls
Work with Scroll Bar, Check Box, Frame controls
Work with additional controls
Modules & Procedures • Module – collection of logically related procedures & functions grouped together • Procedure – a group of ordered statements enclosed by Sub and End Sub • Function – the same as a procedure, but also returns some value and is enclosed between Function and End Function key words
Procedure & Function Examples Sub Show. Time() Range("C 1") = Now() End Sub Function sum. No(x, y) sum. No = x + y End Function Procedure: doesn’ t returns anything Function: returns something
Calling procedures vs. calling functions Sub z(a) Msg. Box a End Sub x() Call z("ABC") End Sub y() z "ABC“ End Sub If there are several sum. No functions in several modules/forms, need to use the full name of the function Sub Show. Sum() var. Sum= Module 1. sum. No(3, 5) Msg. Box var. Sum End Sub Function sum. No(x, y) sum. No = x + y End Function
Passing Arguments by Value or by Reference • Passing arguments by reference – – Is the VBA default – Means, if any changes happened to the argument variables, they will be preserved after the function/procedure finishes • Passing arguments by value – – Is possible in VBA (by explicit definition) – Means, the pre-calling state of the argument variables will be preserved after the procedure/function finishes
Arguments by Ref/by Val. Examples Sub Test. Passing 1() Dim y As Integer y = 50 Add. No 1 y Msg. Box y Add. No 2 y Msg. Box y End Sub Add. No 1(By. Ref x As Integer) x = x + 10 End Sub Add. No 2(x As Integer) x = x + 10 End Sub public Sub Test. Passing 2() Dim y As Integer y = 50 Add. No 3 y Msg. Box y End Sub private Sub Add. No 3(By. Val x _ As Integer) x = x + 10 End Sub
Functions/Procedure Scope • Use public to allow any module to call the function/procedure • Use private to make limited access to the function/procedure (only from the owning module)
VBA Variables • A variable is used to store temporary information within a Procedure, Function, Module… • A variable name – Must start with letter and can’t contain spaces and special characters (such as “&”, “%”, “”) – Can’t be any excel keyword (“if”, “while”…) – Can’t have identical name to any existing class (“Worksheet”, “Workbook”…)
VBA Data Type • • Byte – positive integer numbers (0: 255) Integer – integers (-32, 768 : 32, 767) Long – 4 -byte integer Currency – for fixed-point calculations Single – 2 -byte floating-point numbers Double – double-precision floating-point numbers Date – used to store dates and times as real numbers. String – contains a sequence of characters
Using Variables • Declaring Variables – Format: Dim varible. Name AS data. Type – Examples: • Dim my. Text As String • Dim my. Num As Integer • Dim my. Obj As Range – The default value of • any numeric variable is zero • any string variable – “” (empty string) • an Object variable – is nothing
- Slides: 19