HOWTO: Call a Visual Basic Application from Excel

ID: Q105447

The information in this article applies to:

- Standard and Professional Editions of Microsoft Visual Basic for

  Windows, version 3.0

SUMMARY

This article outlines the steps necessary to initiate dynamic data exchange (DDE) between an Excel Macro and Visual Basic application at run-time.

This article demonstrates how to:

Prepare an Excel Macro that will perform DDE.
Initiate a manual DDE link.
Use Poke to send information from the Excel Worksheet to the Visual Basic application.

MORE INFORMATION

A destination application sends commands through DDE to the source application to establish a link. Through DDE, the source provides data to the destination at the request of the destination or accepts information at the request of the destination.

Example Showing How to Establish a DDE Conversation

The steps below give an example of how to establish a DDE conversation between an Excel Worksheet and a Visual Basic application.

Preparing Visual Basic for DDE

1. Start Visual Basic or from the File menu, choose New Project (ALT, F, N)

   if Visual Basic is already running. Form1 is created by default.

2. Add a text box to form1.

3. Change the following properties:

   Control       Name         Caption       Properties
   --------------------------------------------------------------
   Form1         Form1        Form1         LinkMode  : 1-Source
                                            LinkTopic : Form1
   TextBox       Text1        Text1         LinkItem  : Text1

4. Save the project as DDE.MAK, and save the form as DDE.FRM.

5. From the File menu, choose Make Exe File, and name the .EXE file

   DDE.EXE to enable the link.

6. Close Visual Basic.

Preparing Excel for DDE

1. When you start Excel, Sheet1.XLS is created for you by default.

   In Cell C2, type the text you want to send to the Visual Basic
   application. Save the Sheet1 spreadsheet.

2. Create a new Macro Sheet in Excel by choosing New from the File Menu

   and then selecting Macro Sheet.

3. Type the following macro:

    Record1 (a)
    chan=INITIATE("dde","Form1")
    =POKE(chan,"text1",'A:\SHEET1.XLS'!C2)
    =TERMINATE(chan)
    =RETURN(chan)

    The Initiate function starts the Visual Basic application if it is
    not already running and establishes the link with Form1. The Poke
    function puts text from cell C2 of the Sheet1 worksheet into the
    Text1 box on form1. The Terminate function terminates the link, and
    the Return function ends the macro.

    Important Notes
    ---------------

    The following are requirements. You must:

     - Save the Worksheet before attempting the Link.

     - Store the application in the path by making sure the application
       directory is in the path.

     - Not specify the path to the application in the Initiate function.

4. From the Macro menu, choose Run.

5. You will see a message box saying Run Macro. In the reference section,

   type 'Macro1.xlm!Record1' and click OK.

6. If DDE.EXE is not running, you will a message box saying "Remote Data

   not accessible. Start application 'DDE.EXE'." Click Yes.

7. If DDE.EXE was in the path, it will be run minimized. When you maximize

   it, you will see the data from Sheet1 in the Text1 box. If you don't see
   the data, save Sheet1 and try again.

Keywords          : kbinterop kbprg IAPDDE vbwin 
Version           : WINDOWS:3.0
Issue type        : kbhowto

Last Reviewed: September 30, 1997