VBA/Excel/Access/Word/Excel/Formula

Assigns the values 101 to 200 to the range B1:Bnd again does it more efficiently than a For...Next loop

   <source lang="vb">

Sub main()

    [B1:B100] = [ROW(101:200)]

End Sub

</source>
   
  

Create a set of related formulas in a column: use a looping structure to iterate through the cells that receive the formula

   <source lang="vb">

Sub formula()

   Dim formulaString As String
   Dim I As Integer
   Cells(1, "B").Value = Cells(1, "A").Value
   For I = 2 To 10
   formulaString = "=A" & Trim(str(I)) & "+B" & Trim(str(I - 1))
   Cells(I, "B").formula = formulaString
   Next I

End Sub

</source>
   
  

Fill formula to cell

   <source lang="vb">

Sub AddWatch()

   With Application
       .Range("A1").Formula = 1
       .Range("A2").Formula = 2
       .Range("A3").Formula = "=Sum(A1:A2)"
       .Range("A3").Select
       .Watches.Add Source:=ActiveCell
   End With

End Sub

</source>
   
  

Inserts a formula in cell A11 of a worksheet that calculates the sum of the values in the range A2:A10 using the Excel application"s SUM function

   <source lang="vb">

Sub formal()

   Dim formulaString As String
   formulaString = "=SUM($A$2:$A$10)"
   Cells(11, "A").Formula = formulaString

End Sub

</source>
   
  

Is active cell empty

   <source lang="vb">

    Sub IsActiveCellEmpty()
        Dim sFunctionName As String, sCellReference As String
        sFunctionName = "ISBLANK"
        sCellReference = ActiveCell.Address
        MsgBox Evaluate(sFunctionName & "(" & sCellReference & ")")
    End Sub
</source>
   
  

The Evaluate method can also be used with arrays.

   <source lang="vb">

Sub short()

    vRowArray = [ROW(101:200)]

End Sub

</source>
   
  

Two ways you can use Evaluate to generate a reference to a Range object, and assign a value to that object

   <source lang="vb">

Sub evalDemo()

    Evaluate("A1").value = 10
    [A1].value = 10

End Sub

</source>
   
  

Use Copy and Paste or AutoFill functions

   <source lang="vb">

Sub autoFill()

   Dim formulaString As String
   Dim I As Integer
   Cells(1, "B").Value = Cells(1, "A").Value
   formulaString = "=A2+B1"
   Cells(2, "B").formula = formulaString

End Sub

</source>
   
  

Use [] to evaluate formula

   <source lang="vb">

Sub isBlank()

    MsgBox [ISBLANK(A1)]

End Sub

</source>