How do you restart/reset a Function?

Originally Posted by FDibbins

If you mean, how do you use/activate it again, probably the easiest is to create a button and assign the macro to it

You can create buttons in Excel, I thought that was in Access?

But yes, I need the function I created, StaticRand to generate another random number on command or via some method. Pressing F9 doesn't help, since it is now a non-volatile function.
For now I have to close out of Excel and reopen the workbook to have it generate a new number.

Thanks

Originally Posted by FDibbins

Go to Insert Tab/Shapes/pick 1/set size in sheet
right-click into teh shape and give it some text
right-click again, and Assign the macro

Thanks for the detail steps, though unless I'm doing something wrong, it wants me to RECORD a macro when I click Assign Macro. It also has a button labeled New. I clicked the New button and pasted my StaticRand function into it, but when I click my action button I created earlier, I get a Compile Error: Expected End Sub. So I tried changing the last End Function to End Sub hoping that might fix it. Nope!

Please Login or Register  to view this content.

Is this because my StaticRand function is in VBA?

Thanks again
Mike

Originally Posted by Modify_inc

How do you tell it to restart/reset the function? Here is what I have:

Please Login or Register  to view this content.

Originally Posted by Modify_inc

I need the function I created, StaticRand to generate another random number on command or via some method. Pressing F9 doesn't help, since it is now a non-volatile function.

To minimize recalculations, you can select the Excel cell that references StaticRand, press F2, then press Enter.

In effect, you are "re-editing" the cell. But since you simply press Enter, nothing changes. Nevertheless, Excel recalculates the cell (it calls the function) and any directly and indirectly dependent cells.

PS.... To ensure a random sequence of numbers, you should call Randomize at least once (and preferrably only once) some time after opening the Excel file.

One way is to create a Workbook_Open event macro. In VBA, press ctrl+R to open the Project Explorer pane on the left, double-click on ThisWorkbook, then copy the following and paste into VBA:

Please Login or Register  to view this content.

Alternatively, change your VBA function as follows:

Please Login or Register  to view this content.

Technically, the two are not the same. If VBA is reset (there are several ways that happens), Randomize will be called again. But that should be infrequently and "randomly" enough that it should not adversely affect the sequence of random numbers.

Originally Posted by MrShorty

Of course, at that point, you could simply have =INT(RAND()*617)+1 in a cell, and completely bypass the VBA UDF. This makes me wonder if there is more to the question. Specifically, how to control when a UDF recalculates.

I have created a test that generates the questions from a list. The questions are chosen randomly and the answers are calculated when the user presses enter. This doesn't work very well with the volatile nature of Excel's Rand() function because when you press enter to have it determine if your answer is correct, the question is changed (randomized again automatically) thus your answer is wrong every time.

So using the StaticRand UDF, I can answer all the questions and get the results without the questions changing. But now I need a way to refresh the questions, reset/restart the StaticRand function on demand. The suggestion FDibbins proposed seemed the ideal way. I create a button, assign a Macro, label it Refresh, and when you click it, it would refresh the test questions or rerun the StaticRand function on demand. Unfortunately though, the Assign Macro option doesn't appear to work like that.

For now I can close Excel and reopen it to regenerate my test questions, which is silly, but it works.

I will try the "dummy argument" though.

Thanks
Mike

Originally Posted by joeu2004

To minimize recalculations, you can select the Excel cell that references StaticRand, press F2, then press Enter.

In effect, you are "re-editing" the cell. But since you simply press Enter, nothing changes. Nevertheless, Excel recalculates the cell (it calls the function) and any directly and indirectly dependent cells.

PS.... To ensure a random sequence of numbers, you should call Randomize at least once (and preferrably only once) some time after opening the Excel file.

One way is to create a Workbook_Open event macro. In VBA, press ctrl+R to open the Project Explorer pane on the left, double-click on ThisWorkbook, then copy the following and paste into VBA:

Please Login or Register  to view this content.

Alternatively, change your VBA function as follows:

Please Login or Register  to view this content.

Technically, the two are not the same. If VBA is reset (there are several ways that happens), Randomize will be called again. But that should be infrequently and "randomly" enough that it should not adversely affect the sequence of random numbers.

I'm not certain what this does exactly, but now my StaticRand function does not work; it will not regenerate any random numbers anymore. The same numbers or question are always displayed even when I close and reopen Excel now. I removed the changes, and reverted back the way I originally had it, and it is still not regenerating the questions. It's like the function just died!

I even removed the function and the module it was in. Saved and reopened the workbook, and the same questions are still there using the same numbers. With the function removed, there should be an error in place of the questions, but there is not.

What could possible be going on? I don't even no where to begin to resolve this other than to create a new workbook and start over.

Any suggestions?
Mike

Originally Posted by Modify_inc

I removed the changes, and reverted back the way I originally had it, and it is still not regenerating the questions. It's like the function just died!

Obviously, you introduced a typo when you made the changes that I suggested. But I cannot help you further since you did not provide an exact copy of the code after you changed it. By "exact copy", I mean preferrably the URL of a downloadable copy of the Excel file. Alternatively, at a minimum, copy from VBA and paste into your posting, as you did in your original posting.

Originally Posted by joeu2004

Obviously, you introduced a typo when you made the changes that I suggested. But I cannot help you further since you did not provide an exact copy of the code after you changed it. By "exact copy", I mean preferrably the URL of a downloadable copy of the Excel file. Alternatively, at a minimum, copy from VBA and paste into your posting, as you did in your original posting.

Typo would infer I typed something, but I did not. I only copied and pasted your code. That is why I tried your suggestion before I tried MrShorty's, since yours only required a simple copy and paste. Of course I could have pasted them incorrectly though, I will give you that.

The cells that use the StaticRand function will randomize correctly when I select them individually and press F2+enter on each of them. But that seems to be the only way as even closing the workbook and reopening it will continue to use the last numbers that were previously saved. I think this also confirms there is not an typo in the function if the function still works correctly when you press F2+enter on the relative cells in question. But even using my original code (without yours) it still behaves in the same fashion, so I am at a lost to what is going on.

To be fair, I do not think your code resulted in this odd behavior, but I am still at a loss to why this is happening. Why does F2+enter still work, and why would Excel not execute the function after reopening the workbook? I've noticed that if I completely remove the function from VBA, use F2+enter on the cells (which now produces an error as they should since the function is missing), save the workbook, reopen it and open VBA, add the StaticRand function back, and save and close the workbook. When I reopen the workbook, I have new numbers/questions again. BUT if I save it at this point, close and reopen, I have the same numbers/questions all over again...The function stops working again, forcing me to use the F2+enter on each question.

Thanks
Mike

I just added completely new unrelated code to perform another action using VBA, and it appears to not be working either. The code is suppose to protect a specific cell from being changed by the user. Yet it appears to not work as described, because I can still click on the cell and make changes to it. I'm not sure if it's something I'm doing wrong or if its an issue with Excel misbehaving.

Here is the code:

Please Login or Register  to view this content.

Thank you
Mike