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

ID: Q112813

The information in this article applies to:

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
To use the same instruction in the Visual Basic Standard or Professional Edition, use the following syntax 

   ' Using arguments in correct order, commas as place holders
   xl.Selection.PasteSpecial xlall, , True
where xl is an object variable that refers to Microsoft Excel (the complete Visual Basic Standard or Professional Edition code is included below). The extra comma after the xlall value is a place holder for the Operation argument.

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
To see a list of the Microsoft Excel constants, do the following:

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.

The Methods/Properties list contains the Microsoft Excel 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 professionals 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)
where the named arguments are included in the parentheses.

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 
Version           : 5.0 5.0c 7.0
Platform          : WINDOWS

Last Reviewed: May 17, 1999