VBA Macro Codes Examples Collection

---

Visual Basic for Automation (Macros Collection)

---

Workbook

Worksheets

1. Sort all sheets
2. Remove blank worksheets from Current workbook
3. Color all Sheet Tabs
4. Remove color from all sheet tabs
5. Protect all worksheets
6. Remove Password (Unprotect) all worksheets
7. Insert ‘n’ number of Blank Worksheets

Range

1. Color a range based on its percentage value

Select

1. Invert the Selection

Number


Rows/Columns

1. Delete Blank Rows
2. Remove Empty Columns

Text

1. Change Case (Upper, Lower, Proper, Sentence and Toggle)
   
2. Remove Extra Spaces (Left, Right, Both sides and from Inside)

Format

1. Partly Background Color a Cell (Coloring only a portion of Cell)

Form

1. Resize Form during Run-Time

Event Macro

1. Example of Worksheet_Change Event Macro

Miscellaneous

1. Access Internet Explorer (Search Engine Example)
2. Change Image Location
3. Copy Excel Worksheets to MS Word

 

---

Use of Random Function (Rnd) and Select Case statement

---

Brief Description:
Here, I present the code to use random function in association with the Select Case statement. It use random function to generate a random value of S, H, D, or C for the four card suits (spades hearts diamonds or clubs). When user clicks the button, an input box pops up to accepts a value of S, H, D, C If the guess matches the random number respond with “Good guess”, if not then it respond with “Bad Guess”.

View VBA Macro Code Here

---

Delete or Remove Event Macros, VBA Code and Module

---

Brief Description: 

This macro will delete all event macros, sub routines and user defined functions from Active workbook. This requires reference to “Microsoft Visual Basic for Applications Extensibility 5.3” and trusted access to VBA project model.(Developer –> Macro security –> Trust access to VBA project model).

View VBA Macro Code Here

---

Close all saved workbooks

---

Brief: Imagine you’re working on some important excel files and gradually your taskbar is full of open workbooks. Some have similar names (not same ofcourse) and now you want to close all the saved workbooks and want unsaved workbooks to remain open. This macro will definitely solve your purpose.

View VBA Macro Code Here

---

Force Save and Unsave of Workbooks

---

Brief: Sometimes event macros are of great help. These two macros are example of that. While one macro protects a workbook from saving any changes, other macro force changes to be saved, if the former macro is present in the workbook. Both can be considered hacks of excel programming or a trick, but they’re always useful and as an excel programmer you should know these two fundas.

Macro #1: Make Workbook unacceptable to any changes.
Pre-requisite: Event Programming, VBA and Excel
Logic: Use of Workbook’s Saved property and Workbook’s BeforeSave event. As soon as excel encounter any changes, it changes Saved property value to FALSE so when you attempt to save the workbook, excel checks this property value and save the workbook, if it founds to be FALSE but if it is TRUE, then it ignores the operation.
Now we use BeforeSave event and turn this property value to TRUE everytime BeforeSave event fired. So this makes workbook immune to any changes.
Code:
‘PLACE THIS CODE IN THISWORKBOOK, NOT IN SHEETS OR MODULE
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
‘CHEATING APPLICATION BY FORCING SAVED PROPERTY TO BE TRUE
    ThisWorkbook.Saved = True
End Sub

Macro #2. Force changes to be saved in such workbooks (as mentioned above).
Pre-requisite: Event Programming, VBA and Excel
Logic: Disable Events, Save the workbook and then enable events again. Yes, it is that simple and here is its code.
Code:
 Sub ForceSave()
    On Error Resume Next
‘THIS WILL DISABLE ALL EVENTS INCLUDING Workbook_BeforeSave.
    Application.EnableEvents = False
‘SAVE THE WORKBOOK
    ActiveWorkbook.Save
‘ENABLE THE EVENTS AGAIN
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Note: You fill find workbooks with Macro#1 generally VBE password protected, in such case if you want to use Macro#2, either write it in Personal workbook and assign a shortcut key or assign it to a toolbar button.

---

Invert the Selection

---

Description: This will deselect the selected and will select the deselected range in Used range of Active sheet.

View VBA Macro Code Here

---

UnProtect all WorkSheets in the WorkBook

---

