How to prevent VBA function calls from reevaluting when worksheet opens up

I have some VBA functions which print a string on the cell from where it's called.

For e.g.

Please Login or Register  to view this content.

Now let's say I call this from Cell A1 as "=PrintStuff()", then you see either Lesser or Not Lesser in Cell A1

Now I call this function & other similar functions many, many times in my worksheet.

What I want is that once the function call is evaluated & something is written on to the cell, it becomes permanent. i.e. it doesn't reevaluate the function everytime the sheet is opened - is this possible

Why I want this is because sometimes when I reopen the sheet, Lesser gets changed to Not Lesser & vice versa. This is inspite of the cells which the function checks not changing. I clear out the formula call from the cell, & rewrite the call, the string goes back to what was originally being printed.

So is there a way to freeze the output of a VBA function call?

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

I can't imagine why you would use a UDF for that, but it shouldn't recalculate even if you change the values of the cells it's looking at, since they aren't passed in as direct arguments and you didn't make the function volatile.

What’s wrong with a simple if formula?

I'd imagine you could still do that with built-in functions. Even if you do use a UDF, you should really pass the ranges as arguments rather than using Offset in your code.

You'd use arrays or functions that find values. Impossible to be specific when you haven't given any real detail.

If your UDF loops, it presumably loops through a range. That is what should be passed as the argument(s).

If you create a sample data set (maybe 10 or 20 lines, with your complete/ current/ mostly working code) and upload it, we may be able to give you a better solution than you expect.

Originally Posted by ExcelBoy90

It's not a fixed range, the range also is figured out by traversing till it finds a cell with a particular value. Why does it need to be passed as an argument?

If you don't pass it as an argument, you should make the function volatile, since otherwise it won't react to changes of the data in the range. Making UDFs volatile should generally be a last resort.

If I can figure out the range programmatically inside the function, why should I do the visual, possible error prone task of doing the same manually before calling the function?

It appears that you are wedded to your current approach, so there's little need to address how a formula approach could work.