Using VB and VBA for MS Applications
Learn how to use Visual Basic and Visual Basic for Applications to automate tasks and modify the application environment in various Microsoft Office products, including Word, Excel, Powerpoint, and Access.
- Uploaded on | 4 Views
About Using VB and VBA for MS Applications
PowerPoint presentation about 'Using VB and VBA for MS Applications'. This presentation describes the topic on Learn how to use Visual Basic and Visual Basic for Applications to automate tasks and modify the application environment in various Microsoft Office products, including Word, Excel, Powerpoint, and Access.. The key topics included in this slideshow are . Download this presentation absolutely free.
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
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
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
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
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