none
VBA script works when stepped through, but not when run in realtime RRS feed

  • Question

  • Can anyone give me some suggestions about how to troubleshoot code that works when you step through it, but does not work when run in realtime?

    My VBA script (Word 2010) uses Windows API code to select duplex printing on the ActivePrinter.  (I just lifted the code wholesale from another post--I don't know much about how it works.)

    My code is built into a particular document and it presents a PrintMe user form asking if (among other things) this document should be printed in duplex.  If yes, then the VBA code changes the printer properties to be duplexed, otherwise it sets duplex mode to single-page.  Then it prints the document using Application.Printout. 

    If I run the userform code without breakpoints, it is highly hit or miss whether the job will be duplexed or not.  There are no errors reported,  the printer is just ignoring the setting.

    However, if I set a breakpoint and step through the code, the job comes out  properly every time.  In fact, all I have to do to get the code to work is set a breakpoint at the Application.Printout step--after all the API code has run.  For this reason, I don't think this problem has anything to with the API code itself, but that's just a feeling.

    I have set up a test MsgBox that uses the same API code to report to me the duplex status just before Application.Printout, and it always shows that the printer has the correct duplex status depending on what I selected in the userform, but the printer is not behaving like the duplex setting reports. 

    Any suggestions?

     

    Thanks,

    Peter

    Sunday, March 3, 2013 5:11 PM

Answers

  • Consider the difference between debug mode and running mode: debug mode adds more delay between successive actions, as well as focus switching. Think about whether you can add delay in your code, especially after action that take longger time.

    This is for synchrinization.


    Forrest Guo | MSDN Community Support | Feedback to manager

    Friday, March 8, 2013 7:41 AM
    Moderator

All replies

  • Can anyone give me some suggestions about how to troubleshoot code that works when you step through it, but does not work when run in realtime?

    My VBA script (Word 2010) uses Windows API code to select duplex printing on the ActivePrinter.  (I just lifted the code wholesale from another post--I don't know much about how it works.)

    .... 

    Any suggestions?

    Without seeing the code, know one else will know how it works either!!

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Sunday, March 3, 2013 9:32 PM
  • OK, you asked for it.  As I said in the original post, as evidenced by the output showing in the test MSGBOX, the lifted code seems to do what it is supposed to do even when run in realtime.  Here's my code along with the borrowed code:

    My Code (excerpt):
    =========================================
    Private Sub PrintSection()
        Dim iDuplex As Long
        Dim strConfirmDuplex As Long
        Dim strCurrentPrinter As String
       
        'DUPLEX printing is not working reliably.  It seems to work every time when you step through,
        'but is hit and miss when run in real-time.
       
        strCurrentPrinter = ActivePrinter   'save current printer
        iDuplex = GetDuplex                 'save current printer's duplex setting
        LoginComputerName = StrConv(Environ("COMPUTERNAME"), vbUpperCase)

        'This is code in a UserForm on which there are check boxes  for duplex (ckDuplex), color (ckColor),

        'and PDF (ckPDF) printing.

        If ckPDF.Value = True Then
                 
            ActiveDocument.ExportAsFixedFormat OutputFileName:= _
            strFileName, ExportFormat:=wdExportFormatPDF, _
            OpenAfterExport:=True, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
            wdExportFromTo, From:=strFrom, To:=strTo, Item:=wdExportDocumentContent, _
            IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
            wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
            True, UseISO19005_1:=False
        Else
            If ckColor.Value = True Then
                If ckDuplex.Value = True Then
                    'Peter's PC has a special printer set up to do color and stapling
                    If LoginComputerName = "W7-MLRI06179" Then
                        ActivePrinter = "SHARP_Color-Duplex-Staple"
                    End If
                Else
                    ActivePrinter = "\\MLRI-W8-PRINT\W-Color-HP3000"
                End If
            Else
                'Anywhere else we set duplex for the active printer
                'Query: what if the active printer does not have a duplex mode?
                If ckDuplex.Value = False Then
                    SetDuplex 1     'make sure duplex is off
                Else
                    SetDuplex 2     'set duplex for booklet (3 would be tablet, 1 is Duplex off)
                   
                End If
            End If

            'TEST: show what the lifted code says is the current duplex setting
            strConfirmDuplex = GetDuplex
            MsgBox "Current duplex setting is: " & strConfirmDuplex, vbOKOnly
           
            Application.PrintOut FileName:="", Range:=wdPrintRangeOfPages, Item:= _
                    wdPrintDocumentWithMarkup, Copies:=1, Pages:=strPages, PageType:= _
                    wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _
                    PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
                    PrintZoomPaperHeight:=0
        End If
       
        ActivePrinter = strCurrentPrinter   'restore original active printer
        SetDuplex iDuplex                   'restore original duplex setting

        End Sub
     
    =========================================================================
    Code in MAIN code module borrowed without modification from others: 
    -----------------
     Option Explicit

    Private Type PRINTER_DEFAULTS
       pDatatype As Long
       pDevmode As Long
       DesiredAccess As Long
    End Type

    Private Type PRINTER_INFO_2
       pServerName As Long
       pPrinterName As Long
       pShareName As Long
       pPortName As Long
       pDriverName As Long
       pComment As Long
       pLocation As Long
       pDevmode As Long               ' Pointer to DEVMODE
       pSepFile As Long
       pPrintProcessor As Long
       pDatatype As Long
       pParameters As Long
       pSecurityDescriptor As Long    ' Pointer to SECURITY_DESCRIPTOR
       Attributes As Long
       Priority As Long
       DefaultPriority As Long
       StartTime As Long
       UntilTime As Long
       Status As Long
       cJobs As Long
       AveragePPM As Long
    End Type

    Private Type DEVMODE
       dmDeviceName As String * 32
       dmSpecVersion As Integer
       dmDriverVersion As Integer
       dmSize As Integer
       dmDriverExtra As Integer
       dmFields As Long
       dmOrientation As Integer
       dmPaperSize As Integer
       dmPaperLength As Integer
       dmPaperWidth As Integer
       dmScale As Integer
       dmCopies As Integer
       dmDefaultSource As Integer
       dmPrintQuality As Integer
       dmColor As Integer
       dmDuplex As Integer
       dmYResolution As Integer
       dmTTOption As Integer
       dmCollate As Integer
       dmFormName As String * 32
       dmUnusedPadding As Integer
       dmBitsPerPel As Integer
       dmPelsWidth As Long
       dmPelsHeight As Long
       dmDisplayFlags As Long
       dmDisplayFrequency As Long
       dmICMMethod As Long
       dmICMIntent As Long
       dmMediaType As Long
       dmDitherType As Long
       dmReserved1 As Long
       dmReserved2 As Long
    End Type

    Private Const DM_ORIENTATION = &H1
    Private Const DM_PAPERSIZE = &H2
    Private Const DM_PAPERLENGTH = &H4
    Private Const DM_PAPERWIDTH = &H8
    Private Const DM_DEFAULTSOURCE = &H200
    Private Const DM_PRINTQUALITY = &H400
    Private Const DM_COLOR = &H800
    Private Const DM_DUPLEX = &H1000

    Private Const DM_IN_BUFFER = 8
    Private Const DM_OUT_BUFFER = 2
    Private Const PRINTER_ACCESS_USE = &H8
    Private Const STANDARD_RIGHTS_REQUIRED = &HF0000
    Private Const PRINTER_NORMAL_ACCESS = (STANDARD_RIGHTS_REQUIRED Or _
                    PRINTER_ACCESS_USE)

    Private Const PRINTER_ENUM_CONNECTIONS = &H4
    Private Const PRINTER_ENUM_LOCAL = &H2

    Private Declare Function ClosePrinter Lib "winspool.drv" _
          (ByVal hPrinter As Long) As Long
    Private Declare Function DocumentProperties Lib "winspool.drv" _
          Alias "DocumentPropertiesA" (ByVal hwnd As Long, _
          ByVal hPrinter As Long, ByVal pDeviceName As String, _
          ByVal pDevModeOutput As Long, ByVal pDevModeInput As Long, _
          ByVal fMode As Long) As Long
    Private Declare Function GetPrinter Lib "winspool.drv" Alias _
          "GetPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, _
          pPrinter As Byte, ByVal cbBuf As Long, pcbNeeded As Long) As Long
    Private Declare Function OpenPrinter Lib "winspool.drv" Alias _
          "OpenPrinterA" (ByVal pPrinterName As String, phPrinter As Long, _
          pDefault As PRINTER_DEFAULTS) As Long
    Private Declare Function SetPrinter Lib "winspool.drv" Alias _
          "SetPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, _
          pPrinter As Byte, ByVal Command As Long) As Long
    Private Declare Function EnumPrinters Lib "winspool.drv" _
          Alias "EnumPrintersA" _
          (ByVal flags As Long, ByVal name As String, ByVal Level As Long, _
          pPrinterEnum As Long, ByVal cdBuf As Long, pcbNeeded As Long, _
          pcReturned As Long) As Long

    Private Declare Function PtrToStr Lib "kernel32" Alias "lstrcpyA" _
          (ByVal RetVal As String, ByVal Ptr As Long) As Long

    Private Declare Function StrLen Lib "kernel32" Alias "lstrlenA" _
          (ByVal Ptr As Long) As Long
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
          (pDest As Any, pSource As Any, ByVal cbLength As Long)
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    Private Declare Function DeviceCapabilities Lib "winspool.drv" _
          Alias "DeviceCapabilitiesA" (ByVal lpDeviceName As String, _
          ByVal lpPort As String, ByVal iIndex As Long, lpOutput As Any, _
          ByVal dev As Long) As Long

    Public Sub SetColorMode(iColorMode As Long)
       SetPrinterProperty DM_COLOR, iColorMode
    End Sub

    Public Function GetColorMode() As Long
      GetColorMode = GetPrinterProperty(DM_COLOR)
    End Function

    Public Sub SetDuplex(iDuplex As Long)
       SetPrinterProperty DM_DUPLEX, iDuplex
    End Sub

    Public Function GetDuplex() As Long
       GetDuplex = GetPrinterProperty(DM_DUPLEX)
    End Function

    Public Sub SetPrintQuality(iQuality As Long)
       SetPrinterProperty DM_PRINTQUALITY, iQuality
    End Sub

    Public Function GetPrintQuality() As Long
       GetPrintQuality = GetPrinterProperty(DM_PRINTQUALITY)
    End Function

    Private Function SetPrinterProperty(ByVal iPropertyType As Long, _
          ByVal iPropertyValue As Long) As Boolean

       'Code adapted from Microsoft KB article Q230743

        Dim hPrinter As Long          'handle for the current printer
        Dim pd As PRINTER_DEFAULTS
        Dim pinfo As PRINTER_INFO_2
        Dim dm As DEVMODE
        Dim sPrinterName As String

        Dim yDevModeData() As Byte        'Byte array to hold contents
                                          'of DEVMODE structure
        Dim yPInfoMemory() As Byte        'Byte array to hold contents
                                          'of PRINTER_INFO_2 structure
        Dim iBytesNeeded As Long
        Dim iRet As Long
        Dim iJunk As Long
        Dim iCount As Long
         
        On Error GoTo cleanup

        'Get the name of the current printer
        'sPrinterName = Trim$(Left$(ActivePrinter, _
              InStr(ActivePrinter, " on ")))
        sPrinterName = ActivePrinter
       
        pd.DesiredAccess = PRINTER_NORMAL_ACCESS
        iRet = OpenPrinter(sPrinterName, hPrinter, pd)
        If (iRet = 0) Or (hPrinter = 0) Then
           'Can't access current printer. Bail out doing nothing
           Exit Function
        End If

        'Get the size of the DEVMODE structure to be loaded
        iRet = DocumentProperties(0, hPrinter, sPrinterName, 0, 0, 0)
        If (iRet < 0) Then
           'Can't access printer properties.
           GoTo cleanup
        End If

        'Make sure the byte array is large enough
        'Some printer drivers lie about the size of the DEVMODE structure they
        'return, so an extra 100 bytes is provided just in case!
        ReDim yDevModeData(0 To iRet + 100) As Byte
         
        'Load the byte array
        iRet = DocumentProperties(0, hPrinter, sPrinterName, _
                    VarPtr(yDevModeData(0)), 0, DM_OUT_BUFFER)
        If (iRet < 0) Then
           GoTo cleanup
        End If

        'Copy the byte array into a structure so it can be manipulated
        Call CopyMemory(dm, yDevModeData(0), Len(dm))

        If dm.dmFields And iPropertyType = 0 Then
           'Wanted property not available. Bail out.
           GoTo cleanup
        End If

        'Set the property to the appropriate value
        Select Case iPropertyType
        Case DM_ORIENTATION
           dm.dmOrientation = iPropertyValue
        Case DM_PAPERSIZE
           dm.dmPaperSize = iPropertyValue
        Case DM_PAPERLENGTH
           dm.dmPaperLength = iPropertyValue
        Case DM_PAPERWIDTH
           dm.dmPaperWidth = iPropertyValue
        Case DM_DEFAULTSOURCE
           dm.dmDefaultSource = iPropertyValue
        Case DM_PRINTQUALITY
           dm.dmPrintQuality = iPropertyValue
        Case DM_COLOR
           dm.dmColor = iPropertyValue
        Case DM_DUPLEX
           dm.dmDuplex = iPropertyValue
        End Select
         
        'Load the structure back into the byte array
        Call CopyMemory(yDevModeData(0), dm, Len(dm))

        'Tell the printer about the new property
        iRet = DocumentProperties(0, hPrinter, sPrinterName, _
              VarPtr(yDevModeData(0)), VarPtr(yDevModeData(0)), _
              DM_IN_BUFFER Or DM_OUT_BUFFER)

        If (iRet < 0) Then
           GoTo cleanup
        End If

        'The code above *ought* to be sufficient to set the property
        'correctly. Unfortunately some brands of Postscript printer don't
        'seem to respond correctly. The following code is used to make
        'sure they also respond correctly.
        Call GetPrinter(hPrinter, 2, 0, 0, iBytesNeeded)
        If (iBytesNeeded = 0) Then
           'Couldn't access shared printer settings
           GoTo cleanup
        End If
         
        'Set byte array large enough for PRINTER_INFO_2 structure
        ReDim yPInfoMemory(0 To iBytesNeeded + 100) As Byte

        'Load the PRINTER_INFO_2 structure into byte array
        iRet = GetPrinter(hPrinter, 2, yPInfoMemory(0), iBytesNeeded, iJunk)
        If (iRet = 0) Then
           'Couldn't access shared printer settings
           GoTo cleanup
        End If

        'Copy byte array into the structured type
        Call CopyMemory(pinfo, yPInfoMemory(0), Len(pinfo))

        'Load the DEVMODE structure with byte array containing
        'the new property value
        pinfo.pDevmode = VarPtr(yDevModeData(0))
         
        'Set security descriptor to null
        pinfo.pSecurityDescriptor = 0
        
        'Copy the PRINTER_INFO_2 structure back into byte array
        Call CopyMemory(yPInfoMemory(0), pinfo, Len(pinfo))

        'Send the new details to the printer
        iRet = SetPrinter(hPrinter, 2, yPInfoMemory(0), 0)

        'Indicate whether it all worked or not!
        SetPrinterProperty = CBool(iRet)

    cleanup:
       'Release the printer handle
       If (hPrinter <> 0) Then Call ClosePrinter(hPrinter)
         
       'Flush the message queue. If you don't do this,
       'you can get page fault errors when you try to
       'print a document immediately after setting a printer property.
       For iCount = 1 To 20
          DoEvents
       Next iCount
       End Function

    Private Function GetPrinterProperty(ByVal iPropertyType As Long) As Long

      'Code adapted from Microsoft KB article Q230743

      Dim hPrinter As Long
      Dim pd As PRINTER_DEFAULTS
      Dim dm As DEVMODE
      Dim sPrinterName As String

      Dim yDevModeData() As Byte
      Dim iRet As Long
         
      On Error GoTo cleanup
         
      'Get the name of the current printer
      'sPrinterName = Trim$(Left$(ActivePrinter, _
            InStr(ActivePrinter, " on ")))
      sPrinterName = ActivePrinter
     
      pd.DesiredAccess = PRINTER_NORMAL_ACCESS
         
      'Get the printer handle
      iRet = OpenPrinter(sPrinterName, hPrinter, pd)
      If (iRet = 0) Or (hPrinter = 0) Then
         'Couldn't access the printer
          Exit Function
      End If

      'Find out how many bytes needed for the printer properties
      iRet = DocumentProperties(0, hPrinter, sPrinterName, 0, 0, 0)
      If (iRet < 0) Then
         'Couldn't access printer properties
          GoTo cleanup
      End If

      'Make sure the byte array is large enough, including the
      '100 bytes extra in case the printer driver is lying.
      ReDim yDevModeData(0 To iRet + 100) As Byte
         
      'Load the printer properties into the byte array
      iRet = DocumentProperties(0, hPrinter, sPrinterName, _
                  VarPtr(yDevModeData(0)), 0, DM_OUT_BUFFER)
      If (iRet < 0) Then
         'Couldn't access printer properties
         GoTo cleanup
      End If

      'Copy the byte array to the DEVMODE structure
      Call CopyMemory(dm, yDevModeData(0), Len(dm))

      If Not dm.dmFields And iPropertyType = 0 Then
         'Requested property not available on this printer.
         GoTo cleanup
      End If

      'Get the value of the requested property
      Select Case iPropertyType
      Case DM_ORIENTATION
         GetPrinterProperty = dm.dmOrientation
      Case DM_PAPERSIZE
         GetPrinterProperty = dm.dmPaperSize
      Case DM_PAPERLENGTH
         GetPrinterProperty = dm.dmPaperLength
      Case DM_PAPERWIDTH
         GetPrinterProperty = dm.dmPaperWidth
      Case DM_DEFAULTSOURCE
         GetPrinterProperty = dm.dmDefaultSource
      Case DM_PRINTQUALITY
         GetPrinterProperty = dm.dmPrintQuality
      Case DM_COLOR
         GetPrinterProperty = dm.dmColor
      Case DM_DUPLEX
         GetPrinterProperty = dm.dmDuplex
      End Select
         
    cleanup:
       'Release the printer handle
       If (hPrinter <> 0) Then Call ClosePrinter(hPrinter)

    End Function



    Sunday, March 3, 2013 10:12 PM
  • See.  I knew that putting in the code would put people off.  I REALLY don't think it's the API code that's giving the problem.  So I am still seeking advice as a general matter of how to troubleshoot code that works when stepped through, but not when run real time.

    Thanks,

    Tuesday, March 5, 2013 12:55 PM
  • Hi Peter,

    I'm trying to involve some senior engineers into the issue. It takes some time and you patience will be greatly appreciated.

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 6, 2013 2:54 AM
    Moderator
  • It appears your API code is from: http://support.microsoft.com/?kbid=230743. For that part or your code, you could have provided just the link.

    In any event, I don't believe you need any of the code from the link. Instead, try:

    Private Sub PrintSection()
        Dim strCurrentPrinter As String
        Dim LoginComputerName As String
        Dim bDplx As Boolean
        'save current printer
        strCurrentPrinter = ActivePrinter
        'save current printer's duplex setting
        bDplx = Dialogs(wdDialogFilePrint).DuplexPrint
        LoginComputerName = StrConv(Environ("COMPUTERNAME"), vbUpperCase)
       
        'This is code in a UserForm on which there are check boxes  for duplex (ckDuplex), color (ckColor),
        'and PDF (ckPDF) printing.
        If ckPDF.Value = True Then
       
            ActiveDocument.ExportAsFixedFormat OutputFileName:= _
            strFileName, ExportFormat:=wdExportFormatPDF, _
            OpenAfterExport:=True, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
            wdExportFromTo, From:=strFrom, To:=strTo, Item:=wdExportDocumentContent, _
            IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
            wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
            True, UseISO19005_1:=False
           
        Else
            If ckColor.Value = True Then
                If ckDuplex.Value = True Then
                    'Peter's PC has a special printer set up to do color and stapling
                    If LoginComputerName = "W7-MLRI06179" Then
                        ActivePrinter = "SHARP_Color-Duplex-Staple"
                    End If
                Else
                    ActivePrinter = "\\MLRI-W8-PRINT\W-Color-HP3000"
                End If
            Else
                'Anywhere else we set duplex for the active printer
                'Query: what if the active printer does not have a duplex mode?
                If ckDuplex.Value = False Then
                    'make sure duplex is off
                    Dialogs(wdDialogFilePrint).DuplexPrint = False
                Else
                    'make sure duplex is on
                    Dialogs(wdDialogFilePrint).DuplexPrint = True
                End If
            End If
            Application.PrintOut FileName:="", Range:=wdPrintRangeOfPages, Item:= _
                    wdPrintDocumentWithMarkup, Copies:=1, Pages:=strPages, PageType:= _
                    wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _
                    PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
                    PrintZoomPaperHeight:=0
        End If
        'restore original active printer
        ActivePrinter = strCurrentPrinter
        'restore original duplex setting
        Dialogs(wdDialogFilePrint).DuplexPrint = bDplx
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, March 6, 2013 5:41 AM
  • Consider the difference between debug mode and running mode: debug mode adds more delay between successive actions, as well as focus switching. Think about whether you can add delay in your code, especially after action that take longger time.

    This is for synchrinization.


    Forrest Guo | MSDN Community Support | Feedback to manager

    Friday, March 8, 2013 7:41 AM
    Moderator
  • Hi. I had the exact same experience. I used the same API call and tied the macro to the quick access toolbar. In Word 2010 I never had an issue, but now in Word 2013 it's hit or miss at best. I dealt with it by adding a confirmation - not a bad thing considering I often hit the quick access button without thinking first if I want duplex or not!

    Sub QuickDuplex()
       Dim wdDoc As Document
       Dim vOk
       Set wdDoc = ActiveDocument
       SetPrinterDuplex ActivePrinter, 2
       vOk = MsgBox("Ok to print duplex?", vbYesNo + vbQuestion)
       If vOk = vbNo Then SetPrinterDuplex ActivePrinter, 1
       wdDoc.PrintOut (False)
       SetPrinterDuplex ActivePrinter, 1
    End Sub
    


    • Edited by B Josephs Sunday, August 4, 2013 10:03 PM
    Sunday, August 4, 2013 9:59 PM
  • Hi,

     Does we have solution for this issues?

    I am also facing the same issue while filter operation.

    Filter works in debug mode not in runtime.

    I used Application.wait(Now + TimeValue("0:03:00")) . But this also not work.

    Now, I am go with row by row checking.

    Thanks



    -----------

    After that I have use Option Explicit then working fine

    This may help to any one

    • Edited by aspsak Thursday, December 24, 2015 10:45 AM
    Wednesday, December 23, 2015 8:58 AM
  • I am also having this same issue.  Is there a solution?

    Sincerely,

    Victor Reinhart
    MaintStar.com

    Saturday, June 16, 2018 5:20 PM
  • Hi Victor.

    I had the same issue with code running on step through but not in real time.

    I replaced my DoCmd.RunCommand acCmdRefreshPage lines with Me.repaint and the problem was resolved.

    Hope this helps you in your situation.

    Cheers

    gusgray

    Tuesday, August 14, 2018 11:11 AM