CODEWITHSUNDEEP
vbaargumentsuser-defined-functionsquotation-marks
Viktor Ilienko
Viktor Ilienko

Reputation: 895

VBA, string arguments in UDF that does not have quotation marks - how to access their value?

My function in a VBA is:

Function myFunc(a)
    myFunc = a    
End Function

When I use this function in Excel sheet in this way =myFunc("abc"), it is working, but when I use formula without pair of quotating marks =myFunc(abc), then I'm receiving error #NAME?.

Trying change argument from Function myFunc(a) to Function myFunc(chr(34) & a & chr (34) ) leads to error Expected: ).

How can I access a value that was typed without quotation marks in a UDF ( user defined function )?

Update: I need it to simplify usage of UDF for end user.

Upvotes: 0

Views: 840

Answers (2)

Rafał B.
Rafał B.

Reputation: 404

I don't know why do you need something like that. But it is possible! Read about Application.Caller - it's rng where UDF is running. 

 Private Function myFuncCalc(ByVal xstr As String)
 ' it is your main function to calculate what you want
 ' just sample code to test below
   If xstr = "USD" Then
    myFuncCalc = "yes it's american dollar!"
   Else
    myFuncCalc = "it's no american dollar"
   End If
 End Function

 Function myFunc(a)
 ' function just to be available in worksheet
 ' and extracting currency letter codes from formula between brackets
   bra1 = InStr(Application.Caller.Formula, "(")
   bra2 = InStr(Application.Caller.Formula, ")")
   x = Mid(Application.Caller.Formula, bra1 + 1, bra2 - bra1 - 1)
   myFunc = myFuncCalc(x)
 End Function

Voila!

Upvotes: 2

Sama Tori
Sama Tori

Reputation: 141

If you use it without quotes, excel is expecting a named range. If what you want is to get the contents from another cell, you should define the argument as myfunction(a as Range) and then get its value using a.Value2 or a.Text.

Upvotes: 1

Related Questions