Calling a function in VBA

Question

I am trying to write a function that passes two string arguments and returns string type and I am calling the same function through a sub. I am getting an error with the arguments suggesting argument mismatch whereas I checked and the arguments are correct. Cant see to understand where the problem is. Can someone please help out . Here is the code

    Sub TableCat()
    Dim Key As String
    Worksheets("Table").Activate
    Call RemoveDupes
    Do Until ActiveCell.Value = ""
     TableMode = Application.Trim(ActiveCell.Value)
     TableId = Application.Trim(ActiveCell.Offset(0, -1).Value)
     ControlID = Application.Trim(ActiveCell.Offset(0, -2).Value)
     Key = ControlID & TableId
     ActiveCell.Offset(0, 1).Value = TableCatCalling(Key, TableMode)
    ActiveCell.Offset(1, 0).Select
    Loop
    End Sub

The function

    Function TableCatCalling(Key As String, Mode As String) As String
    Dim CatCell As Range
    Dim searchRange As Range
    Worksheets("CCM Analysisv2").Activate
    Set searchRange = Range("C1", Range("C1").End(xlDown))
    Set CatCell = searchRange.Find(what:=Key, lookAt:=xlWhole)
    If CatCell Is Nothing Then
     TableCatCalling = " "
    Else
     If TableMode Like "New" Then
        TableCatCalling = CatCell.Offset(0, -1).Value
     End If
    End If
    Worksheets("Table").Activate
    End Function

Answer 1

Your issue appears to be because you are passing a variable of type Variant to a ByRef (by default) parameter of type String.

You should get into the habit of always declaring your variables. Use Option Explicit as the first line of your code module to force you to do it.

The code below:

• declares TableMode (plus a few other variables that weren't declared),
• fixes up the error in your function where you were using a variable (TableMode) which had never been declared or assigned a value.
• gets rid of most of the uses of Activate, ActiveCell (by introducing a specific variable called CurrentCell) and Select

Hopefully it resolves your problems.

'Use "Option Explicit" to force you to declare variables
Option Explicit

Sub TableCat()
    'Declare variables that weren't declared before
    Dim TableMode As String
    Dim TableId As String
    Dim ControlID As String
    Dim CurrentCell As Range

    Dim Key As String
    'These two lines should be replaced with something which doesn't
    'use Activate and ActiveCell
    Worksheets("Table").Activate
    Set CurrentCell = ActiveCell

    RemoveDupes
    Do Until CurrentCell.Value = ""
        TableMode = Application.Trim(CurrentCell.Value)
        TableId = Application.Trim(CurrentCell.Offset(0, -1).Value)
        ControlID = Application.Trim(CurrentCell.Offset(0, -2).Value)
        Key = ControlID & TableId
        CurrentCell.Offset(0, 1).Value = TableCatCalling(Key, TableMode)
        Set CurrentCell = CurrentCell.Offset(1, 0)
    Loop
End Sub

Function TableCatCalling(Key As String, Mode As String) As String
    Dim CatCell As Range
    Dim searchRange As Range
    With Worksheets("CCM Analysisv2") 'Avoid activating the worksheet
        Set searchRange = .Range("C1", .Range("C1").End(xlDown))
    End With
    Set CatCell = searchRange.Find(what:=Key, lookAt:=xlWhole)
    If CatCell Is Nothing Then
        TableCatCalling = " "
    Else
        'This should refer to "Mode", not "TableMode"
        If Mode Like "New" Then
            TableCatCalling = CatCell.Offset(0, -1).Value
        End If
        'Note: You are not setting a return value if "Mode" is not like "New"
        'so your function will return "" by default
    End If
End Function