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:
- Insert or select a module sheet.
- On the View menu, click Object Browser.
- In the Libraries/Workbooks list, select Excel.
- 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