How to Use the LinEst Function in VBA (With Example)

You can use the LinEst method in VBA to fit a linear regression model.

This function uses the following basic syntax:

LinEst(Arg1, Arg2, Arg3, Arg4)

where:

• Arg1: Set of y-values
• Arg2: Set of x-values
• Arg3 (optional): TRUE = calculate intercept normally, FALSE = force intercept to be zero
• Arg4 (optional): TRUE = calculate additional regression statistics, FALSE = only calculate coefficients

The following example shows how to use the LinEst method to fit a regression model in VBA in practice.

Example: How to Use LinEst Function in VBA

Suppose we have the following dataset that contains one predictor variable (x) and one response variable (y):

We can create the following macro that uses the LinEst method to fit a simple linear regression model to this dataset:

Sub UseLinEst()
Range("D1:E1") = WorksheetFunction.LinEst(Range("B2:B15"), Range("A2:A15"))
End Sub

When we run this macro, we receive the following output:

The values in the output represent the coefficients for the regression model:

• The coefficient for β₀ is 3.52169.
• The coefficient for β₁ is 0.693717.

Using these values, we can write the equation for this simple regression model:

y = 3.52169 + 0.693717(x)

We interpret this to mean that each additional one unit increase in the predictor variable, x, is associated with an average increase of 0.693717 in the response variable, y.

We interpret the intercept term to mean that when x is equal to zero, the average value of y is 3.52169.

If we would like to use the LinEst method to produce additional regression statistics, we can use the following syntax:

Sub UseLinEst()
Range("D1:E5") = WorksheetFunction.LinEst(Range("B2:B15"), Range("A2:A15"),True, True)
End Sub

When we run this macro, we receive the following output:

The following screenshot provides an explanation of each value in the output:

By supplying a value of True to the last argument of the LinEst method, we’re able to calculate several additional regression statistics.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Rank a List of Values
VBA: How to Calculate Standard Deviation of Range
VBA: How to Calculate Weighted Average