New Techniques – Post #2

In the last post, I mentioned that there was a problem with the random technique that I was doing. There are two problems: I figured out one problem – more or less – but haven’t figured out the second problem.

The first problem is the fact that every time you enter something, the spreadsheet will recalculate all of those RAND functions. At some point you want the random numbers to stop changing, especially for budgeting purposes. To deal with this, I learned a new kind of macro that is geared specifically for a certain worksheet. You can actually right-click a tab and view code for that tab. Normally when you are in the VBA editor, you see “General” in the upper left hand corner and “Declarations” in the upper right. If you change “General” to “Worksheet”, the upper right hand corner’s options will change. I think they give you event handlers. The one I learned is “Change”. If you change “Declarations” to “Change”, the VBA editor will automatically start the beginnings of a new macro: Private Sub Worksheet_Change(ByVal Target As Range).

Then, you make the next statement to reference a cell where you want to track changes: If Target.Range = $B$1 Then…

That statement will set up the private macro to run whenever B1 changes. Every time B1 changes, the macro executes the statements following the Then.

How does that work with my RAND function? When I’m ready to lock down the random numbers, I go to cell B1 and place an “x” to signify I want to lock the numbers. So after the Then, I’ll have something like If B1 = “x”, then copy and paste special values the random numbers. So in effect, I actually have a column of RAND functions and a column of special values. Now, whenever I have “x” in B1, I reference the column with special values. If B1 does not have an “x”, then I reference the column with RAND functions.

However, whenever I create one of these Change event macros, I still have problems making the macro work so I still haven’t totally figured out how it works. Right now it is working but when I create a new one….

The second problem I mentioned is how RAND will make one last change in the random numbers and that change apparently messes up my ranking. I will have to see if my last “solution” fixes it.

So those are the two new things that I have been working on: using RAND function to generate random choices according to some business rule or statistical rule and using the event handler macro.