Formatting Techniques for Data Access from Excel VBA - Formatting Techniques Continued (Page 2 of 5 )
The second method is calledSpecialCells. TheSpecialCellsmethod returns a range object based on constants built into Excel. I usexlCellTypeLastCell, a special cell that refers to the last cell being used. Of the others you can use, two that I find particularly useful arexlCellTypeFormulas andxlCellTypeComments. These return a range that includes all of the cells that have either formulas or comments, respectively.
Often, I want to send an Excel Worksheet to someone, but I don’t want the recipient to change something on one sheet that will affect formula results on another. For example, if you show a scenario of net income based on sales assumptions, and you do not want that net income scenario to be changed if the sales assumptions change, you can change all of the formulas to values using the following procedure. This allows you to maintain a history of the results at various stages. For example, if you make a copy of the scenario with one sales assumption and then remove the formulas, you can maintain the results while still having a second worksheet that will change when the sales assumptions change. Note that the following example assumes that your report is in cells A1 to D100. (You could also build that first range dynamically by using thexlCellTypeLastCellmethod used above instead of building it directly into the code if you needed more flexibility or if the size of your spreadsheet will grow larger.)
Public Sub FormulatoConstant()
Dim rng As Excel.Range
Dim xlws As Excel.Worksheet
Dim rng2 As Excel.Range
Set xlws = ActiveSheet
Set rng = xlws.Range("A1:D100").SpecialCells(xlCellTypeFormulas)
For Each rng2 In rng.Cells
rng2.Copy
rng2.PasteSpecial xlPasteValues
Next rng2
xlws.Range("A1").Select
Application.CutCopyMode = False
Set rng = Nothing
Set rng2 = Nothing
Set xlws = Nothing
End Sub
This procedure uses aFor Each...Nextloop to loop through every cell in the Range variablerng. Each individual cell is copied and then pasted back onto itself with only the values. When it is done, cell A1 is selected and theCutCopyModeis set toFalse(if you do not set theCutCopyModetoFalse, cell A1 will be selected, but the last cell copied will still have the marquee box around it as if it is being copied). You could select the entire sheet and use Copy and Paste Special Values, or aFor Each...Nextloop can be placed into code and set to a particular range by changing it like this:
Public Sub FormulatoConstant(targetrange As Excel.Range)
Dim rng As Excel.Range
Dim xlws As Excel.Worksheet
Dim rng2 As Excel.Range
Set xlws = ActiveSheet
Set rng = targetrange.SpecialCells(xlCellTypeFormulas)
For Each rng2 In rng.Cells
rng2.Copy
rng2.PasteSpecial xlPasteValues
Next rng2
xlws.Range("A1").Select
Application.CutCopyMode = False
Set rng = Nothing
Set rng2 = Nothing
Set xlws = Nothing
End Sub
UsingFor Each...Nextallows you to target just the range of cells that you want to find formulas in and change them to values. You could take this code, put it into any of your Excel workbooks, and with a simple call to the procedure, perform this action.
Next: Formatting Techniques with Style >>
More Database Articles
More By O'Reilly Media
|
 This article is excerpted from chapter three of the book Integrating Excel and Access, written by Michael Schmalz (O'Reilly, 2005; ISBN: 0596009739). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 9 
