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

Question

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.

Answer 1

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!