The Infamous Win32 Exception

[dmcclurg]

I am receiving an unhandled win32 exeption [256] when I execute my code. When I walk through the debugger line-by-line, I do not receive the error! It also goes away when I comment out two specific lines. These lines update the progress bar on a userform shown at the end of the loop. Very strange...any ideas? I have been starring at this too long.

This macro loops through a massive workbook and transposes monthly data from two sheets and merges them into the first active sheet.

Public Sub Main()
   On Error GoTo ErrorMessage
   
   Dim MainSheet As Worksheet, SisterSheet As Worksheet
   Dim CurrentContract As Range, FirstSisterContract As Range,    NextContract As Range
   Dim NextSisterContract As Range
   Dim SisterContractRow As Long, CurrentContractNum As Long
   Dim SubSectionCount As Integer, CurSectionNum As Integer, DestinationIndex As Integer
   Dim FirstRun As Integer, p As Integer
   
   CurSectionNum = 1
   FirstRun = 0
   Set MainSheet = ActiveSheet
   Set SisterSheet = Sheets(FindComSheet)

   Set CurrentContract = GetCompanyCell
      
      '------------------------------------------------------
      'UI Initialization
      '------------------------------------------------------
      Application.ScreenUpdating = False
      frmProgress.lblInfo.Visible = True
      Call FindLastContract
      frmProgress.lblInfo.Caption = "Transposing Data..."
      '------------------------------------------------------
      
   Do
   
      CurrentContractNum = CurrentContract.Offset(0, -1).Value
      SisterContractRow = FindContractRow(SisterSheet, CurrentContractNum)
      
      EngageStopRow = True
      Set NextContract = TransposeSingleContract(MainSheet, CurrentContract, SubSectionCount, True)
      
      If SisterContractRow = -1 Then GoTo SkipMerge
      
      EngageStopRow = False
      Set NextSisterContract = TransposeSingleContract(SisterSheet, _
        SisterSheet.Range("C" & SisterContractRow), SubSectionCount)
      
        
        For p = 1 To UBound(SisterSubSectionHeaders, 2)
            
          MainSheet.Activate
          DestinationIndex = EvaluateSubSectionHeaders(SisterSubSectionHeaders(1, p))
          If DestinationIndex > 0 And FirstRun > 0 Then
            Call MergeContract(SisterSheet, _
              Range("X" & SubSectionHeaders(2, DestinationIndex)), CurSectionNum)
          ElseIf DestinationIndex > 0 Then
            Call MergeContract(SisterSheet, _
              Range("X" & SubSectionHeaders(2, DestinationIndex)), CurSectionNum, True)
            FirstRun = 1
          End If
          SisterContractRow = NextSisterContract.Row
          CurSectionNum = CurSectionNum + 1
          
        Next
        
SkipMerge:
      Set CurrentContract = NextContract
      CurSectionNum = 1
      SubSectionCount = 1
      
      '--------------------------------------------------
      'UI Update
      '--------------------------------------------------
      frmProgress.ProgressStyle1 (NextContract.Row / StopRow), True 'update progress bar
      DoEvents
      '--------------------------------------------------
      
   Loop Until StopRow = NextContract.Row

[Code]

 

[dmcclurg]

Wow. That came out ugly. I would be happy to send the workbook to anyone who is interested! Any help is appreciated!

 

[shg]

The closing code tage is [/code]

Where is StopRow declared and initialized?

 

[dmcclurg]

StopRow, sadly, is a global variable that is constantly changed by other subroutines when rows are deleted. It is initialized in the FindLastContract function.

 

[Hermanito]

What happens when you comment out only one of those rows? Maybe you can narrow it down to the exact line that causes the issue?

 

[dmcclurg]

When I comment out the progress bar update line of code it goes away. However, this is a line of code that I have been using in a lot of programs, and I don't understand why it errors out in this one. The strange thing is that if I F8 through the code the error never shows up, but if I break a line and F5 the code quickly, the error will appear.

The error is a complete Excel crash, and I don't have an opportunity to break into my code. I have tried to debug the stack in VStudio, but I just don't know that much. I can hear the sound of the code breaking behind the crash window (where Excel tries to recover), but everything freezes.

 

[dmcclurg]

UPDATE: I have coerced the VBA error from the program. It is a runtime error (automation) labeled: -2147417848 (80010108). The description is: "the object invoked has disconnected from its clients".

It immediately freezes when I hit Debug, and I cannot see the line in question; however, in order to coerce the message, I break at the progress bar update line, F5 once, undo the break, hit F5, and I get the error message.

Any thoughts?

 

[Hermanito]

This progress bar, is that one you created yourself?
Which component are you using?
In standard Excel configuration, there is no progress bar in the toolkit to build userforms, so you must have gotten an extra component from somewhere. My guess it that that component contains a nasty bug.
Maybe check if you can find another progressbar component somewhere?

 

[dmcclurg]

It is built on layered labels with color and width manipulations (similar to this one: http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/). It has worked for all other implementations with identical code. Here is the code on the UserForm:

Sub ProgressStyle1(Percent As Single, ShowValue As Boolean)
  On Error GoTo ErrorCode
  
    Const PAD = "                         "
    
    If ShowValue Then
        labPg1v.Caption = PAD & Format(Percent, "0%")
        labPg1va.Caption = labPg1v.Caption
        labPg1va.Width = labPg1.Width
    End If
    labPg1.Width = Int(labPg1.Tag * Percent)
    Sleep 100
    Exit Sub
   
ErrorCode:
  MsgBox (Err.Number & ": " & Err.Description)

End Sub

 

[dmcclurg]

I have coded error handlers in every subroutine related to Sub Main(). I do not understand how to handle the "unhandled win32 exception". Is there a different method for this?