I would like to ask what is the right way to move from a worksheet to a subroutine as an argument.
1. What works but corrupts the workbook
For example, I have a simple code:
Sub mainMacro subRoutineMacro ws:=wsDostawy ' codename of one ThisWorkbook sheets End Sub Private Sub subRoutineMacro(ws As Worksheet) ' code ' ... End Sub
It works, but sometimes after the registration and reopening, I received a note stating that the workbook is corrupted. I've noticed that in the project explorer, I have two workbook objects, as shown in the attached screen capture. Using "ByRef" does not solve the problem.
So I started to pass a chain:
2. What works without problems
Sub mainMacro subRoutineMacro ws:="Dostawy" ' name of one ThisWorkbook sheets End Sub Private Sub subRoutineMacro(ws As String) Dim wkst As Worksheet Set wkst = ThisWorkbook.Sheets(ws) ' code ' ... End Sub
And the problem does not arise now, everything works like a charm. Maybe I should make some small improvements with sheet.Codeename instead of a simple string name.
Is there another better solution or just an explanation why this is happening? Thank you in advance.