Office VBA Macros You Can Use Today: Over 100 Amazing Ways to Automate Word, Excel, PowerPoint, Outlook, and Access
By Juan Pablo González, Cindy Meister, Suat Ozgur and
3/5
()
About this ebook
Related to Office VBA Macros You Can Use Today
Related ebooks
Guerilla Data Analysis Using Microsoft Excel Rating: 0 out of 5 stars0 ratingsPower Query for Power BI and Excel Rating: 0 out of 5 stars0 ratingsDAX Patterns: Second Edition Rating: 5 out of 5 stars5/5Excel VBA: A Beginners' Guide Rating: 4 out of 5 stars4/5Excel VBA Recipes Rating: 4 out of 5 stars4/5Excel VBA - Intermediate Lessons in Excel VBA Programming for Professional Advancement: 2 Rating: 0 out of 5 stars0 ratings15 Most Powerful Features Of Pivot Tables: Save Your Time With MS Excel Rating: 0 out of 5 stars0 ratingsExcel Dynamic Arrays Straight to the Point 2nd Edition Rating: 5 out of 5 stars5/5Secrets of MS Excel VBA Macros for Beginners !: Save Your Time With Visual Basic Macros! Rating: 4 out of 5 stars4/5VBA for Excel: Programming VBA Macros - The Easy Introduction for Beginners and Non-Programmers Rating: 3 out of 5 stars3/5Mastering VBA for Microsoft Office 365 Rating: 5 out of 5 stars5/5Mastering Excel Macros: Introduction: Mastering Excel Macros, #1 Rating: 4 out of 5 stars4/5Excel VBA Programming: Automating Excel through Visual Basic for Application Rating: 0 out of 5 stars0 ratingsExcel Power Pivot and Power Query For Dummies Rating: 3 out of 5 stars3/5Excel 2019 Bible Rating: 5 out of 5 stars5/5Secrets of Access Database Development and Programming Rating: 5 out of 5 stars5/5Excel VBA: A Step-By-Step Tutorial For Beginners To Learn Excel VBA Programming From Scratch: 1 Rating: 4 out of 5 stars4/5More Excel Outside the Box: Unbelievable Excel Techniques from Excel MVP Bob Umlas Rating: 0 out of 5 stars0 ratingsMrExcel 2021: Unmasking Excel Rating: 0 out of 5 stars0 ratingsExcel Gurus Gone Wild: Do the IMPOSSIBLE with Microsoft Excel Rating: 4 out of 5 stars4/5MrExcel 2022: Boosting Excel Rating: 0 out of 5 stars0 ratingsThe Programmer’s Guide to Microsoft Access Rating: 5 out of 5 stars5/5Excel 2019 PivotTables: Easy Excel Essentials 2019, #1 Rating: 5 out of 5 stars5/5Access 2019 Intermediate: Access Essentials 2019 Rating: 0 out of 5 stars0 ratings
Applications & Software For You
How to Create Cpn Numbers the Right way: A Step by Step Guide to Creating cpn Numbers Legally Rating: 4 out of 5 stars4/5Excel : The Ultimate Comprehensive Step-By-Step Guide to the Basics of Excel Programming: 1 Rating: 5 out of 5 stars5/5Logic Pro X For Dummies Rating: 0 out of 5 stars0 ratingsLearn to Code. Get a Job. The Ultimate Guide to Learning and Getting Hired as a Developer. Rating: 5 out of 5 stars5/5Sound Design for Filmmakers: Film School Sound Rating: 5 out of 5 stars5/5The Best Hacking Tricks for Beginners Rating: 4 out of 5 stars4/5Blueprints to Building Your Own Voice-Over Studio Rating: 0 out of 5 stars0 ratingsDigital Video Production Handbook Rating: 0 out of 5 stars0 ratingsGarageBand For Dummies Rating: 5 out of 5 stars5/5Adobe Illustrator CC For Dummies Rating: 5 out of 5 stars5/5Start Your Own Podcast Business: Your Step-By-Step Guide to Success Rating: 5 out of 5 stars5/5Synthesizer Cookbook: How to Use Filters: Sound Design for Beginners, #2 Rating: 3 out of 5 stars3/5Blender 3D By Example Rating: 4 out of 5 stars4/5The Most Concise Step-By-Step Guide To ChatGPT Ever Rating: 3 out of 5 stars3/5GarageBand Basics: The Complete Guide to GarageBand: Music Rating: 0 out of 5 stars0 ratingsBlender 3D Basics Beginner's Guide Second Edition Rating: 5 out of 5 stars5/5Adobe Illustrator: A Complete Course and Compendium of Features Rating: 0 out of 5 stars0 ratingsMastering QuickBooks 2020: The ultimate guide to bookkeeping and QuickBooks Online Rating: 0 out of 5 stars0 ratingsSix Figure Blogging In 3 Months Rating: 4 out of 5 stars4/5Beautiful eBooks With Scrivener Rating: 4 out of 5 stars4/5Animation for Beginners: Getting Started with Animation Filmmaking Rating: 3 out of 5 stars3/5Mastering ChatGPT Rating: 0 out of 5 stars0 ratingsBlender 3D Printing Essentials Rating: 0 out of 5 stars0 ratingsKodi User Manual: Watch Unlimited Movies & TV shows for free on Your PC, Mac or Android Devices Rating: 0 out of 5 stars0 ratingsGray Hat Hacking the Ethical Hacker's Rating: 5 out of 5 stars5/5
Reviews for Office VBA Macros You Can Use Today
4 ratings0 reviews
Book preview
Office VBA Macros You Can Use Today - Juan Pablo González
versions.
Introducing the Visual Basic Editor
Understanding a concept begins with defining unfamiliar terms. The terminology required to understand Visual Basic for Applications (VBA) is fairly straightforward and is introduced throughout the book. In order to start learning, you need to become familiar with the following terms:
Accessing Visual Basic Editor (VBE)
The VBE (the programming environment) can be accessed from any of the Microsoft Office programs by opening the program and then either pressing Tools | Macro | Visual Basic Editor from the Menu or using the keyboard shortcut: Alt+F11. Clicking the right mouse button on some objects in the Microsoft Office programs gives the option to View Code; others have the Visual Basic Editor as an option on the drop-down menu. Any of the methods mentioned can be used to open the VBE.
For consistency, we use the VBE for Microsoft Excel to illustrate the functionality of the VBE in this book. When one of the other applications varies from the way Excel uses the VBE, we note this exception.
When the VBE opens, it pops up in a new window. An example for Excel is shown in Figure 1.
Figure 1 –Microsoft Excel Visual Basic Editor
The VBE window is just like any other window. It has a Title bar, a Menu bar, and a Tool bar, and likely contains some familiar options or icons, and some that you might not have seen before. The main body of the VBE window consists of one or more sub-windows.
Each individual Office program launches its own VBE. When running multiple programs, multiple VBE windows can be open at the same time without interfering with each other. The coding in any window is relevant only to the program that launched it.
Touring the VBE Toolbar
Figure 2 depicts the Excel VBE Menu bar and Standard toolbar, collectively called the Visual Basic Editor toolbar in this book. You can perform various tasks from this toolbar when using or writing code.
Figure 2 – The VBE Toolbar
Some of the buttons available on the VBE toolbar are described in the table below.
Setting VBE Options
The VBE has a set of options that allow for customizing the interface to individual user preferences. From the VBE Toolbar, select Tools | Options to display the Option dialog shown in Figure 3.
Figure 3 – The VBE Options Menu
The Options Dialog in the VBE affords users the opportunity to modify the way the VBE looks and behaves. From the Editor tab, make sure that all the checkboxes are checked. While the individual settings are not covered in detail in this book, we do recommend checking the box for Require Variable Declaration. It is not checked by default, but it is good programming practice to enable this option.
Tip:
For those interested, more detail and online VBA Training can be found online at www.VBAExpress.com, as well as other websites.
Using Project Explorer
To navigate within the various elements in the programming environment, there is one very useful window: the Project Explorer. By default, it is located at the top-left of the VBE; it is shown in Figure 4.
Figure 4 – The Project Explorer Window
The Project Explorer window should be visible by default. If it is not, or if you accidentally close it, it can be shown by any of the following methods:
Select View | Project Explorer from the VBE Toolbar.
on the VBE Toolbar.
Use the Ctrl+R keyboard shortcut.
The Project Explorer is much like any other Windows Explorer interface. It shows a hierarchy of objects from which to choose. Branches to expand and/or choose elements to view in detail in one of the other panes are shown with the familiar + or –.
Understanding VBA Project
The first item in the Project Explorer (see Figure 4) is called VBAProject(Book1). Book1 is simply the name of the Excel workbook being used in the screen capture. When the workbook is saved, the name changes to the name and file extension of the workbook. For example, if the workbook were saved as MyWorkbook.xls, then the Project Explorer window would also change to display VBAProject(MyWorkbook.xls). If multiple workbooks are open, each one is listed in the Project Explorer alphabetically, as well as any loaded Add-ins. For simplicity, we assume that just one workbook is open at this time.
Word displays the Normal project as open in the VBE if a document is open. It loads when Word is launched. The file name of the Normal project is normal.dot and it is Word’s default template; it is always in use when Word is in use, much like an Add-in.
The first item within the VBAProject is Microsoft Excel Objects. As with Windows Explorer, any of the items with a plus (+) or a minus (–) can be expanded or retracted by double-clicking on the name or by single-clicking the + or –.
These are some of the primary differences in the user interface between the five applications:
Working with Modules
VBA has several places in which to store code, but the vast majority of code is stored in Standard modules, regardless of the application. Before a module can be used, it must first be added to the project. From the VBE Toolbar, select Insert | Module as shown in and select Module.
Figure 5 – Inserting a Module
The only limitation to the number of modules an Office file can have is the limitation imposed by the amount of the computer’s memory. Each module can have one or many procedures. Writing a project using modules to group similar procedures together helps keep your project better organized.
In this book, the word module
is used to indicate a Standard module, though the word Standard
is rarely used to describe it; it’s just called a module
. Class modules can also be used in VBA Projects; these are referred to as Class modules in this text.
In our next graphic, the project is expanded to display the contents. Double-click to hide the contents. Click on the Toggle Folders button to display the projects as folders, as shown in Figure 6.
Figure 6 – Toggle Folders View
Once the module is inserted, be sure to give it a meaningful name. If you leave the name as Module 1, when the project is revised later there will be no immediate clue as to the content of that module. Applying meaningful, descriptive names to modules makes troubleshooting or retooling much