Need assistance with VBA (array creation) for logarithmic regression forecast UDF

[LRobert]

MY GOAL:
My goal is to create a uniform set of UDFs that perform forecasts under various forms of regression, such as exponential, logarithmic, and power. Excel has several native functions for forecasting under various forms of regression, but there are several and the syntax is not uniform.

I created this UDF for forecasting using linear regression to serve as an example here:

Function Forecast_Lnr(X As Range, knownYs As Range, knownXs As Range)
Forecast_Lnr = Application.Forecast(X, knownYs, knownXs)
End Function​

This is identical to Excel’s native function:

=FORECAST(X, knownYs, knownXs)​

I would like to create a similar UDF for forecasting using exponential regression and will use the syntax for other UDFs. This requires a natural log transform of Range knownXs. Excel’s native formula for forecasting using exponential regression is:

=FORECAST(LN(X), knownYs, LN(knownXs))​

MY PROBLEM:
I am having difficulty translating the indicated native formula to VBA. Specifically, I am having difficulty creating an array that is the natural log transform of Range knownXs represented above as LN(knownXs). I started with this function:

Function Forecast_Log(X As Range, knownYs As Range, knownXs As Range)
Forecast_Log = Application.Forecast(Application.Log(X), knownYs, Application.Log(knownXs))
End Function​

and found it did not work. I then took an honest shot at building an array for LN(knownXs) with a loop and still could not get the UDF working.

MY ASK:
I am requesting help with creating an interim step for Forecast_Log() that loops through the knownXs input and creates an array of their natural logarithms. Alternatively, I simply need assistance with Fixing Forecast_Log(). You help will be greatly appreciated.

 

[Tetra201]

... and found it did not work...

Could you please provide more detail on what exactly was not working?

Meanwhile, try

Function Forecast_Log(X As Range, knownYs As Range, knownXs As Range)
    Forecast_Log = Application.Evaluate("FORECAST(LN(" & X.Address & ")," & knownYs.Address & ",LN(" & knownXs.Address & "))")
End Function

 

[LRobert]

Could you please provide more detail on what exactly was not working?

Meanwhile, try

Function Forecast_Log(X As Range, knownYs As Range, knownXs As Range)
    Forecast_Log = Application.Evaluate("FORECAST(LN(" & X.Address & ")," & knownYs.Address & ",LN(" & knownXs.Address & "))")
End Function

Thank you! Worked like a charm!

 

[Tetra201]

You are welcome.