Class Parameter (Excel VBA)

The class Parameter represents a single parameter used in a parameter query.

The main procedures of class Parameter are Parameters.Add and Parameters.Delete


To use a Parameter class variable it first needs to be instantiated, for example

Dim prm as Parameter
Set prm = ActiveCell.QueryTable.Parameters(Index:=1)

The following procedures can be used to set variables of type Parameter: Parameters.Item, Parameters.Add and QueryTable.Parameters

For Each

Here is an example of processing the Parameter items in a collection.

Dim prm As Parameter
For Each prm In ActiveCell.QueryTable.Parameters
Next prm


These are the main methods of the Parameter class

Parameters.Add - Creates a new query parameter.

Set qt = Sheets("sheet1").QueryTables(1) 
qt.Sql = "SELECT * FROM authors WHERE (city=?)" 
Set param1 = qt.Parameters.Add("City Parameter", _ 
param1.SetParam xlConstant, "Oakland" 

Parameters.Delete - Deletes the object.


Other Methods

SetParam - Defines a parameter for the specified query table.

Parameters.Item - Returns a single object from a collection.


DataType returns or sets an XlParameterDataType value that represents the data type of the specified query parameter.

Name returns or sets a String value representing the name of the object.

Parent returns the parent object for the specified object. Read-only.

PromptString returns the phrase that prompts the user for a parameter value in a parameter query.

RefreshOnChange true if the specified query table is refreshed whenever you change the parameter value of a parameter query.

SourceRange returns a Range object that represents the cell that contains the value of the specified query parameter.

Type returns an XlParameterType value that represents the parameter type.

Value returns a Variant value that represents the parameter value.

Parameters.Count returns a Long value that represents the number of objects in the collection.

Parameters.Parent returns the parent object for the specified object. Read-only.