CST-273 Fall 2008

1. CST-273 Fall 2008 Using VB with MS Applications R. Juhl, Delta College

2. Getting to Know VBA • Visual Basic for Applications (VBA) • VBA works with many applications including Microsoft Office • Use VBA to: • accomplish tasks automatically • Event driven or when a user generates a stimulus • change the application environment • VBA is integrated with all Microsoft Office products • In Office 2007 you have to enable VBA • MS Word, Excel, and Powerpoint • Located in the Developer Tab • MS Access • Located on the “Database Tools “ Tab

3. Getting to Know VBA - Excel

4. Getting to Know VBA - PowerPoint

5. Getting to Know VBA - Word

6. Getting to Know VBA - Access

7. Getting to Know VBA • To enable VBA Click on: • ‘Show Developer tab in the Ribbon’

8. Getting to Know VBA • The “Developer Tab” is now added:

9. Getting to Know VBA • Note that other 2007 Office products: • Visio • Project • Publisher • and previous products in Office 2003 do not use the Ribbon Interface.

10. Getting to Know VBA • You access VBA in these products by selecting the: • Tools  Macro menu

11. Getting to Know VBA • Other product s use VBA, too: • In reference to our text book…….. • MSDN or Microsoft Developer Network website • http://msdn.microsoft.com/en-us/isv/bb190538.aspx • States Microsoft's position on Licensing VBA • Contains Support links for using VBA

12. Getting to Know VBA • Use VBA to automate : • Documents • Compose letters by picking predetermined text • Spreadsheets • Click a button for predefined automatic data entry • Database • Importing Data and performing calculations • Labor Loading

13. Getting to Know VBA • Use VBA to automate : • Customizing an application interface • Create new version of features • Exclude unwanted information • Include only the information needed • Performing calculations • Change data before you use it • Simplify by using small steps rather than large

14. Looking at the IDE • Select the ‘Visual Basic’ Icon from the ‘Developer’ Menu

15. Looking at the IDE • The Integrated Development Environment Parts

16. Looking at the IDE • The Integrated Development Environment Parts • Project Explorer • Contains • List of items in the project • Contains all project elements in a single file • VB Application exists within project file

17. Looking at the IDE • The Integrated Development Environment Parts • Properties • Contains • Properties or Attributes of the object selected • Color • Words on it • Boolean attributes • Password

18. Looking at the IDE • The Integrated Development Environment Parts • Code • Contains • The VB code needed to make the application • Subroutines • Functions • Procedures

19. Looking at the IDE – VBA Toolbox

20. Looking at the IDE • IDE Supports Forms: You decide: • What appears on the form • How the form acts / user interaction • Use the toolbox • To create controls used in forms • Text Box, Labels, Check box, etc… • More in Chapter 7

21. Looking at the IDE • A note on objects • Your VBA programs will contain objects • A form could be an object • The Apple Metaphor • Properties ? • Methods ?

22. Looking at the IDE • A note on objects • Your VBA programs will contain objects • A form could be an object • The Apple Metaphor • Properties ? (color, taste) • Methods ?

23. Looking at the IDE • A note on objects • Your VBA programs will contain objects • A form could be an object • The Apple Metaphor • Properties ? (color, taste) • Methods ?

24. Looking at the IDE • A note on objects • Your VBA programs will contain objects • A form could be an object • The Apple Metaphor • Properties ? (color, taste) • Methods ? ( pick, eat )

25. Looking at the IDE • A note on objects • Your VBA programs will contain objects • A form could be an object • The Apple Metaphor • Properties ? (color, taste) • Methods ? (pick, eat ) • Events ? ( changes color over time ) o

26. Setting up security (to run macros / VBA) Looking at the IDE o

27. Looking at the IDE • Setting up security (to run macros / VBA) • Select the Developer or Database Tools Tab • Click Macro Security • Using Project Explorer • Interact with the objects that make up the project • Objects listed depend on the application

28. Looking at the IDE • Project Explorer - Word Project • Documents • Doucment Templates • Project Explorer - Excel Project • Worksheets • Workbooks • Project Explorer - Access Project • Database(s)

29. Project Explorer can also contain other objects: • Forms – User interface • Modules – Nonvisual code • Class Modules – Contain new object to build Looking at the IDE

30. Project Explorer – Right click to find context menu properties (more in chapter 3) Looking at the IDE

31. Looking at the IDE • Working with special entries • In Word you may see a reference folder

32. Looking at the IDE • Working with special entries • In Word you may see a reference folder

33. Looking at the IDE • Working with special entries • In Word you may see a reference folder • It may contain templates • In many cases you can’t modify objects in the special folder • Its used for information only • You normally don’t need to work with these objects

34. Looking at the IDE • Using the Properties Window • Most objects in the VBA IDE have properties • Properties describe objects • Some common properties are: • text • boolean • i.e. visible property may be • True • False

35. Looking at the IDE • Using the Properties Window • Object properties may also have: • Numeric value • i.e. to place a control use the • left / top properties set to numbers • Drop down list • Color property • For help with properties • Highlight the property and press F1

36. Looking at the IDE

37. Looking at the IDE • Using the Code Window • Where you write application code • Sub SayHello() • ' Display the message box • Result = MsgBox("Click a Button Please", vbYesNoCancel, "A Messege") • 'See which button the user pressed. • MsgBox Result • End Sub

38. Looking at the IDE • Using the Code Window • Where you write application code • Sub SayHello() • ' Display the message box • Result = MsgBox("Click a Button Please", vbYesNoCancel, "A Messege") • 'See which button the user pressed. • MsgBox Result • End Sub

39. Looking at the IDE • Using the Code Window • To open existing code • Click on the module folder and open the module you want to edit.

40. Looking at the IDE • Using the Code Window • The code window also automatically appears when you perform other tasks • When you double click on a forms control: • The code window opens for that control • Allows you to edit code for the event handler that corresponds to the control

41. Looking at the IDE • Creating a new code window • From the IDE select Insert Module • It appears as a new code window • It appears as a module in the Project / Module folder

42. Looking at the IDE • Typing text in the Code window • If you make an error VBA checks what you type.

43. Looking at the IDE • The code window has a context menu (right click)

44. Looking at the IDE • Code context help is available by right-clicking the key code word

45. Looking at the IDE • Using the Immediate window • Select View  Immediate Window

46. Looking at the IDE • You can type code directly into the immediate window to test…

47. Looking at the IDE • The immediate window remembers the variable state…. • Using MyVarin the previous slide and typing MsgBoxMyvar

48. Looking at the IDE • IDE Object Browser • Use View  Object Browser

49. Looking at the IDE • The Object Browser • Provides all the VBA objects at your disposal • Objects are the fundamental building block of Visual Basic; • Nearly everything you do in Visual Basic involves modifying objects. • Every element of Microsoft Word — documents, tables, paragraphs, bookmarks, fields and so on — can be represented by an object in Visual Basic.

50. Looking at the IDE • The Object Browser • Provides all the VBA objects at your disposal • Objects are the fundamental building block of Visual Basic; • Nearly everything you do in Visual Basic involves modifying objects. • Every element of Microsoft Word — documents, tables, paragraphs, bookmarks, fields and so on — can be represented by an object in Visual Basic.