Having an Issue Using WorksheetFunction in a UDF

[cerfani]

Hi I have what I thought was a simple task but I keep getting unexpected results... I googled around but could not find an answer so hopefully this question is not redundant...

I am creating a UDF which is going to wrap some regular excel functions. This UDF will reference ranges in one of the sheets and this UDF is saved in a module that is part of the same workbook.

I am doing this and it only returns 0... note... this is not the actual final code but I wrote this to test... and the test failed

Function TestFunc()
    TestFunc = WorksheetFunction.Index(Sheet1.Range("A2:A10"), 1)
End Function

so on Sheet1, in ranges A2 through A10... there are random values... so i assumed the value in A2 would be returned but I only get 0...

I know my problem must be basic but I am more of a beginner with VBA (not a beginner to programming or excel)

Thanks to whoever is reading

 

[pjmorris]

Glad to have helped, and thanks for sharing your solution.

Regards