1 / 22

VBA Excel

VBA. Is a significant subset of the stand-alone Visual Basic programming languageIt is integrated into Microsoft Office applications (and others, like Arena)It is the macro language of ExcelYou can addForms for dialog boxes with userClasses for object definitionsModules containing procedures.

jamal
Download Presentation

VBA Excel

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. VBA & Excel Barry L. Nelson IEMS 465 Fall Quarter 2003

    2. VBA Is a significant subset of the stand-alone Visual Basic programming language It is integrated into Microsoft Office applications (and others, like Arena) It is the macro language of Excel You can add Forms for dialog boxes with user Classes for object definitions Modules containing procedures

    3. Accessing VBA in Excel Tools ? Macros ? Visual Basic Editor Enter VBA through the navigation buttons in the top toolbars

    4. VB Edit Window

    5. Variables Declare by Dim Better to use Data Types: Dim amount As Double Dim year As Integer Dim name As String Other data types: Boolean, Byte, Currency, Date Default (no type) is Variant

    6. Variable(cont’d.) % - integer & - long integer ! - single # - double @ currency $ - string anIntegerValue% =3, aString$ = "hallo" Can modify with scope (outside procedure) Private I As Integer Public billsPaid As Currency Make values permanent Static yourName As String Multiple variables Private test, amount, J As Integer

    7. Constants [Public|Private] Const constantName [As type] = expression Public Const PI = 3.1, NumPLANETS = 9 Const PI2 = PI * 2 Const RELEASE = #1/1/99/#

    8. Control Structures Decision If anyDate < Now Then anyDate = Now If anyDate < Now Then anyDate = Now End If If … Then … Else

    9. Decisions(cont’d.) If Index = 0 Then CopyActiveControl ClearActiveControl Else If Index = 1 Then CopyActiveControl Else If Index = 2 Then ClearActiveControl Else PasteActive Control End If

    10. Decisions(cont’d.) Select Case Index Case 0 CopyActiveControl ClearActiveControl Case 1 CopyActiveControl Case 2 ClearActiveControl Case 3 PasteActive Control Case Else frmFind.Show End Select

    11. Loops/Iterations Do … Loop Do While condition statements Loop Do statements Loop While condition

    12. Loops(cont’d.) For … Next For counter = start To end [Step increment] statements Next counter For Each … Next For Each element In group statements Next element

    13. Exiting Control Structures For counter = start To end [Step increment] [statement block] [Exit For] [statement block] Next [counter [, counter][, …]] Do [ {While|Until} condition] [statement block] [Exit Do] [statement block] Loop

    14. Exiting(cont’d.) Can also exit from a procedure: Exit Sub

    15. Code Modules Excel Objects (ThisWorkbook, Sheet#) Modules Typically we put our code here A Module is a collection of Subs and Functions Insert ? Module Class Modules User Forms

    16. Procedures Sub (routines) no value returned Called without parenthesis mySub param1, param2 Called with parenthesis Call mySub(param1, param2) Functions value returned assign return value to function name

    17. Arguments for Procedures Pass by Reference (default) sub stuff(item As String) ‘or sub stuff(ByRef item As String) … stuff(“car”) Pass by Value (must be declared) sub stuff(ByVal item As String) Note that arguments must be formally declared (as must the return type of functions)

    18. Some Useful Code The following are some pieces of code that are useful for doing VBA with Excel. See the code on the course web site for other examples.

    19. Finding/Creating a Sheet

    20. Writing to a Sheet Put the absolute value of the variable Fudge in row 2, column 20 of the Sheet named mySheet.

    21. Use an Excel Function VBA has a limited number of built-in functions You can access any Excel worksheet function. This example uses the Excel Max function

    22. Running the Code Perhaps the easiest way to run the code is to place your cursor in the module you want to run and press the Run Sub/UserForm button. Your modules will as appear as Macros that can be run from Excel under Tools ? Macros ? Macros…

    23. Finishing Up Explore what is available in the IEMS 465 code Exercise: Write a Sub that inserts a worksheet named “Count” into the Workbook, then writes the numbers 1,2,…,10 in the first row, the first ten columns. Use a loop to do this.

More Related