Modeling using VBA Covered materials Userforms Controls Module

Modeling using VBA

Covered materials - Userforms Controls Module Procedures & Functions Variables Scope

Variant “Data Type” • In VB you don’t have to declare variable before its usage – Then, VB will by itself declare such variable as “Variant” • You can also declare variable as “Variant” – Dim my. Var as Variant • Variant – means that the variable may contain any data type

Variables Assignment • To assign a value to a Numeric or String type Variable, you simply use your Variable name, followed by the equals sign (=) and then the String or Numeric • To assign an Object to an Object type variable you must use the key word "Set"

Variables Assignment – cont. Sub Parse. Value() Dim var. Name as String var. Name = “Cuong Do” Dim var. Age as Integer var. Age = “ 28” Dim var. Cell as Range Set var. Cell = Range("A 1") var. Cell. Value = “ 123” End Sub

VBA Variables Scope & Lifecycle The scope & lifecycle of a variable defines the code where the variable can be accessed and time when the stored data is kept inside the variable • Procedure-Level – Variables defined inside procedures – Can be accessed only inside the procedure and keep their data until the End statement of the procedure • Module-Level – Defined in the top of a Module – Any procedure inside the Module can access the variable – The variable retains the values unless the Workbook closes • Project-Level, Workbook Level, or Public Module-Level – Defined as “Public” in the top of a Module – Can be accesses by any procedure in any module – The variable retains the values unless the Workbook closes

VBA Variables Scope & Lifecycle – cont. • Sub scope. Example() Dim x as Integer Procedure level variables x=5 End Sub Module level variables • Dim y as Integer ‘all the module procedures are here… Project level variables • Public z as Integer ‘all the module procedures are here…

Which variable? Module 1 Userform 8

How to address public variables? Module 1 Userform 8

Basic Excel Classes • Workbook: the class represents an Excel file • Worksheet: represents a single worksheet • Cell: represents a single cell

Excel Containers • Workbooks: a collection of objects of class “Workbook” • Worksheets: a collection of objects of class “Worksheet” • Range: a range of objects of class Cell

Referring to Cells by Using a Range Object Sub Random() Dim my. Range As Range Set my. Range = Worksheets("Sheet 1"). Range("A 1: D 5") my. Range. Formula = "=RAND()" my. Range. Font. Bold = True End Sub

Referring to Cells by Using a Range Object Sub Test 1() Range("A 10"). Value = "123" End Sub Test 2() Set var. Range = Range("A 10") var. Range. Value = "123" End Sub Test 3() Worksheets(1). Range("A 10") = "123" End Sub Test 4() Worksheets("Sheet 1"). Range("A 10") = "123" End Sub Same outcome here

Referring to Multiple Ranges Sub Clear. Ranges() Worksheets("Sheet 1"). _ Range("C 5: D 9, G 9: H 16, B 14: D 18"). Clear. Contents End Sub

Referring to Cells by Using Index Numbers • Cells indexing format: – Cells(row, column), where both row and column are given as integers (starting from 1) Sub Enter. Value() Worksheets("Sheet 1"). Cells(6, 1). Value = 10 End Sub – Cells(index) Sub Enter. Value() Worksheets(1). Range("A 1: D 3"). Cells(7). Value = "Here it is" End Sub

Referencing Cells Relative to Other Cells Active. Cell. Offset(1, 2). Value = "Here it is"

Some useful methods/properties of Excel Classes • Workbooks. Close – closes the active workbook • Workbooks. Count – returns the number of currently open workbooks • Range(“A 1”) is the same as Range(“A 1”). Value • Cells(1, 1) is the same as Cells(1, 1). Value • Worksheets(1). Column(“A: B”). Auto. Fit • Worksheets(1). Range(“A 1: A 10”). Sort_ • Workbooks. Open file. Name: =“test. xls”

Arrays Dim Students(1 to 5) As String Students(1) = “Jack” Students(2) = “Nick”

