1 / 23

VBA & Excel

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

blair-gibbs
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 VBA Design Mode Visual Basic Editor Design mode is the time during which no code from the project is running and events from Excel or project will not execute. You can leave design mode by executing a macro or using the Immediate window.

  4. VB Edit Window Edit Window Options Project Explorer Code Window Property Inspector

  5. Variables • Declare by Dim • Better to use Data Types:Dim amount AsDoubleDim year AsIntegerDim name AsString • Other data types: Boolean, Byte, Currency, Date • Default (no type) is Variant

  6. Variable(cont’d.) • % - integer & - long integer ! - single # - double@ currency $ - stringanIntegerValue% =3, aString$ = "hallo" • Can modify with scope (outside procedure)Private I AsIntegerPublic billsPaid AsCurrency • Make values permanentStatic yourName AsString • Multiple variablesPrivate test, amount, J AsInteger

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

  8. Control Structures • DecisionIf anyDate < Now Then anyDate = NowIf anyDate < Now Then anyDate = NowEndIf If … Then … Else

  9. Decisions(cont’d.) If Index = 0 Then CopyActiveControl ClearActiveControlElseIf Index = 1 Then CopyActiveControlElseIf Index = 2 Then ClearActiveControlElse PasteActive ControlEndIf

  10. Decisions(cont’d.) SelectCase IndexCase 0 CopyActiveControl ClearActiveControlCase 1 CopyActiveControlCase 2 ClearActiveControlCase 3 PasteActive ControlCaseElse frmFind.ShowEndSelect

  11. Loops/Iterations • Do … LoopDoWhile condition statementsLoopDo statementsLoopWhile condition

  12. Loops(cont’d.) • For … NextFor counter = start To end [Step increment] statementsNext counter • For Each … NextForEach element In group statementsNext element

  13. Exiting Control Structures For counter = start To end [Step increment] [statement block] [ExitFor] [statement block]Next [counter [, counter][, …]]Do [ {While|Until} condition] [statement block] [ExitDo] [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 Dim found As Boolean Dim sheetNext As Worksheet ' Set up mySheet sheet for output found = False For Each sheetNext In Worksheets If sheetNext.Name = “mySheet" Then found = True Exit For End If Next sheetNext If found = True Then Worksheets(“mySheet").Select ActiveSheet.UsedRange.Clear Else Worksheets.Add ActiveSheet.Name = “mySheet" End If

  20. Writing to a Sheet • Put the absolute value of the variable Fudge in row 2, column 20 of the Sheet named mySheet. Worksheets(“mySheet”).Cells(2,20) = VBA.Abs(Fudge)

  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 W = WorksheetFunction.Max(0, W + S - a)

  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 underTools  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