HOWTO: Call a Visual Basic Application from Excel

- 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          : IAPDDE vbwin GnrlVb kbinterop kbprg kbfasttip
Technology        : kbvba
Version           : WINDOWS:3.0
Issue type        : kbhowto