Description: This macro will protect all the worksheets of the Active workbook with the password you will provide to them.
View VBA Macro Code Here

---

Protect all WorkSheets in the WorkBook

---

Description: This macro will protect all the worksheets of the Active workbook with the password you will provide to them.
View VBA Macro Code Here

---

Sort all worksheets on the basis of Sheet names

---

Description: This VBA macro code will sort all the worksheets on the basis of their corresponding names.

View Code Here

---

Remove Color of all Sheets tabs

---

Description: This VBA macro code will remove colour from all worksheets tabs in the activeworkbook.

View Code Here

---

Color all sheet tabs in the workbook

---

Description: This VBA Macro will colour all the worksheet tabs present in the Active/Current Workbook.

View VBA Macro Code Here

---

Delete/Remove Blank/Empty Worksheets

---

Description: This VBA Macro will delete blank worksheets from the active workbook.
View VBA Macro Code Here

---

Insert ‘n’ number of worksheets in Active workbook

---

Description: This VBA macro code will allow you to insert ‘n’ number of worksheets in Active workbook.

View VBA Macro Code Here

---

Copy Excel Worksheets to Microsoft Word

---

Description: This VBA Macro code will copy all worksheets of Active workbook to Microsoft Word Document.

View VBA Macro Code

---

Change Picture or Image Location

---

Query Source  : Excel Macros Google Group
Solution Type  : VBA Macro
Query by              : Carlos Pedro (Sister Ray)
Solution by         : Ashish Jain (MCAS; MCA; Lead Trainer, Success Electrons)

---

Query / Problem: I’m trying to change a Picture object that is included in a Excel Worsheet, since I haven’t had much sucess I was wondering if it is possible to change the image source (the file that is being displayed) using VBA.

---

View Solution Here

---

Color only a part of cell using Macro or REPT Function

---

Query Source    : Excel Macros Google Group
Solution Type   : VBA Macro
Query by               : ExcelUser777
Solution by         : Ashish Jain (MCAS; MCA; Lead Trainer, Success Electrons)

---

Query / Problem:
HI
Basically i’d like to only show coloring in part of a cell, so for example
Cell A1 is grey only 33% of the cell is grey -I’d like to color one cell grey 33%. I have several cells to color, different percentages. Once I see one macro I can create the other percentages. Is that possible with a macro?
Appreciate all your help.
Excel 2003 preferably
Thanks,
ExcelUser777

---

Solution:

1. Using Macro

i)   Select the range (It should be in terms of percentage, else change the following code accordingly.)
ii)  Press Alt+F11.
iii) Paste the following code in Code window.
iv) Run the macro and enjoy.

View VBA Macro Code here.

---

2. Using REPT Function

i) Use REPT function to repeat the pipe symbol, | as displayed in formula box Below.

---

Delete/Remove Blank/Empty Rows/Columns

---

Description: This VBA Macro will delete blank rows from the selection in the active worksheet.
View VBA Macro Code Here

Description: This VBA Macro will remove empty columns from the active worksheet.
View VBA Macro Code Here
 

---

Access Search Engines on Browser (Internet Explorer) using VBA/Excel

---

Query Source   : Excel Macros Google Group
Solution Type   : Internet Explorer and VBA
Query by          : Dave K
Solution by       : Ashish Jain (MCAS;MCA;Lead Trainer, Success Electrons)

---

Query / Problem:
Is there code that could perform the following:

1. Copy the data that lies in the active cell.
2. Go to an open window (in explorer or mozilla) of http://www.ebay.com (or open an entirely new window of ebay.com, if that makes it easier).
3. Paste the data into the search box at http://www.ebay.com (which by default is the active field when a new window of ebay is opened)
4. Hit “enter” at ebay to display the search results.

I am new to VBA and computer programming, but any suggestions or guidance you can provide would be very much appreciated. 

Solution: 
1. Press Alt+F11. (To write the macro in Code Editor.)
2. Go to Tools –> References –> Select “Microsoft Internet Controls”.

3. Copy-Paste the code available here.

4. Now, run this macro as desired and whenever required.

---

Resize Form using Worksheet Event macro

---

