CODEWITHSUNDEEP
excelvbafunctionnested
HellKat02
HellKat02

Reputation: 57

How to call another function within a function in VBA

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

Upvotes: 1

Views: 78

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

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 sh As Object
    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 sh = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    If Not sh Is Nothing Then
        MsgBox "Duplicate Name! Please try again!"
        Exit Sub
    End If
        
    '~~> Create the worksheet
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = sheetName
        MsgBox """" & sheetName & """ was successfully created!"
    End With
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
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = sheetName
        MsgBox """" & sheetName & """ was successfully created!"
    End With
End Sub

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

Upvotes: 2

Related Questions