XL: OLE Automation, Can't Use Named Arguments in Visual Basic

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0

SUMMARY

When you use a Microsoft Visual Basic application, you cannot call a Microsoft Excel OLE automation object using named arguments. Instead, you must use arguments in their correct order, using commas as place holders even for omitted arguments.

NOTE: You can used Named arguments if you are using Microsoft Visual Basic version 4.0 and Microsoft Excel 97.

In Microsoft Excel Visual Basic for Applications, names are defined for the arguments to their properties and methods. These names allow you to list arguments in any order or omit preceding arguments.

The following is an instruction in Microsoft Excel Visual Basic for Applications that pastes the entire contents of the clipboard on a worksheet, into the current selection, skipping any blank cells that were copied:

   ' Using Named arguments
   Selection.PasteSpecial Paste:=xlAll, SkipBlanks:=True

   ' Using arguments in correct order, commas as place holders
   xl.Selection.PasteSpecial xlall, , True

NOTE: In Microsoft Excel, there are many symbolic constants defined that are used for application-specific settings. These constants all begin with the letters "xl", without quotes. For example, xlall in the above example is used to paste all the contents of the clipboard with the Paste.Special command.

These Microsoft Excel constants are available automatically in Microsoft Excel Visual Basic for Applications. For the Standard or Professional Editions of Visual Basic, you must either add the Microsoft Excel constants file XLCONST.BAS to your project to use the constant by name as in the above example, or you must use the numeric value of the constant. The Microsoft Excel constants file includes the name of each constant along with the value of that constant.

This constants file is included with the "Built-in Constants in Microsoft Visual Basic for Applications" (WC0993) Application Note.

For more information about obtaining this application note, query on the following words in the Microsoft Knowledge Base:

   xlconst.bas and application and note

1. Insert or select a module sheet.

2. On the View menu, click Object Browser.

3. In the Libraries/Workbooks list, select Excel.

4. In the Objects/Modules list, select Constants.

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

The syntax for the PasteSpecial method in Microsoft Excel Visual Basic for Applications is the following:

   object.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

In Visual Basic Standard or Professional Edition, if you want to leave out an argument such as operation in the example above, you must indicate the missing argument with a comma. Trailing commas at the end of an instruction can be omitted. In the example above, it is not necessary to add additional commas after the final argument (SkipBlanks).

The following procedure in Visual Basic Standard or Professional Edition opens the workbook TEST.XLS, selects the range A1:B6, copies the cells to the clipboard, selects a new range, and pastes the entire copied range (except for any blank cells) to this location.

   Sub Form_Load ()
      ' Dimension variable xl as object type
      Dim xl As object
      ' Activate Microsoft Excel and assign to variable xl
      Set xl = GetObject(, "Excel.Application.5")
      ' Open workbook TEST.XLS
      xl.Workbooks.Open "c:\excel\test.xls"
      ' Select range A1:B6 on active worksheet
      xl.Activesheet.Range("A1:B6").Select
      ' Copy selection to clipboard
      xl.Selection.Copy
      ' Select cell A11
      xl.Activesheet.Range("A11").Select
      ' Paste contents of clipboard to active cell
      ' Skip blank cells in copy range
      xl.Selection.PasteSpecial xlall, , True
      Set xl = Nothing
   End Sub

Keywords          : kbprg PgmHowTo kbprg
Version           : 5.0 5.0c 7.0
Platform          : WINDOWS