CODEWITHSUNDEEP
excelvba
Yaurav
Yaurav

Reputation: 1

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

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

Upvotes: 0

Views: 130

Answers (1)

iDevlop
iDevlop

Reputation: 25272

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

Upvotes: 1

Related Questions