CODEWITHSUNDEEP
arraysvbaexcel
Wakan Tanka
Wakan Tanka

Reputation: 8042

Variant vs String type mismatch when passing an array to function

I have function that returns me list of current sheets:

Function getListOfSheetsW() As Variant
  Dim i As Integer
  Dim sheetNames() As Variant

  ReDim sheetNames(1 To Sheets.Count)
  For i = 1 To Sheets.Count
    sheetNames(i) = Sheets(i).name
  Next i

  getListOfSheetsW = sheetNames
End Function

Then I have function which returns TRUE or FALSE depending on if needle is in haystack or not.

Function IsInArray2(ByVal needle As String, haystack() As String) As Boolean
  Dim element As Variant
  For Each element In haystack
    If element = needle Then
      IsInArray = True
      Exit Function
    End If
  Next element
  IsInArray = False
End Function

My goal is to create new subroutine which will first check if sheet with given name already exist and if not then create new one. I've tried following:

Sub CreateNewSheet(ByVal dstWSheetName As String)
  Dim srcWSheetName As String

  ' Dim sheetNames() As String
  Dim sheetNames() As Variant

  sheetNames = getListOfSheetsW()
  Dim sheetCount As Integer

  If IsInArray2(dstWSheetName, sheetNames) Then
    MsgBox "Sheet with following name: " & dstWSheetName & " already exists"
  Else
    srcWSheetName = ActiveSheet.name
    sheetCount = Sheets.Count

    ' CREATE NEW SHEET
    ' Worksheets(dstWsheetName).Delete
    Sheets.Add.name = dstWSheetName
    ' Q: why 6 instead of 5
    ' Worksheets("Test").Move after:=Worksheets("Sheet5")
    Worksheets(dstWSheetName).Move After:=Worksheets(sheetCount + 1)

    ' SWITCH TO SRC SHEET
    Worksheets(srcWSheetName).Activate
  End If
End Sub

I'm calling it this way:

Sub CallCreateNewSheet()
  Call CreateNewSheet("test")
End Sub

I guess the problem is with Dim sheetNames() As String or Dim sheetNames() As Variant.

When I use Dim sheetNames() As String I get

Run-time error '13': Type mismatch

When I use Dim sheetNames() As Variant I get:

Compile error: Type mismatch: array or user-defined type expected

I had similar problem before but defining sheetNames as array did not helped here. What is the problem and what does the two different errors mean?

Upvotes: 2

Views: 11199

Answers (3)

EEM
EEM

Reputation: 6659

The variables has to be in sinc.

Declare the variable sheetNames in the same manner in both procedures:

Sub CreateNewSheet(ByVal dstWSheetName As String) and

Function getListOfSheetsW() As Variant

declare it as : Dim sheetNames() As String

Also note that the Function IsInArray2 always returns False. To correct this replace IsInArray with IsInArray2 in the body of the function.

It's a good practice to always have the

Option Explicit

at the beginning of the modules.

However it'll save all the trouble to validate the existence of a worksheet, just to assign the target worksheet to a variable, it will give an error and the variable return nothing if the worksheet in not present. Try this:

Dim Wsh As Worksheet
On Error Resume Next
Set Wsh = Workbook(x).Worksheets("Test")
On Error GoTo 0
If Wsh Is Nothing Then Add Worksheet

Upvotes: 1

Dirk Reichel
Dirk Reichel

Reputation: 7979

How about a new script like:

Sub NewSheetByName(SName as String)
  Dim oldSheet as Object

  For Each oldSheed in ThisWorkbook.Sheets
    if oldSheet.Name = Sname Then
      MsgBox "Sheet with following name: " & SName & " already exists"
      Exit Sub
    End If 
  Next

  oldSheet = ActiveSheet
  Sheets.Add.Name = SName
  ActiveSheet.Move , Worksheets(Sheets.Count)

  oldSheet.Activate

End Sub

Upvotes: 1

Excel Hero
Excel Hero

Reputation: 14764

You will avoid all these problems if you switch from typed arrays to variant-arrays.

In your first function, delete this line:

 Dim sheetNames() As Variant

Change the definition line of your 2nd function from this:

Function IsInArray2(ByVal needle As String, haystack() As String) As Boolean

...to this:

Function IsInArray2(ByVal needle As String, haystack) As Boolean

In your sub, change this line:

Dim sheetNames() As Variant

...to this:

Dim sheetNames

Upvotes: 2

Related Questions