With it almost being 2 months since the last time I wrote a blog post, I figured I was due. It hasn’t been due to laziness; quite the opposite. I’ve been writing and testing insane amounts of code. I’ve stated a few times that I’m writing code to write code for me – working smarter, not harder. It has been more out of necessity and sanity than higher level thinking. I’ve been creating copious amounts of code for Hyperion Planning Business Rules, but the principles can apply to any language. Take my example… (Be patient with it…it doesn’t make full sense, but is used for educational purposes! 🙂 )
Say we have a list of items, bottles of wine in this example. They all need to be accounted for in the code, but there are 1,624 bottles for which to write code. That’s a lot of hand-written code. This is tedious, especially if the code is all the same with the exception of the item number.
Business Rules script to be copied:
We can use Excel and VBA to help us out. The first thing we want to do is go to the VBA screen. The easiest way to do this is to hit Alt + F11. You will be taken to the main screen.
On the left hand side, right-click on “Microsoft Excel Objects” then choose Insert -> Module.
You will be brought to a blank Module screen. Here is where you will type the code to use to generate your code.
When you complete the code, it will look like the following on the screen:
But I’ve made it a bit easier to see and replicated by going to my trusty text editor. Following my notes in the code, you can see what each line of code does. There is the portion at the top that sets the variables, mainly numerical values and references to the worksheet containing the item values (“Example”) and the worksheet where the code will be written to (“ExampleCode”). Notice that I created a For/Next loop to go through each of the items in column A on the “Example” worksheet.
(Note: If you click the image, you will get a bigger view of the full code. Also, if you wanted to include “indenting” by putting some lines in columns B or C, for example, the same logic would follow so long as the LastRow value is referencing the column of the last line of code.)
The result of the VBA code is an Excel worksheet with the Business Rule code that can be copied and pasted into Calc Manager for immediate use. (Note: you’ll still need to add the “SETs” at the beginning of the code manually unless you add it to the VBA code before the loop).
To double-check that the script worked fully, I ended up with 22,736 lines of code which divided by 14 lines of code per item comes out to 1,624…the number of items we needed to write code for. Perfect! How long did this code take to run for 22,736 lines of output? 8.03 seconds. Heckuva lot better than typing out manually, huh?