How To Call a Visual Basic Application from Excel

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
   
   
   • 
     
     
   • 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.