Query Source  : Excel Macros Google Group
Solution Type   : Forms and VBA
Query by          : Kanwaljit
Solution by       : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:
I have a User Form which needs to take a specified size depending upon the data source. Data Source is in Range DataS1 and DataS2. The following range (named as “Dimensions”) shows the dimensions which the User Form needs to have when ever we use DataS1 or DataS2.UserFormSpec    DataS1      DataS2
FormWidth                   50            100
FormHeight                  40             80

Cell A1 of Sheet1 contains a dropdown containing the names of the datasources, i.e., DataS1 and DataS2. The following code is used to show the UserForm.

Private Sub MyForm_Click()
        UserForm.Show
End Sub

What is wish is that when I select DataS1 or DataS2 from Cell A1, the UserForm width and height changes according to the numbers in range “Dimensions”.

---

Solution:

Brief Description: Solution requires use of Event Macros, VBA, Forms, Object Hierarchy. Even if you don’t know refer below to solve such problems in a very easy manner.
As you want to re-size the form whenever value in Cell A1 of Sheet1 changes, you mustuse event macro to achieve this. Since event macros are automatically fired in response to associated events. Following are some examples of excel events for which event macros are already in the MS Excel applications:
      Name of Event                           |             Event Macro
1. Opening of Workbook              |          Workbook_Open()
2. Double click a cell                     |          Worksheet_BeforeDoubleClick
3. Activate a Sheet                        |          Worksheet_Activate()
4. Pivot Table Update                  |           Worksheet_PivotTableUpdate
5. Change Cell Value                     |            Worksheet_SelectionChange
etc etc etc

Since, each event macro is associated with an event, it is also associated with a particular object. Like, if defined then a workbook event macro is associated to the workbook where it is defined. So if you’ve written a workbook event macro in Workbook A then it won’t affect Workbook B. Same for Sheets, pivot tables etc.

Full Solution to the Query:
1. Open the desired Excel file.
2. Go to VBE by pressing Alt+F11.
3. Under Microsoft Excel Objects for this workbook, Double Click on Sheet1 to open it’s code window.
4. Paste the Code Available here in it.

5. Test your Macro by changing the value of Cell A1.

---

 Example: 

---

Remove Extra Spaces from a Cell Value – Text VBA Macro

---

Left Trim
Description: This module will remove extra spaces from left side of selected cells. For e.g if a cell value is ”    excel    “ then after running the macro it will become “excel    “. (Notice spaces from left side has been removed.)

View VBA Macro Code Here

---

Right Trim
Description:This module will remove extra spaces from right side of selected cells. For e.g if a cell value is ”      excel   “ then after running the macro it will become ”     excel”. (Notice spaces from right side has been removed.)

View VBA Macro Code Here 

---

 Trim from Both sides
Description:This module will remove extra spaces from both sides of selected cells. For e.g if a cell value is ”      excel   “ then after running the macro it will become “excel”. (Notice spaces from both sides has been removed.)
View VBA Macro Code Here

---

Remove Extra Excessive Spaces
Description:This module will remove extra spaces from both sides and from inside the selected cells. For e.g if a cell value is ”      excel       items   “ then after running the macro it will become “excel items”. (Notice spaces from left , right and between excel and items words has been removed.)
View VBA Macro Code Here 

---

Change Case (Upper Case, Lower Case, Proper Case, Toggle and Sentence Case)

---

Change Text Case

---

UPPER CASE

Description: A word or sentence is said to be in upper case if its all the letters are in capital letters like this: EXCELITEMS IS BEST COLLECTION OF EXCEL RELATED STUFF.
View VBA Macro Code Here

---

lower case
 Description: A word or sentence is said to be in lower case if its all the letters are in small letters like this: excelitems is best collection of excel related stuff.
View VBA Macro Code Here

---

Proper Case 
Description: A word or sentence is said to be in proper case if first letter of each word is in capital letter and rest are in small letters like this: Excelitems Is Best Collection Of Excel Related Stuff.
View VBA Macro Code Here

---

Sentence case
Description: A sentence is said to be in sentence case if first letter of sentence is in capital letter and rest are in small letters like this: Excelitems is best collection of excel related stuff.
View VBA Macro Code Here

---

ToGgLe CaSe
Description: A word or sentence is said to be in toggle case if first letter of sentence is in capital letter and next is in small letter then next in capital letter and so on like this: ExCeLiTeMs iS BeSt cOlLeCtIoN Of eXcEl rElAtEd sTuFf.
View VBA Macro Code Here

