Visual€Basic€Macro Examples - microsoftproducttraining.com

1 Visual Basic Macro Examples 1. ABOUT THIS BOOK. microsoft excel Visual Basic Examples Release 5, February 19, 2008. Copyright 2003-2008, LLC. The Examples and information in these books are for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. The easiest way to search for a subject is to click on the Search tab and type in key words in lower case. Then choose a topic to display. To turn search highlighting off: Click on Options Click the menu item "Search Highlighting Off If on, then when you do a search and click on a topic, all matching words are highlighted.

2 If you change the setting, you will need to display a different topic and then 1. click back on the original topic to see the change. To copy an example : Highlight the code you want to copy Right click and select "Copy". Or, press CTL-C. Topics can be stored in a favorites list for quick access. First display the topic if it is not already displayed. Then click on the Favorites tab and click the "Add" button. 2. 1. ABOUT THIS BOOK ..1. microsoft excel Visual Basic Examples ..1. 2. New Examples ..21. 3. GENERAL INTEREST Problems Accessing Visual Basic The Menu Editor And excel 97 Determining the excel Version ..22. Protecting Your Code From excel 2000 VBA vs.

3 excel 97 excel Runtime Version ..23. Country And Language Versions Of excel ..24. High Security And Enabling How To Determine Regional Settings or Properties ..25. Controlling The Cursor Displaying the Developer 29. Using The Immediate Window ..29. How To Clean Your Useful Module Level Statements ..30. Recovering Code From A Corrupt File ..30. Naming Your Visual Basic Projects ..31. Docking Windows In The Visual Basic Editor ..31. Books On Learning Windows API ..32. Disabling Macro Virus Check ..32. Translating 123 Macros To excel Macros ..32. Converting Lotus 1-2-3 Macros To Visual The Equivalent Of A Lotus 1-2-3 Macro 4.

4 ADD-INS ..34. Creating Add-Ins ..34. Certification of Your Add-ins ..34. Running Add-In Procedures From Other How To Create XLL' Creating COM Using DLL Functions In excel ..36. Problems With Add-Ins - ActiveWorkbook Problem ..37. Installing Add-Ins Via Visual Basic Using Solver With Visual 5. MODULES ..40. Naming Your Modules And Copying Modules ..40. Showing Just A Single Procedure ..40. Removing Modules Via Visual Basic Delete Modules With Removing All Modules From A Workbook ..41. Exporting And Importing Modules ..42. 3. Deleting A Macro Via Listing The Subroutines In A Using A Class Module To Capture Events In excel .

5 44. Declaring A New WithEvent Class ..45. 6. VARIABLES AND THEIR USE ..47. Declaring Variables ..47. Variable Names To Avoid ..49. Environment Variable Values ..49. About Local Variables, Module Variables, And Global Global Or Public Variables In A UserForm's Code Module ..50. Actions That Reset Setting Variables To Refer To Cell Sharing Variable Values Between Resetting Or Clearing An Object Variable ..52. Disabling Toolbar Right Click ..52. Testing To See If An Object Variable Is Setting An Object Variable To A Column:..53. Storing Values In Workbook Names ..53. 7. ARRAYS ..54. Determining The Size Of An Passing An Array To A Subroutine.

6 54. Clearing Arrays and Object Variables ..54. How To Get The Unique Entries In A Getting A List Of The Unique Items In A List ..56. Storing Range Values In An Setting Array Size Copying Array Values To A Range Of How To Transpose A Range Of Values ..62. Editing Cells The Fast Way ..63. Sorting An Array ..64. Writing Arrays To A Worksheet ..66. Assign Range Values Directly To An Looping through an Aray of 8. Using Case Instead Of If Using A Select Statement To Take Action ..69. Determining What Type A Value Using Select As A Multiple Or Statement ..70. How To Return To Your Starting Location ..71. Processing All The Entries In A Column.

7 71. Some Simple Loop Examples ..73. 9. CELL AND 75. excel 2007 versus Prior EDITING, COPYING, AND PASTING ..75. Copying And Pasting ..75. Writing Large Numbers To Cells ..76. 4. A Technique To Writing Text To The Clearing The Clipboard After A Copy An example Of How To Copy One Range To Another Range ..79. ROW Determining The Currently Selected Cell's Testing Whether A Row Is How To Select All The Rows In A Selecting Rows Based On Cell Entries ..81. Select Odd-Numbered Rows ..82. How To Determine If A Selection Has Non-Contiguous Determining If A Row Or Column Is Empty ..82. Duplicating The Last Row In A Set Of Data.

8 83. Inserting Multiple Insert Rows And Sum Formula When Cells An example Of Inserting Rows And Sum Deleting Rows ..87. Deleting Sets Of Rows ..87. Deleting Error Rows ..88. Deleting Duplicate Remove/Highlight Duplicate Rows ..90. Conditionally Deleting Rows ..90. How To Delete Blank Examples That Delete Rows Based On A Cell's Value ..93. Auto Sizing Rows When Cells Are COLUMN Examples ..94. Making Certain That A Selection Is Only A Single Column Or Row Wide ..94. Converting Column Letters To Column Numbers ..95. Converting Alphabetic Column Labels To Numeric Column Labels ..95. Getting The Letter Of A Comparing Two Columns.

9 96. How To Convert Alphabetic Column Labels To Numeric ..98. How To Copy Multiple Columns At A How To Delete Columns In Multiple Sheets At One Time ..98. How To Insert Columns In Multiple Sheets At One Time ..99. An Insert A Column And Formula Deleting Columns .. 100. Setting Column Widths .. 101. Setting Column Widths And Row Heights .. 101. Setting Column Widths To A Minimum Width .. 102. Setting Column Width And Row Height In 103. Determining The Populated Cells In A Column Of Data .. 103. FINDING THE FIRST BLANK 104. Determining Where The First Blank Is In A 104. Finding The First Blank Cell In A 105. How To Find The Next Available Row In 105.

10 5. SELECTING THE LAST 105. The VBA Equivalents Of Ctrl-Shift-Down And Determining The Last Cell In A 106. Finding The Last Entry In A Column .. 107. Finding The Last Non-Blank Cell In A Column .. 108. Finding The Last Entry In A Row .. 108. Determining The Last Cell In A Row .. 109. Finding The Last Cell, Last Row, or Last 110. Selecting from the ActiveCell to the Last Used Cell .. 110. Determining The Last Cell When Multiple Areas Are Selected .. 111. Finding the Last Row and Column Numbers .. 112. Fill 113. COLOR AND FORMAT 113. Color Every Other Row Gray And Bold Text .. 113. Coloring Cells Based On Their Value .. 114.