Code Snippets

VBA - Clearing Data

There is a way to clear a worksheet model of data and leave the formulas intact.

Range("A1").SpecialCells(Type:=xlCellType.xlCellTypeConstants, _ 
                         Value:=xlSpecialCellsValue.xlNumbers).ClearContents. 

xlCVError.xlErrRef 

ActiveWindow.DisplayFormulas = True 

VBA - Removing a Formula

Selection.Formulas = Selection.Value 
Range("D4").Formula = "=SUM(B5:D20)" 
Range("F5").Formula = "=5-10*RAND()" 
Cells(1,2).Formula = "=G20" 

Range("E2").FormulaR1C1 = "=R[-2]C[2]-R[-2]C[3]" 
Range("E2").FormulaR1C1 = "=RC[-1]+RC[-3]-(8/24)" 

VBA - Entering dates

Range("A1").FormulaR1C1 = "=DATEVALUE(""01/07/77"")+30 
Range("A1").NumberFormat = "mmm-dd-yy" 

VBA - Converting Formulas between A1 and R1C1

Converting Formulas

Public Function FORMULAGET(ByVal rgeCell As Range, _ 
                   Optional ByVal bCellAddressPrefix As Boolean = False) As String 
   
   If VarType(rgeCell) = 8 And Not rgeCell.HasFormula Then 
      FORMULAGET = "'" & rgeCell.Formula  
   Else 
      FORMULAGET = rgeCell.Formula 
   End If 
   If rgeCell.HasArray Then 
      FORMULAGET = "{" & rgeCell.Formula & "}" 
   End If 
   
   If bCellAddressPrefix = True Then 
      FORMULAGET = rgeCell.Address(0, 0) & ":  " & FORMULAGET 
   End If 
End Function 

Application.FormulaBarHeight

Reads and writes the height of the formula bar.

Name as many ways as you can to make Excel calculate something.
Press F9
Shift + F9
F2 in a cell
Enter a formula in a cell
Select a cell containing a formula, press F2 then ENTER
Create a pivot table
Refresh a pivot table
Using VBA code spin buttons/scroll bar etc