Creating a script

[Geoff]

This is a long question which I hope has a short answer! There is something I needed to do, and the solution I found is, I am sure, about the most complciated one I could have devised. But I do need to set ot the problem and how I tackled it so that soemone can advise me as to a better solution. Please bear with me and forgive me the length of this!

I have a VBa program in Word (latterly Word 2003) which has built up over several years. It is used to create court orders. It uses about sixty forms. Each form is used to generate a paragraph of text having taken details of thes uer's requirements from a combination of checkboxes, option buttons and text boxes.

The intention is that the user can build up an order paragraph by paragraph using a few mouse clicks per paragraph to select the otpions he wants and with ahrdly any keyboard use at all.

What we have found over the years is that users are repeatign similar orders with frequency. Over half of the orders generated by a user are identical versions of one of four or five "basic" orders. Howver, each user has his own "basic" orders - his own ideas as to what they should include, and lcoal conventions as to timings etc.

So what I needed was a way to generate a "script" which would run through half a dozen ro more forms and set them to standard settings. If the script could eb run form a mouse-click, then the all of the requried paragaphs could come from that click.

However, the basic concept needed to be left untouched - it must still be possible to construct an order paragraph by paragrpah from the individual forms (which are called from a set of menus on a toolabar).

Also, I could not pre-define the "basic" orders because the user's requirements were individual.

It is easier to demonstrate than explain the method I adopted to do this.

I created a user object for each form. This is the one for statements:

Type pfStatements
AmRequired As Boolean
StatementsDateDelay As Integer
chkConfined As Boolean
optLiability As Boolean
optLimitation As Boolean
optDamages As Boolean
End Type

I vreated one such for each form that might be needed, and then put all of those objects into another object:

Type ProForma
Name As String * 70
...
Statements As pfStatements
Documents As pfDocuments
Part18 As pfPart18
...
End Type

Then, using another form, the user a checkbox to states whether a particular form is required (the AmRequired property which is present in very type related to a specific form) and to set the optional elements - the "delay" integer is used to calculate the date (for compliance - each form has its own DoDates subroutine to work out the dates) as a specific number fo weeks ahead and the rest are checkboxes or option buttons as the names suggest.

This is the bit for Statements:

With .Statements
.AmRequired = chkStatementsRequired
.chkConfined = chkStatementsConfined
.optDamages = optStatementsDamages
.optLiability = optStatementsLiability
.optLimitation = optStatementsLimitation
.StatementsDateDelay = spnStatementsWeeks
End With


The "ProForma" is then saved in a random acess database (also home grown with VBa).

The user elects to run a proforma and the code then steps through the ProForma record, opening forms as required by the AmRequired property, completing the forms and then printing the paragaphs. Here is the bit for statements:

With CurrentProForma
' Openers
If .HeadingRequired And Not HeadingDone Then DoHeading
...

With .Statements
If .AmRequired Then
frmStatements.StatementsDelay = 7 * .StatementsDateDelay
frmStatements.DoDates
frmStatements.chkLiability = .optLiability And .chkConfined
frmStatements.chkLimitation = .optLimitation And .chkConfined
frmStatements.chkDamages = .optDamages And .chkConfined
frmStatements.cmdOK_Click
End If
End With
...

I hope you can see how it works from this.

So, after this very long explanation, this very simple question: what is the easiest way to create a script in using VBa which, when run in VBa, will either perform the functions of a lot of forms or use those forms to perform those functions?

Thanks for anyone who is still with me!

Geoff

[Frosty]

I love answering questions like this, because I always learn something in the process.

1) You haven't explained enough what you want to do. I know what *I* mean when I say "forms" and "functions" but I don't really know what you mean.

2) I didn't know you could do the following:

Type ptChild
Name As String
End Type
Type ptParent
Alive As Boolean
Child1 As ptChild
Child2 As ptChild
Child3 As ptChild
End Type
Sub TestParentChild()
Dim myParent As ptParent

With myParent
.Child1.Name = "Jack"
.Child2.Name = "Jill"
.Child3.Name = "Jane"
End With

MsgBox myParent.Child1.Name
End Sub
I definitely haven't encountered any time where the actual *order* of something in a module would matter (i.e., the ptChild type has to be declared before the ptParent type, or you get a forward reference compile error).

I'm not a huge fan of public types, preferring instead to create and use classes. But I'm not trying to completely change your methodology, I'm trying to help you work with your existing process.

I assume, from your explanation, that simply using a collection object has been explored and discarded?

Can you simplify your code some more and give a working (but basic) model of what you want to do (start with the child/parent structure above) and then maybe we can work out some kind of proof of concept.

I suspect you have *a lot* of code behind what you're trying to explain, so I appreciate you not posting it all. But you're going to need to post a project or post some sample code which demonstrates what you have and then we can discuss what you want to do.

