XL: Visual Basic Example Using the Verb Method

ID: Q128711

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel you can use the Verb method to activate an embedded OLE object. For example, you can use the verb Open or Primary (represented by the xlOpen and xlPrimary constants, respectively).

MORE INFORMATION

When you use the Open verb (xlOpen is the constant for this verb), it opens the application in its own window. When you use the Primary verb (xlPrimary is the constant for this verb) it activates the object and prepares it for in-place editing. The following example uses a Microsoft Word version 6.0 object.

Sample Visual Basic Code

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

The following Visual Basic procedures use both the xlOpen and xlPrimary constants. The example also includes a subroutine that clears out the embedded Word object.


Sub Embedded_Ole_Object_Using_xlPrimary()
   'Activates the object
   ActiveSheet.DrawingObjects("Picture 1").Verb (xlPrimary)
      Set myobj = CreateObject("word.basic")  'Creates a Word Basic object
      With myobj
         .Insert "Hello"                      'Word Basic commands
         .Insertpara
         .Insertpara
         .Insert "Goodbye"
      End With

      Set myobj = Nothing                      'clears the object
      Range("a1").Select                       'selects cell A1
End Sub


Sub Embedded_Ole_Object_Using_xlOpen()
   'Opens up Word
   ActiveSheet.DrawingObjects("Picture 1").Verb (xlOpen)
      Set myobj = CreateObject("word.basic")  'creates a Word Basic object
      With myobj
          .Insert "Hello"                     'Word Basic commands
          .Insertpara
          .Insertpara
          .Insert "Goodbye"
      End With

   'Sends keystrokes to close Word
   SendKeys "%fx{TAB}{ENTER}", True
   Set myobj = Nothing                        'clears the object
   Range("a1").Select                         'selects cell A1
End Sub

Sub Clearit()
   'Activates the object
   ActiveSheet.DrawingObjects("Picture 1").Verb
   'Creates a Word Basic object
      Set myobj = CreateObject("word.basic")
      With myobj
         .EditSelectAll                       'Word Basic commands
         .EditClear
      End With

      Set myobj = Nothing 'clears the object
      Range("a1").Select 'selects cell A1
End Sub

To use these subroutines, do the following to set up the workbook:

In Microsoft Excel, from the Insert menu, choose Object.

Select the Create New tab. On the Create New tab, select Microsoft Word 6.0 Document from the Object Type list, and choose OK.

To return focus to Microsoft Excel, click any cell on the worksheet.

REFERENCES

The Microsoft Press book: "Excel 5 Visual Basic for Applications Reference," version 5.0, page 708

For more information about the Verb Method, choose Contents in Help, then choose Programming With Visual Basic and choose Search, and type:

verb

Additional query words:

Keywords :
Version : WINDOWS:5.0,5.0c,7.0,7.0a,97
Platform : WINDOWS
Issue type :