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]

 

[tweedle]

Is there any opportunity for Percent to be Empty, Null or negative value?

 

[dmcclurg]

I have walked halfway through the sheet and there has not been either zero or null values. The win32 exception occurs immediately and the progress bar does not move at all.

The only thing I can think of is that frmProgress somehow never enters the UserForm collection globally. I show the form in a separate subroutine (user executes from Macro menu ALT-F8), and the Main() function is called from the form when the user hits the execute button.

Could the module lose the connection to the UserForm when switching from sheet to sheet?

 

[shg]

Further to Tweedle's thought, you might add error handling to the calling routine. Maybe something happens where NextContract.Row / StopRow can't be coerced to a Single.

 

[dmcclurg]

Thanks! I have added a simple counter to see how far the program gets. Essentially, it gets to the aforementioned lines and generates the exception. On the other hand, If I F8 through line by line, I can continue through the entire worksheet with no problems. This makes no sense.

 

[dmcclurg]

In addition, Excel has generated a strange file in my working directory named: "559A2510". When opened in NotePad, it is gibberish, but there is a line that says "Root Entry".

 

[shg]

That sounds like an Excel temporary file.

The whole problem sounds like some kind of race condition. The good news is, the solution is usually drop-dead simple once you figure it out. The bad news is figuring it out.