[gmaxey]

Jason,

DataType declarations are a great way to return multiple values from a function.

Option Explicit
Type TestData
Long As String
Short As String
Custom As String
End Type
Sub CallDateFormat()
Dim pDate As TestData
pDate = DateFormatter(Date)
MsgBox pDate.Custom
MsgBox pDate.Long
MsgBox pDate.Short
End Sub
Function DateFormatter(ByVal pDate As Date) As TestData
DateFormatter.Long = Format(pDate, "dddd, MMMM dd yyyy")
DateFormatter.Short = Format(pDate, "MM/d/yy")
DateFormatter.Custom = Format(pDate, "yyyy")
End Function

[Geoff]

I am sorry that I didn't explain myself very well. I'll have another go! You will have to excuse my ignorance - I taught myself Basic a long long time ago, and then taught myself VBa. I have all the sins of an amateur basic programmer - spaghetti coding, insufficient commentary etc.

My template contains about forty user forms. Each is used to generate a paragraph of text. There are various variables in the text which the user selects from the controls in the form. The text is generated when the "OK" button is clicked.

Each form is opened from a toolbar and drop down menus.

I attach a sample which is probably the simplest of all the forms. Some are a lot more complex, generating up to a dozen sub-paragraphs.

I have removed some irrelevant code and added a few comments. I hope it is enough for you to understand the project.

The user calls up the forms he needs in succession, completes the optional elements and generates the text into the Word document.

At any given time he might need any combination of the forms and any variety of the settings.

However, each user finds that half the time he is generating one (or one of one or two) identical documents using the same forms (and therefore the same paragraphs of text) and the same settings (ie the same variable values) each time. Unfortunately, each user's ideal combination of forms and values is different.

What I have tried to do is create (yet another!) form from which the user can create scripts for the documents he commonly generates. The idea is that the script will call up the forms he wants to use, fill in the variable settings he wants to use, and generate the text. The only way I could of think of doing this was to create a "type" for each form with:

"AmRequired" (boolean) to check if the form is required and values for each control in the form. Here is the one for the form I have uploaded:

Type pfAllocation
AmRequired As Boolean
chkAllocate as Boolean
optAllocated as Boolean
optReAllocated as Boolean
optSCT As Boolean
optFT As Boolean
optMT As Boolean
chkReserved as Boolean
cbxJudgeName as String * 20
End Type

Then I create another user object with each form's object in it:

TypeProForma
Allocation as pfAllocation
.... [oodles more of them]
End Type

Finally there is a long user form which effectively lists the variables for the other forms, plus a checkbox to indicate whether the form is required (the .AmRequired value) The user works through that form selecting the paragraph generating forms he wants to use and setting his values for them.

The ProForma is saved.

Now the user can generate a standardised form. He selects the ProForma he wants which is loaded and then stepped through.

Here is the part that runs the form I have uploaded:

With .Allocation
If .AmRequired Then
frmAllocation.chkAllocate = .chkAllocate
frmAllocation.optAllocated= .optAllocated
frmAllocation.optReallocated = .optReAllocated
frmAllocation.chkHearing = False
frmAllocation.chkLocked = True
frmAllocation.optSCT = .optSCT
frmAllocation.optFT = .optFT
frmAllocation.optMT = .optMT
frmAllocation.chkReserved= .chkReserved
frmAllocation.cbxJudgeName=.cbxJudgeName
frmAllocation.cmdOK_Click
End If
End With

As you can see, the routine literally pokes each value from the ProForma into the relevant control on the form (You will see that a couple of values are not user variable for reasons I will spare you). Then it "clicks" the OK button to generate the text.

It works through the whole ProForma completing and "OK"ing each required form until the document is complete.

That, then, is my version of creating a script to automatically work through a series of User Forms.

I hope this explanation helps. I am sure that there is a better way.

I wish I could say that, as you suggest, the collection object has been considered and discarded but the truth is that it hasn't been considered at all. It probably got filed in the "don't understand" drawer along with the distinction between modules and class modules.

Any help would be greatly appreciated.

Geoff

[Frosty]

