How to put a complex worksheet function in VBA?

[JenniferMurphy]

In this thread,

Is there any way to access the trendline parameters in cell expressions?

If I have a chart with a trendline, is there any way that I can use the trendline parameters in cell expressions? The chart on the right plots the Conf column on the Y axis against the #Reviews column on the X axis. I found that a power series trendline matches almost perfectly. Now I want to...

www.mrexcel.com

AhoyNC provided two worksheet expressions for fitting data to a power equation. The expressions are fairly complex. I fear that I will never remember them and will make many typos trying to type them. So I want to put them in a UDF. But they make use of several worksheet functions. Is there a way to put this expression in VBA without putting "application.worksheetfunction" in front of each function and making the expression span half a dozen lines?

=EXP(INDEX(LINEST(LN(D2:D13),LN(B2:B13),,),1,2))

Thanks

 

[JenniferMurphy]

After a lot of fiddling around, I came up with this UDF, which seems to work. I still need to add error codes. A couple of questions:

1. Why does the EXP function fail if I put Application. in front of it?
2. Is there any way to simplify this?

'================================================================================================
'                           Fit a Power Equation to Sample Data

' Excel has an unreqasonably complex expression that finds the power equation that is
' the best fit for a set of sample data. This function makes that available in a more
' easily accessible form.

' The expression for the coefficient of the power equation:
'   =EXP(INDEX(LINEST(LN(TblMrX[Conf]),LN(TblMrX['#Reviews]),,),1,2))
'   =EXP(INDEX(LINEST(x2,x1,,),1,2))
'   =EXP(INDEX(x3,1,2))
' The expression for the exponent of the power equation:
'       =INDEX(LINEST(LN(TblMrX[Conf]),LN(TblMrX['#Reviews]),,),1)
'       =INDEX(LINEST(x2,x1),1)
'       =INDEX(x3,1)

'   Parameters
'     pXAxis  The X Axis values
'     pYAxis  The Y Axis values
'     pParm   "COEF" = Retirn the coefficient
'             'EXP"  = Return the exponent

'   Change Log
' 05/10/23  Based on this MrExcel thread:
'  https://www.mrexcel.com/board/threads/is-there-any-way-to-access-the-trendline-parameters-in-cell-expressions.1236795/
'================================================================================================
Function FitPower(pXAxis, pYAxis, pParameter As String)

Dim x1, x2, x3, x4
x1 = Application.Ln(pXAxis)
x2 = Application.Ln(pYAxis)
x3 = Application.LinEst(x2, x1)

Select Case UCase(pParameter)
  Case "COEF"
    x4 = Application.index(x3, 1, 2)
    FitPower = Exp(x4)            'Why no Application.?
  Case "EXP"
    FitPower = Application.index(x3, 1)
  Case Else
      
End Select

End Function

Here's a minisheet using it:

Amazon Ratings.xlsx
	C	D	E	F
5	Rtg	#Reviews	Rank	Conf
6	5	9,467	1	0.0132
7	3	9,101	2	0.0130
8	4	8,499	3	0.0136
9	5	744	4	0.0445
10	4	444	5	0.0608
11	3	427	6	0.0625
12	5	88	7	0.1328
13	4	56	8	0.1673
14	3	39	9	0.2045
15	4	9	10	0.4081
16	5	4	11	0.6270
17	3	2	12	0.8727
18
19		Coefficient	Exponent
20		1.242141	-0.49808	Long expression
21		1.242141	-0.49808	My UDF
MrExcel

