0 votes
in VBA by Skilled (328 points)

Lori introduced the RunVBA function in this link. I have used recursion to reduce it to a single Excel UDF, added error handling, and renamed it VBAResult:

Public Function VBAResult(Expression As Variant) As Variant
' Return the Result (left side of equal sign) of VBA Expression (right side of equal sign)
' If Result is an array, its initial value is returned (or #VALUE! if preferred)
' Examples:
'   ="Build #"&VBAResult("Application.Build")
'   ="ActiveSheet has "&VBAResult("ActiveSheet.Hyperlinks.Count")&" hyperlinks"
' This function will call itself with bRecursion set to True for the second pass
    Static bRecursion As Boolean
    Static vResult As Variant       ' result of each pass; second pass replaces first pass
    On Error GoTo ErrorHandler
    If IsArray(Expression) Then
        vResult = Expression(LBound(Expression))    ' or CVErr(xlErrValue)
    Else
        vResult = Expression
    End If
    bRecursion = (Not bRecursion)
' WARNING: Do not change the next statement; apostrophe does the magic
    If bRecursion Then Application.Run "'VBAResult " & Expression & "'"
    VBAResult = vResult
    Exit Function
ErrorHandler:
    bRecursion = False
    VBAResult = CVErr(xlErrValue)
End Function

See examples of use in the code above. The function’s text argument should be a VBA expression permitted on the right side of an equal sign which results in a Variant value on the left side of the equal sign (Result = Expression). Here’s an example that returns 1 (vbOK) after MsgBox is dismissed:
=VBAResult("MsgBox(""Hello World!"")")
But this is incorrect and returns a #VALUE! error:
=VBAResult("MsgBox ""Hello World!""")
An Expression that represents an Object simply returns Expression (the Object), so this example returns ActiveWorkbook:
=VBAResult("ActiveWorkbook")
But this example returns the workbook’s filename:
=VBAResult("ActiveWorkbook.Name")
If Expression represents an array, only its first value is returned (unless you prefer #VALUE! error); this example returns Now:
=VBAResult("Split(""Now is the time"")")

This function might not be very useful, but it is interesting. The magic is done by apostrophes in the Run statement, but I don’t know why. I have tried no apostrophes or different arrangement of apostrophes or added parentheses or Evaluate instead of Run, but the statement in the listing is the only one that works. Except for obvious differences, it is similar to running a macro in another file like this:
Application.Run "'My File.xlsm'!MyMacro"
Lori thought the magic might be related to “the old RUN macro function which only passes a single string.” I looked at RUN in the Excel 4.0 Macro Functions Reference and did not find anything about apostrophes.

Does anyone here have a better explanation for this use of apostrophes with the Application.Run method?

1 Answer

0 votes
by Beginner (23 points)

I believe the answer is that the Application.Run method requires a string, and within that string if any part of the command contains spaces, then the apostrophes keep that string 'together'. This is what you suspected. But I think with the VBA run command that you have setup, when you run the command after an =, you must put any arguments within parentheses. However, you are calling the VBAResult without the = before it, which means you are not required to use the parentheses. Thus you have a space in the command.

=VBAResult(string statement)

vs

VBAResult string statement

So when you have If bRecursion then application.run ... you have a single string statement after this, and this string statement is calling the VBAResult without the equals, so you put the space between it and the string statement. Without the apostrophes I think it means you have two separate strings, rather than one single string for the run command to use.

I hope this make sense... Can anyone confirm what I wrote?

by Skilled (328 points)

Thanks for your answer, but I still don't get it. 

According to Microsoft, here is the general statement for the Application.Run method:
x = Application.Run(Macro, Arg1, Arg2, ...)
where Macro is the name of a procedure expressed as a string and each optional Arg is an argument expected by Macro (if any). When the return value (x) is ignored, the Run method can be called with this alternative statement (as usual):
Application.Run Macro, Arg1, Arg2, ...

If I understand your answer correctly, the VBAResult function uses the Run method as if Macro and Arg1 are combined into a single procedure name surrounded by apostrophes and expressed as a string:
Application.Run 'Macro Arg1'
or alternatively:
Application.Run 'Macro(Arg1)'
I agree, but I don't understand why this works.

Regarding use of Application.Run in the VBAResult function, either of these four statements yields the same result:

If bRecursion Then x = Application.Run("'VBAResult " & Expression & "'")
If bRecursion Then x = Application.Run("'VBAResult(" & Expression & ")'")
If bRecursion Then Application.Run "'VBAResult " & Expression & "'"
If bRecursion Then Application.Run "'VBAResult(" & Expression & ")'"

My original code uses the 3rd statement to produce the desired result.

But none of these statements gives the desired result:

If bRecursion Then Application.Run "VBAResult", Expression
If bRecursion Then Application.Run "VBAResult", 'Expression'
If bRecursion Then Application.Run "VBAResult", "'Expression'"
If bRecursion Then Application.Run "VBAResult", "'" & Expression & "'"
If bRecursion Then Application.Run "'VBAResult'", "'Expression'"
If bRecursion Then Application.Run "'VBAResult ", Expression & "'"
If bRecursion Then Application.Run "'VBAResult", " " & Expression & "'"
If bRecursion Then Application.Run "VBAResult " & Expression
If bRecursion Then Application.Run "VBAResult " & "'" & Expression & "'"
If bRecursion Then Application.Run "VBAResult('" & Expression & "')"
If bRecursion Then Application.Run "VBAResult(" & Expression & ")"

 

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...