I was wonder if someone could help me rewrite this VBA function to remove the application Volatile?
Thank YouPlease Login or Register to view this content.
Josh
I was wonder if someone could help me rewrite this VBA function to remove the application Volatile?
Thank YouPlease Login or Register to view this content.
Josh
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?
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?
Originally Posted by shg
It doesn't. I was wondering why you'd used it in the first place.
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)?
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:
should works.Please Login or Register to view this content.
Last edited by KOKOSEK; 04-12-2019 at 07:55 AM.
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks