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

Set

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

Dim strName As String: strName = 
Dim qrytbl As QueryTable: Set qrytbl = 
Dim prm As Parameter
Set prm = qrytbl.Parameters.Add(Name:=strName)

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 qrytbl As QueryTable: Set qrytbl = 
Dim prmParameter As Parameter
For Each prmParameter In qrytbl.Parameters
	
Next prmParameter

Methods

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", _ 
 xlParamTypeVarChar) 
param1.SetParam xlConstant, "Oakland" 
qt.Refresh

Parameters.Delete - Deletes the object.

Other Methods

SetParam - Defines a parameter for the specified query table.

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

Properties

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.

With Worksheets(1).QueryTables(1).Parameters(1) 
 .SetParam xlPrompt, "Please " & .PromptString 
End With

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

Set objQT = Worksheets("Sheet1").QueryTables(1) 
objQT.CommandText = "Select * From Customers Where (ContactTitle=?)" 
Set objParam1 = objQT.Parameters _ 
 .Add("Contact Title", xlParamTypeVarChar) 
objParam1.RefreshOnChange = True 
objParam1.SetParam xlRange, Range("D4")

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

Set qt = Sheets("sheet1").QueryTables(1) 
Set param1 = qt.Parameters(1) 
Set r = param1.SourceRange 
r.Value = "New York" 
qt.Refresh

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.