Unformatted text preview:

Modeling using VBAUsing ToolboxUsing User Form Work with Common Button, Text BoxSlide 4Working with Combo BoxWorking with List BoxWorking with Image Control, Spin ButtonWork with Multipage, Option controlsWork with Scroll Bar, Check Box, Frame controlsWork with additional controlsModules & ProceduresProcedure & Function ExamplesCalling procedures vs. calling functionsPassing Arguments by Value or by ReferenceArguments by Ref/by Val. ExamplesFunctions/Procedure ScopeVBA VariablesVBA Data TypeUsing VariablesModeling using VBAUsing ToolboxThis is a labelThis is a buttonUsing the Toolbox select a GUI element and by mouse-click place it on the frameThis is a text boxUsing User Form Work with Common Button, Text BoxUsing User Form Work with Common Button, Text BoxWorking with Combo BoxWorking with List BoxWorking with Image Control, Spin ButtonWork with Multipage, Option controlsWork with Scroll Bar, Check Box, Frame controlsWork with additional controlsModules & 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 wordsProcedure & Function ExamplesSub ShowTime() Range("C1") = Now()End SubFunction sumNo(x, y) sumNo = x + yEnd FunctionFunction: returns somethingProcedure: doesn’ t returns anythingCalling procedures vs. calling functionsSub z(a) MsgBox aEnd SubSub x() Call z("ABC")End SubSub y() z "ABC“End SubSub ShowSum()varSum= Module1.sumNo(3,5) MsgBox varSumEnd SubFunction sumNo(x, y) sumNo = x + yEnd Function If there are several sumNo functions in several modules/forms, need to use the full name of the functionPassing 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 finishesArguments by Ref/by Val. ExamplesSub TestPassing1() Dim y As Integer y = 50 AddNo1 y MsgBox y AddNo2 y MsgBox yEnd SubSub AddNo1(ByRef x As Integer) x = x + 10End SubSub AddNo2(x As Integer)x = x + 10End Subpublic Sub TestPassing2() Dim y As Integer y = 50 AddNo3 y MsgBox yEnd Subprivate Sub AddNo3(ByVal x _ As Integer) x = x + 10End SubFunctions/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 charactersUsing Variables •Declaring Variables–Format: Dim varibleName AS dataType–Examples: •Dim myText As String •Dim myNum As Integer•Dim myObj As Range–The default value of •any numeric variable is zero•any string variable – “” (empty string)•an Object variable – is


View Full Document

UConn MATH 5850 - Modeling using VBA

Documents in this Course
Load more
Download Modeling using VBA
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Modeling using VBA and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Modeling using VBA 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?