Formula To User Defined Function

[bloodmilksky]

Hi Guys,

I was just wondering if anyone knows how to turn the below formula into a user defined function? for VBA

=IFERROR(INDEX('Hidden Sheet'!B2:B19,MATCH(B7,Employees,0)),"")

Many thanks

Jamie

 

[mikerickson]

VBA is significantly slower that native excel and uses more resources.
There is a disadvantage in creating a UDF for that.
The formula as it is fine.

 

[bloodmilksky]

my thought was is that I didnt want the users to be able to amend or interfer with the formula and thought that setting it up as a UDF would help me accomplish this.

 

[mikerickson]

Users can mess with UDF's as easily as they can with native formula.
The most common error would be to enter the wrong cell addresses or the wrong relative referencing. Switching to a UDF wouldn't address that at all.
(And, if your users feel free to alter formulas, I'd not encourage them to get into the VBE. )

One thought that I had about your formula is that if you sort the Employees range, you could use the formula
=IFERROR(LOOKUP(B7, Employees, 'Hidden Sheet'!B2:B19), "")