1 / 24

VBA Programming

VBA Programming. Session #2. Things to Review. Variables Procedures: Subs & Functions If…Then For…Next. Variables. Just like in math, variables: Have a name Have a value Value can be changed Examples: x = 1 : debug.print x x = 2 : debug.print x. Procedures. Subroutines (Subs)

osma
Download Presentation

VBA Programming

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 Programming Session #2

  2. Things to Review • Variables • Procedures: Subs & Functions • If…Then • For…Next

  3. Variables • Just like in math, variables: • Have a name • Have a value • Value can be changed • Examples: • x = 1 : debug.print x • x = 2 : debug.print x

  4. Procedures • Subroutines (Subs) • Can do things but not return values. • Subs without arguments can be called from the Excel macro toolbar. • Functions • Functions return values in VBA with the form: FunctionName = SomeValue • Functions can be inserted into Excel sheets from the Insert|Function menu.

  5. If…Then • If…Then has two basic forms: • Statement If (x = 1) Then Debug.Print "Yes" • Code Block If (x = 1) Then y = 1 Else y = -1 End If

  6. For…Next • Loops through a range of values and executes the statements in the block.

  7. New Stuff

  8. VBA Variable Types • The most commonly used VBA types are: • Boolean (True or False) • Numeric Types: Integer, Double, Currency • Date • String • Variant (holds almost anything) • Object Types: Ranges, Worksheets, etc.

  9. Objects • A combination of code and data that can be treated as a unit, for example, a control, form, or application component. Each object is defined by a class. (MS) • Examples: • Workbooks • Worksheets • Charts • Ranges

  10. Declaring Variables • Declaring means: • Telling VBA that you are going to use it • Defining the variable's type • Declare variables using the Dim statement • Dim MyName as String • Dim MyBirthday as Date • Dim BigMoney as Currency • Dim wks as Worksheet

  11. Assigning Values to Variables • Most variables can simple be assigned like this: • X = 5 • MyName = "Bob" • Objects need to be assigned using Set • Set MyDataSheet = ActiveSheet • Set MyChart = ActiveChart

  12. Forgot to set?

  13. Range Object • Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range. (MS)

  14. Understanding Ranges

  15. Ranges: Writing Code Sub SimplestHello() Range("A1") = "Hello" Range("A2", "B4") = "Goodbye" End Sub

  16. Ranges: Using a Range Variable Sub HelloRange() Dim a1 As Range Set a1 = Range("A1") a1 = "Hello world!" End Sub

  17. Offsets: If only there was just "A1"

  18. Combining it all... Data Table

  19. The Macro Recorder: Part II

  20. Instead of... Range("A1") Sheets("Sheet1") Charts("Chart1") Use Selection or ActiveCell ActiveSheet ActiveChart Specific -> Generic

  21. Collection Object • Most of the VBA objects you use are parts of collections. For example: • Charts: a collection of Chart objects • Worksheets: a collection of Worksheet objects • Collections have the following: • Count: number of items in the collection • Add: add an item • Item: get the value of an item • Remove: delete item

  22. Collection: Example Sub AddWorksheet() Dim wks As Worksheet Set wks = Worksheets.Add MsgBox "Added: " + wks.Name End Sub

  23. Looping Through Collections • Collections are handy to use because we can easily look at each item in the collection using a "For Each" loop. • For example: Sub ListWorksheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets MsgBox wks.Name Next End Sub

  24. Collections: Exercise

More Related