Function is not fully executed within Sub Routine

Question

I am trying to attach an email body (Excel Range) using a html publishing procedure (within a function). This works fine, as long as I do not try to format the underlying Sheet from what the html file is created.

"###" mark the important lines in the code.

In this code snippet the function get launched (RangetoHTNL(rng)) from the underlying sub.

    With OutMail
    .To = rec
    .cc = cc
    .BCC = ""
    .Subject = "Convertibles - Execution " & cover.Range("D4").Value 
    .HTMLBody = StrBody & RangetoHTML(rng) '#### Here the function get launched #####
    .Attachments.Add (filename)
    .Display   'or use .Send
End With

Now the strange thing is that the function works properly until I want to change the column width. After this line the the algorithm "jumps" out of the function and executes the underlying sub without finishing the whole function first.

Find below the code snippet of the function, where I do some formatting in an Excel Sheet that should be published as HTML afterwards.

With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False

    row = .Range("A" & Rows.Count).End(xlUp).row
    lastcolumn = .Cells(6, Columns.Count).End(xlToLeft).Column
    default = .Range(.Cells(6, 1), .Cells(6, lastcolumn)).Width '-> Default width of whole table Range("A:?")
    ReDim myArray(row)
    For i = 1 To row
    myCell = .Cells(i, 1).text
    mysize = getLabelPixel(myCell)
    myArray(x) = mysize
    Next i

    Max = WorksheetFunction.Max(myArray)
    max_width = Max / con
    default_width = default / con

    If max_width > default_width Then
    prop = max_width / lastcolumn

    '####### until here the function is executed. Then it jumps out #######

    .Columns("1:" & lastcolumn).Width = prop
    .Columns("2:" & lastcolumn).Columns.AutoFit
    '###### without these two lines the function is executed properly #####

    End If
    .Cells(1).Select
    Application.CutCopyMode = False
On Error Resume Next
    .DrawingObjects.Visible = True
    .DrawingObjects.Delete
On Error GoTo 0
End With

Has someone ever experienced something like this? I already tried other versions of coding (e.g. not using the "With" - Statement approach) - Nothing helped.

Answer 1

My guess is that you have on error resume next placed in the subroutine that calls the function, then error occurs when resizing columns (perhaps overflow if one of the variables is not valid).

Make sure you put on error goto 0 just before the function call, then debug the function and check variables are valid.

/edit: Now that we've got an error, let's look into it. I understand that error occurs here:

.Columns("1:" & lastcolumn).Width = prop

It doesn't look right, try this instead (making sure lastcolumn and prop are valid):

.Cells(1, lastcolumn).ColumnWidth = prop