0

I want to hand over paramters to another sub. Trying to build a toolbox of snippets i am using often.

Modul1

Dim sheet_calc As String: Set sheet_calc = "calc"
Modul3.check_sheet(sheet_calc, "Main", True)

Modul3 (ToolModul what i want to build)


Private Sub check_sheet(sheetname As String, positionsheet As String, delt As Boolean)
    
    For Each Sheet In ActiveWorkbook.Worksheets
         If Sheet.Name = sheetname Then
              Sheet.Delete
         End If
    Next Sheet
    
    If delt = True Then
        ActiveWorkbook.Sheets.Add(After:=positionsheet).Name = sheetname
    End If

End Sub

An error occure. Is this the right way to build this? I dont understand how to code subs as toolbox. Can someone help and explain me how this works. My research confused me more.

credenco
  • 255
  • 2
  • 12
  • 1
    Try without the bracket `Modul3.check_sheet sheet_calc, "Main", True` and if you are calling `Modul3.check_sheet` from other module, you need to make `check_sheet` a public sub instead of private. – Raymond Wu Sep 06 '21 at 08:56
  • 2
    And you don't use `Set` with strings, only objects. – Rory Sep 06 '21 at 09:24
  • Ok thank you. Its working know. But i have onemore questions. why is it working only without brackets. – credenco Sep 06 '21 at 09:25
  • @credenco This should answer your question - https://stackoverflow.com/questions/5413765/what-are-the-rules-governing-usage-of-brackets-in-vba-function-calls – Raymond Wu Sep 06 '21 at 09:46
  • `After:=Sheets(positionsheet)`. `After` expects a sheet object reference, not a `string`. You'd check if referred sheet (positionsheet) exists before using it. Or you can suppress error with `On Error Resume Next`, but be careful with it. Issue `Application.DisplayAlerts = False` before deletion to suppress confirmation question and reverse it after. – AcsErno Sep 07 '21 at 13:18

0 Answers0