Excel Workshop Part II

1. Excel Workshop Part II André Johnson UBC Okanagan IEEE Student Branch March 16, 2011

2. Outline: • VBA and Excel • VBA Basics • Recording a Macro • Writing Basic Marcos • User Prompts • Importing Data • Questions

3. VBA and Excel • Visual Basic for Applications (VBA) is an implementation of  Microsoft’s Visual Basic 6 • Object Orientated • Supports Subroutine and Functions • Supports most common • VBA in Excel • Allows access to excel function in a programming language

5. VBA Basics • Types of Variable • Declaring Variable • Pointers • Flow Control

6. Types of Variables • Common Types • Variant (default), Byte, Boolean, Integer, Long, Single, Double, Date, String • Advanced Types • Object, Currency , Decimal, Date • User Defined • Excel Specific • Worksheet • Workbook

7. Declaring a Variable • Dim – declare and dimension the variable • All variables are arrays • Private, Public, ReDim, or Static • Explicit option – define variables before using them in separate statement • Examples: • dim st_name As String • dim d_distance As Doube

8. Pointers • Can be used to implement common date structures • Ie. Stacks and queues • Useful when dealing with multiple sheets or workbook • Set and Get • Example: Dim sh_info As Worksheet 'Pointer for worksheet Set sh_info = Worksheets.Add 'Make new work sheet sh_info.Name = "Info" 'Name new Work sheet

9. Flow Control - For Statement • Example: Dim X as Integer For X = 1 To 10 Step 1 … Next X

10. Flow Control - If Statement • Example: Dim X as Integer X = 1 If X < 100 Elseif Else End if

11. Flow Control - While Statement • Example: Dim X as Integer X = 1 While X < 10 X = X + 1 Wend • See also DO … WHILE

12. Flow Control - Slectect Statement • Example: Dim X as Double X = 1.25 Select Case X Case 1 Case 1.25 Case 1.5 End Select

13. More Information