Visual€Basic€Macro Examples - …

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

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

3 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. 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 ..44. Declaring A New WithEvent Class ..45. 6. VARIABLES AND THEIR USE.

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

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

6 82. Duplicating The Last Row In A Set Of Data ..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 ..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.

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

8 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. Coloring Cells example .. 115. Copying Formats From One Sheet To 115. Summing Cells Based On Cell Color .. 116. Outlining A 116. Getting The Formatted Contents Of A Cell .. 116. WORKING WITH 117. Writing Formulas That Require Double 117. The Difference Between Formula And FormulaR1C1 .. 117. Modifying A Cell's 118. Determining If A Cell Contains A Formula .. 119. WORKING WITH 119. Checking For 119. Commenting A Cell With A 119. Working With Comments .. 120. How To Create Or Append A Comment On A 121. Deleting Comments .. 122. Auto-Sizing Comments .. 122. CELL Examples .. 123. Determining What Is In A 123. Determining Information About A Cell .. 124. Reading And Writing Cell Values Without Switching Sheets.

9 126. Determining If A Cell Is Empty And Problems With 126. Testing To See If A Cell Is 127. Assigning A Value To A 128. Using visual Basic To Extract Data From Cells .. 128. Copying Values Without Using PasteSpecial .. 129. Checking For Division By Filling A Range With A Formula .. 130. Changing The Value Of Cells In A Range Based On Each Cell's 131. Undoing The Last Manual Entry .. 132. Determining The Number Of Selected 132. 6. How To Determine If A Range Is Determining The Number Of Empty Cells In A 133. Cell References And Merge 133. Determining if there are Merged Cells in a Range .. 133. Determining The Number Of Cells With Entries .. 134. Modifying Cell Values Based On Two 134. Replacing Characters in a 135. VBA Code for 136. SELECTING AND SPECIFYING CELLS .. 136. Using Column Letters to Reference Cells .. 136. How To Reference The Selected Cells .. 136. Specifying Cells Relative To Other Cells.

10 137. Referring To Cells And 137. Using The Offset Function To Specify Cells .. 139. Use The Offset Method To Specify Cells Relative To Other 139. Scrolling To A Particular Cell .. 140. Controlling Cell Selection And The Scroll Area .. 140. Selecting A Range For Sorting Or Other Use .. 141. Making Certain That A Selection Consists Of Only A Single 142. Counting And Selecting Cells With Certain 142. How To Expand Or Resize A Range: .. 144. Resizing Or Expanding A Range .. 145. Selecting Just Blank Cells .. 146. Selecting Just Number 146. Setting Number Cells to Zero .. 150. Selecting The Current Region .. 151. Using the Used Range Property In Your 151. Resetting the Used 152. Selecting The Used Range On A 152. Restricting A Selection To The Cells In The Sheet's Used Range .. 153. Using The Intersect Method With Ranges .. 155. Getting The Intersection Of Two 155. Union Method Problem.