Mehedi Hasan
Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. Moreover, one interesting thing is that we can use the FORECAST function in VBA easily in Excel. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences. In this article, I will show you 3 suitable examples of the FORECAST function in VBA in Excel. Hence, read through the article to learn more and save time.
Excel VBA Forecast Function (Quick View)
The overview of the VBA Forecast function is demonstrated in the following image. Here, I have calculated the forecasted yearly sales using the function with VBA. We will discover more about using the VBA Forecast function in different examples in later portions of this article.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
What Is VBA Forecast Function in Excel?
- Description
The FORECAST function is a Statistical function in Excel. Usually, it calculates or predicts a future value based on existing value. However, the existing values are known as x-values and y-values and the future value is predicted by using linear regression. Additionally, the linear regression forecasts the future value and we can easily use it with the help of VBA. For instance, you can predict future numeric values of sales, earnings and expenses, inventory, consumer trends, measurements etc.
- Purpose
To predict or calculate a future value with a linear trend
- Syntax
WorksheetFunction.FORECAST(x, known_ys, known_xs)
- Arguments
| Value | Required/Optional | Description |
|---|---|---|
| x | Required | The value for which the future value to predict or calculate |
| known_ys | Required | The dependent array or range of data (y values) |
| known_xs | Required | The independent array or range of data (x values) |
- Return Value
A predicted or calculated value.
- Version
The function is available from Excel 2000.
3 Suitable Examples of VBA FORECAST Function in Excel
In this tutorial, I will show you some examples of the VBA FORECAST function and how the process becomes more interesting with Excel. In addition, the task is easy and simple. But you will need an arrangement in order to perform the operation properly. However, it includes several real-life examples that we regularly use in Excel. Here, I have used 3 different examples to help you understand the scenario properly. For the purpose of demonstration, I have used different sample datasets for each example, and it will help you to identify the differences between the scenarios easily.
📌 Example 1: Calculate Forecasted Amount Spent
First of all, I will show you an easy example of the VBA FORECAST function in Excel. For the purpose of demonstration, I have used a dataset of Distance and the Amount Spent on that distance. However, I want to know the future value of the miles driven in 127. The predicted amount will be calculated using the FORECAST function in VBA. Hence, go through the steps below in order to complete the operation properly.
📌 Steps:
- Firstly, hold the Alt + F11 keys in Excel, which opens the Microsoft Visual Basic Applications window.
- Secondly, click the Insert button and select Module from the menu to create a module.
- Thirdly, a new window will open and write the following VBA macro in the Module.
Sub VBAForecastEX1()
'ExcelDemy Publications
Dim j As Range
Dim k As Range
Set j = Sheets("Example 1").Range("B5:B15")
Set k = Sheets("Example 1").Range("C5:C15")
Sheets("Example 1").Range("C18").Value = _
WorksheetFunction.Forecast(Range("B18"), k, j)
End Sub
VBA Code Breakdown
- Firstly, we create a new procedure Sub in the worksheet using the below statement
Sub VBAForecastEX1()- Secondly, we declare variables as
Dim j As Range
Dim k As Range- Thirdly, we set the variables.
Set j = Sheets("Example 1").Range("B5:B15")
Set k = Sheets("Example 1").Range("C5:C15")- After that, we activate the VBA sheet and select the range.
Sheets("Example 1").Range("C18").Value = _
WorksheetFunction.Forecast(Range("B18"), k, j)- Finally, we end the Sub of the VBA macro as
End Sub- Fourthly, press the F5 key to run the code.
- Lastly, you will receive the Forecasted Amount Spent.
Read More: How to Use FORECAST Function with Multiple Variables in Excel
📌 Example 2: Find Yearly Sales
Furthermore, through this example, I will show the steps to find the yearly sales using the FORECAST function in VBA. For the purpose of demonstration, I have selected the following sample dataset. Here, the dataset contains two columns, which include Year and Sales. However, I have the sales data from 2012 to 2020 of a company, and I will forecast the sales for 2021 and 2022 using this data. So, go through the steps mentioned below.
📌 Steps:
- Similarly, insert another Module in the VBA window.
- Now, insert the VBA macro mentioned below in the Module.
Sub VBAForecastEX2()
'ExcelDemy Publications
Dim j As Range
Dim k As Range
Set j = Sheets("Example 2").Range("B5:B14")
Set k = Sheets("Example 2").Range("C5:C14")
Sheets("Example 2").Range("C15").Value = _
WorksheetFunction.Forecast(Range("B15"), k, j)
End Sub
- Afterward, press the F5 key to run the code.
- In the end, the forecasted Sales for 2022 will appear as in the image below.
Read More: TREND vs FORECAST Function in Excel (3 Handy Examples)
📌 Example 3: Forecasting Demand
Last but not least, I will show the monthly demand for Rice in a store. For the purpose of demonstration, I have the following dataset containing the records of Demands in Kg from Jan-21 to Sep-21. However, using these values, I will determine the demand for Oct-21 by using the FORECAST function in VBA. Hence, read through the steps below in order to complete the task properly.
📌 Steps:
- Initially, insert another Module in the VBA window following a similar process mentioned in example 1.
- Then, insert the VBA macro mentioned below in the Module.
Sub VBAForecastEX3()
'ExcelDemy Publications
Dim j As Range
Dim k As Range
Set j = Sheets("Example 3").Range("B5:B13")
Set k = Sheets("Example 3").Range("C5:C13")
Sheets("Example 3").Range("C14").Value = _
WorksheetFunction.Forecast(Range("B14"), k, j)
End Sub
- Next, press the F5 key to run the code.
- Finally, you will get your desired forecasted Demand for the month of October.
Read More: [Fixed!] FORECAST Function Not Accurate in Excel (5 Solutions)
Conclusion
These are all the steps you can follow to see some examples of the VBA FORECAST function in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
For more information like this, visit Exceldemy.com.
