How to call another function within a function in VBA-CodePudding

I'm currently trying to detect duplicated sheet name using "CheckSheet" function. And I want to call this function to run in "Add Sheet" to prevent users from creating duplicate sheet names. However, I ran into error "Compile Error: Expected function or variable" and still not succeeding in solving the problem. Kindly enlighten me where I am doing it wrong and feel free to point out if there are any weakness and better optimization to my code. Thanks in advance.

Option Explicit

Public sheetName As Variant
Public cS As Variant

Sub CheckSheet(cS)  'To check duplicate sheet name - used in AddSheet function.
    Dim wS As Worksheet
    Dim wsName As String
    wsName = wS(sheetName)
    On Error GoTo 0
    If wS Is Nothing Then
    cS = False
    
    Exit Sub
End Sub

Sub AddSheet()
    Dim cSheet As Variant
    cSheet = CheckSheet(cS).Value
    On Error Resume Next
    sheetName = Application.InputBox(prompt:="New Sheet Name", Left:=(Application.Width / 2), Top:=(Application.Height / 2), Title:="Add Sheet", Type:=2)
    If sheetName = "" Then
        MsgBox "Sheet name cannot be empty!"
        Exit Sub
    ElseIf cSheet = False Then
        MsgBox "Duplicate Name! Please try again!"
        Exit Sub
    Else
        Application.ScreenUpdating = False
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = sheetName
        MsgBox """" & sheetName & """ was successfully created!"
        Sheets("Sheet1").Activate
    End If
End Sub

CodePudding user response：

Two things.

1. Your code can be simplified. You do not need a function to check if a worksheet exists.

Option Explicit

Sub AddSheet()
    Dim ws As Worksheet
    Dim sheetName As Variant
    
    '~~> Accept user input
    sheetName = Application.InputBox(prompt:="New Sheet Name", _
                                     Left:=(Application.Width / 2), _
                                     Top:=(Application.Height / 2), _
                                     Title:="Add Sheet", Type:=2)

    '~~> User presses cancel
    If sheetName = False Then Exit Sub
    
    '~~> Check if the sheet name is empty
    If sheetName = "" Then
        MsgBox "Sheet name cannot be empty!"
        Exit Sub
    End If
    
    '~~> Check if the sheet exists
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    If Not ws Is Nothing Then
        MsgBox "Duplicate Name! Please try again!"
        Exit Sub
    End If
        
    '~~> Create the worksheet
    ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(Sheets.Count)).Name = sheetName
    MsgBox """" & sheetName & """ was successfully created!"
End Sub

2. Even if you want to use a function, your code has lot of errors. (One of them is pointed out by @braX above.

Is this what you are trying?

Option Explicit
    
Sub AddSheet()
    Dim sheetName As Variant
        
    '~~> Accept user input
    sheetName = Application.InputBox(prompt:="New Sheet Name", _
                                     Left:=(Application.Width / 2), _
                                     Top:=(Application.Height / 2), _
                                     Title:="Add Sheet", Type:=2)

    '~~> User presses cancel
    If sheetName = False Then Exit Sub
       
    '~~> Check if the sheet name is empty
    If sheetName = "" Then
        MsgBox "Sheet name cannot be empty!"
        Exit Sub
    End If
        
    '~~> Check if the sheet exists
    If DoesSheetExists(CStr(sheetName)) = True Then
        MsgBox "Duplicate Name! Please try again!"
        Exit Sub
    End If
            
    '~~> Create the worksheet
    ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(Sheets.Count)).Name = sheetName
    MsgBox """" & sheetName & """ was successfully created!"
End Sub

'~~> Function to check if sheet exists
Private Function DoesSheetExists(wsName As String) As Boolean
    Dim ws As Worksheet
        
    '~~> Check if the sheet exists
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(wsName)
    On Error GoTo 0
        
    If Not ws Is Nothing Then DoesSheetExists = True
End Function