Using VB and VBA for MS Applications

1. Using VB with MS Applications R. Juhl, Delta College

2. 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.

4.

5.

6.

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

8. • The “Developer Tab” is now added:

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

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

11. • 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. • 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. • 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. • Select the ‘Visual Basic’ Icon from the ‘Developer’ Menu

15. • The Integrated Development Environment Parts

16. • 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. • The Integrated Development Environment Parts • Properties • Contains • Properties or Attributes of the object selected • Color • Words on it • Boolean attributes • Password

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

19.

20. • 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. • A note on objects • Your VBA programs will contain objects • A form could be an object • The Apple Metaphor • Properties ? • Methods ?

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

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

24. • 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. o • 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 )

26. o • Setting up security (to run macros / VBA)

27. • 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. • 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

30. • Project Explorer – Right click to find context menu properties (more in chapter 3)

31. • Working with special entries • In Word you may see a reference folder

32. • Working with special entries • In Word you may see a reference folder

33. • 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. • 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. • 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 F 1

37. • 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. • 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. • Using the Code Window • To open existing code • Click on the module folder and open the module you want to edit.

40. • 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. • 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. • Typing text in the Code window • If you make an error VBA checks what you type.

43. • The code window has a context menu (right click)

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

45. • Using the Immediate window • Select View  Immediate Window

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

47. • The immediate window remembers the variable state…. • Using MyVar in the previous slide and typing MsgBox Myvar

48. • IDE Object Browser • Use View  Object Browser

49. • 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. • 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.

51. • The Object Browser • Lists • Libraries of: • Objects • Modules • Forms • Methods • Projects And their properties / Functions

52. • The Object Browser • Applications • Library has the name of the application

53. • The Object Browser Office Library • Lists • Objects that Microsoft Office supports • Used to find support for Office Assistant • 2003 • The Object Browser also contains the current • VBA project

54. • The Object Browser Lists • StdOLE Library • Object Linking and Embedding • Embed a picture in a document • Link one document to anther • VBA Library • Special Utility Objects • MsgBox function

55. • The Object Browser Searching • Type in the keyword / method etc….

56. • You can cut & paste from the Object Browser by: • Selecting the item • Clicking the Double Document Icon • Copies to the clipboard • Then pasting directly into your application