Help Rewriting VBA Function

[cgi2099]

I was wonder if someone could help me rewrite this VBA function to remove the application Volatile?

Please Login or Register  to view this content.

Thank You

Josh

[BellyGas]

Can I ask what problem you're trying to solve?

Application.volatile marks a function to recalculate whenever any cell changes. If we remove that, your function is going to need another calling method. Why do you want to turn off automatic running and what would you like to trigger it instead?

[MrShorty]

I simply removed the Application.Volatile statement, and it seemed to work just fine as a non-volatile function. It was called when inputs to the function were changed and ignored changes made to other cells in the spreadsheet. Why do you think it needs the Application.Volatile statement, or needs a significant re-write to remove the volatile statement?

[BellyGas]

It doesn't. I was wondering why you'd used it in the first place.

[cgi2099]

Can I ask what problem you're trying to solve?

Application.volatile marks a function to recalculate whenever any cell changes. If we remove that, your function is going to need another calling method. Why do you want to turn off automatic running and what would you like to trigger it instead?

The reason I am trying to remove it is because any changes to the Spreadsheet data on any cell (not necessarily the cell the function is in) it recalculates every mention of that function and is slowing my spreadsheet down when a lot of data is being input.

The reason Application.volatile is needed is because I need it to recalculate until it finds an empty cell.

I am using the function like this: =My_Text_Join("",1, Formulas!S:S)

Maybe I don't even need this function and there is another way to join the text together of S:S (not including blank cells)?

[KOKOSEK]

I am not surprise that it slowing down, if you checking full column S.
Is that necessary? VBA is checking over million cells, are they empty or not?

EDIT:
If you are not sure how many rows do you need in column, I've tweak a bit your VBA function to find out last row in col. S.
Try like this, maybe it helps:

Please Login or Register  to view this content.

should works.