---

Books on Excel, VBA and VSTO Macros

---

Title: Microsoft Office Home and Student 2007
Product by: Microsoft
Product Features:
1. Essential software suite for home computer users makes it a pleasure to complete schoolwork and other tasks
2. Includes 2007 versions of Excel, PowerPoint, Word, and OneNote.
3. Intuitive user interface that exposes commonly used commands; updated graphics and formatting galleries help you to easily produce high-quality documents.
4. Work with confidence and security thanks to the improved automatic Document Recovery tool and the Document Inspector tool, which removes personally identifiable information from your document
5. Enhanced Help system includes online tutorials with step-by-step instructions; includes OneNote, a digital notebook that helps you gather, organize, and search many types of information in one place.

---

Title: Excel 2007 Power Programming with VBA
Author: John Walkenbach
Pages: 1104
Description: This power-user’s guide is packed with procedures, tips, and ideas for expanding Excel’s capabilities with Visual Basic® for Applications. Excel 2007 has a few new tricks up its sleeve, and John Walkenbach helps you make the most of them all.
You’ll learn to customize Excel UserForms, develop new utilities, use VBA with charts and PivotTables, and create event-handling applications. Work with VBA subprocedures and function procedures, facilitate interactions with other applications, build user-friendly toolbars, menus, and help systems, and much more. Get ready to make Excel do your bidding.

---

Title: Excel 2007 Formulas
Author: John Walkenbach
Pages: 840
Description: If you already know your way around the newest Excel and want to concentrate on using formulas to extend its capabilities even further, you’ve come to the right place.

It’s estimated that only about ten percent of Excel users really understand how to get the most out of worksheet formulas. If you’re ready to join that elite group, here’s your book which has:
1. Master operators, error values, and absolute vs. relative references.
2. Find errors and debug formulas.
3. Use pivot tables to eliminate formulas.
4. Create single- and multi-cell array formulas.
5. Build custom functions with VBA.

---

Title: Access 2007 VBA Programmer’s Reference
Authors: Teresa Hennig, Rob Cooper, Geoffrey L. Griffith, Armen Stein
Pages: 1152
Description: Written by Access MVPs and Microsoft Access testers, this resource will give you the tools to leverage Access 2007’s built-in functionality and VBA in order to build more dynamic applications. It explores all aspects of VBA programming for Access and describes the latest innovations. You’ll also find several commonly used techniques and sample code to help you get started, as well as expert tips to make your code easier to maintain.

The expert author team will show you how to create and name variables, manipulate data using Data Access Object (DAO) and ActiveX® Data Object (ADO), handle errors, and create classes. You’ll also learn how to utilize the latest wizards as well as modify and enhance the code that the wizards create. And you’ll learn how to take advantage of key new objects, enhanced macros, and the Office Ribbon.

---

Title: Microsoft Visual Basic 2005 Step by Step
Author: Michael Halvorson
Pages: 608
Description: Visual Basic 2005 focuses on enabling developers to rapidly build applications, with enhancements across its visual designers, code editor, language, and debugger that help accelerate the development and deployment of robust, elegant applications across the Web, a business group, or an enterprise. Now you can teach yourself the essentials of working with Microsoft Visual Studio® 2005 and the new features of the Visual Basic language—one step at a time. With STEP BY STEP, you work at your own pace through hands-on, learn-by-doing exercises. Whether you’re a beginning programmer or new to this specific language, you’ll understand the core capabilities and fundamental techniques for Visual Basic 2005. Each chapter puts you to work, showing you how, when, and why to use specific features of Visual Basic and guiding as you create actual components and working applications for Microsoft Windows®. You’ll also explore data management and Web-based development topics.

---

Title: VBA and Macros for Microsoft Office Excel 2007
Author: Bill Jelen and Tracy Syrstad
Pages: 624
Description: The book assumes that you know Excel well, but there is no need for prior programming experience. This book describes everything you could conceivably need to know to automate reports and design applications in Excel VBA. Whether you want to automate reports for your office or design full-blown applications for others, this book is for you.

