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.

4 Responses to “VBA Macro Arguments”

  1. Jim Campbell Says:

    I have to create cumbersome reports by copying certain remedy fields and pasting them into Excel. Is there any magic code that can do this?

  2. Mike Barton Says:

    Hi,

    I am very interested in getting the code as I have taken on running Remedy reports and dread having to manually go in and pull data out. If your code can even get me to the point where I can run reports without having to open the UI it would be a big help.

  3. kevin zhang Says:

    Hi,

    Nice article. Can I get your code (VBA script in Microsoft Word )?

    And question for you, Is there web service in Remedy User that I can use to get contents of ticket(request)?

    Thanks,

    Kevin zhang

  4. Nd Says:

    Hi there!
    I am very interested in getting the code. As i want to pull data out of remedy automatically rather than taking it manually. plz reply on this.

    Thanks,

    ND

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: