VBA Macro Arguments

After we started to use Remedy for our customer support, I got annoyed by the way Remedy displays the history of customer cases (Remedy calls them issues), so I wrote a VBA script in Microsoft Word that takes the history generated in Remedy and displays it in a readable format (let me know if you’re interested in this script – it is really great if you are working with Remedy to support customers)

Since I intended this macro to be used by others, and not only by me, I tried to make its installation as simple as possible. I had no choice but asking my users to open the VBA editor and import my Remedy.bas module (I really didn’t want to go into developing a Word add-in with an installation program), and then users would have to add a toolbar button calling the macro. Since this was quite a complex script, it had many internal functions which I didn’t want users to call from a toolbar button – I only wanted them to add one or two buttons that called some of the public functions. To make sure they only use the public functions, I prefixed all the internal functions names with “RAH_” (RAH stands for Remedy Activity History), so they’ll know they shouldn’t look at macros with that name.

Later, I added a new procedure (“Sub” as it is called in VBA) that do some functionality. This function could be called from within other macros, in which case a document argument can be passed, or it could be called from a toolbar button, in which case the active document should be used. I noticed, however, that the Customize dialog didn’t show any macro function or procedure that has an argument, even though this argument was optional, and it was valid to call the function without arguments. This was annoying at first, and to bypass it, I had to create another proxy procedure that simply calls the internal procedure without any parameters, and only this proxy procedure could be added as the action for a new toolbar button.

However, later I realized that this feature of the Customize dialog can be put to good use and solve the internal procedures problem I had – I simply added an optional dummy argument to any internal procedure that didn’t have any argument. On one hand, it didn’t change the meaning of my code, and any code that called this internal procedure didn’t have to change. On the other hand, all these internal procedures don’t appear in the Customize dialog any more, since they now have an argument.

Update: This trick of hiding the internal procedures has one problem. My script uses the Application.OnTime procedure when the Remedy application haven’t finished opening the history window to make the script sleep and then check again one second later. It appears that the Application.OnTime procedure behaves like the Customize dialog, and is only willing to accept a procedure without arguments in its Name argument. If the procedure specified in the Name argument has at least one argument, the OnTime procedure won’t run it. So the macro has to have at least one internal function with no arguments.

One cumbersome way to solve it is to set some global flag before calling Application.OnTime, and then make Application.OnTime call a public procedure which will first check for that flag and if it is set, call the internal procedure and exit, but I’m not sure that the goal of hiding this one internal function worth the effort of making the code less readable.

Update 2: Thinking about it some more, I came to the conclusion that the dummy argument is a nice trick, but is not really needed. Setting the internal procedures as Private also hides them from the Customize dialog. How didn’t I think about it before…?

This, however, doesn’t solve the Application.OnTime problem, as the OnTime procedure cannot call Private procedures, so the procedure it calls must be public with no arguments, so it will appear in the Customize dialog.

Update 3: I found another reason for having non-private functions. My macro tries to hide unimportant parts of the case history, such as “original message” parts and confidentiality signatures. When doing so, it adds a link that when clicked, shows the hidden text. This is done by adding a MACROBUTTON field that calls the appropriate macro. It appears that this macro must also obey the rules of the Customize dialog for the field double-click to work – it has to be non-private, and with no arguments.

Setting Word’s default suggested filename

Our company recently moved to use Remedy for bug management and customer support.

One of the things that bothered me the most in Remedy was its inconvenient way of showing issues’ (a.k.a cases) activity history.

So I sat down to write a Word macro that manipulates Remedy windows to get the history data as a flat text and conveniently format it as a Word document. (If you are using Remedy and would like to get this script, leave me a note)

I had several problems in writing this macro, and I learned some more about Word VBA while overcoming them.

One of the problems I encountered was that I couldn’t find a way to control, from the VBA code, what will be the default name of the document when the user first saves it. Word seemed to insist on using Doc1.doc (on Word 2003) or 01.doc (on Word 2007). At first, I left it as an known issue, since I didn’t think this would really hurt anyone. But I got some user that has some more requests from that macro, and he also urged me to solve this problem.

I then realized that Word actually takes the first line to be the file name, so I simply added a header to the file with the details of the issue (removing all special characters which Word sees as delimiters) and this indeed solved the problem.

While writing this post, I search for questions on the web on how this can be done, since I can’t be the only one who needs it, and found that it can be done from script. However, since the header I added actually make the generated document look nicer, I think I’ll leave it that way.