VBA - Anonymous objects

Advert: Come to the Foo Bar! See a whole working application based on Excel VBA, JSON, JavaScript, Angular with help of a beauty of C# COM Addin. The Foo Bar where the beers are easily replenished with Angular.

So in Java, C# and other object orientated languages one has the capability to create an object without assigning to a variable, call a method on it and then throw it away in a tight little block of code. The line of code would look something like this...

{  
   print((new Class1).Greeting);
}

This is called an anonymous object. If you tried this in VBA you would get a syntax error

Sub SyntaxError()
    '* the following line appears in red as a syntax error
    Debug.Print ((New Class1).Greeting)
End Sub

Where Class1 is a trivial class

Public Function Greeting()
    Greeting = "Hello world"
End Function

What is interesting is that VBA does allow the creation of an object on the fly when passing an argument thus...

Sub PassNewlyCreated()
    Bar New Class1
End Sub

Sub Bar(obj As Object)
    Debug.Print obj.Greeting
End Sub

So we can pull a trick here. If we define a function which simply passes an object on then we can get closer to the anonymous object syntax given at the top of this post. We'll call that function Anon() ...

Sub UseAnAnonymousObject()
    Debug.Print (Anon(New Class1).Greeting)
End Sub

Function Anon(obj As Object) As Object
    Set Anon = obj
End Function

Just some syntactic sugar for you there.

VBA - Shelling Microsoft Edge and other Protocol Activation

Just a quickie, Microsoft Edge is a new generation windows app and is activated using a protocol. So from the command line we go

start microsoft-edge:http://www.cnn.com

And this launches Microsoft Edge with a new tab with CNN web page inside. But tring to shell to this using VBA gives the error file not found. What is required is to add the command specifier C:\WINDOWS\system32\cmd.exe which is also set as the environment variable comspec. So now we can shell from VBA if we precede with the comspec thus ...

Sub ShellMicrosoftEdge()
    VBA.Shell Environ$("comspec") & " /c start microsoft-edge:http://www.cnn.com"
End Sub

Python code

For a bonus we can read this trick across to Python code. Again add the comspec ...

import os 
from subprocess import Popen

browserJob=Popen([os.environ['comspec'],'/c','start' , 'microsoft-edge:http://www.cnn.com' ])

Thoughts

As these new activation protocols proliferate with Window 8 onwards apps, don't worry it is still possible to launch/shell in a traditional manner. The links below give some further reading.

Links

• Jerry Nixon on Windows_ Walkthrough_ using Windows 8 Custom Protocol Activation
• How to script Microsoft's Edge browser _ ITworld
• windows 10 - How to open URL in Microsoft Edge from the command line_ - Stack Overflow