Procedure Too Large - Ideas?? 2

[RP1America]

In my cmdCreate_click procedure, I got this error:

Procedure too large
When compiled, the code for a procedure can't exceed 64K. This error has the following cause and solution:

Code for this procedure exceeds 64K when compiled.
Break this, and any other large procedures, into two or more smaller procedures.

This is basically like clicking OK. How can I break an OK click procedure into two!?

Your help is appreciated!!

Thanks!

 

[RP1America]

Nevermind, I figured out to divide that procedure over multiple modules and call the procedure within the click event.

 

[fumei]

Woa! Best practice would have this broken up, and that is a GOOD best practice. Use Call.

Sub cmdOK_Click()
   Call DoThisStuff
   Call DoSomethingElse
   Call AndAnotherThing
   Call Finally
End Sub

Sub  DoThisStuff()
  ' stuff
  ' stuff
  ' stuff
  ' stuff
End Sub

Sub DoSomethingElse()
  ' stuff
  ' stuff
  ' stuff
  ' stuff
  ' stuff
  ' stuff
  ' stuff
  ' stuff
End Sub

Sub AndAnotherThing()
  ' stuff
  ' stuff
  ' stuff
  ' stuff
End Sub

Breaking up procedures into chunks makes debugging much much much easier.

While not a hard and fast rule, generally, I never let any procedure be longer than 30 lines. Generally, I have found anything more than that can, and should, be "chunked".

If you got that error, that must be a monster.

I am curious about: "over multiple modules"

Multiple modules? Why multiple modules? Your procedures can be in the same module. In fact, as it appears to be a click event, thus likely in a userform module, you can have all your procedures in that module.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[RP1America]

The "beast" was all in one click event where I was opening, populating bookmarks, saving, then closing 246 Word documents.

I figured I would need multiple modules in order to be able to hold all the code, else I would get the same error, only this time in the module.

 

[fumei]

I figured I would need multiple modules in order to be able to hold all the code, else I would get the same error, only this time in the module. "

No, that is not correct. The error comes from too much code for one procedure. I will repeat your quote.

Procedure too large

Code for this procedure exceeds 64K when compiled.
Break this, and any other large procedures, into two or more smaller procedures.

The answer to that is, as I stated, break it into smaller Called chunks. They do NOT have to be in multiple, or even different modules whatsoever.

"The "beast" was all in one click event where I was opening, populating bookmarks, saving, then closing 246 Word documents."

I just did a test (and I admit it is likely less complicated that what you are doing...however), where I:

1. opened 120 doc files
2. populated bookmarks with text
3. saved each doc file
4. closed them

It took 14 lines of code. That's it. 14 lines (two of which are actually one line split into two using the underscore character).

My point being is the number of documents is irrelevant (in terms of the length of the procedure code which is your error).

What are you coding/doing that would be using such a gigantic amount of code????

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[strongm]

>The "beast" was all in one click event where I was opening

Crikey, there was nothing in the build up to this point (i.e. your previous posts on different aspects of this project) that even vaguely indicated that you were going to have such a monster click event ...

Have you got a different processing requirement for each document or something?

 

[RP1America]

 '1 - 401(K) FS LS COst Pages
With Documents.Open(FolIn6 + "\401(k) FS LS Architect A2.doc")
    With .Bookmarks("QYD").Range
        .Text = strDateText
        .AutoFormat
    End With
    .Bookmarks("RT2").Range.Text = txtRT21
    .Bookmarks("RT1").Range.Text = txtRT11
    .SaveAs (FolOut6 + "\401(k) FS LS Architect A2.doc")
    .Close
End With

This is one of 246 sets of code within the click event. Bookmarks RT2 and RT1 vary depending on which document it is.

I am (probably quite obviously) extremely new to VB. I imagine there may be some short version of what I have, yet, that is all I have been able to put together thus far.

 

[SkipVought]

When you have a repetative process, you ought to look for ways to leverage on the repetition of code.
As Gerry pointed out, it can be done with a whole lot less code.

Where you do have uniqueness in each element of a process, store those things in a lookup table. So instead of 14 lines, you might end up with 20 or so and a table of 246 rows.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[strongm]

>This is one of 246 sets of code

Ah - so you have something like this for every single individual document?

When you say

Bookmarks RT2 and RT1 vary depending on which document

Do you mean that the bookmarks have different names in different documents, or that the action that needs to occur is different for each document's bookmarks, or that the bookmarks point to a different location ... please clarify.

We certainly ought to be able to streamline this

 

[RP1America]

Ah - so you have something like this for every single individual document?

Yes.

Do you mean that the bookmarks have different names in different documents, or that the action that needs to occur is different for each document's bookmarks, or that the bookmarks point to a different location ... please clarify.

Bookmark names are identical in each document. They are simply valued differently for each document. Those values come from the userform. There are definitely many documents that end up having the same value. I am sure this is where my code could become more concise.

I suppose I originally thought I would need to open each doc, perform the merge of data, save, and close each doc individually before moving on to the next. Perhaps that is still the case, yet my code has turned out very rudimentary in not realizing there is a more concise approach.

 

[fumei]

I simply do NOT believe you need "something like this for every single individual document?"

There is an inconsistency here.

Bookmark names are identical in each document. They are simply valued differently for each document. Those values come from the userform.

A call to a process will only take a parameter value one time. That is, it takes a value and does what it does...and then stops. For example:

Sub YaddaYadda()
Dim file
Dim path As String
path = "C:\ZZZ\Bookmarks\"
file = Dir(path & "*.doc")
Do While file <> ""
    Documents.Open Filename:=path & file
    [COLOR=red]' the above is now the ActiveDocument[/color red]
    With ActiveDocument
       .Bookmarks("here").Range.Text _
            = txtWhatever.Text
       .Save
       .Close
    End With
    file = Dir()
Loop
End Sub

The code above will - as I have already stated:

1. open EACH .doc file in c:\zzz\bookmarks
2. puts the text of a userform textbox - txtWhatever - in the bookmark "here".

This assumes each file HAS such a bookmark! This can be tested for (and probably should be!) of course.

3. saves the file
4. closes the file
5. opens the next file and repeats 1 to 4 until all .doc files are processed. This happens whether it is 20 files, 100 files, 246 files, or 800 files. The number of files is irrelevant (aside from possible memory hogging...)

If you are trying to state that you are processing 246 files in ONE process, and using different values for different files...then, hmmmm:

1. HOW are you determining which files gets which value?
2. More importantly, HOW are you changing the value of the textbox on the userform? You can not do that while there are other processes actually executing.

I agree with strongm, this absolutely, positively, can be stream-lined to a tiny fraction of whatever code you have now.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[macropod]

Hi Gerry,

I simply do NOT believe you need "something like this for every single individual document?"

Indeed, I showed RP1 how to update all the files in a given folder, using much the same approach as you have done (except that I provided for folder browsing as well) here:

http://www.tek-tips.com/viewthread.cfm?qid=1573323&page=1

One could just as easily have a comma-separated filename list and use essentially the same approach for looping through all files in the list, without having to repeat the same bits of bookmark updating code over and over.


Cheers
[MS MVP - Word]

 

[fumei]

Of course. There are many different ways to go about what seems to needed. The is one of the beauties of VBA, there are often multiple methods to achieve the same thing. Really, it is all about knowing what requirements are actually needed.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry