Microsoft KB Archive/112813

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

Article ID: 112813

Article Last Modified on 10/10/2006

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q112813





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

Additional query words: XL

Keywords: kbprogramming KB112813

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.