Cut down on account preparation time

Manually editing VBA code

When you are broadly familiar with the Visual Basic editor, the next step is to manually edit Visual Basic code. The macro which I’ve looked at thus far has only dealt with code written directly by the editor in response to key-presses. That’s fine as far as it goes, but there are situations where a higher degree of customisation is required.

One of the reasons I first started looking at Visual Basic concerned the way in which letters are charged for under the Advice and Assistance scheme. The regulations specify that you can charge £6 for each page, a page being 125 words or part thereof. Many of us use the ‘word count’ facility in Word to ascertain how many words are in a letter, so that it can then be charged at the appropriate rate. Frankly, I got fed up at account preparation time having to then divide the number of words on each letter by 125, rounding it up to the next highest whole number (remember you can charge £6 for each whole or part block of 125 words), and then multiplying it by six to arrive at the price. You could never use the macro recorder to automate that process, but you can manually edit code to do so, and this is what it looks like:

Sub wordcountAAletter()

‘ macro written 10/9/02 by scott cownie scownie@yahoo.com

Set temp = Dialogs(wdDialogToolsWordCount)

    temp.Execute

    numWords = temp.Words

    decimalpages = Round(numWords / 125, 2)

    exactpages = (Int((numWords / 125) * -1)) * -1

    cost = exactpages * 6

    Selection.TypeText Text:= numWords & “ words,  “ & decimalpages & “ pages, charge as “ & exactpages & “ pages “ at £” & cost & “ (Advice and Assistance letter rate)”

End Sub

Once you have opened the Visual Basic editor, you can type code, such as the above, directly into macros, instead of just having the macro recorder interpret your key-presses. Running the above macro at this point in this article prints this to the page: 1,903 words,  15.22 pages, charge as 16 pages at £96 (Advice and Assistance letter rate).

Taking the macro line by line:

Set temp = Dialogs(wdDialogToolsWordCount) is a way of referring to the in-built word count facility.

temp.Execute is a command to actually calculate a word count, by executing, or running, the DialogToolsWordCount object.

numWords = temp. Words takes the number of words and gives it a name, numWords, for further manipulation in code.

At this stage, the program knows the actual number of words in the document, 1,903, and you then need to know how many pages there are in terms of the Advice and Assistance regulations. The line, decimalpages = Round(numWords / 125, 2), takes numWords, divides it by 125, then rounds it off to two decimal places by placing that calculation within the Visual Basic Round method. The result, in this example 15.22, is then given the name ‘decimalpages’ for further manipulation. This macro is written to run in Word 2000, but Round does not exist in earlier versions of Word. Earlier versions should use this line: decimalpages = Format(numWords/125, “0.00”).

Knowing the number of pages to two decimal places is fine, but, remember, as a page is 125 words or a part thereof, you need to then round the number of pages up to the next highest whole number to figure out the actual number of chargeable pages. Rounding of this type is not as simple as it should be in Word and the next rather convoluted line needs to be used: exactpages = (Int((numWords / 125) * -1)) * -1, although if anyone can suggest a more elegant way of doing this I’d be pleased to hear from them.  This line starts off by dividing the number of words by 125 to give the number of pages, which at this stage will run to any number of decimal places. It then multiplies by –1 to turn it into a negative number. It is then passed through the Int method. Int is a function which removes the fractional part of a number and returns the remaining integer part of the number. If the number which it is operating on is a negative number, Int returns the first negative number less than or equal to the number. In effect, if Int is applied to –15.22 it rounds it down to the next lowest negative whole number, -16. This is then multiplied by –1 again, turning it back into a positive number. Finally, it is given a name, ‘exactpages’.

The next line, cost = exactpages * 6,  takes the number of pages calculated in the previous line and then multiplies it by six, and gives it a name, ‘cost’.

Finally, the last line uses the phrase, Selection.TypeText Text:=, to print to the page some text and the result of  these various calculations and it does this by referring to the name given to each calculation, numWords, decimalpages, exactpages and cost.

This is a short macro, but it does a lot, and it does it in a fraction of a second. If you wanted to achieve the same result manually, your secretary would have to click Tools | Word Count, read the number of words, get a calculator, spend a bit of time trying to remember whether there are 125 or 250 words in a page for AA letter purposes, probably bother you to double check, then divide the number of words by 125, then round up to the next highest number, then bother you again to check whether you can charge £6 or £6.20 for an AA letter, then multiply by six, then type all that stuff onto the file copy letter.

The main point of this macro is that it shows how a legal process can be represented in computer code. A parameter (the number of words) has been passed to a logical structure (the AA regulation which says that you can charge £6 for each 125 words or part thereof) and a result returned (the price of the letter).

UserForms

The first macro in this article had a limitation insofar as it had a single fixed parameter, namely the LP details text, which couldn’t be changed without editing the macro. The second macro moved on from that to the extent that it extracted a changing parameter from Word and operated on it according to defined rules.

However, the limitation of the second macro lies in the fact that the only parameter it can work with is the word count which it extracts from Word. The real power of VBA stems from the fact that it allows you to draw up custom dialog boxes to allow you to pass any number of parameters you want, to any number of logical structures you care to define.

