Excel Mac VBA Evaluate not returning string

[pdoak]

I am using Excel version 16.9 for Mac.

Evaluate works if the returning value is an integer but not if it is text. The following code returns `Error 2029` when evaluating the RetText function.

 temp = Evaluate(RetText())
 temp1 = Evaluate(RetNo())

Public Function RetText() As String
    RetText = "Te"
End Function
Public Function RetNo()
    RetNo = 5
End Function

Is this a known bug or is there a way around the problem?

 

[AlphaFrog]

Why are you evaluating "TE"? It would evaluate to an error. It's not a formula. The number 5 just evaluates to 5.

If you just want to call a function, you don't need Evaluate.

 temp = RetText()
 temp1 = RetNo()

 

[pdoak]

The code I showed was a simplified example of my problem. The actual Evaluate() call uses a variable to determine the function to call. For example, the call may look like this:

temp = Evaluate(functionToCall)

where

functionToCall is a string variable which holds the correct function to call along with any parameters that the functionToCall requires eg. GetWeb(url) could be held in the variable. I am expecting the Evaluate function to call the function GetWeb(url) and return the result of the function.

 

[mikerickson]

Evaluate takes a string as its argument.

temp = Evaluate(RetText())

Will set temp = Evaluate("Te"), which is probably an error (unless there is a Name "te")

Perhaps that should be

temp=Evaluate("RetText()")

But I can't understand why one would use Evaluate to get the value from a UDF, rather than calling the UDF directly.

 

[AlphaFrog]

The code I showed was a simplified example of my problem. The actual Evaluate() call uses a variable to determine the function to call. For example, the call may look like this:

temp = Evaluate(functionToCall)

where

functionToCall is a string variable which holds the correct function to call along with any parameters that the functionToCall requires eg. GetWeb(url) could be held in the variable. I am expecting the Evaluate function to call the function GetWeb(url) and return the result of the function.

The Evaluate function will not call another procedure by evaluating a string. I think you want to use Application.Run

[color=darkblue]Sub[/color] Foo()
    [color=darkblue]Dim[/color] Temp [color=darkblue]As[/color] [color=darkblue]String[/color], MyArg [color=darkblue]As[/color] [color=darkblue]String[/color], Result [color=darkblue]As[/color] [color=darkblue]String[/color]
    Temp = "RetText"
    MyArg = "XYZ"
    
    Result = Application.Run(Temp, MyArg)
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]




[color=darkblue]Public[/color] [color=darkblue]Function[/color] RetText(Arg1 [color=darkblue]As[/color] [color=darkblue]String[/color]) [color=darkblue]As[/color] String
    [color=darkblue]If[/color] Arg1 = "XYZ" [color=darkblue]Then[/color] RetText = "Te" [color=darkblue]Else[/color] RetText = "ET"
[color=darkblue]End[/color] [color=darkblue]Function[/color]