“In this day and age of ‘too much information and not enough time,’ the ability to get to the bottom line quickly and in a concise method is what excels companies to the top of their industry. The techniques in this book will allow you to do things you only dreamt of.”
—Jerry Kohl, president of Brighton Collectibles

---

Title: Programming Excel with VBA and .NET
Author: Jeff Webb and Steve Saunders
Pages: 1114
Description: This book teaches you how to use Excel VBA by explaining concepts clearly and concisely in plain English, and provides plenty of downloadable samples so you can learn by doing. You’ll be exposed to a wide range of tasks most commonly performed with Excel, arranged into chapters according to subject, with those subjects corresponding to one or more Excel objects. With both the samples and important reference information for each object included right in the chapters, instead of tucked away in separate sections, Programming Excel with VBA and .NET covers the entire Excel object library. For those just starting out, it also lays down the basic rules common to all programming languages. Developers looking forward to .NET development will also find discussion of how the Excel object model works with .NET tools, including Visual Studio Tools for Office (VSTO).

---

Title: Integrating Excel and Access
Author: Michael Schmalz
Pages: 232
Description: Introducing “Integrating Excel and Access,” the unique reference that shows you how to combine the strengths of Microsoft Excel with those of Microsoft Access. In particular, the book explains how the powerful analysis tools of Excel can work in concert with the structured storage and more powerful querying of Access. The results that these two applications can produce together are virtually impossible to achieve with one program separately.

But the book isn’t just limited to Excel and Access. There’s also a chapter on SQL Server, as well as one dedicated to integrating with other Microsoft Office applications.

---

Title: VBA for Modelers: Developing Decision Support Systems Using Microsoft® Excel
Author: S. Christian Albright
Pages: 204
Description: Learn more effective modeling techniques than ever before! VBA FOR MODELERS: DEVELOPING DECISION SUPPORT SYSTEMS USING MICROSOFT ® EXCEL shows you the easy way to automate methods and models and create special applications. You’ll learn sophisticated techniques through a simple and clean interface, so there’s no more long nights of trying to make it work. And because it works with Microsoft ® Excel, you’ll be saving time while the program crunches all the numbers.

---

Title: Visual Studio Tools for Office 2007: VSTO for Excel, Word, and Outlook
Author: Eric Carter and Eric Lippert
Pages: 1120
Description: This is the definitive book on VSTO 2008 programming, written by the inventors of the technology. VSTO is a set of tools that allows professional developers to use the full power of Microsoft Visual Studio 2008 and the .NET Framework to program against Microsoft Office 2007.

This book delivers in one place all the information you need to succeed using VSTO to program against Word 2007, Excel 2007, and Outlook 2007, and provides the necessary background to customize Visio 2007, Publisher 2007, and PowerPoint 2007. It introduces the Office 2007 object models, covers the most commonly used objects in those object models, and will help you avoid the pitfalls caused by the COM origins of the Office object models. Developers who wish to program against Office 2003 should consult Carter and Lippert’s previous book, Visual Studio Tools for Office.

In VSTO 2008, you can build add-ins for all the major Office 2007 applications, build application-level custom task panes, customize the new Office Ribbon, modify Outlook’s user interface using Form Regions, and easily deploy everything you build using ClickOnce.

---

Title: Pro Office 2007 Development with VSTO
Author: Ty Anderson
Pages: 320
Description: This book is for Professional Microsoft Office application developers (and those aspiring to be so) who are interested in creating enterprise applications on the solid base of Microsoft Office 2007. The complexity of the examples assumes an intermediate–to–advanced level of experience with Microsoft .NET. Proficient VBA developers wishing to take their skills to the next level will also benefit by this book.

This takes you far beyond traditional VBA programming, showing you how to transform Microsoft Office 2007 into a complete enterprise application development platform. By the end of the book, you will be creating your own powerful, customized Office Business Applications (OBAs), using the techniques Ty has taught you throughout. The author demonstrates how to leverage all aspects of the Microsoft Office application platform (covering Word, Excel, Outlook, PowerPoint, Visio, and InfoPath) so you can create OBAs that increase information worker productivity, unlock business data stored in documents, reduce end–user training costs, increase developer productivity, and reduce IT operations costs.

---

