User defined function working fine in vba, while when called from excel sheet showing 'Value' error

Question

I have made a function for calling from excel having two arguments of string type and returning integer value after counting numbers present in a particular range of excel sheet.

This function is working properly when called from vba code, but showing 'Value' error when called from excel sheet

I did change the arguments type to range. But it is still showing 'Value error'

It is working in both the cases when i am just returning an integer value just by assigning, without using excel functions. But while using Excel functions in user defined function code, it is again showing 'Value' error.

Public Function countEvent(ByVal name As String, ByVal travelType As String) As Integer

    Dim finalValue, tempValue, i As Integer
    Dim rng As Range

    Dim current As Worksheet
    countEvent = 10
    Set current = ActiveWorkbook.Worksheets("Sheet2")
    Set rng = current.Range("T4:T50000")


    'These two values are pasted in two cells in excel sheet, as these values are further being used by some other formula
    current.Range("T2").Value = name
    current.Range("U2").Value = travelType

    countEvent = Application.WorksheetFunction.Count(rng)

End Function

it has been defined to return the numbers in a particular range of the excel sheet, but showing 'Value error' in excel sheet

Probably some problem with state, like ActiveWorkbook isn't what you expect. Perhaps try some "messagebox debugging"? Add on error goto... and output the error as a messagebox or write it to a file. — DaggeJ
I don't understand your function. You give arguments that you literally never use in your calculations. You could just put these two values manually in the cells where you try to put them programatically. — iSpain17

Patrick Honorez Patrick Honorez · Accepted Answer · 2019-06-19T11:51:09

A UDF cannot modify the formatting of a cell or workbook or move values around on a worksheet. If you remove the lines below, it should work.

current.Range("T2").Value = name
current.Range("U2").Value = travelType

Edit:
For what you want to do, I think you should explore the sheet events, like Worksheet_Change

User defined function working fine in vba, while when called from excel sheet showing 'Value' error

1 Answers