1 / 32

Modeling using VBA

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 ”

mitzel
Download Presentation

Modeling using VBA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Modeling using VBA

  2. Covered materials • Userforms • Controls • Module • Procedures & Functions • Variables • Scope

  3. 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 myVar as Variant • Variant – means that the variable may contain any data type

  4. 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"

  5. Variables Assignment – cont. Sub ParseValue() Dim varName as String varName = “Cuong Do” Dim varAge as Integer varAge = “28” Dim varCell as Range Set varCell = Range("A1") varCell.Value = “123” End Sub

  6. 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

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

  8. Which variable? Module1 Userform8

  9. How to address public variables? Module1 Userform8

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

  11. 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

  12. Referring to Cells by Using a Range Object Sub Random() Dim myRange As Range Set myRange = Worksheets("Sheet1").Range("A1:D5") myRange.Formula = "=RAND()" myRange.Font.Bold = True End Sub

  13. Referring to Cells by Using a Range Object Sub Test1() Range("A10").Value = "123" End Sub Sub Test2() Set varRange = Range("A10") varRange.Value = "123" End Sub Sub Test3() Worksheets(1).Range("A10") = "123" End Sub Sub Test4() Worksheets("Sheet1").Range("A10") = "123" End Sub Same outcome here

  14. Referring to Multiple Ranges Sub ClearRanges() Worksheets("Sheet1")._ Range("C5:D9,G9:H16,B14:D18").ClearContents End Sub

  15. 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) • Cells(index) Sub EnterValue() Worksheets("Sheet1").Cells(6, 1).Value = 10 End Sub Sub EnterValue() Worksheets(1).Range("A1:D3").Cells(7) .Value = "Here it is" End Sub

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

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

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

  19. 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 MsgBox Grades(i,1) & “ got ” & Grades(i,2) Next MsgBox Grades(1, 1) & " got " & Grades(1, 2) & vbCrLf & _ Grades(2, 1) & " got " & Grades(2, 2)

  20. 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 ReDim the array ReDim Grades(1 to 10,1 to 2) ‘previous values won’t be kept ReDim Preserve Grades(1 to 10,1 to 2) ‘preserve previous values Only the first dimension can be changed!

  21. 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”

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

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

  24. VBA Control Structures – Loops N! Sub factorial1() 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 Sub factorial2() 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 Sub factorial3() 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 Loop at least once

  25. Loops for Collection Objects Sub WorkSheetsLoop()Dim mySheet As WorksheetFor EachmySheetInWorksheetsMsgBox mySheet.NameNextmySheet End Sub

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

  27. Worksheet functions Sub FuncExample() Range("D6") = Application. WorksheetFunction. Sum(Range("D1:D5")) ‘ Can also use Application. Sum for short End Sub ‘ Another way to get the sum Range("D6").Formula = "=SUM(D1:D5)"

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

  29. Graphs

  30. Graphs Private Sub cmdGenerate_Click() Range("B2:E13").Formula = "=RAND()*100" End Sub Private Sub cmdPlot_Click() Create_Chart_Sheet End Sub Sub Create_Chart_Sheet() Charts.Add With ActiveChart .ChartType = xlColumnClustered .SetSourceData Source:=Sheets("Sheet4").Range("B1:E13") End With End Sub

  31. Graphs (Columnclusted)

  32. Graphs (BarStacked)

More Related