Title: VSTO for Mere Mortals(TM): A VBA Developer’s Guide to MS Office Development Using VSTO
Author: Paul Stubbs and Kathleen McGrath
Pages: 736
Description: This is for VBA developers who are interested in migrating their skills to the next generation of Office development. Readers will benefit from a straightforward, practical introduction to writing managed code applications for Word 2003, Excel 2003, and Outlook 2003. Readers will also learn how to create add-ins for the most popular applications for Office 2003 and the 2007 Microsoft Office system using VSTO 2005 SE.

The expert authors provide a wealth of code samples that show off popular features of VSTO, such as smart tags and the actions pane. Sample code also shows you how to customize the new UI features of the 2007 Microsoft Office system, including the ribbon, custom task pane, and Outlook forms region.

---

Title: Beginning Office 2007 Development with VSTO
Author: Vivek Thangaswamy
Pages: 300
Description: This book starts from the beginning, building a solid programming foundation in VSTO for brand–new Office developers and keen enthusiasts. Author Vivek Thangaswamy, a professional application developer and Microsoft MVP, uses his vast experience to help you better understand the ins and outs of VSTO in the context of each of the popular Office applications: Word, Excel, InfoPath, Outlook, PowerPoint, and Visio.

This book takes you beyond the traditional VBA programming boundaries and shows you the new world of transforming Microsoft Office 2007 into a complete enterprise application development platform. By the end, you will be able to create your own fully featured Office extensions using the simple examples that the author lays out for you.

---

Title: Word 2007 Document Automation with VBA and VSTO
Author: Scott Driza
Pages: 500
Description: This book focuses on innovative ways to create customized Word documents and templates. It contains an in-depth introduction to VBA (Visual Basic for Applications), which is the embedded programming language in the Microsoft Office 2007. VBA provides a complete integrated development environment (IDE) that allows for document automation, the process of using an automated template for creating documents. The book also includes coverage of the new features of Word 2007 including Content Controls, programming the Ribbon, and more.

---

Title: Excel 2007 Bible
Author: John Walkenbach
Pages: 912
Description: Whether you’re already a power user or just starting, find out how to get the most out of this major new release of Excel from expert instructor and Microsoft MVP, “Mr. Spreadsheet,” John Walkenbach. Each of the book’s seven parts thoroughly focuses on key elements, so no matter what your level of expertise, you’ll find what you need in the hundreds of examples, techniques, and tips in this comprehensive resource.

This book is a single reference that’s indispensable for Excel beginners, intermediate users, power users, and would-be power users everywhere.It is fully updated for the new release, this latest edition provides comprehensive, soup-to-nuts coverage, delivering over 900 pages of Excel tips, tricks, and techniques readers won’t find anywhere else. John Walkenbach is one of the world’s leading authorities on Excel. Thoroughly updated to cover the revamped Excel interface, new file formats, enhanced interactivity with other Office applications, and upgraded collaboration features. It includes a valuable CD-ROM with templates and worksheets from the book.

---

Title: Office 2007 All-in-One Desk Reference For Dummies
Author: Peter Weverka
Pages: 816
Description: It gives you a set of tools for managing documents and data, analyzing numbers, organizing your e-mail and schedule, building presentations, and designing publications – and this book covers them all! Get the scoop on all the Office programs, find out how to supercharge your productivity by using them together, and much more.

---

Title: Excel 2007 VBA Programming For Dummies
Author: John Walkenbach
Pages: 368
Description: Make Excel 2007 work for you! This clear, nonintimidating guide shows you how to use VBA to create Excel apps that look and work the way you want. Packed with plenty of sample programs, it explains how to work with range objects, control program flow, develop custom dialog boxes, create custom toolbars and menus, and much more.

---

Title: iWork ’09: The Missing Manual
Author: Josh Clark
Pages: 850
Description: With iWork ’09, Apple’s productivity applications have come of age. Unfortunately, their user guides are stuck in infancy. That’s where iWork ’09: The Missing Manual comes in. This book quickly guides you through everything you need to know about the Pages word-processor, the Numbers spreadsheet, and the Keynote presentation program that Al Gore and Steve Jobs made famous.

Friendly and entertaining, iWork ’09: The Missing Manual gives you crystal-clear and jargon-free explanations of iWork’s capabilities, its advantages over similar programs — and its limitations. You’ll see these programs through an objective lens that shows you which features work well and which don’t.

---