CODEWITHSUNDEEP
excelvba
Luca
Luca

Reputation: 69

ByRef argument type mismatch error raised when I change location of Dim statement

Dim rangeStr, dataRow, bomRow, levelRow, sNewSheetName, quantRow As String
Dim y, desc, endcap As String
If SheetExists(sNewSheetName) Then
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets(sNewSheetName).Delete
    Application.DisplayAlerts = True
End If

Private Function SheetExists(sheetToFind As String) As Boolean
    Dim sSheet As Worksheet

    SheetExists = False
    For Each sSheet In Worksheets
        If sheetToFind = sSheet.Name Then
            SheetExists = True
            Exit For
        End If
    Next sSheet   
End Function

The error is raised on the third line in the If statement. I have a solution to my problem, but I want to understand why it works. The code is error free if I simply move the quantRow variable down to the 2nd dim statement so the first line ends with sNewSheetName As String. It seems adding a variable in front of sNewSheetName in the Dim statement makes this error appear and I'm curious to know why.

Upvotes: 2

Views: 40

Answers (1)

Ricardo A
Ricardo A

Reputation: 1815

You are misunderstanding the Dim Statement.

Dim blah, blah2, blah3 As String

Only Blah3 is declared as String. The first two are Variants.

When you move the quantRow down, then the sNewSheetName is declared as String and it works fine. Before you move quantRow, sNewSheetName is declared as Variant and the subscript doesn't like it when you check for SheetExists() because it is expecting a String not a Variant.

Private Function SheetExists(sheetToFind As **String**) As Boolean

The correct way to Dim several items without declaring them as variants is:

Dim blah as String, blah2 as String, blah3 as String

Upvotes: 4

Related Questions