To draw up a custom dialog box, go back to the Visual Basic editor, and, while working within the project Normal.dot, click ‘Insert | User Form’. In the right hand of the editor a grey box called UserForm should appear along with a dialog box called Toolbox, as per figure 2.

The UserForm is a blank canvas upon which you can place controls  such as buttons and text-boxes from the Toolbox. People who played with Lego in their younger days will like this sort of thing. The whole point of UserForms is that they allow you to draw up user interfaces which allow the user to pass parameters  to sub-procedures for processing. Whenever you use a computer program, you are using a user interface to pass parameters to the rules within the program.

Let’s keep this simple by imagining a fictitious legal procedure in which, depending on the particular facts of the case, you need to draft either a form A, a form B or both at the same time, and let’s imagine that both forms need to contain the names of the pursuer and defender. This type of scenario could be dealt with by placing controls on a User Form so that it looks like figure 3.

The form in figure 3 is created by inserting a User Form into Normal.dot as described above. Two text-boxes, two labels for the text-boxes, two checkboxes and a command button are then placed on the User Form from the Toolbox by clicking the appropriate control in the Toolbox, then clicking on the User Form. The appearance and other aspects of the controls once on the User Form can be changed by right-clicking on a control, then selecting ‘Properties’ from the menu which appears, then making the necessary changes in the Properties dialog box which appears. Once you’ve finished fiddling about with the layout of the form, you can see what it looks like in real life by clicking Run | Run Sub | User Form. The Form should open as a dialog box on top of Word.

At this stage, the User Form doesn’t actually do anything. You need to figure out a way of using the Form to tell the computer to draw up a form A if the form A checkbox is checked, and also a form B if the form B checkbox is checked, or both forms A and B if both boxes are checked, and to insert within those forms the names of the pursuer and defender as entered in the text-boxes, and you need to get the computer to do this when the ‘draw’ button is clicked. In essence, what you need to do is figure out all possible combinations, write them down in plain English which you understand and then re-write it in VBA code which the computer understands.

Each control on the Form is an object which has a name, which can be used to identify the control in a program. To ascertain the name of a control on the form, while in the VBA editor, right-click it and select ‘Properties’. If you do this, you’ll see, for example, that the first text-box is called TextBox1, although for it to be properly identified in the context of this form, it is referred to as UserForm6.TextBox1. Text typed into the box can be referred to as UserForm6.TextBox1.Text.

Each object can deal with certain events which may happen to it, such as being clicked. While in the VBA editor, right-click the ‘draw’ button, and select ‘View Code’. The editor will show some text like this:

Private Sub CommandButton1_Click()

End Sub

The syntax here is that the ‘draw’ button has the name CommandButton1, and when it is clicked, the program should run whatever code or macros are contained within the words ‘Sub’ and ‘End Sub’. You want it to run code which will draw up your forms based on the rules outlined above. The following code will  do this:

Without going into this in detail, this code takes the text within the text-boxes and assigns the names ‘pursuer’ and ‘defender’ for use later, then uses a thing called an ‘If’ statement to figure out whether one or other or both of the checkboxes are ticked, then depending on the state of the checkboxes, uses a method called Document.Add to open either one or two entirely new documents, and writes the details for forms A and B as the case may be to them, along with the names of pursuer and defender, then hides the User Form, so that you can then continue editing, saving and printing the forms.

Once the User Form is running properly from within the VBA editor, you’ll need a way of opening the form directly from Word. The easiest way is to set up another macro and manually edit it so that it reads ‘UserForm6.Show’, which is a straightforward instruction to show the User Form. You can then attach that macro to a custom tool-bar as mentioned earlier.

Summary

Many legal processes follow a standard and highly predictable procedure. If, for example, you intend to set up an inhibition, you can pretty much say in advance exactly what you will need in terms of court documents, letters and memos. Having pro-forma templates on a computer quickens the process to a certain extent, but this approach still suffers from the fact that pro-formas are normally unconnected documents, usually dotted about in various locations in the computer, and each still has to be completed individually, normally on a piecemeal basis as the transaction progresses, with time-consuming and unnecessary retyping of common details such as names and addresses. Many firms have cottoned on to the inefficiencies of traditional methods of document production, hence the rise of software packages which “commoditise” certain legal procedures, such as remortgages.

In essence, why prepare a series of documents at different times when you can prepare them all at once from a single interface? Many such pieces of software use techniques not far removed from those described in this article and a fair few actually use VBA and it’s big brother Visual Basic.

This article has necessarily looked at VBA at a simple level, but once you start looking at the methods available in VBA, you will realise that a great deal more is possible with it, such as “flow control”, which allows for an appearance of “decision making” by the computer to guide the progress of the work, and for automated calculations of dates and figures far more complicated than the AA letter example described above. VBA can also be used to allow Word to communicate with other Office programs such as the calendar or tasks facility in Outlook so that steps taken in Word can automatically generate reminders in Outlook.

As with all IT matters, the lawyers know a great deal about the law, and the programmers know a great deal about computers, but never the twain shall meet. The trick is for both sides to learn enough to be able to talk to each other properly.