Thanks for uploading the form. Can you also upload (don't put any sensitive info in it) a module which contains the code the form uses, as well as any text of the various paragraphs? In short, can you provide a streamlined version of just one document template which I can run a macro, see the form pop up, make some choices, and see a resultant document based on my choices? I can begin to see what you're doing, but I need something concrete to give you a real response. Other than that, I can only talk about concepts:

1. Class modules are simply ways of organizing data structures (similar to the way you're using public Types) by utilizing properties. But you can also add in methods (think subroutines) to make your classes self-sufficient (as opposed to needing a subroutine in a code module in order to do something with your custom data storage "thing"

2. Forms are also class modules, with a user interface component added (so you design a form, add your check boxes, but then can use multiple instances of that form). What a lot of people miss with Forms is that they think there can only be one instance of a form in use while running a macro. You can have multiple instances, you just need to create a variable for the form, rather than reference the form by name. Think:

Dim oForm1 as frmAllocation
Dim oForm2 as frmAllocation
Dim ptAllocation as pfAllocation

ptAllocation.chkAllocate = True
Set oForm1 = New frmAllocation
With oForm1
.chkAllocation = ptAllocation.chkAllocate
End With

Set oForm2 = New frmAllocation
With oForm2
.chkAllocation = ptAllocation.chkAllocate
End With
Or maybe that's backwards, maybe you would have multiple ptAllocation1 and ptAllocation2 (which you would use for your two documents), and a single form to capture the data, a la

Dim oForm As frmAllocation
Dim ptAllocation1 As pfAllocation
Dim ptAllocation2 As pfAllocation

Set oForm = New frmAllocation
oForm.chkAllocation = True
ptAllocation1.chkAllocation = oForm.chkAllocation
ptAllocation2.chkAllocation = oForm.chkAllocation
Obviously I'm just trying to explain the concept to you, as you would need a lot more code to make the above work. But, at the core, you want your form to collect (and hold) data, and then you want to use that data in multiple places without having to recollect the same data multiple times.

3. Collections are a general repository for letting you "hold" a bunch of things you may want to work with. So you *might* be able to use a collection at a top level to hold a bunch of your data in order to re-use it (i.e., grab all the info you've populated into your ProForma Type, and then use it to generate multiple documents, rather than having to re-fill in the same data for a different document).

4. No apologies for being an amateur programmer. There is always someone who knows more or is more experienced. To me, that's the fun. I may know more than you, but there are people on this website who know more than me. But we all benefit by trying to solve problems, and then everyone who reads the post gets to learn (if they want to).

I suspect the reason you haven't investigated the differences between code modules and class modules is because you never really investigated OOP (Object-Oriented Programming) because it didn't exist when you learned Basic (it came around with C++). Before OOP, everything was linear programming. But by creating your own objects (and re-using them) you can get out of 1 to 1 relationship it sounds like you're stuck in of Capture Data then Use Data... you can Capture Data and then Use Data for Document 1, Use Data for Document 2... or Capture Data, then Use Data for Document 1, then Modify Data, then Use Modified Data for Document 2, etc etc.

[Geoff]

It is very kind of you to give me this much time.

I have uploaded a cut down version of the whole project. All of the files in the attached zip need to be in the same folder for it to work.

I have stripped out a lot of the forms to cut the size down from 4MB to something manageable.

To try it out:

Start a new document from the .dot file

"OK" past the warning note (it is triggered because of material I have removed)

On the far left of the toolbar, you will see "ProFormas" Click that.

Select any case number you like from the combo-box. (They are genuine, but they are in the public arena).

From the next combo-box select "Fast Track - PI - 1 day". The others use forms that I have removed.

Click OK and you will see what I am trying to achieve with the script.

The rest of the buttons on the toolbar will now be available. Most won't work or will crash because of the stuff I have removed, but "Allocation" will work (under "Case Management"), as will "Documents" and "Statements - all parties" under "Evidence", "One Party Only" under "Experts" and "Trial" under "Listing".

One or two of the others will work because they are also forms used by the "FT - PI - 1 day" ProForma.

If you try a few of the valid forms above you will see how the templates works a form at a time, which is the usual way of using it. The ProFormas are intended for repetitive tasks.

The "Create ProForma" button will work too, although if you create one running it will probably crash because some of the forms it uses have been removed.

Please feel free to poke around in the code generally. When you have stopped laughing (but it is the best I can do!), any advice as to a better way to create a "pro forma" script (or any other comments on better ways to do anything for that matter) would be gratefully received.

Geoff

[Frosty]

Geoff,

Hmm. As I suspected, there is *a lot* of code. And it seems as if it is currently working, but you're looking for a better methodology? Is that an accurate summary?

Quite frankly, there is too much code to really go through and give specific comments on. That said, a couple of comments :)

1. First off, it seems as if you've succeeded in your primary mission: making it work. So that's great. And this is a complicated enough process that simply succeeding in making it work is quite an impressive achievement. I'm sure tracking down bugs is hellish, and if you were ever hit by a bus and someone else had to come in and start modifying this code-- it would become very difficult. But it works. And that is the first rule of application development.

2. User Interface design (broadly). Any time you have to have several paragraphs of explanation at the top of a user form, you would probably be well-served to re-think the form and the structure of that form.

Simple things like having your OK button at the bottom right and the Cancel button to the left of that are not great ideas, because every built-in microsoft dialog works exactly the opposite (OK on the left, Cancel on the right). Unless, of course, this is different in the UK because you drive on the "funny" side (grin), in which case, ignore my comment.

Also, using the various control events to disable/enable/reset other controls is much better than popping up a messagebox to say "you've selected the wrong thing." For example, use the _Click event for the option and/or the _Change event for the ProForma Name dropdown to only allow the Continue button to be enabled if the proper combos are selected (i.e., amend/delete an existing, or create with something not in the list).

And rather than having a scroll bar, I think tabs would be a better interface for these purposes.

3. What next? It depends on what you want to do:

If there isn't a specific bug in your project and you don't really want to start on a massive revamp, I don't think there is a simple answer to your original question. I think there is a slightly simpler way of doing what you're doing within your existing methodology, but even that is just a guess based on my instinct and toying around with your code for a bit. And, at the end of the day, if it's already working, I'm not sure there's a big bang for the buck (your time or mine).

But if you're curious about utilizing a better structure, I think you're going to need to do the legwork to, essentially, create a "proof of concept" project which uses your existing methodology (i.e., homegrown database, .ini files for settings, a couple of vba forms, a toolbar, etc).

And then you need to build the absolute simplest version of what you're doing. Removing enough code to bring your code project (and ancillary files) down from 4 meg to 1.2 meg isn't enough. There's still too much to wade through.

But if you start with 1 form which has 2 options and creates a single document showing one or both options. And then a "proforma" form which allows you to set some individual default settings for that form, and build multiple documents from those two options settings... It shouldn't be too hard to convert a project that simple into a better system which is also a proof-of-concept. And you'll probably get input from a couple of different people on this forum, so that you're not just stuck with my perspective as to what would be "better."

From there, I think you'd get a "template" for doing a major revamp, and then you'd be able to ask specific questions at problem points... rather than ask for general advice about a project which is the result of several years of coding.

Hope this helps!

Cheers,
Jason

[Geoff]

Many thanks for putting time and effort into this for me.

I think I have known for a long time that I really need to start from scratch. Apart from anything else, I learned a lot as I went along and already know many things I could go back to and do better.

First, I think, I need to get to grips properly with the concept of classes. I think many of my answers are there.

Yes - I did discover that I had reversed the normal order for Cancel and OK, but only after the program had been in use for a couple of years. The level of IT literacy among my user base would make you weep, so I decided to leave it as is rather than confuse the poor dears. Which is also the reason for the message boxes pointing out what they have failed to complete - they don't seem to be able to work it out for themselves (even when I change control borders to flashing red to give them a clue).

One day I will start from the beginning. Unless there is a better day when I can persuade the powers that be to part with the money to have it rewritten professionally, which would be a much better solution.

Meanwhile, as you spotted, my concern was to try to find a better way to generate the scripts. It seems I have a lot to learn before I can do that.

Thanks again for your time and for taking an interest.

Geoff

[Frosty]

You don't really need to understand classes in order to accomplish this goal. There are many many professional programmers with good programs who don't use classes. They are not a necessity for programming in VBA (except when you desire to use document events).

A real understanding of classes can help (it's helped my programming), but you can accomplish everything you're doing without any classes (except for the forms, which are classes too).

But you can get a lot of help for free from this forum, if you have palatable chunks of code and questions which are answerable in a reasonable amount of time. It just requires you to do a lot of the prep work to simplify your questions. You are, at the core, asking a very broad question ("how can I make this program better?").

I wouldn't say you have a lot to learn, honestly. A couple of simple concepts and you will be on your way. But those concepts will be easier to explain with some concrete code samples to work with, rather than what you are actually working with (because, as you've stated, you've got a lot of stuff going on in a number of different places).

At the core you have:
1. A document generating program based on answers in a user form.
2. A desire to generate more than document without having to ask the same questions multiple times (especially when they will be the same answers).
3. The beginnings of a system of setting up "default answers" individualized to the user.

How I have addressed this kind of thing, in the past, is to follow Microsoft's module by putting user settings (i.e., your user defaults) into the profile in a folder. But where you put the user settings is less important. You could just as easily have a UserDefaults.ini, and separate it out into keys based on the user login ID.

I think if you separated out the "populate x type" function from the routine which calls a particular form (so that you can populate the type from the form OR from the defaults) you'd be well on your way. It's just a further modularization of what you're already doing.

Remember that you can use your forms without showing them. That may be the simplest way to answer your fundamental question. You've already got the code which takes the data in a form and generates the document. Figure out a way to get the data into the form without user interaction, and most of your work would probably be done (since then you could choose whether to use existing defaults, or show the form to get different answers).

Anyway, it's a pleasure to help point you in the right direction. I learn as much (or more) from facilitating others as I do from writing my own code.