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
– DaggeJ, Commented Jun 19, 2019 at 11:50
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
– iSpain17, Commented Jun 19, 2019 at 13:47

Patrick Honorez · Accepted Answer · 2019-06-19 14:01:18Z

1

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

edited Jun 19, 2019 at 14:01

answered Jun 19, 2019 at 11:51

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yaurav Over a year ago

thanks for the reply. It is working but in that case it won's perform, what i have wished for.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related