Hi

**alee001**
I am not familiar with your function per say; however, I have some general info about this which might help.

In general, there are two ways to speed any macro up that inputs formulas into cells or does calculations row by row:

1) Add the below code at the start and end of macro sub's / functions:

VBA Code:

```
Sub xyz()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual
Dim x as 'etc
'Enter code here
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
End sub
```

2) a - If you use a Sub to insert the formula and/or to call the function, stipulate the precise range that the formula will be inserted into within the Sub. Some people will add 'IF statements' to a formula they insert into cells e.g., =IF(A2="","",Index(..... etc. This would mean that you'd see nothing in the formula column if another column had no data; however, if you didn't stipulate the range for inserting the formula (just the column), you could get it inserted in all cells in a column, thus taking ages. I always create a dynamic table when inserting formulas, and make sure that the dynamic table is adjusted in size to match the CurrentRegion, thus getting around that issue.

b - If you are using the function in a limited number of cells within an excel formula, the only ideas I have are one's that speed up excel:

e.g., Set the Excel.exe process to high priority; in Performance management, set visual effects to 'best performance'.

Usually, if formulas are used on large data-sets (hundreds of thousands of lines) and those formulas call VBA functions, then it's going to take a long time regardless.

More advanced VBA programmers will no doubt have better suggestions.

Kind regards,

Doug.