Sub ConvertToValues()
On Error GoTo ErrorHandler

Dim r As Range

If ActiveSheet.ProtectContents Then ‘Need This?
MsgBox “This Worksheet is Protected”, vbOKCancel + vbDefaultButton1, “Unable to Proceed”
Exit Sub
End If

If ActiveWindow.SelectedSheets.Count > 1 Then
MsgBox “Cannot Perform on Multiple Sheets”, vbOKCancel + vbDefaultButton1, “Unable to Proceed”
Exit Sub
End If

If Selection.Cells.CountLarge = Cells.CountLarge Then ‘If user selected entire sheet, just grabs Used Range
ActiveSheet.UsedRange.Select
End If

Application.Calculate ‘Do this before converting to values

‘This isn’t needed
‘ If Application.CalculationState xlDone Then ‘Is File Done Calculating?
‘ MsgBox “File Is Not Done Calculating”, vbOKCancel + vbDefaultButton1, “Results”
‘ Exit Sub
‘ End If

‘ Speed Optimization
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False ‘Note this is a sheet-level setting

‘ Selection.Value = Selection.Value ‘This is supposed to be faster than copy & paste. Will error if a cell has huge number of characters.

For Each r In Selection ‘This is far faster than Selection.Value = Selection.Value
r = r.Value
Next r

‘ Re-activate the screen
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlAutomatic
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True

Exit Sub

ErrorHandler:
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlAutomatic
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
MsgBox Err.Description, vbOKCancel + vbDefaultButton1, “Error Number: ” & Err.Number
End Sub

A few thoughts on core data. If the core data is your own creation, you must be clear about where it came from and how you created it. If you are using someone else’s data, you must be clear about how and where you got it. The spreadsheet should clearly state the location of the data so that if someone is using your work, they can go back to that data source and get the same set of numbers you got. If they can’t do this, you have a credibility problem. You don’t want that.

Core data should (and usually does) exist as a data table. This is all about data tables, sometimes called tabular data. A data table is a set of rows and columns that are formally structured. Oh, and another thing. A data table is a set of instance of a thing. And that thing can be almost anything. But it has to be something. You should be able to clearly say “This data table is a listing of X”, and know what X is. This is surprisingly rare, and the reason aside from lack of hard thinking, is that a data table is frequently a messy combination of two different things, because the author did not know how to link together 2 well organized data tables into one report or analysis.

An Excel problem

Someone sends you a file with company and county. The county is obviously wrong on some of them. What do you do? You have a column called “Original County” with their data. This is never changed. Then you have a column called “Corrected County”. Then you copy the data in the “Original” column into the “corrected” column. Then you edit the “Corrected” column. In step form:

1. Create a column called “Original Data”.
2. Consider locking these cells and protecting the sheet.
3. Copy the column called “Original Data” to a new column, and call it “Corrected Data”.
4. Do all your editing, changing, and correcting in the column labeled “Corrected Data”. Never change anything in the column labeled “Original Data”.

Conceptually, what we are doing here is creating an informal audit trail of our work. If we or another person comes back to this work in a week or a month or a year, we can quickly figure out what was done.