Confessions of an HFM Admin: Hunting Down Dead VBScript Code with Gemini
If you’ve spent any time managing Oracle Hyperion Financial Management (HFM), you know the sheer terror of opening a legacy rules file or member list. These VBScript behemoths (.vbs, .rle, .lst) are often the product of years of “quick fixes” and multiple consultants passing in the night. The result? Thousands of lines of code, riddled with unused variables, abandoned subroutines, and ghost functions that make maintenance a nightmare.
Recently, I decided it was time to clean house. But standard code linters don’t play nice with VBScript and AI could not do the job. So, I turned to Gemini to help me build a custom HTML and JavaScript tool to find the dead weight.
Here is how I used AI and a bit of spec-driven development to build my own VBScript garbage collector.
The Challenge: The Wild West of HFM VBScript
A hurdle in cleaning up HFM VBScript is the loose nature of the language itself. In a perfect world, every script would start with `Option Explicit`, forcing the developer to properly `Dim` (dimension) every variable before using it.
In the real world of HFM rules? `Option Explicit` is not always used. Variables spring into existence the moment they are typed. This means you can’t just look for `Dim` statements and see if those variables are referenced later. You have to look at every single word in the script, count how many times it appears, and figure out if it’s actually doing anything.
Spec-Driven Development (and a Lot of Trial and Error)
When working with an AI like Gemini to generate code, I’ve found that just chatting back and forth eventually leads to a messy, tangled context window where the AI forgets your original goals.
Instead, I used Spec-Driven Development. I created a master text file, detailing exactly what I wanted the HTML/JS tool to do. Whenever the generated tool didn’t work right or missed an edge case, I didn’t just tell Gemini to “fix it.” I updated my specification document and fed the whole spec back to Gemini to generate a fresh, clean version of the code.
And trust me, there was trial and error required. Getting JavaScript to accurately tokenize VBScript means dealing with strings, specific character escaping, and ignoring comments (which in VBScript start with a single quote `’`). I had to iterate a few times to ensure the tool stripped out quoted text like “EntityCurrency” so those didn’t get counted as code tokens.
Spotting the Dead Weight
Once the tool was successfully parsing the file, stripping comments, and counting tokens, analyzing the results was fascinating.
Unused Variables and Subroutines: The logic here is beautifully simple. If you sort the token counts in ascending order, the ones at the very top—the tokens that occur exactly once—are your prime suspects. If a variable name only appears once, it was either assigned a value that was never used, or checked for a value but never assigned. Similarly, if a subroutine name like `Sub CalculateAllocations` appears exactly once, that means it’s defined, but it is never actually called anywhere else in the script.
The Tricky Part: Unused Functions Functions are a bit more deceptive. This script is incredibly helpful for identifying unused functions, but you have to know what you are looking for.
In VBScript, to return a value from a function, you assign the result to the function’s own name. For example:
Function GetTopNode()
' ... some logic ...
GetTopNode = "TotalGeography"
End Function
Because of this quirk, an entirely unused function will actually show up twice* in your token count: once where it is defined, and once where it returns its value. By looking at the tokens that occur exactly two times, I was able to track down massive blocks of function logic that were completely disconnected from the rest of the rules file.
The Final Specification
If you want to try building this yourself, here is the final prompt/specification I used with Gemini to generate the working HTML/JS tool. It uses the Simple.css framework to keep things looking clean without fussing over styling.
# Find unused variables
Create an HTML and JavaScript page to count the number of occurrences of all tokens in a VBscript file.
## Count occurrences of tokens
To identify the number of times a token occurrences:
1. Load the vbscript file into a string
2. Remove comments from the vbscript string, comments start with ‘ or rem
3. Remove quoted text for example “CAT” from the vbscript string
4. Convert the vbscript string to lower case
5. Split the vbscript string into an array containing all the tokens do not identify unique tokens
6. Display the array containing all the tokens in a scrollable text box
7. Go through the array and count the number of times a token occurs
8. Sort the number of occurrences of tokens in ascending order
9. Display the tokens and the number of times they occur
## Page appearance
Style the page using Simple.css Framework. See https://simplecss.org/ for details
Do not add any styles to the page just use Simple.css
Have a box that allows the selection of a vbscript file file. Filter for .vbs, .rle and .lst files
Have a button counts the number of occurrences
Ensure the button is clickable and the a message awaiting file selection is not seen
The Final Script
If you do not want to create a script yoursleve the final tested script can be found here Find unused variables