2

When I run the code with debug window open, everything works fine, but when I run the exact same code with debug window closed, weird thing happens, such as it won't copy/paste data into new worksheets, it won't populate values to worksheet from a dictionary, or it will mess up the destination worksheet name/codename.

I accidentally fix the issue once by replacing a function that returns a sheet based on the sheet codename, but this function has been widely used before and didn't have any issue. I really rely on this function in my subs and it would be impossible for me to replace this function in the tool I'm currently working on. 

Public Function Code2Sheet(wbkWbk As Workbook, strSheetCodeName As String) As Worksheet

    Dim shtSht As Worksheet

    For Each shtSht In wbkWbk.Worksheets
        If shtSht.CodeName = strSheetCodeName Then
            Set Code2Sheet = shtSht
            Exit Function
        End If
    Next shtSht

End Function

Please see one of the subs I have issue with. I highlighted the code that didn't work. It's just not populate value in worksheet when I have debug window closed:

enter image description here

One more weird thing that keeps happening is that when I add a new tab, the new tab wont show up in the object list. I think only if I open the debug window after add the new tab, it will show up

rmiuc
  • 21
  • 2
  • 2
    I bet the issue is not in this function but in the code where you use this function. Please show that code (see [mcve] and provide an example that we can reproduce). – Pᴇʜ Jun 25 '19 at 11:30
  • Once I had a problem with some VBA which worked fine in debug mode but failed in production. In debug mode the Workbook was keeped open and in production the workbook was closed/opened during the process. This was really driving me carzy. –  Jun 25 '19 at 12:04
  • @HomerJay Such issues are an almost every case because of using `ActiveSheet`, `ActiveWorkbook`, `.Activate` and `.Select` wrong. I never saw a case where it really was an Excel bug, but always bad code. Refer to [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Jun 25 '19 at 12:25
  • I just wanted to point out, that this is the case. –  Jun 25 '19 at 12:37
  • [This](https://stackoverflow.com/questions/48437731/dictionary-is-populated-with-an-empty-item-after-checking-dictionary-item-in-wat) *might* be relevant, I only propose this because you tolld us you're using dictionaries – AntiDrondert Jun 25 '19 at 12:52
  • @PEH Thank you guys for your response. I updated my post with the one of the subs. Thanks! – rmiuc Jun 25 '19 at 19:53

0 Answers0