1 / 30

Overview of VBA Programming Syntax

Programming With Objects. ObjectsProperties: attributes or characteristics of an object (e.g., font size, color, name, value, visible)Methods: things an object can do (e.g., calculate, additem, show, hide.)Events: things that can happen to an object (e.g., click, activate, change, scroll.) Excel

hung
Download Presentation

Overview of VBA Programming Syntax

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. Overview of VBA Programming & Syntax

    2. Programming With Objects Objects Properties: attributes or characteristics of an object (e.g., font size, color, name, value, visible) Methods: things an object can do (e.g., calculate, additem, show, hide.) Events: things that can happen to an object (e.g., click, activate, change, scroll.) Excel’s Object Model The VBA Editor

    3. Properties: attributes or characteristics of an object.

    4. Methods: things an object can do.

    5. Events: things that can happen to an object.

    6. Variables Variable - a named location in the computer’s Random Access Memory (RAM) in which we can store data (i.e., text or numbers). RAM is volatile so data stored there is not permanent. Examples of variables... X = 7 Lname = “Major”

    7. Do You Get It? Sub Test( ) X = 7 Y = 2 Z = X * Y Msgbox Z End Sub

    8. The MsgBox Statement MsgBox prompt [, buttons] [, title] Example msg = “I like VBA” MsgBox msg , ,“My Message”

    9. Types of Variables

    10. Declaring Variables Use Dim or Static within a Sub or Function Examples… Sub Test( ) Dim X As Integer Dim Y As Single Dim Z As Boolean Dim A, B, C As Currency ‘ A & B are Variant! Static D As Long ‘ D will retain its value …….. End Sub

    11. Declaring Variables Use Public or Private at the Module Level Examples… Private Term As Integer Public Payment As Currency Public variables in code modules are ‘global’ Public variables in sheet modules are basically user-defined properties of the sheet

    12. Arrays An array is an indexed set of memory locations. Each element of the array has a unique index. Example...

    13. Arrays cont’d Arrays can also be multi-dimensional Example...

    14. Procedures Two types: Sub(routines) & Functions They can be either Private or Public (default) A function always returns a value! Sub Example… Public Sub Test( ) Dim x As String x = “BIT 5474” Msgbox “This course is ” & x End Sub

    15. Procedures Function Example… Private Function AddThree( x as Single) As Single AddThree = x + 3 End Function We might use (or call) this function as follows Z = AddThree( 7 )

    16. Procedure Arguments Arguments may be passed to Subs and Functions either ByVal or ByRef Example... Sub MyExample ( ) Dim A as Integer, B as Integer A = 5 : B = 7 TestIt A, B ‘ Here, A becomes 25, B is still 7 End Sub Sub TestIt(ByRef X As Integer, ByVal Y As Integer) X = X^2 : Y = Y^2 End Sub

    17. The InputBox Function InputBox(prompt [, title] [, default] [, xpos] [, ypos] ) Example msg = “How much would you like to borrow?” x = InputBox(msg, “Amount Financed”) If the user clicks OK or hits Enter, the InputBox function returns whatever is in the text box. If the user clicks Cancel, the function returns a zero-length string (“”).

    18. The MsgBox Function MsgBox( prompt [, buttons] [, title] ) Example msg = “Would you like to continue?” If MsgBox(msg, vbYesNo, “Terminate Job?”)=vbYes Then ‘ blah, blah, blah Else ‘ blah, blah, blah End If

    19. Decision Structures

    20. If…Then Examples… If X < 0 And Z>2 Then Y = X*Z If Q>Z Then X=5*Q : Y = 2*Z If X > 5 Then Y = X + 25 X = 0 End If

    21. If…Then…Else If age < 16 Then prompt = "You are not old enough for a license." MsgBox prompt Else prompt = "You can be tested for a license." MsgBox prompt End If

    22. If…Then…ElseIf If jobClass = 1 Then Bonus = salary * 0.1 * rating / 10 ElseIf jobClass = 2 Then Bonus = salary * 0.09 * rating / 10 ElseIf jobClass = 3 Then Bonus = salary * 0.07 * rating / 10 Else Bonus = 0 End If

    23. Select Case Select Case jobClass Case 1 Bonus = salary * 0.1 * rating / 10 Case 3 Bonus = salary * 0.07 * rating / 10 Case 2, 4, 5 'The expression list can contain several values... Bonus = salary * 0.05 * rating / 5 Case 6 To 8 '...or be a range of values Bonus = 150 Case Is > 8 '...or be compared to other values Bonus = 100 Case Else Bonus = 0 End Select

    24. Looping Structures Do While...Loop Do Until...Loop Do...Loop While Do...Loop Until For...Next For Each...Next

    25. Do While…Loop Do While Not( rs.Eof ) ' rs.Eof returns True/False Stocklist.Additem rs(“ticker”) rs.MoveNext Loop

    26. Do Until…Loop Response = MsgBox("Do you want more data?", vbYesNo) Do Until Response = vbNo ProcessUserData 'Call procedure to process data Response = MsgBox("Do you want more data?", vbYesNo) Loop

    27. Do…Loop While Do ProcessUserData 'Call procedure to process data Response = MsgBox("Do you want more data?", vbYesNo) Loop While Response = vbYes

    28. Do…Loop Until Do ProcessUserData 'Call procedure to process data Response = MsgBox("Do you want more data?", vbYesNo) Loop Until Response = vbNo

    29. For…Next For i = 1 to 3 Step 1 Sum = Sum + A(i) Next i

    30. For Each…Next For Each x In Worksheets If x.Name = “Grades” Then X.Delete Next x

More Related