none
VBA halts at first use of Worksheet object. RRS feed

  • Question

  • I've written VBA to load data from a CSV file, copy it to a sheet, and then copy a portion of it to another sheet. It's used offsite by customers, but not commonly. One customer has reported a problem on two computers. This customer might be the only one to have used the VBA since this bug developed. The workbook is copied to the installation directory by Inno Setup.

    The problem is not consistently reproducible, except that once it appears it stays. It's resolved by making a new workbook with the same cell data and VBA. I haven't seen it manifest again after being resolved once.  The VBA encounters an error at the first line where the information in the CSV and workbook actually matters. Unfortunately, I didn't make a note of the exact error at the last incident, but I think it was a null reference.

    Without going into great detail, the logic triggering the error resembles this... (I would provide a minimal test case if I had a reproducible bug.)

    Dim data As Worksheet ' CSV Dim sheet As Worksheet ' XLSM

    '...

    ' halting here data.Cells.Copy Destination:=sheet.Cells(1, 1)

    This seems to be a problem with Excel's program state, somehow being written to and loaded from disk. Is this familiar to anyone? Is there any likely cause I can investigate? I might not be able to use this information, as I'll be replacing the workbook in the installer.

    Wednesday, April 22, 2015 8:31 PM

All replies

  • It may be the size of the two sheets are the issue. I had something similar with sheets that were created as xls and used as xlsx, and vice-veras (I forget the exact details...)  Anyway, try limiting the copied cells:

    data.Usedrange.Cells.Copy Destination:=sheet.Cells(1, 1)

    Wednesday, April 22, 2015 8:44 PM
  • When weird thing like that happen, try adding a DoEvents statement to your code.
    Thursday, April 23, 2015 12:52 AM
  • It may be the size of the two sheets are the issue. I had something similar with sheets that were created as xls and used as xlsx, and vice-veras (I forget the exact details...)  Anyway, try limiting the copied cells:

    I've changed the installer as described, thanks for the suggestion! Hopefully this means the bug will just go away, as uncomfortable as that kind of bug makes me.

    • Edited by Jesdisciple Thursday, April 23, 2015 4:39 PM
    Thursday, April 23, 2015 4:36 PM
  • When weird thing like that happen, try adding a DoEvents statement to your code.
    I see lots of warnings about how it's "evil" or dangerous, but can you link to an explanation of how it helps with weird things? I think you mean for my logic to wait until a resource is available, but I'm not entirely clear on how DoEvents achieves that.
    • Edited by Jesdisciple Thursday, April 23, 2015 4:39 PM
    Thursday, April 23, 2015 4:39 PM