0

I have created a huge VBA analysis code set (5000 lines+) that interrogates PLM (Aras, Windchill) design data to determine what division of a multi-divisional corporation created (owns) the part data. The function DeriveCreByDiv() loops through 327,000 rows of excel data and uses the master function GetCreByDivision() to apply rules to determine what division owns the Part data. To monitor the execution of a run I created am excel form that has fields like "Start Time", "Number of Rows", "Rows Completed", and "End Time". enter image description here

The form has a button "Run Derivation" that executes the function DeriveCreByDiv().

In the function DeriveCreByDiv() I have the following lines of VBA: To initiate the derivation run I implemented:

With DeriveCreatedByDivisionStatus
    .startTime.Caption = Now()
    .numberOfRows.Caption = CStr(gclastRowToExitFor)
    .rowsCompleted.Caption = "1"
End With

To update the "Rows Completed" I implemented the following:

Application.EnableEvents = True ' enable event processing
statusCount = statusCount + 1
rowCnt = rowCnt + 1

If statusCount = 100 Then
    With DeriveCreatedByDivisionStatus
        .rowsCompleted.Caption = CStr(rowCnt)
    End With
    Application.Wait (Now + TimeValue("00:00:10"))
    statusCount = 0
End If

The issues is that the "Rows Completed" field "rowsCompleted.caption" is not updating as the VBA function DeriveCreByDiv() runs.

enter image description here

The Form is Started with F5 I then click "Run Derivation"

Private Sub RunDerivation_Click()
    EvalTools.DeriveCreByDiv
    With DeriveCreatedByDivisionStatus
        .endTime.Caption = Now()
    End With
End Sub

In the Module "EvalTools" there is the Function DeriveCreByDiv. At the bottom of the main loop

    For Each partRow In partWks.Rows
    ...
        Application.EnableEvents = True ' enable event processing
        statusCount = statusCount + 1
        rowCnt = rowCnt + 1
        
        If statusCount = 100 Then
            With DeriveCreatedByDivisionStatus
                .rowsCompleted.Caption = CStr(rowCnt)
            End With
            Application.Wait (Now + TimeValue("00:00:10"))
            statusCount = 0
        End If
   
   Next partRow
8
  • 2
    Did you try to use the .Repaint method? Commented Sep 3 at 13:17
  • 3
    Needs a bit more context - exactly how is your form launched, and is this code in the form itself? If Yes then use (eg) With Me in place of With DeriveCreatedByDivisionStatus Commented Sep 3 at 15:16
  • 1) I did not try .repaint Commented Sep 3 at 21:01
  • 2
    You are using the default instance of the form. Here's a good article on why not to do that. Commented Sep 3 at 22:11
  • 2
    Rather than writing to the form control directly I'd add a method to the form that updates the caption and repaints the form example here Commented Sep 3 at 22:13

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.