Class Parameter (Excel VBA)

The class Parameter represents a single parameter used in a parameter query. 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)

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

DataType

Returns or sets an XlParameterDataType value that represents the data type of the specified query parameter. Here you can find possible values for XlParameterDataType.

ActiveCell.QueryTable.Parameters(1).DataType = xlParamTypeBigInt

Name

Returns or sets a String value representing the name of the object.

ActiveCell.QueryTable.Parameters(1).Name =

PromptString

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

Dim strPromptString As String
strPromptString = ActiveCell.QueryTable.Parameters(1).PromptString

RefreshOnChange

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

You can set this property to True only if you use parameters of type xlRange and if the referenced parameter value is in a single cell. The refresh occurs when you change the value of the cell.

ActiveCell.QueryTable.Parameters(1).RefreshOnChange = True

SetParam

Defines a parameter for the specified query table.

SetParam (Type, Value)

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

Arguments

The following arguments are required:

Type (XlParameterType) - One of the constants of XlParameterType, which specifies the parameter type.

Possible return values are xlConstant - Uses the value specified by the Value argument, xlPrompt - Displays a dialog box that prompts the user for the value. The Value argument specifies the text shown in the dialog box, xlRange - Uses the value of the cell in the upper-left corner of the range. The Value argument specifies a Range object.

Value (Variant) - The value of the specified parameter, as shown in the description of the Type argument.

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. Possible return values are xlConstant - Uses the value specified by the Value argument, xlPrompt - Displays a dialog box that prompts the user for the value. The Value argument specifies the text shown in the dialog box, xlRange - Uses the value of the cell in the upper-left corner of the range. The Value argument specifies a Range object.

Dim xptType As XlParameterType
xptType = ActiveCell.QueryTable.Parameters(1).Type

Value

Returns a Variant value that represents the parameter value.

For more information, see the Parameter object.

Dim varValue As Variant
varValue = ActiveCell.QueryTable.Parameters(1).Value