Multidimensional Arrays Dim Grades(1 to 5, 1 to 2) Grades(1, 1)=“Jack” Grades(1, 2)=“A” Grades(2, 1)=“Nick” Grades(2, 2)=“B” For i=1 to 2 Msg. Box Grades(i, 1) & “ got ” & Grades(i, 2) Next Msg. Box Grades(1, 1) & " got " & Grades(1, 2) & vb. Cr. Lf & _ Grades(2, 1) & " got " & Grades(2, 2)

Resizing the Arrays Dim Grades(1 to 5, 1 to 2) Grades(1, 1)=“Jack” Grades(1, 2)=“A” Grades(2, 1)=“Nick” Grades(2, 2)=“B” ‘ Now Re. Dim the array Re. Dim Grades(1 to 10, 1 to 2) ‘previous values won’t be kept Re. Dim Preserve Grades(1 to 10, 1 to 2) ‘preserve previous values Only the first dimension can be changed!

Upper & Lower Index Bounds of an Array • Dim A(3): index from 0 to 3 • Dim A(1 to 3): index from 1 to 3 • Dim A(1 To 100, 0 To 3, -3 To 4) – UBound(A, 1) – will return “ 100” – UBound(A, 2) – will return “ 3” – UBound(A, 3) – will return “ 4” – LBound(A, 1) – will return “ 1” – LBound(A, 2) – will return “ 0” – LBound(A, 3) – will return “-3”

VBA Control Structures - If • If Grade >= 90 Then Msg. Box "You got an A" Else. If Grade >= 80 Then Msg. Box "You got a B" Else. If Grade >= 70 Then Msg. Box "You got a C" Else Msg. Box "You are out of scope” Need to capture the rest of cases End If

VBA Control Structures – Select • Select Case Grade Case Is >= 90 Msg. Box "You got an A Case Is >= 80 Msg. Box "You got an B” Case Is >= 70 Msg. Box "You got a C” Case Else Msg. Box "You out of scope” Need to capture the rest of cases End Select

VBA Control Structures – Loops N! Sub factorial 2() Dim N N = 10 i = N Cells(1, 2) = 1 Do While i >= 1 Cells(1, 2) = Cells(1, 2) * i i = i - 1 Loop End Sub Loop at least once Sub factorial 1() Dim N N = 10 Cells(1, 1) = 1 For i = N To 1 Step -1 Cells(1, 1) = Cells(1, 1) * i Next End Sub factorial 3() Dim N N = 10 i = N Cells(1, 3) = 1 Do Cells(1, 3) = Cells(1, 3) * i i = i - 1 Loop While i >= 1 End Sub

Loops for Collection Objects Sub Work. Sheets. Loop() Dim my. Sheet As Worksheet For Each my. Sheet In Worksheets Msg. Box my. Sheet. Name Next my. Sheet End Sub

Nested Loops Example Sub Nested. Loop. Example() For i = 1 To 5 For j = 1 To 5 Cells(i, j) = "Row " & i & " Col " & j Next j Next i End Sub

Worksheet functions Sub Func. Example() Range("D 6") = Application. Worksheet. Function. Sum(Range("D 1: D 5")) ‘ Can also use Application. Sum for short End Sub ‘ Another way to get the sum Range("D 6"). Formula = "=SUM(D 1: D 5)"

Other Issues in VBA programming • What if need to pass an array of arguments without knowing the number of arguments

Graphs

Graphs Private Sub cmd. Generate_Click() Range("B 2: E 13"). Formula = "=RAND()*100" End Sub Private Sub cmd. Plot_Click() Create_Chart_Sheet End Sub Create_Chart_Sheet() Charts. Add With Active. Chart . Chart. Type = xl. Column. Clustered . Set. Source. Data Source: =Sheets("Sheet 4"). Range("B 1: E 13") End With End Sub

Graphs (Columnclusted)

Graphs (Bar. Stacked)