Results 1 to 14 of 14
  1. #1
    Bill Altmann
    Guest

    Faster Form Field Updating? (VB6/Word2000)

    I've written VBA code for Word to read text or binary file
    and use its contents to update fields in a User Form.
    The codes runs very slowly when updating the fields.
    (114 fields requires more than 30 seconds.) The
    "lock/unlock" form toolbar button clears all fields
    very quickly.

    How can I write new code to update all fields in a loop
    very fast?

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Faster Form Field Updating? (VB6/Word2000)

    Shouldn't be that slow. Want to post your code so we can see...
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    Bill Altmann
    Guest

    Re: Faster Form Field Updating? (VB6/Word2000)

    I basically read in the ASCII file and decode its
    content. Then I format the text fields as follows:

    Sub WriteField(nstr as String, val as String)
    ActiveDocument.Range.FormFields(nstr).result=val
    end sub

    The execution proceeds more quickly when I first
    open the form file and execute the macro. But if I
    repeatedly execute the same macro (I use a new
    toolbar command), then the time goes way up.
    Is Word managing its structures well with a long
    list of fields in one document?

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Faster Form Field Updating? (VB6/Word2000)

    If the relationship between the ASCII values and the fields is
    simply sequential, you could try a different approach.

    Assuming the ASCII stuff is in an arrary "strArray()",

    <pre>Dim i as long
    Dim aField as Field
    For each aField in Activedocument.StoryRanges(wdMainTextStory)
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  5. #5
    Bill Altmann
    Guest

    Re: Faster Form Field Updating? (VB6/Word2000)

    This would work if the form is never changed and I
    could count on the sequence of the form fields by
    name and index. If the user unlocks the form
    (which is really a report being filled in with the
    macro), and then rearranges the fields on the page
    without renaming them, then will the indexing of the
    fields in formfields() be changed?

    Are you suggesting that the processing in VBA of
    text names of fields is the slowest part of the loop
    of processing?

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Faster Form Field Updating? (VB6/Word2000)

    You might try putting the names and values into 2 arrays, then modify Kefvin's code to cycle through the form fields once, but add code which cycles through the array for every form field.

    It sounds messy, but would probably speed up things a lot.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Bill Altmann
    Guest

    Re: Faster Form Field Updating? (VB6/Word2000)

    The following code takes more than 30 seconds on
    a single-page form with 114 fields:

    For Each Field In ActiveDocument.FormFields
    If Field.Type = wdFieldFormCheckBox Then
    Field.CheckBox.value = False
    Else
    Field.result = ""
    End If
    Next

  8. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Faster Form Field Updating? (VB6/Word2000)

    Bill,

    I know little about the workings of the guts of VBA. I only know that I use code like that and it seems pretty fast!

    But your thought makes sense. I would not be surprised to learn that your PC only has 128MB or less of RAM. But that's only a guess too.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  9. #9
    Bill Altmann
    Guest

    Re: Faster Form Field Updating? (VB6/Word2000)

    My machine is a Pentium-II laptop with 224MB.

    I tried the following two routines using a Word file
    with a table including 100 text fields (10 rows by
    10 columns).

    reloadFields1 requires 28 seconds.
    reloadFields2 requires only 9 seconds.

    Sub reloadFields1()
    starttime = Timer
    With ActiveDocument
    For Each fld In .FormFields
    fld.Result = fld.Name
    Next
    End With
    MsgBox Format(Timer - starttime, "##0") & " seconds", , "Seconds to Execute" 'debug
    End Sub

    Sub reloadFields2()
    Dim i, fCount As Long
    starttime = Timer
    With ActiveDocument
    fCount = FormFields.Count
    For i = 1 To fCount
    .FormFields(i).Result = "F" & Format(i, "###0")
    Next
    End With
    MsgBox Format(Timer - starttime, "##0") & " seconds", , "Seconds to Execute" 'debug
    End Sub

  10. #10
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Faster Form Field Updating? (VB6/Word2000)

    There was an <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=vb&Number=35390&page=&view= &sb=&o=&vc=1>earlier thread</A>, which indicated a completely different result (albeit for a different collection), that indicated it was much faster to user the "for each" construct than to use an index.

    My machine gives a response of 1 second for each. Except it's perhaps doing something slightly different if you run one routine which populates the fields, and then run another routine with the formfields populated.
    (Rerun with 1000 form fields- I get 25 & 28 secs first run; 34 & 42 second run)

    Id fld defined as a formfield? Or is it a variant by default? That may make a difference.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  11. #11
    Bill Altmann
    Guest

    Re: Faster Form Field Updating? (VB6/Word2000)

    I found that the sequence of formfield objects in the
    formfields() collection corresponds to their position
    in the text document. Because I want to allow the
    user to re-arrange the fields in a form of their own
    design (without deleting or renaming any fields),
    I cannot count on the index in the formfields()
    collection to always correspond to the same field
    name.

    My code is written to use the field names, as they
    correspond to the decoded information read from the
    imported file.

    I wrote the following where I create a new array of
    formfield objects and store pointers ot the fields
    of the form in this array. This reduces the execution
    time from >30 seconds (see earlier append for 100
    fields) to about 12 seconds. I think it just eliminates
    the VBA work to "find" each field in a for/each loop.

    Dim myFields(128) As FormField

    Sub reloadFields3()
    Dim i As Long, fld As FormField, fCount As Integer
    starttime = Timer
    With ActiveDocument
    fCount = .FormFields.Count
    For i = 1 To fCount
    Set myFields(i) = .FormFields(i)
    Next
    For i = 1 To fCount
    myFields(i).Result = "XXX" & Format(i, "000")
    Next
    End With
    MsgBox Format(Timer - starttime, "##0") & " seconds", , "Times"
    End Sub

  12. #12
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Faster Form Field Updating? (VB6/Word2000)

    Bill,

    The code below reports 0 seconds to execute. I used it to
    populate 100 fields in 100 table cells with the field
    index number. Notice I use the Fields collection NOT
    FordFields. That might have something to do with your slow
    times.

    <pre>dim aField as Field
    StartTime = Timer
    For Each aField In ActiveDocument.Fields
    aField.Result.Text = CStr(aField.Index)
    Next
    Set aField = Nothing
    MsgBox Format(Timer - StartTime, "##0") & " seconds", , "Seconds to Execute" 'debug
    </pre>

    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  13. #13
    Bill Altmann
    Guest

    Re: Faster Form Field Updating? (VB6/Word2000)

    All-
    I realize that loading the array or collection of fields
    can be accomplished quickly, but I need a method
    which can randomly access the collection (or
    array) based on the name of the field. When I
    decode the imported data, I need to write the
    decoded values into the right fields, regardless
    of the SEQUENCE of index numbers of those
    fields. The indices change as the fields are
    rearranged on the Word page.

    I've tried storing the names of the fields in a
    concatenated string and then using the LEN()
    function to find the name in the single string;
    then I use that offset as an index into an array.
    The value of that array element is the index of
    the collection term which should be loaded
    with the new data. All of this string and array
    initialization can be done be ordinal sequencing
    through the formfields collection.

    This method reduces the execution time for
    loading data into the formfields by about 20%,
    but not a dramatic reduction I'm seeking.
    There seems to be a general inefficiency in
    the way Word indexes a collection using the
    'name' attribute.

  14. #14
    Bill Altmann
    Guest

    Re: Faster Form Field Updating? (VB6/Word2000)

    Here's the example code which reduced the time for a form with 100 fields from
    28 seconds to 10 seconds, pretty close to the time for sequential
    access only:

    Sub reloadFields4() ' 10 seconds for 100 fields
    Dim i As Long, fld As FormField, fCount As Integer, ttl As String
    Dim myFieldIndex(1000) As Long, off As Long
    Dim myFieldNames(100) As String
    On Error GoTo errorreloadfields4
    starttime = Timer
    With ActiveDocument
    fCount = .FormFields.Count
    For i = 1 To fCount ' this can be initialized when doc opened
    Set myFields(i) = .FormFields(i)
    myFieldIndex(Len(myFieldNames(0)) + 1) = i
    myFieldNames(0) = myFieldNames(0) & "<" & myFields(i).Name & ">"
    Next
    midtime = Timer
    For i = fCount To 1 Step -1
    ttl = "<" & myFields(i).Name & ">" ' find this field by name
    off = InStr(1, myFieldNames(0), ttl) ' using concatenated names
    myFields(myFieldIndex(off)).Result = "A" & Format(i)
    Next
    End With
    endtime = Timer
    ttl = Format(ActiveDocument.FormFields.Count, "###") & " fields in reloadFields3()"
    MsgBox Format(endtime - midtime, "##0") & " seconds", , ttl 'debug
    GoTo endreloadfields4
    errorreloadfields4:
    MsgBox ttl, , "Error in reloadFields4"
    endreloadfields4:
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •