A Visual Basic COM object is simple to create, call and debug from Excel

  • Article

At the SouthWest Fox conference in Phoenix I asked “How many people have Visual Studio?” Almost everybody raised their hand. Then I asked “How many people have used it?” Very few hands were raised. Here’s a simple way to create some VB.Net code and call it from VFP or Excel

Start Visual Studio.

Choose File->New->Project (VB Class Library)

Name VBCom

In Solution Explorer, Delete Class1.VB

Choose Project->Add Class->COM Class (you might have to scroll down to find “Com Class”) named ComClass1.VB

Add some code in the class definition just before the “End Class”:

Public function foobar(p1 as string) as string

            Return p1 + “ VB was here”

Hit F9 to set a breakpoint on the Return statement

Hit F5. to build and execute. An error will occur:

A project with an Output Type of Class Library cannot be started directly.

In order to debug this project, go to the Debugging tab under Configuration Settings in Project Properties, and set the Start Action to Start External Program or Start URL. Alternatively, you can add a non-library project to this solution that uses a reference to this project and set it as the startup project.

Choose File->Add Project->Existing Project. Navigate to VFP9.EXE (typically in “c:\Program Files\Microsoft Visual Foxpro 9”)

Rt-Click on VFP9 in Solution Explorer. Choose “Set as startup project”

Rt-Click on VFP9 in Solution Explorer. Choose Properties->Debugger Type->Change from Auto to Managed Only

Hit F5. VFP starts up

Execute in VFP command window

x=CREATEOBJECT("vbcom.comclass1")

?x.foobar(“testparm”)

The breakpoint then hits in the debugger.

Instead of VFP, you can use Excel.

Choose Project->Add Existing Project. (It’s ok to have both VFP and Excel and Word, etc. in your solution.)

Navigate to Excel.Exe (typically in “c:\Program Files\Microsoft Office\Office11”)

When Excel starts, choose Tools->Macro->Visual Basic Editor.

When VB starts up, choose Insert->Module. Type

Sub Foo

MsgBox “test”

Hit the F5 button and see that the MessageBox fires.

Now choose Tools->References, add VBCom as a reference.

Add this code and hit F5

Set ox = CreateObject("vbcom.comclass1")

MsgBox ox.FooBar("Excel")

The breakpoint hits and Excel has called the VB Com object

If you’re feeling really adventurous, try using VFP and use this VB code:

    Public Function FooBar(ByVal oVFP As Object) As VBCom.ComClass1

        oVFP.DoCmd("Messagebox('Called via late binding')")

        oVFP.GetType().InvokeMember("DoCmd", Reflection.BindingFlags.InvokeMethod, _

            Nothing, oVFP, New Object() {"Messagebox('Called by Reflection')"})

        Return Me

    End Function

And call it with this VFP code:

x=CREATEOBJECT("vbcom.comclass1")

PUBLIC ox

ox=x.foobar(_vfp)