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 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.