Cell Formulas
Range	Formula
E6:E17	E6	=RANK.EQ([@['#Reviews]],['#Reviews])
D20	D20	=EXP(INDEX(LINEST(LN(TblMrX[Conf]),LN(TblMrX['#Reviews]),,),1,2))
E20	E20	=INDEX(LINEST(LN(TblMrX[Conf]),LN(TblMrX['#Reviews]),,),1)
D21	D21	=fitpower(TblMrX['#Reviews],TblMrX[Conf],"coef")
E21	E21	=fitpower(TblMrX['#Reviews],TblMrX[Conf],"exp")

 

[Norie]

VBA has it's own Exp function.

 

[JenniferMurphy]

VBA has it's own Exp function.

(sigh) So what? That's no reason for application.exp to fail. The worksheet has one, too.

PS: This is not directed at you. Sorry. It's just that Excel in general and VBA in particular is just a torture chamber.

 

[Norie]

What's the point of replicating the function if it already exists?

You can see the full list of worksheet functions available via WorksheetFunction if you open the Object Browser (F2) and search for WorksheetFunction.

The members of the WorksheetFunction class are the worksheet functions that are available.

Similarly if you search for Math you'll see all the functions available in that class, including Exp.

Note, there are some functions, notably Trim, which have 2 'versions', a VBA one and a WorksheetFunction one.

The reason for this, as far as I'm aware, is that the worksheet function TRIM acts differently from the Trim function in VBA - I think the main difference is
that the worksheet version replaces multiple spaces between words with a single space, which the VBA version doesn't do.

 

[Alex Blakenburg]

You are probably way past this stage but:

Is there a way to put this expression in VBA without putting "application.worksheetfunction" in front of each function and making the expression span half a dozen lines?

Did you try:

    With Application
        FitPower = Exp(.Index(.LinEst(.Ln(pYAxis), .Ln(pXAxis)), 1, 2))
    End With

I fear that I will never remember them and will make many typos trying to type them.

For you consideration:
1) Wyn Hopkins from Access Analystics is a big fan of using Autocorrect to insert formulas he uses regularly.
This is then independent of the workbook, and available to you in all workbooks.
So you go into autocorrect and set up 2 or 3 characters that you want to use for the formula, put the formula in the replace with box.
Instead of the 2 ranges you would put a word or phrase no spaces as place holders.
To use it use your enter the identifying characters it inserts the formula and you double click on each range place holder and select the range.
Includes a 5 min video > Ref: Master INDEX MATCH in 60 Seconds

2) Use a Lambda function
Save it as a template (.xltx) workbook for easy access when creating a new workbook.

 

[JenniferMurphy]

You are probably way past this stage but:

Did you try:

    With Application
        FitPower = Exp(.Index(.LinEst(.Ln(pYAxis), .Ln(pXAxis)), 1, 2))
    End With

Doh! Of course. Thanks for jogging my addled brain.

Function FitPower(pXAxis, pYAxis, pParameter As String)

Select Case UCase(pParameter)
  Case "COEF"
    With Application
'               =EXP(INDEX(LINEST(LN(TblMrX[Conf]),LN(TblMrX['#Reviews]),,),1,2))
      FitPower = Exp(.index(.LinEst(.Ln(pYAxis), .Ln(pXAxis)), 1, 2))
    End With
  Case "EXP"
    With Application
'                =INDEX(LINEST(LN(TblMrX[Conf]),LN(TblMrX['#Reviews]),,),1)
      FitPower = .index(.LinEst(.Ln(pYAxis), .Ln(pXAxis)), 1)
    End With
  Case Else
      
End Select

End Function

So much better! Thank you.

For you consideration:
1) Wyn Hopkins from Access Analystics is a big fan of using Autocorrect to insert formulas he uses regularly.
This is then independent of the workbook, and available to you in all workbooks.
So you go into autocorrect and set up 2 or 3 characters that you want to use for the formula, put the formula in the replace with box.
Instead of the 2 ranges you would put a word or phrase no spaces as place holders.
To use it use your enter the identifying characters it inserts the formula and you double click on each range place holder and select the range.
Includes a 5 min video > Ref: Master INDEX MATCH in 60 Seconds

2) Use a Lambda function
Save it as a template (.xltx) workbook for easy access when creating a new workbook.

Hmmm... I'll look into that, but I fear I would forget what is what. I am more comfortable with VBA. Thanks.

 

[Alex Blakenburg]

No problem. I am more comfortable with VBA than Lambda as well but the autocorrect is an interesting concept.