Tabulating a Function with Excel VBA

The AutoFill method can be used to solve the problem of function tabulation, i.e., outputting its values as its parameter changes. For example, we may want to find the values of the function sin(x) for the parameter x changing from 0 to 2 in steps of 0.2.

First, enter the first term of the arithmetic sequence of the required parameter values into cell A1, and then use the DataSeries method to build the entire sequence down column A. Next, define the current range containing these values. The range where the corresponding function values will be placed is in column B, which can be obtained using the Offset property.

Finally, enter the formula =SIN(A1) in cell B1 to calculate the function value for the parameter equal to 0, and then copy this formula across the entire range allocated for the function values.

Function Tabulation

Sub DemoDataSeries()
    Range("A1").Value = 0
    Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlDataSeriesLinear, _
        Step:=0.2, Stop:=2  
    Dim rgn As Range
    Set rgn = Range("A1").CurrentRegion
    Set rgn = rgn.Offset(0, 1)  
    Range("B1").Formula = "=SIN(A1)"
    Range("B1").AutoFill Destination:=rgn, Type:=xlCopy
End Sub