VBA memory eater

Question

I've created a small macro for inserting a hidden bookmark to a numbered paragraph

Sub blablabla()
    Dim BkmrkName As String
    ActiveDocument.Bookmarks.ShowHidden = True
    Application.ScreenUpdating = False

        heanum = InputBox("Enter Heading1 number", "List paragraph", "1")
            Select Case Len(heanum)
             Case 1
              sPos1 = "00" & Left(heanum, 1)
             Case 2
              sPos1 = "0" & Left(heanum, 2)
             Case 3
              sPos1 = Left(heanum, 3)
            End Select

    ActiveDocument.ConvertNumbersToText
         lisnum = Left(Selection, InStr(Selection, vbTab))
         ActiveDocument.Undo
         If IsNumeric(Left(lisnum, 1)) = True Then
         lisnum = Left(lisnum, Len(lisnum) - 2)
            Select Case Len(lisnum)
             Case 3
              sPos2 = "00" & Right(lisnum, 1)
             Case 4
              If Mid(lisnum, 2, 1) = Chr(46) Then
               sPos2 = "0" & Right(lisnum, 2)
              ElseIf Mid(lisnum, 3, 1) = Chr(46) Then
               sPos2 = "00" & Right(lisnum, 1)
              End If
             Case 5
              If Mid(lisnum, 2, 1) = Chr(46) Then
               sPos2 = Right(lisnum, 3)
              ElseIf Mid(lisnum, 3, 1) = Chr(46) Then
               sPos2 = "0" & Right(lisnum, 2)
              ElseIf Mid(lisnum, 4, 1) = Chr(46) Then
               sPos2 = "00" & Right(lisnum, 1)
              End If
             Case 6
              If Mid(lisnum, 3, 1) = Chr(46) Then
               sPos2 = Right(lisnum, 3)
              ElseIf Mid(lisnum, 4, 1) = Chr(46) Then
               sPos2 = "0" & Right(lisnum, 2)
              End If
             Case 7
              sPos2 = Right(lisnum, 3)
            End Select
         End If
        ActiveDocument.Bookmarks.Add Name:=Chr(95) & sPos1 & Chr(95) & sPos2

    Application.ScreenUpdating = True
End Sub

The user select a numbered paragraph and triggers the macro. Macro runs once per trigger and inserts a hidden bookmark with the name like _001_042 if the selection begins with "any_character".42. The first "001" is meant to depict a chapter number (i.e. "Chapter 1"), but could be any number and is determined by a user input through a message box. This macro works, though with each next numbered paragraph runs slower and slower. When I get to paragraph 1.100 it takes ~5 minutes (!!!) for macro to insert a single bookmark "_001_100".

Why does such a long latency happen? Is it possible to optimize the macro to run faster?

Many thanks in advance!

Answer 1

This macro does it's job in only few seconds:

Sub AddBkmrkSmart()
    Dim Author, Year As String
    ActiveDocument.Bookmarks.ShowHidden = True

        heanum = InputBox("Enter Heading1 number", "Heading1", "1")
            Select Case Len(heanum)
             Case 1
              sPos1 = "H00" & Left(heanum, 1)
             Case 2
              sPos1 = "H0" & Left(heanum, 2)
             Case 3
              sPos1 = "H" & Left(heanum, 3)
            End Select
        'Debug.Print "sPos1: " sPos1

        lisnum = Selection.Range.ListFormat.ListValue
            'Debug.Print "Iteration 1 lisnum: " & lisnum
            Select Case Len(lisnum)
             Case 1
              sPos2 = "L00" & Left(lisnum, 1)
             Case 2
              sPos2 = "L0" & Left(lisnum, 2)
             Case 3
              sPos2 = "L" & Left(lisnum, 3)
            End Select
        'Debug.Print "sPos2: " sPos2

    ActiveDocument.Bookmarks.Add Name:= "_" & sPos1 & "_" & sPos2
End Sub

I suggest is as answer along with Tomalak's one.

Answer 2

Avoid making useless changes to a document. That applies to manual editing, and it applies doubly to VBA code.

Your ConvertNumbersToText / Undo is as close to completely useless as it gets. Don't do such things. Word has an Undo buffer that you stress for no reason with this pointless edit. Despite being a wasteful no-op, you also destroy the user's ability to undo their own actions with this.

First off, you solve the problem of padding a string in the worst possible way, (multiple times!). Lets fix that.

Function PadLeft(ByVal value As String, length As Integer, Optional padding As String = " ")
    PadLeft = String(Max(0, length - Len(value)), padding) & value
End Function

This function will pad any string to any given length. However, it depends on another utility function that will return the greater of two numbers.

Function Max(a As Long, b As Long) As Long
    If b > a Then Max = b Else Max = a
End Function

Now, how about this code:

Sub SetParagraphBookmark()
    Dim para As Range, _
        paraNum As Long, headerNum As String, _
        prefix As String, suffix As String

    Set para = Selection.Paragraphs(1).Range
    paraNum = para.ListFormat.ListValue

    If paraNum Then
        headerNum = InputBox("Enter Heading1 number", "List paragraph", "1")
        If headerNum > "" Then ' otherwise the user clicked Cancel
            prefix = PadLeft(headerNum, 3, "0")
            suffix = PadLeft(paraNum, 3, "0")
            ActiveDocument.Bookmarks.Add "_" & prefix & "_" & suffix, para
        End If
    Else
        MsgBox "Please click on a valid list paragraph first.", vbInformation
    End If
End Sub

This sets a bookmark that spans the entire paragraph the cursor is in, without moving the cursor or making any other changes to the document.

General notes:

• Why would you ever write Chr(46) instead of "."?
• Indent your code properly, this increases readability.
• If you find yourself copy-pasting any section of code, you are already doing something wrong. If you need something complex done in two places, write a function.
• Try breaking up the work you do into the smallest possible useful unit, like I did with PadLeft and Max. This allows re-using bits of your code elsewhere. You might want to place them into a separate Utilities module as well.
• Set breakpoints in your code to see what's going on.
• Word has a comprehensive object model. You can find out just about anything about the document by navigating around that object model without resorting to steamroller tactics like ConvertNumbersToText. Taking some time to pick the right property from the right object pays. There will be a lot documentation-reading involved, you'll just have to deal with that. Luckily the Microsoft documentation is superb.
• It's most useful to enable the "Locals Window" and the "Immediate Window" in the VBA editor. It allows you to browse the objects you work with while you are in break mode, which helps to identify the properties you are looking for.
• Last, but not least: Always, always, always have Option Explicit at the top of your modules. There is a setting in the VBA IDE's options for that ("Require variable declaration"). Enable it. Manually add that line to any module that does not have it. Fix the errors you get before you do anything else. (Disable the "Auto syntax check" feature while you are at it, this feature is counter-productive.)