Re: VB newbie - exposing VB subs to VBA?

From: Ralph (msnews.20.nt_consulting32_at_spamgourmet.com)
Date: 02/13/05 

Date: Sun, 13 Feb 2005 12:23:37 -0600

"Maury Markowitz" <MauryMarkowitz@discussions.microsoft.com> wrote in
message news:8BC3253A-6929-406B-ABAC-844D16B80F1C@microsoft.com...
> I've programmed for some time using VBA, and I have written some code that
> works well when run on a particular machine -- it's hardware keyed. I
would
> like to call that function remotely from Access however.
>
> I tried this using DCOM between the machines, but this has proven to be a
> real problem. Debugging is basically impossible, as far as I can tell, and
> any problem generally turns into a totally generic message, or both ends
> stalling. I have the _feeling_ that the problems are due to version
> differences of the apps on the two machines, and also that the programs
are
> popping up invisible dialog boxes that can't be answered (for instance, it
> seems that Excel will ask you if you want to run macros).
>
> The code in question is very generic, so I'm thinking of simply putting it
> into VB and thereby avoiding these problems. So, my questions:
>
> How do I expose VB subs via COM? Is this automatic? If not, is it easy? I
> understand there's typically a registration process, but does VB do this
> automatically?
>
> Once exposed, what is the syntax for calling them? For instance in Excel I
> would do something like:
>
> xl = CreateObject("Excel.Application", "targetmachine")
> xl.Worksheets.Open("thesheet")
> xl.Run("theMacro")
>
> Alternate solution?
>
> There's only two parameters needed for this function, they could be passed
> in on the command line easily enough. This being the case, is there some
easy
> way to fire up a command line app from a remote machine? This way I could
> avoid all of the DCOM issues entirely.

Maury,

There is no way to avoid 'DCOM', per se, as you will have to create an
ActiveX component and use COM to connect to it. Using either an ActiveX DLL
or EXE. Excel is an example of an ActiveX Exe. You would 'connect' to your
component the same way.

To create one in VB is very simple, just chose the correct Project Wizard
when you create the project. Then make sure the component is properly
registered on each box and configured thru the Computer Managment Console
(MMC, Component Services Admin, ..., or whatever for your platform).

Not sure what you mean by "fire up a command line app from a remote
machine". There are various ways to run a remote shell on another computer,
but you will likely run into all kinds of permission and security issues if
you try to do this in the adverage 'production' environment, as a 'normal'
user. You might want to look at the various COM+ services such as messaging
for a more easily controlled cross-box communication. But this likely
over-kill for what you are trying to do.

hth
-ralph