Range Selection & Case Statements.

Question

Could someone please help me out with this? I've have about 2000 lines of code here all in the "ThisDocument" and all under the sub name of Document_ContentControlOnExit. The code all works but some parts can sometimes overlap with each other in action. Isn't there anyway that I can divvy up some of these so that the entire document isn't bogged down? I just want to be able have a sort of "End Sub" after most of these but the vba won't allow two of the same name. Thanks.

Answer 1

Hi,

Could you give more detail description of your issue?Do you wanna optimize the macro code? Or other mind? I'm sorry but I cann't understand....

Thanks,

Milan

Answer 2

Hi,

Could you give more detail description of your issue?Do you wanna optimize the macro code? Or other mind? I'm sorry but I cann't understand....

Thanks,

Milan

Please, please, please forgive my stupidity, but it just hit me. Here is a typical code from me:

Select Case ContentControl.Title
Case "pmh"
Select Case True
Case InStr(1, ContentControl.Range.Text, "DU") > 0 


            For Each CCrf9 In oCCrf9
            If CCrf9.Range.InRange(Selection.Tables(1).Range) Then
                CCrf9.DropdownListEntries.Item(9).Select
            End If
            Next
            For Each CCboxrf9 In oCCboxrf9
            If CCboxrf9.Range.InRange(Selection.Tables(1).Range) Then
               CCboxrf9.Checked = True
            End If
            Next

            For Each CCrf11 In oCCrf11
            If CCrf11.Range.InRange(Selection.Tables(1).Range) Then
                CCrf11.DropdownListEntries.Item(11).Select
            End If
            Next
            
            For Each CCboxrf11 In oCCboxrf11
            If CCboxrf11.Range.InRange(Selection.Tables(1).Range) Then
               CCboxrf11.Checked = True
            End If
            Next
        End Select
        Case Else
        End Select

I previously thought that the ContentControlsOnExit event was slowing my computer down, but it was the code. I have the code checking all the contentcontrols in the document named "pmh" and then only making the necessary changes in the selected table, which gets to be a hassle by the 50th table. What i need is to have vba only check the pmh in the selected table. That should be possible, right? even if i have 50 content controls under the same name? I thinking something like a SetRange method.

Answer 3

Allright, I guess what i'm looking for is something like:

If ContentControl.Range.InRange(Selection.Tables(1).Range) Then

But is that easy to incorporate into that case statement? Will it work really restrict itself to checking only that one in that table and not everything under the name and making changes where applicable? I gotta be honest, i'm not sure about it. Here's some of my guesses:

Select Case ContentControl.Title
Case "pmh"
Select Case True
Case pmh.Range.InRange(Selection.Tables(1).Range) And InStr(1, ContentControl.Range.Text, "DU") > 0 


            For Each CCrf9 In oCCrf9
            If CCrf9.Range.InRange(Selection.Tables(1).Range) Then
                CCrf9.DropdownListEntries.Item(9).Select
            End If
            Next
            For Each CCboxrf9 In oCCboxrf9
            If CCboxrf9.Range.InRange(Selection.Tables(1).Range) Then
               CCboxrf9.Checked = True
            End If
            Next

I guess I could also do the For Each + If-Then combo statements like the nested commands. but will these methods give better performance?

Answer 4

There is only an infinitesimal difference in resources between using 'if' statements and 'case' statements; it is best to choose whatever is easiest to read. In fact, depending on the vagaries of the compiler, there may well be no difference (a compiler will often interpret a short 'case' statement in exactly the same way as a series of 'if' statements; and a large number of consecutive 'if' statements in the same way as a 'case' statement).

Only if commands are selected by the if/case statement will they take up memory/processor time.

You say you are dealing with thousands of lines of code...in which case I would recommend you call different subroutines based on the name of the ContentControl. To have so many lines within a single subroutine can make it very difficult to read! But it's really up to you. If you've now gotten used to it, and you've no plans to hand it over to another programmer, you may as well keep it in one block.

Answer 5

FWIW, Your code could probably be made somewhat more efficient by having 'Exit For' or 'Exit Sub' statements in the loops, so that unnecessary code execution can be avoided. For example:

For Each CCrf9 In oCCrf9
  If CCrf9.Range.InRange(Selection.Tables(1).Range) Then
    CCrf9.DropdownListEntries.Item(9).Select
    Exit For
  End If
Next

Further improvements can be achieved by structuring the code so that the most likely controls/conditions are tested first.

---

Cheers
Paul Edstein
[MS MVP - Word]

Answer 6

Thanks a lot guys! i could do well with advice like these. everything seems in order for now, now that i'm taking everything off the onexit event and into public subs and converting everything to if statements. though, i guess i'll never be sure until i test it out. curiously, my issue of the minute (cause i can't go a minute without one) is:

Selection.Tables(1).Rows(3).Delete
Selection.Tables(1).Rows(4).Delete
Selection.Tables(1).Rows(5).Delete
Selection.Tables(1).Rows(6).Delete
Selection.Tables(1).Rows(7).Delete
Selection.Tables(1).Rows(8).Delete
Selection.Tables(1).Rows(9).Delete
Selection.Tables(1).Rows(10).Delete
Selection.Tables(1).Rows(11).Delete
Selection.Tables(1).Rows(12).Delete
Selection.Tables(1).Rows(13).Delete
Selection.Tables(1).Rows(14).Delete
Selection.Tables(1).Rows(15).Delete
Selection.Tables(1).Rows(16).Delete
Selection.Tables(1).Rows(17).Delete

Always get an error by row 11, telling me that it doesn't exist

Answer 7

That's because, by the time you get to row 11, it no longer exists. When you deleted row 3, what was row 17 became row 16, and so on. You need to delete in reverse order:

With Selection.Tables(1)
  .Rows(17).Delete
  '...
  .Rows(3).Delete
End With

Alternatively, you could use:

Dim i As Long
With Selection.Tables(1)
  For i = 3 To 17
    .Rows(3).Delete
  Next
End With

PS: If you know which table it is, you can avoid the selection and use much more efficient code:

With ActiveDocument
  .Range(.Tables(1).Rows(3).Range.Start, .Tables(1).Rows(7).Range.End).Rows.Delete
End With

---

Cheers
Paul Edstein
[MS MVP - Word]

Answer 8

Christ macropod, you're my knight in shining armor. sincerely, you're such a big help, thanks.

Answer 9

well, after all that effort; sadly, the result was the same. The code just runs so slow and threatens to crash the program at 30+ pages. If i had to guess, perhaps it was this command: ContentControl.Range.Information(wdEndOfRangeColumnNumber). Ample use of it was made. Or maybe it simply attempted to do to much, It was sort of like a autocorrect macro after all. Well at least I tried, thanks so much guys for your helping. I really learned a lot! lol, this thread totally got outta hand.

Answer 10

Without seeing all of the code, preferably in the document it applies to, it is difficult to give specific advice.

---

Cheers
Paul Edstein
[MS MVP - Word]