Is it possible to use excel inbuilt function within user defined or custom made function created by VBA?

Question

I am trying to create a function in excel via VBA and wondering if excel inbuilt function can be used within that function. for example, functions like Lookup or Match.I have the solution with VBA & formula but interested to know if function can do the same.

I am trying to create a function where VLookup should only return value for first found item.

'function which can return the Vlookup value only for 1st found value
Function SLOOKUP(Pvalue As Range, Rng As Range, Rng1 As Range, pIndex As Long)

    Dim Cvalue As Variant
    Dim Mvalue As Long
    Dim Uvalue As Long
    Dim Result As Variant
    Result = ""

Cvalue = Pvalue.Value
Mvalue = Application.Worksheet.Function.Match(Cvalue, Rng, -1)
Uvalue = Pvalue.Row

If Mvalue = Uvalue Then
Result = Application.Worksheet.Function.VLookup(Cvalue, Rng1, pIndex, 0)
Else
Result = 0
End If

SLOOKUP = Result

End Function

The answer is "yes", with some caveats/limitations (i.e., notably UDFs are not generally allowed to alter the environment). If you'd like more specificity, you should probably include what you're trying to do, and what code you have attempted to write or use. — David Zemens
– David Zemens, Commented Apr 7, 2017 at 19:17
yes, it doesn't return any value. when I am testing it through temporary sub, the error point is the Application.Match function where run time error 438 "Object doesn't support this property or method — user3200076
– user3200076, Commented Apr 7, 2017 at 20:23
Now the code works, code is correct. Worksheet.Function is wrong. it must be worksheetfunction. — user3200076
– user3200076, Commented Apr 7, 2017 at 20:27

Gary's Student · Accepted Answer · 2017-04-07 20:01:05Z

1

Here is an example that uses MAX() and SUM():

Public Function whatever(rin As Range) As Variant
    Dim mx As Variant
    mx = Application.WorksheetFunction.Max(rin)
    whatever = Application.WorksheetFunction.Sum(rin) / mx
End Function

Note that not all built-in worksheet functions can be used like this in VBA. Here are the available ones:

List of Worksheet Functions Available to Visual Basic

edited Apr 7, 2017 at 20:01

answered Apr 7, 2017 at 19:17

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

3 Comments

user3200076 Over a year ago

thanks, how can I get the details which function cannot be used?

user3200076 Over a year ago

thanks for sharing the link. somehow my code doesn't work if I use Match and Vlookup.

Gary's Student Over a year ago

@user3200076 worksheetfunction should have no dot in it

Collectives™ on Stack Overflow

Is it possible to use excel inbuilt function within user defined or custom made function created by VBA?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related