Error Handler invoked but no error number, description or source?

[AOB]

I have a function that is designed to accept a range as an argument and export that range as a PNG file to a given location (second argument)

It seems to be a bit temperamental; after much tweaking, I discovered that I had to temporarily switch ScreenUpdating on in order for it to work, and then switch it back off again. But even then, it still seemed to sporadically fail, in that the PNG generated would just be a blank / empty image rather than, effectively, a screenshot of the range provided. The failures seem to correlate with whether or not the Windows session is locked or not. The workbook runs on a schedule, hidden, throughout the day; if I am at my workstation working while it is running, the PNG generates as expected, if I lock the workstation, the PNG is empty. I can't confirm this is definitely related, it's just an anecdotal observation.

What's really weird (to me, anyway), is that when the function "fails" like this, the ErrorHandler block is invoked (suggesting an error or exception has been thrown) - but the log records no error number, description or source? Which is really puzzling me because, how could the ErrorHandler block be invoked, if there's no error?

Here is the function :

Private Function PublishRangeAsPNG(rng As Range, strPNGOutputFilename As String) As Boolean

    On Error GoTo ErrorHandler
   
    ' Declarations
    Dim sht As Worksheet
    Dim shtStaging As Worksheet
    Dim chtStaging As ChartObject
    Dim strTempLocalFilename As String
    Dim strNetworkFilename As String
    Dim lngFileSize As Long
    Dim result As Boolean
   
    ' Status bar update and log entry
    Application.StatusBar = "Publishing : " & strPNGOutputFilename
    If blnWriteToLog Then Call WriteToLog("Publishing : " & strPNGOutputFilename)
   
    ' Initialise the File System Object
    If objFSO Is Nothing Then Set objFSO = CreateObject("Scripting.FileSystemObject")
   
    ' Create a temporary worksheet for staging the chart object, from which the PNG will be exported
    With ThisWorkbook
        For Each sht In .Sheets
            If sht.Name = "Staging" Then
                sht.Delete
                Exit For
            End If
        Next sht
        Set shtStaging = .Sheets.Add
        shtStaging.Name = "Staging"
    End With
   
    ' Define paths for output files (local temporary and network share)
    strTempLocalFilename = ThisWorkbook.Path & "\" & strPNGOutputFilename & ".png"
    strNetworkFilename = strNetworkPath & "\" & strPNGOutputFilename & ".png"
   
    ' Temporarily re-establish screen updating
    Application.ScreenUpdating = True
   
    ' Create an empty, transparent, borderless chart object the same height and width as the target range
    Set chtStaging = shtStaging.ChartObjects.Add(0, 0, rng.Width, rng.Height)
    With chtStaging
        .ShapeRange.Fill.Visible = 0        ' msoFalse
        .ShapeRange.Line.Visible = 0        ' msoFalse
    End With

    ' Copy the target range as a picture to the chart object, export to the local temporary file and then delete it again
    rng.CopyPicture xlScreen, xlBitmap
    With chtStaging
        .Activate
        .Chart.Paste
        .Chart.Export Filename:=strTempLocalFilename, FilterName:="PNG"
        .Delete
    End With
   
    ' Copy the local output to the network share
    With objFSO
        ' Sporadically, this export fails resulting in an "empty" PNG file with no error thrown for some reason
        ' Check that the output file contains an image before replacing the existing PNG file on the network share
        ' Should be of the order of 100-120KB - empty image should be ~1KB - use 10KB as a threshold for success
        lngFileSize = FileLen(strTempLocalFilename)
        If lngFileSize > 10000 Then
            If blnWriteToLog Then Call WriteToLog("PNG generated successfully (" & Format(lngFileSize, "#,##0") & " bytes)")
            ' Copy the local temporary file to the network share and flag the function successful
            .CopyFile strTempLocalFilename, strNetworkFilename, True
            result = True
        Else
            If blnWriteToLog Then Call WriteToLog("PNG generated was an empty file (" & Format(lngFileSize, "#,##0") & " bytes)")
            ' Do not copy the local temporary file to the network share and flag the function as unsuccessful
            result = False
        End If
        ' Delete the local temporary file
        If .FileExists(strTempLocalFilename) Then .DeleteFile (strTempLocalFilename)
    End With
   
Exit_PublishRangeAsPNG:
    On Error Resume Next
    PublishRangeAsPNG = result
    Set objFSO = Nothing
    Set chtStaging = Nothing
    shtStaging.Delete
    Set shtStaging = Nothing
    Application.ScreenUpdating = False
    Exit Function

ErrorHandler:
    result = False
    If blnWriteToLog Then
        Call WriteToLog("Error in [PublishRangeAsPNG] function")
        Call WriteToLog("Error #" & Err.Number)
        Call WriteToLog("Description : " & Err.Description)
        Call WriteToLog("Source: " & Err.Source)
        Err.Clear
        Call CommitLog
    End If
    Resume Exit_PublishRangeAsPNG
   
End Function

(FYI - before anybody asks - objFSO, strNetworkPath and blnWriteToLog are all declared at module level - none of these are causing issues...)

Here is the log when the PNG output is blank :

19 Aug 2022 19:08:12 | Publishing : MyPNGOutput
19 Aug 2022 19:08:14 | Error in [PublishRangeAsPNG] function
19 Aug 2022 19:08:14 | Error #0
19 Aug 2022 19:08:14 | Description :
19 Aug 2022 19:08:14 | Source:

Odd, no?

 

[=ODIN=]

Really interesting! All I can suggest is, while it's tedious, figure out exactly which line is causing the error. so instead of just using your "writetolog" to capture errors, use it in within your code to see if specific lines execute as expected -- until you narrow down the specific offending line.

With code copying and moving files, I've usually found that it's a timing issue -- like you're trying to delete a file while it's still copying... So I'd probably focus my "writetologs" check around these lines

    ' Copy the target range as a picture to the chart object, export to the local temporary file and then delete it again
    rng.CopyPicture xlScreen, xlBitmap
    With chtStaging
        .Activate
        .Chart.Paste
        .Chart.Export Filename:=strTempLocalFilename, FilterName:="PNG"
        .Delete
    End With

Sorry I couldn't be more help, but please post back if you do manage to find the solution as I would love to know what the issue turns out to be.

 

[AOB]

With code copying and moving files, I've usually found that it's a timing issue -- like you're trying to delete a file while it's still copying...

Yeah that's a good point / spot actually - now that I review the code, that really jumps out - quite embarrassed I didn't notice it myself if I'm honest!

I've modified the function as follows and I haven't had an issue since :

    Const TIMEOUT_SECONDS As Integer = 30
    Dim datTimeout As Date

    ....

    ' Copy the target range as a picture to the chart object, export to the local temporary file and then delete it again
    rng.CopyPicture xlScreen, xlBitmap
    With chtStaging
        .Activate
        .Chart.Paste
        .Chart.Export Filename:=strTempLocalFilename, FilterName:="PNG"
        datTimeout = Now
        Do Until DateDiff("s", datTimeout, Now) > TIMEOUT_SECONDS
            If objFSO.FileExists(strTempLocalFilename) Then
                If FileLen(strTempLocalFilename) > 10000 Then Exit Do
            End If
        Loop
        .Delete
    End With

So it does appear that the VBA was trying to delete the chart object before the export had completed - which I should really have accounted for in the first place

That seems to explain why the error is occurring (which is awesome); the bit that still troubles me is that the code was throwing an error (in order to enter the handler block) but the Err object contained nothing. Which begs the question of how it "knew" there was an error in the first place? Even some kind of unhandled exception or something - anything!

Thanks for the response!