www.sanketham.tk  Welcome to the Microsoft Office Excel 2007 Developer Reference This reference contains conceptual overviews, programming tasks, samples, and references to guide you in developing solutions based on Excel. Press <Alt > < F11 > to view Visual Basic Window OR Right Click on Sheet tab and click “View Code” Returning an Object from a Collection The Item property returns a single object from a collection. The following example sets the firstBook variable to a Workbook object that represents workbook one. Set FirstBook = Workbooks.Item(1) The Item property is the default property (default property: A property that you can set for a control so that each time a new control of that type is created, this property will have the same value.) for most collections, so you can write the same statement more concisely by omitting the Item keyword. Set FirstBook = Workbooks(1) For more information about a specific collection, see the Help topic for that collection or the Item property for the collection. Named Objects Although you can usually specify an integer value with the Item property, it may be more convenient to return an object by name. Before you can use a name with the Item property, you must name the object. Most often, this is done by setting the object's Name property. The following example creates a named worksheet in the active workbook and then refers to the worksheet by name. ActiveWorkbook.Worksheets.Add.Name = "A New Sheet" With Worksheets("A New Sheet") .Range("A5:A10").Formula = "=RAND()" End With Predefined Index Values Some collections have predefined index values you can use to return single objects. Each predefined index value is represented by a constant. For example, you specify an XlBordersIndex constant with the Item property of the Borders collection to return a single border. The following example sets the bottom border of cells A1:G1 on Sheet1 to a double line. Worksheets("Sheet1").Range("A1:A1"). _ Borders.Item(xlEdgeBottom).LineStyle = xlDouble       SolverAdd Function Adds a constraint to the current problem. Equivalent to clicking Solver in the Data | Analysis group and then clicking Add in the Solver Parameters dialog box. Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. SolverAdd(CellRef, Relation, FormulaText) CellRef Required Variant. A reference to a cell or a range of cells that forms the left side of a constraint. Relation Required Integer. The arithmetic relationship between the left and right sides of the constraint. If you choose 4 or 5, CellRef must refer to adjustable (changing) cells, and FormulaText should not be specified. Relation Arithmetic relationship 1 <= 2 = 3 >= 4 Cells referenced by CellRef must have final values that are integers. 5 Cells referenced by CellRef must have final values of either 0 (zero) or 1. FormulaText Optional Variant. The right side of the constraint. Remarks After constraints are added, you can manipulate them with the SolverChange and SolverDelete functions. Example This example uses the Solver functions to maximize gross profit in a business problem. The SolverAdd function is used to add three constraints to the current problem. Worksheets("Sheet1").Activate SolverReset SolverOptions precision:=0.001 SolverOK setCell:=Range("TotalProfit"), _ maxMinVal:=1, _ byChange:=Range("C4:E6") SolverAdd cellRef:=Range("F4:F6"), _ relation:=1, _ formulaText:=100 SolverAdd cellRef:=Range("C4:E6"), _ relation:=3, _ formulaText:=0 SolverAdd cellRef:=Range("C4:E6"), _ relation:=4 SolverSolve userFinish:=False SolverSave saveArea:=Range("A33")   SolverChange Function Changes an existing constraint. Equivalent to clicking Solver in the Data | Analysis group and then clicking Change in the Solver Parameters dialog box. Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. SolverChange(CellRef, Relation, FormulaText) CellRef Required Variant. A reference to a cell or a range of cells that forms the left side of a constraint. Relation Required Integer. The arithmetic relationship between the left and right sides of the constraint. If you choose 4 or 5, CellRef must refer to adjustable (changing) cells, and FormulaText should not be specified. Relation Arithmetic relationship 1 <= 2 = 3 >= 4 Cells referenced by CellRef must have final values that are integers. 5 Cells referenced by CellRef must have final values of either 0 (zero) or 1. FormulaText Optional Variant. The right side of the constraint. Remarks If CellRef and Relation do not match an existing constraint, you must use the SolverDelete and SolverAdd functions to change the constraint. Example This example loads the previously calculated Solver model stored on Sheet1, changes one of the constraints, and then solves the model again. Worksheets("Sheet1").Activate SolverLoad loadArea:=Range("A33:A38") SolverChange cellRef:=Range("F4:F6"), _ relation:=1, _ formulaText:=200 SolverSolve userFinish:=False   SolverDelete Function Deletes an existing constraint. Equivalent to clicking Solver in the Data | Analysis group and then clicking Delete in the Solver Parameters dialog box. Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. SolverDelete(CellRef, Relation, FormulaText) CellRef Required Variant. A reference to a cell or a range of cells that forms the left side of a constraint. Relation Required Integer. The arithmetic relationship between the left and right sides of the constraint. If you choose 4 or 5, CellRef must refer to adjustable (changing) cells, and FormulaText should not be specified. Relation Arithmetic relationship 1 <= 2 = 3 >= 4 Cells referenced by CellRef must have final values that are integers. 5 Cells referenced by CellRef must have final values of either 0 (zero) or 1. FormulaText Optional Variant. The right side of the constraint. Example This example loads the previously calculated Solver model stored on Sheet1, deletes one of the constraints, and then solves the model again. Worksheets("Sheet1").Activate SolverLoad loadArea:=Range("A33:A38") SolverDelete cellRef:=Range("C4:E6"), _ relation:=4 SolverSolve userFinish:=False SolverFinish Function Tells Microsoft Office Excel what to do with the results and what kind of report to create when the solution process is completed. Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. SolverFinish(KeepFinal, ReportArray) KeepFinal Optional Variant. Can be either 1 or 2. If KeepFinal is 1 or omitted, the final solution values are kept in the changing cells, replacing any former values. If KeepFinal is 2, the final solution values are discarded, and the former values are restored. ReportArray Optional Variant. The kind of report that Excel will create when Solver is finished: 1 creates an answer report, 2 creates a sensitivity report, and 3 creates a limit report. Use the Array function to specify the reports you want to display — for example, ReportArray:= Array(1,3). Example This example loads the previously calculated Solver model stored on Sheet1, solves the model again, and then generates an answer report on a new worksheet. Worksheets("Sheet1").Activate SolverLoad LoadArea:=Range("A33:A38") SolverSolve UserFinish:=True SolverFinish KeepFinal:=1, ReportArray:=Array(1) SolverFinishDialog Function Tells Microsoft Office Excel what to do with the results and what kind of report to create when the solution process is completed. Equivalent to the SolverFinish function, but also displays the Solver Results dialog box after solving a problem. Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. SolverFinishDialog(KeepFinal, ReportArray) KeepFinal Optional Variant. Can be either 1 or 2. If KeepFinal is 1 or omitted, the final solution values are kept in the changing cells, replacing any former values. If KeepFinal is 2, the final solution values are discarded, and the former values are restored. ReportArray Optional Variant. The kind of report that Excel will create when Solver is finished: 1 creates an answer report, 2 creates a sensitivity report, and 3 creates a limit report. Use the Array function to specify the reports you want to display — for example, ReportArray:= Array(1,3). Example This example loads the previously calculated Solver model stored on Sheet1, solves the model again, and then displays the Finish dialog box with two preset options. Worksheets("Sheet1").Activate SolverLoad loadArea:=Range("A33:A38") SolverSolve userFinish:=True SolverFinishDialog keepFinal:=1, reportArray:=Array(1)   SolverGet Function Returns information about current settings for Solver. The settings are specified in the Solver Parameters and Solver Options dialog boxes. Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. SolverGet(TypeNum, SheetName) TypeNum Required Integer. A number specifying the type of information you want. The following settings are specified in the Solver Parameters dialog box. TypeNum Returns 1 The reference in the Set Target Cell box, or the #N/A error value if Solver has not been used on the active sheet. 2 A number corresponding to the Equal To option: 1 represents Max, 2 represents Min, and 3 represents Value Of. 3 The value in the Value Of box. 4 The reference (as a multiple reference, if necessary) in the By Changing Cells box. 5 The number of constraints. 6 An array of the left sides of the constraints, in text form. 7 An array of numbers corresponding to the relationships between the left and right sides of the constraints: 1 represents <=, 2 represents =, 3 represents >=, 4 represents int, and 5 represents bin. 8 An array of the right sides of the constraints, in text form. The following settings are specified in the Solver Options dialog box. TypeNum Returns 9 The maximum calculation time. 10 The maximum number of iterations. 11 The precision. 12 The integer tolerance value. 13 True if the Assume Linear Model check box is selected; False if it is cleared. 14 True if the Show Iteration Results check box is selected; False if it is cleared. 15 True if the Use Automatic Scaling check box is selected; False if it is cleared. 16 A number corresponding to the type of estimates: 1 represents Tangent, and 2 represents Quadratic. 17 A number corresponding to the type of derivatives: 1 represents Forward, and 2 represents Central. 18 A number corresponding to the type of search: 1 represents Newton, and 2 represents Conjugate. 19 The convergence value. 20 True if the Assume Non-Negative check box is selected. SheetName Optional Variant. The name of the sheet that contains the Solver model for which you want information. If SheetName is omitted, this sheet is assumed to be the active sheet. Example This example displays a message if you have not used Solver on Sheet1. Worksheets("Sheet1").Activate state = SolverGet(TypeNum:=1) If IsError(State) Then MsgBox "You have not used Solver on the active sheet" End If     SolverLoad Function Loads existing Solver model parameters that have been saved to the worksheet. Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. SolverLoad(LoadArea) LoadArea Required Variant. A reference on the active worksheet to a range of cells from which you want to load a complete problem specification. The first cell in the LoadArea contains a formula for the Set Target Cell box in the Solver Parameters dialog box; the second cell contains a formula for the By Changing Cells box; subsequent cells contain constraints in the form of logical formulas. The last cell optionally contains an array of Solver option values. For more information, see SolverOptions. The range represented by the argument LoadArea can be on any worksheet, but you must specify the worksheet if it is not the active sheet. For example, SolverLoad("Sheet2!A1:A3") loads a model from Sheet2 even if it is not the active sheet. Example This example loads the previously calculated Solver model stored on Sheet1, changes one of the constraints, and then solves the model again. Worksheets("Sheet1").Activate SolverLoad loadArea:=Range("A33:A38") SolverChange cellRef:=Range("F4:F6"), _ relation:=1, _ formulaText:=200 SolverSolve userFinish:=False   SolverOk Function Defines a basic Solver model. Equivalent to clicking Solver in the Data | Analysis group and then specifying options in the Solver Parameters dialog box. Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. SolverOk(SetCell, MaxMinVal, ValueOf, ByChange) SetCell Optional Variant. Refers to a single cell on the active worksheet. Corresponds to the Set Target Cell box in the Solver Parameters dialog box. MaxMinVal Optional Variant. Corresponds to the Max, Min, and Value options in the Solver Parameters dialog box. MaxMinVal Specifies 1 Maximize 2 Minimize 3 Match a specific value ValueOf Optional Variant. If MaxMinVal is 3, you must specify the value to which the target cell is matched. ByChange Optional Variant. The cell or range of cells that will be changed so that you will obtain the desired result in the target cell. Corresponds to the By Changing Cells box in the Solver Parameters dialog box. Example This example uses the Solver functions to maximize gross profit in a business problem. The SolverOK function defines a problem by specifying the SetCell, MaxMinVal, and ByChange arguments. Worksheets("Sheet1").Activate SolverReset SolverOptions precision:=0.001 SolverOK SetCell:=Range("TotalProfit"), _ MaxMinVal:=1, _ ByChange:=Range("C4:E6") SolverAdd CellRef:=Range("F4:F6"), _ Relation:=1, _ FormulaText:=100 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=3, _ FormulaText:=0 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=4 SolverSolve UserFinish:=False SolverSave SaveArea:=Range("A33")   SolverOkDialog Function Same as the SolverOK function, but also displays the Solver dialog box. Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. SolverOkDialog(SetCell, MaxMinVal, ValueOf, ByChange) SetCell Optional Variant. Refers to a single cell on the active worksheet. Corresponds to the Set Target Cell box in the Solver Parameters dialog box. MaxMinVal Optional Variant. Corresponds to the Max, Min, and Value options in the Solver Parameters dialog box. MaxMinVal Specifies 1 Maximize 2 Minimize 3 Match a specific value ValueOf Optional Variant. If MaxMinVal is 3, you must specify the value that the target cell is matched to. ByChange Optional Variant. The cell or range of cells that will be changed so that you will obtain the desired result in the target cell. Corresponds to the By Changing Cells box in the Solver Parameters dialog box. Example This example loads the previously calculated Solver model stored on Sheet1, resets all Solver options, and then displays the Solver Parameters dialog box. From this point on, you can use Solver manually. Worksheets("Sheet1").Activate SolverLoad LoadArea:=Range("A33:A38") SolverReset SolverOKDialog SetCell:=Range("TotalProfit") SolverSolve UserFinish:=False SolverOptions Function Allows you to specify advanced options for your Solver model. This function and its arguments correspond to the options in the Solver Options dialog box. Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. SolverOptions(MaxTime, Iterations, Precision, AssumeLinear, StepThru, Estimates, Derivatives, Search, IntTolerance, Scaling, Convergence, AssumeNonNeg) MaxTime Optional Variant. The maximum amount of time (in seconds) Microsoft Excel will spend solving the problem. The value must be a positive integer. The default value 100 is adequate for most small problems, but you can enter a value as high as 32,767. Iterations Optional Variant. The maximum number of iterations Microsoft Excel will use in solving the problem. The value must be a positive integer. The default value 100 is adequate for most small problems, but you can enter a value as high as 32,767. Precision Optional Variant. A number between 0 (zero) and 1 that specifies the degree of precision to be used in solving the problem. The default precision is 0.000001. A smaller number of decimal places (for example, 0.0001) indicates a lower degree of precision. In general, the higher the degree of precision you specify (the smaller the number), the more time Solver will take to reach solutions. AssumeLinear Optional Variant.True to have Solver assume that the underlying model is linear. This speeds the solution process, but it should be used only if all the relationships in the model are linear. The default value is False. StepThru Optional Variant.True to have Solver pause at each trial solution. You can pass Solver a macro to run at each pause by using the ShowRef argument of the SolverSolve function. False to not have Solver pause at each trial solution. The default value is False. Estimates Optional Variant. Specifies the approach used to obtain initial estimates of the basic variables in each one- dimensional search: 1 represents tangent estimates, and 2 represents quadratic estimates. Tangent estimates use linear extrapolation from a tangent vector. Quadratic estimates use quadratic extrapolation; this may improve the results for highly nonlinear problems. The default value is 1 (tangent estimates). Derivatives Optional Variant. Specifies forward differencing or central differencing for estimates of partial derivatives of the objective and constraint functions: 1 represents forward differencing, and 2 represents central differencing. Central differencing requires more worksheet recalculations, but it may help with problems that generate a message saying that Solver could not improve the solution. With constraints whose values change rapidly near their limits, you should use central differencing. The default value is 1 (forward differencing). Search Optional Variant. Use the Search options to specify the search algorithm that will be used at each iteration to decide which direction to search in: 1 represents the Newton search method, and 2 represents the conjugate search method. Newton, which uses a quasi-Newton method, is the default search method. This method typically requires more memory than the conjugate search method, but it requires fewer iterations. Conjugate gradient searching requires less memory than the Newton search method, but it typically requires more iterations to reach a particular level of accuracy. You can try this method if you have a large problem and memory usage is a concern. Conjugate searching is especially useful if stepping through the iterations reveals slow progress between successive trial points. IntTolerance Optional Variant. A decimal number between 0 (zero) and 1 that specifies the degree of integer tolerance. This argument applies only if integer constraints have been defined. You can adjust the tolerance figure, which represents the percentage of error allowed in the optimal solution when an integer constraint is used on any element of the problem. A higher degree of tolerance (allowable percentage of error) would tend to speed up the solution process. Scaling Optional Variant. If two or more constraints differ by several orders of magnitude, True to have Solver scale the constraints to similar orders of magnitude during computation. This is useful when the inputs (in the By Changing Cells box in the Solver Parameters dialog box) and outputs (in the Set Target Cell and Subject to the Constraints boxes in the Solver Parameters dialog box) have large differences in magnitude — for example, maximizing percentage of profit based on million-dollar investments. False to have Solver calculate without scaling the constraints. The default value is False. Convergence Optional Variant. A number between 0 (zero) and 1 that specifies the degree of convergence tolerance for the nonlinear Solver. When the relative change in the target cell value is less than this tolerance for the last five iterations, Solver stops and displays the message "Solver converged to the current solution. All constraints are satisfied." AssumeNonNeg Optional Variant.True to have Solver assume a lower limit of 0 (zero) for all adjustable (changing) cells that do not have explicit lower limits in the Constraint list box (the cells must contain nonnegative values). False to have Solver use only the limits specified in the Constraint list box. Example This example sets the Precision option to .001. Worksheets("Sheet1").Activate SolverReset SolverOptions Precision:=0.001 SolverOK SetCell:=Range("TotalProfit"), _ MaxMinVal:=1, _ ByChange:=Range("C4:E6") SolverAdd CellRef:=Range("F4:F6"), _ Relation:=1, _ FormulaText:=100 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=3, _ FormulaText:=0 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=4 SolverSolve UserFinish:=False SolverSave SaveArea:=Range("A33") SolverReset Function Resets all cell selections and constraints in the Solver Parameters dialog box and restores all the settings in the Solver Options dialog box to their defaults. Equivalent to clicking Reset All in the Solver Parameters dialog box. The SolverReset function is called automatically when you call the SolverLoad function. Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. SolverReset( ) Example This example resets the Solver settings to their defaults before defining a new problem. Worksheets("Sheet1").Activate SolverReset SolverOptions Precision:=0.001 SolverOK SetCell:=Range("TotalProfit"), _ MaxMinVal:=1, _ ByChange:=Range("C4:E6") SolverAdd CellRef:=Range("F4:F6"), _ Relation:=1, _ FormulaText:=100 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=3, _ FormulaText:=0 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=4 SolverSolve UserFinish:=False SolverSave SaveArea:=Range("A33")   SolverSave Function Saves the Solver problem specifications on the worksheet. Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. SolverSave(SaveArea) SaveArea Required Variant. The range of cells where the Solver model is to be saved. The range represented by the SaveArea argument can be on any worksheet, but you must specify the worksheet if it is not the active sheet. For example, SolverSave("Sheet2!A1:A3") saves the model on Sheet2 even if Sheet2 is not the active sheet. Example This example uses the Solver functions to maximize gross profit in a business problem. The SolverSave function saves the current problem to a range on the active worksheet. Worksheets("Sheet1").Activate SolverReset SolverOptions Precision:=0.001 SolverOK SetCell:=Range("TotalProfit"), _ MaxMinVal:=1, _ ByChange:=Range("C4:E6") SolverAdd CellRef:=Range("F4:F6"), _ Relation:=1, _ FormulaText:=100 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=3, _ FormulaText:=0 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=4 SolverSolve UserFinish:=False SolverSave SaveArea:=Range("A33")   SolverSolve Function Begins a Solver solution run. Equivalent to clicking Solve in the Solver Parameters dialog box. Before you use this function, you must establish a reference to the Solver add-in. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. SolverSolve(UserFinish, ShowRef) UserFinish Optional Variant.True to return the results without displaying the Solver Results dialog box. False or omitted to return the results and display the Solver Results dialog box. ShowRef Optional Variant. Used only if True is passed to the StepThru argument of the SolverOptions function. You can pass the name of a macro (as a string) as the ShowRef argument. This macro is then called whenever Solver returns an intermediate solution. Example This example uses the Solver functions to maximize gross profit in a business problem. The SolverSolve function begins the Solver solution run. Worksheets("Sheet1").Activate SolverReset SolverOptions Precision:=0.001 SolverOK SetCell:=Range("TotalProfit"), _ MaxMinVal:=1, _ ByChange:=Range("C4:E6") SolverAdd CellRef:=Range("F4:F6"), _ Relation:=1, _ FormulaText:=100 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=3, _ FormulaText:=0 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=4 SolverSolve UserFinish:=False SolverSave SaveArea:=Range("A33")   SQLBind Function It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLBind and the other ODBC functions in the Xlodbc.xla add-in. SQLBind specifies where results are placed when they’re retrieved with SQLRetrieve or SQLRetrieveToFile. Use SQLBind to change the column order of the result set from a query, or to place the result set columns in nonadjacent worksheet columns. This function is contained in the Xlodbc.xla add-in. Before you use the function, you must establish a reference to the add-in by using the References command (on the Tools menu) in the Visual Basic Editor. SQLBind(ConnectionNum, Column, Reference) ConnectionNum Required. The unique connection ID (returned by SQLOpen) of the data source for which you want to bind results. Column Optional. The column number of the result set you want to bind. Columns in the result set are numbered from left to right, starting with 1. If you omit Column, all bindings for ConnectionNum are removed. Column 0 (zero) contains row numbers for the result set. You can return the row numbers by binding column 0 (zero). Reference Optional. A Range object that specifies the location of a single cell on a worksheet where you want the results to be bound. If Reference is omitted, binding is removed for the column. Return Value This function returns an array that lists the bound columns for the current connection, by column number. If SQLBind is unable to bind the column to the cell in the specified reference, it returns Error 2042. If ConnectionNum is not valid or if you try to bind a cell that is unavailable, SQLBind returns Error 2015. If Reference refers to more than a single cell, SQLBind returns Error 2023. If SQLRetrieve does not have a destination parameter, SQLBind places the result set in the location indicated by Reference. Remarks SQLBind tells the ODBC Control Panel Administrator where to place results when they are received by way of SQLRetrieve The results are placed in the reference cell and the cells immediately below it. Use SQLBind if you want the results from different columns to be placed in disjoint worksheet areas. Use SQLBind for each column in the result set. A binding remains valid as long as the connection specified by ConnectionNum is open. Call SQLBind after you call SQLOpen and SQLExecQuery, but before you call SQLRetrieve or SQLRetrieveToFile. Calls to SQLBind do not affect results that have already been retrieved. Example This example runs a query on the Northwind database, and then it uses the SQLBind function to display only the fourth and ninth columns of the query result set (the product name and the quantity on order) on Sheet1. databaseName = "Northwind" queryString = _ "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString Set output1 = Worksheets("Sheet1").Range("A1") Set output2 = Worksheets("Sheet1").Range("B1") SQLBind chan, 4, output1 SQLBind chan, 9, output2 SQLRetrieve chan SQLClose chan   SQLClose Function It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLClose and the other ODBC functions in the Xlodbc.xla add-in. SQLClose closes a connection to an external data source. This function is contained in the Xlodbc.xla add-in. Before you use the function, you must establish a reference to the add-in by using the References command (on the Tools menu) in the Visual Basic Editor. SQLClose(ConnectionNum) ConnectionNum Required. The unique connection ID of the data source you want to disconnect from. Return Value If the connection is successfully closed, this function returns 0 (zero) and the connection ID is no longer valid. If ConnectionNum is not valid, this function returns Error 2015. If SQLClose is unable to disconnect from the data source, it returns Error 2042. Example This example runs a query on the Northwind database. The result of the query, displayed on Sheet1, is a list of all products that are currently on order. databaseName = "Northwind" queryString = _ "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString Set output = Worksheets("Sheet1").Range("A1") SQLRetrieve chan, output, , , True SQLClose chan   SQLError Function It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLError and the other ODBC functions in the Xlodbc.xla add-in. SQLError returns detailed error information when it’s called after one of the other ODBC functions fails. If SQLError itself fails, it cannot return error information. Error information is defined and stored in memory whenever an ODBC function fails. To make the error information available, call the SQLError function. SQLError provides detailed error information only about errors that occur when an ODBC function fails. It does not provide information about Microsoft Excel errors. This function is contained in the Xlodbc.xla add-in. Before you use the function, you must establish a reference to the add-in by using the References command (on the Tools menu) in the Visual Basic Editor. SQLError() Return Value If there are errors, SQLError returns detailed error information in a two-dimensional array in which each row describes one error. Each row has the following three fields for information obtained through the SQLError function call in ODBC: A character string that indicates the ODBC error class and subclass A numeric value that indicates the data source native error code. A text message that describes the error. If a function call generates multiple errors, SQLError creates a row for each error. If there are no errors from a previous ODBC function call, this function returns only Error 2042. Example This example generates an intentional error by attempting to open a connection to the Northwind database by using an incorrect connection string (Northwind is misspelled). The error information is displayed on Sheet1. chan = SQLOpen("DSN=Nortwind") returnArray = SQLError() For i = LBound(returnArray, 1) To UBound(returnArray, 1) Worksheets("Sheet1").Cells(1, i).Formula = returnArray(i) Next i SQLClose chan   SQLExecQuery Function It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLOpen and the other ODBC functions in the Xlodbc.xla add-in. SQLExecQuery executes a query on a data source with a connection that has been established with SQLOpen. SQLExecQuery executes only the query. Use SQLRetrieve or SQLRetrieveToFile to get the results. This function is contained in the Xlodbc.xla add-in. Before you use the function, you must establish a reference to the add-in by using the References command (on the Tools menu) in the Visual Basic Editor. SQLExecQuery(ConnectionNum, QueryText) ConnectionNum Required. The unique connection ID returned by SQLOpen that identifies the data source you want to query. QueryText Required. The query to be executed on the data source. The query must follow the SQL syntax guidelines for the specific driver. Return Value The value returned by SQLExecQuery depends on the SQL statement, as shown in the following table. SQL statement Return value SELECT The number of columns in the result set UPDATE, INSERT, or DELETE The number of rows affected by the statement Any other valid SQL statement 0 (zero) If SQLExecQuery is unable to execute the query on the specified data source, it returns Error 2042. If ConnectionNum isn’t valid, SQLExecQuery returns Error 2015. Remarks Before calling SQLExecQuery, you must establish a connection to a data source by using SQLOpen. The unique connection ID returned by SQLOpen is used by SQLExecQuery to send queries to the data source. If you call SQLExecQuery using a previously used connection ID, any pending results on that connection are replaced by the new results. Example This example runs a query on the Northwind database. The result of the query, displayed on Sheet1, is a list of all products that are currently on order. databaseName = "Northwind" queryString = _ "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString Set output = Worksheets("Sheet1").Range("A1") SQLRetrieve chan, output, , , True SQLClose chan   SQLGetSchema Function It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLGetSchema and the other ODBC functions in the Xlodbc.xla add-in. SQLGetSchema returns information about the structure of the data source on a particular connection. This function is contained in the Xlodbc.xla add-in. Before you use the function, you must establish a reference to the add-in by using the References command (on the Tools menu) in the Visual Basic Editor. SQLGetSchema(ConnectionNum, TypeNum, QualifierText) ConnectionNum Required. The unique connection ID of the data source you connected to by using SQLOpen and for which you want information. TypeNum Required. Specifies the type of information you want returned, as shown in the following table. Value Meaning 1 A list of all the available data sources. 2 A list of databases on the current connection. 3 A list of owners in a database on the current connection. 4 A list of tables for a given owner and database on the current connection. 5 A list of columns in a particular table and their ODBC SQL data types, in a two-dimensional array. The first field contains the name of the column; the second field is the column’s ODBC SQL data type. 6 The user ID of the current user. 7 The name of the current database. 8 The name of the data source defined during setup or defined by using the ODBC Control Panel Administrator. 9 The name of the DBMS that the data source uses. For example, ORACLE or SQL Server. 10 The server name for the data source. 11 The terminology used by the data source to refer to the owners. For example "owner", "Authorization ID", or "Schema". 12 The terminology used by the data source to refer a table For example, "table" or "file". 13 The terminology used by the data source to refer to a qualifier. For example, "database" or "folder". 14 The terminology used by the data source to refer to a procedure. For example, "database procedure", "stored procedure", or "procedure". QualifierText Optional. Included only for the TypeNum values 3, 4, and 5. A string that qualifies the search, as shown in the following table. TypeNum QualifierText 3 The name of the database in the current data source. SQLGetSchema returns the names of the table owners in that database. 4 Both a database name and an owner name. The syntax consists of the database name followed by the owner's name, with a period separating the two; for example, "DatabaseName.OwnerName". This function returns an array of table names that are located in the given database and owned by the given owner. 5 The name of a table. SQLGetSchema returns information about the columns in the table. Return Value The return value from a successful call to SQLGetSchema depends on the type of information that’s requested. If SQLGetSchema cannot find the requested information, it returns Error 2042. If ConnectionNum isn’t valid, this function returns Error 2015. Remarks SQLGetSchema uses the ODBC API functions SQLGetInfo and SQLTables to find the requested information. Example This example retrieves the database name and DBMS name for the Northwind database and then displays these names in a message box. databaseName = "Northwind" chan = SQLOpen("DSN=" & databaseName) dsName = SQLGetSchema(chan, 8) dsDBMS = SQLGetSchema(chan, 9) MsgBox "Database name is " & dsName & ", and its DBMS is " & dsDBMS SQLClose chan   SQLOpen Function It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLOpen and the other ODBC functions in the Xlodbc.xla add-in. SQLOpen establishes a connection to a data source. This function is contained in the Xlodbc.xla add-in. Before you use the function, you must establish a reference to the add-in by using the References command (on the Tools menu) in the Visual Basic Editor. SQLOpen(ConnectionStr, OutputRef, DriverPrompt) ConnectionStr Required. Supplies the information required by the driver being used to connect to a data source; must follow the driver's format. ConnectionStr supplies the data source name and other information, such as user ID and passwords, that the driver requires to make a connection. You must define the data source name (DSN) used in ConnectionStr before you try to connect to it. OutputRef Optional. A Range object (must be a single cell) that contains the completed connection string. Use OutputRef when you want SQLOpen to return the completed connection string to a worksheet. DriverPrompt Optional. Specifies whether the driver dialog box is displayed and, if it is, which options are available in it. Use one of the values described in the following table. If DriverPrompt is omitted, SQLOpen uses 2 as the default. Value Meaning 1 The driver dialog box is always displayed. 2 The driver dialog box is displayed only if information provided by the connection string and the data source specification aren’t sufficient to complete the connection. All dialog box options are available. 3 The same as 2 except that dialog box options that aren’t required are dimmed (unavailable). 4 The driver dialog box isn’t displayed. If the connection isn’t successful, SQLOpen returns an error. Return Value If successful, SQLOpen returns a unique connection ID number. Use the connection ID number with the other ODBC functions. If SQLOpen is unable to connect using the information you provide, it returns Error 2042. Additional error information is placed in memory for use by SQLError. Example This example runs a query on the Northwind database. The result of the query, displayed on Sheet1, is a list of all products that are currently on order. DatabaseName = "Northwind" QueryString = _ "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" Chan = SQLOpen("DSN=" & DatabaseName) SQLExecQuery Chan, QueryString Set output = Worksheets("Sheet1").Range("A1") SQLRetrieve Chan, Output, , , True SQLClose Chan   SQLRequest Function It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLRequest and the other ODBC functions in the Xlodbc.xla add-in. SQLRequest connects to an external data source and runs a query from a worksheet, and then it returns the result of the query as an array. This function is contained in the Xlodbc.xla add-in. Before you use the function, you must establish a reference to the add-in by using the References command (on the Tools menu) in the Visual Basic Editor. SQLRequest(ConnectionStr, QueryText, OutputRef, DriverPrompt, ColNamesLogical) ConnectionStr Required. Supplies information (such as the data source name, user ID, and passwords) required by the driver being used to connect to a data source; must follow the driver's format. You must define the data source name (DSN) used in ConnectionStr before you try to connect to it. If SQLRequest is unable to access the data source using ConnectionStr, it returns Error 2042. QueryText Required. The SQL statement you want to execute on the data source. If SQLRequest is unable to execute QueryText on the specified data source, it returns Error 2042. OutputRef Optional. A Range object (must be a single cell) where you want the completed connection string to be placed. DriverPrompt Optional. Specifies whether the driver dialog box is displayed and which options are available. Use one of the values described in the following table. If DriverPrompt is omitted, SQLRequest uses 2 as the default. Value Meaning 1 The driver dialog box is always displayed. 2 The driver dialog box is displayed only if information provided by the connection string and the data source specification is not sufficient to complete the connection. All dialog box options are available. 3 The driver dialog box is displayed only if information provided by the connection string and the data source specification is not sufficient to complete the connection. Dialog box options that aren’t required are dimmed (unavailable). 4 The dialog box isn’t displayed. If the connection is not successful, it returns an error. ColNamesLogical Optional. True to have the column names returned as the first row of results. False to not have the column names returned. If ColNamesLogical is omitted, the default value is False. Remarks The arguments to the SQLRequest function are in a different order than the arguments to the SQL.REQUEST macro function. Return Value If this function completes all of its actions, it returns an array of query results or the number of rows affected by the query. If SQLRequest is unable to complete all of its actions, it returns an error value and places the error information in memory for SQLError. If SQLRequest is unable to access the data source using connectionStr, it returns Error 2042. Example This example runs a query on the Northwind database. The result of the query, displayed on Sheet1, is a list of all products that are currently on order. The SQLRequest function also writes the full connection string to Sheet2. databaseName = "Northwind" queryString = _ "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" returnArray = SQLRequest("DSN=" & databaseName, _ queryString, _ Worksheets("Sheet1").Range("A1"), _ 2, True) For i = LBound(returnArray, 1) To UBound(returnArray, 1) For j = LBound(returnArray, 2) To UBound(returnArray, 2) Worksheets("Sheet1").Cells(i, j).Formula = _ returnArray(i, j) Next j Next i   SQLRetrieve Function It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLRetrieve and the other ODBC functions in the Xlodbc.xla add-in. SQLRetrieve retrieves all or part of the results from a previously executed query. Before using SQLRetrieve, you must establish a connection with SQLOpen, execute a query with SQLExecQuery, and have the results pending. This function is contained in the Xlodbc.xla add-in. Before you use the function, you must establish a reference to the add-in by using the References command (on the Tools menu) in the Visual Basic Editor. SQLRetrieve(ConnectionNum, DestinationRef, MaxColumns, MaxRows, ColNamesLogical, RowNumsLogical, NamedRngLogical, FetchFirstLogical) ConnectionNum Required. The unique connection ID returned by SQLOpen and for which you have pending query results that were generated by SQLExecQuery. If ConnectionNum is not valid, SQLExecQuery returns Error 2015. DestinationRef Optional. A Range object that specifies where the results should be placed. This function overwrites any values in the cells, without confirmation. If DestinationRef refers to a single cell, SQLRetrieve returns all the pending results in that cell and in the cells to the right of and below it. If DestinationRef is omitted, the bindings established by previous calls to SQLBind are used to return results. If no bindings exist for the current connection, SQLRetrieve returns Error 2023. If a particular result column hasn’t been bound and DestinationRef is omitted, the results are discarded. MaxColumns Optional. The maximum number of columns returned to the worksheet, starting at DestinationRef. If MaxColumns specifies more columns than are available in the result, SQLRetrieve places data in the columns for which data is available and clears the additional columns. If MaxColumns specifies fewer columns than are available in the result, SQLRetrieve discards the rightmost result columns until the results fit the specified size. The order in which the data source returns the columns determines column position. If MaxColumns is omitted, all the results are returned. MaxRows Optional. The maximum number of rows to be returned to the worksheet, starting at DestinationRef. If MaxRows specifies more rows than are available in the results, SQLRetrieve places data in the rows for which data is available and clears the additional rows. If MaxRows specifies fewer rows than are available in the results, SQLRetrieve places data in the selected rows but doesn’t discard the additional rows. You can retrieve extra rows by using SQLRetrieve again and setting FetchFirstLogical to False. If MaxRows is omitted, all the rows in the results are returned. ColNamesLogical Optional. True to have the column names be returned as the first row of results. False or omitted to have the column names not be returned. RowNumsLogical Optional. Used only when DestinationRef is included in the function call. True to have the first column in the result set contain row numbers. False or omitted to have the row numbers not be returned. You can also retrieve row numbers by binding column 0 (zero) with SQLBind. NamedRngLogical Optional. True to have each column of the results be declared as a named range on the worksheet. The name of each range is the resulting column name. The named range includes only the rows that are returned with SQLRetrieve. The default value is False.FetchFirstLogicall Optional. Allows you to request results from the beginning of the result set. If FetchFirstLogical is False, SQLRetrieve can be called repeatedly to return the next set of rows until all the result rows have been returned. When there are no more rows in the result set, SQLRequest returns 0 (zero). If you want to retrieve results from the beginning of the result set, set FetchFirstLogical to True. To retrieve additional rows from the result set, set FetchFirstLogical to False in subsequent calls. The default value is False. Return Value SQLRetrieve returns the number of rows in the result set. If SQLRetrieve is unable to retrieve the results on the specified data source or if there are no results pending, it returns Error 2042. If no data is found, SQLRetrieve returns 0 (zero). Remarks Before calling SQLRetrieve, you must do the following: 1. Establish a connection with a data source by using SQLOpen. 2. Use the connection ID returned in SQLOpen to send a query with SQLExecQuery. Example This example runs a query on the Northwind database. The result of the query, displayed on Sheet1, is a list of all products that are currently on order. databaseName = "NorthWind" queryString = _ "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString Set output = Worksheets("Sheet1").Range("A1") SQLRetrieve chan, output, , , True SQLClose chan   SQLRetrieveToFile Function It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLRetrieveToFile and the other ODBC functions in the Xlodbc.xla add-in. SQLRetrieveToFile retrieves all the results from a previously executed query and places them in a file. To use this function, you must have established a connection with a data source by using SQLOpen, executed a query by using SQLExecQuery, and have the results of the query pending. This function is contained in the Xlodbc.xla add-in. Before you use the function, you must establish a reference to the add-in by using the References command (in the Tools menu) in the Visual Basic Editor. SQLRetrieveToFile(ConnectionNum, Destination, ColNamesLogical, ColumnDelimiter) ConnectionNum Required. The unique connection ID returned by SQLOpen and for which you have pending query results that were generated by SQLExecQuery. If ConnectionNum isn’t valid, SQLExecQuery returns Error 2015.Destination Required. A string that specifies the name and path of the file where you want to place the results. If the file exists, its contents are replaced with the query results. If the file doesn’t exist, SQLRetrieveToFile creates and opens the file and fills it with the results. The format of the data in the file is compatible with the Microsoft Excel .csv (comma-separated value) file format. Columns are separated by the character specified by ColumnDelimiter, and the individual rows are separated by a carriage return. If the file specified by Destination cannot be opened, SQLRetrieveToFile returns Error 2042. ColNamesLogical Optional. True to have the column names be returned as the first row of data. False or omitted to have the column names not be returned. ColumnDelimiter Optional. A string that specifies the character used to separate the elements in each row. For example, use "," to specify a comma delimiter, or use ";" to specify a semicolon delimiter. If you omit ColumnDelimiter, the list separator character is used. Return Value If successful, SQLRetrieveToFile returns the query results, writes them to a file, and then returns the number of rows that were written to the file. If SQLRetrieveToFile is unable to retrieve the results, it returns Error 2042 and doesn’t write the file. If there are no pending results on the connection, SQLRetrieveToFile returns Error 2042. Remarks Before calling SQLRetrieveToFile, you must do the following: 1. Establish a connection with a data source by using SQLOpen. 2. Use the connection ID returned by SQLOpen to send a query with SQLExecQuery. Example This example runs a query on the Northwind database. The result of the query, which is a list of all products that are currently on order, is written as the delimited text file Output.txt in the current folder. databaseName = "Northwind" queryString = _ "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" chan = SQLOpen("DSN=" & databaseName) SQLExecQuery chan, queryString SQLRetrieveToFile chan, "OUTPUT.TXT", True SQLClose chan         How to: Create a Workbook To create a workbook in Visual Basic, use the Add method. The following procedure creates a workbook. Microsoft Excel automatically names the workbook BookN, where N is the next available number. The new workbook becomes the active workbook. Sub AddOne() Workbooks.Add End Sub A better way to create a workbook is to assign it to an object variable. In the following example, the Workbook object returned by the Add method is assigned to an object variable, newBook. Next, several properties of newBook are set. You can easily control the new workbook by using the object variable. Sub AddNew() Set NewBook = Workbooks.Add With NewBook .Title = "All Sales" .Subject = "Sales" .SaveAs Filename:="Allsales.xls" End With End Sub   How to: Refer to Sheets by Index Number An index number is a sequential number assigned to a sheet, based on the position of its sheet tab (counting from the left) among sheets of the same type. The following procedure uses the Worksheets property to activate worksheet one in the active workbook. Sub FirstOne() Worksheets(1).Activate End Sub If you want to work with all types of sheets (worksheets, charts, modules, and dialog sheets), use the Sheets property. The following procedure activates sheet four in the workbook. Sub FourthOne() Sheets(4).Activate End Sub     How to: Refer to Sheets by Name You can identify sheets by name using the Worksheets and Charts properties. The following statements activate various sheets in the active workbook. Worksheets("Sheet1").Activate Charts("Chart1").Activate DialogSheets("Dialog1").Activate You can use the Sheets property to return a worksheet, chart, module, or dialog sheet; the Sheets collection contains all of these. The following example activates the sheet named "Chart1" in the active workbook. Sub ActivateChart() Sheets("Chart1").Activate End Sub   Opening a Workbook When you open a workbook using the Open method, it becomes a member of the Workbooks collection. The following procedure opens a workbook named MyBook.xls located in the folder named MyFolder on drive C. Sub OpenUp() Workbooks.Open("C:\MyFolder\MyBook.xls") End Sub   Saving Documents as Web Pages In Microsoft Excel, you can save a workbook, worksheet, chart, range, query table, PivotTable report, print area, or AutoFilter range to a Web page. You can also edit HTML files directly in Excel. Saving a Document as a Web Page Saving a document as a Web page is the process of creating and saving an HTML file and any supporting files. To do this, use the SaveAs method, as shown in the following example, which saves the active workbook as C:\Reports\myfile.htm. ActiveWorkbook.SaveAs _ Filename:="C:\Reports\myfile.htm", _ FileFormat:=xlHTML Customizing the Web Page You can customize the appearance, content, browser support, editing support, graphics formats, screen resolution, file organization, and encoding of the HTML document by setting properties of the DefaultWebOptions object and the WebOptions object. The DefaultWebOptions object contains application-level properties. These settings are overridden by any workbook-level property settings that have the same names (these are contained in the WebOptions object). After setting the attributes, you can use the Publish method to save the workbook, worksheet, chart, range, query table, PivotTable report, print area, or AutoFilter range to a Web page. The following example sets various application-level properties and then sets the AllowPNG property of the active workbook, overriding the application-level default setting. Finally, the example saves the range as "C:\Reports\1998_Q1.htm." With Application.DefaultWebOptions .RelyonVML = True .AllowPNG = True .PixelsPerInch = 96 End With With ActiveWorkbook .WebOptions.AllowPNG = False With .PublishObjects(1) .FileName = "C:\Reports\1998_Q1.htm" .Publish End With End With You can also save the files directly to a Web server. The following example saves a range to a Web server, giving the Web page the URL address http://example.homepage.com/annualreport.htm. With ActiveWorkbook With .WebOptions .RelyonVML = True .PixelsPerInch = 96 End With With .PublishObjects(1) .FileName = _ "http://example.homepage.com/annualreport.htm" .Publish End With End With Opening an HTML Document in Microsoft Excel To edit an HTML document in Excel, first open the document by using the Open method. The following example opens the file "C:\Reports\1997_Q4.htm" for editing. Workbooks.Open Filename:="C:\Reports\1997_Q4.htm" After opening the file, you can customize the appearance, content, browser support, editing support, graphics formats, screen resolution, file organization, and encoding of the HTML document by setting properties of the DefaultWebOptions and WebOptions objects.   Formatting and VBA Codes for Headers and Footers The following special formatting and Visual Basic for Applications (VBA) codes can be included as a part of the header and footer properties (LeftHeader, CenterHeader, RightHeader, LeftFooter, CenterFooter, and RightFooter). Format code Description &L Left aligns the characters that follow. &C Centers the characters that follow. &R Right aligns the characters that follow. &E Turns double-underline printing on or off. &X Turns superscript printing on or off. &Y Turns subscript printing on or off. &B Turns bold printing on or off. &I Turns italic printing on or off. &U Turns underline printing on or off. &S Turns strikethrough printing on or off. &"fontname" Prints the characters that follow in the specified font. Be sure to include the double quotation marks. &nn Prints the characters that follow in the specified font size. Use a two-digit number to specify a size in points. &color Prints the characters in the specified color. User supplies a hexidecimal color value. VBA code Description &D Prints the current date. &T Prints the current time. &F Prints the name of the document. &A Prints the name of the workbook tab. &P Prints the page number. &P+number Prints the page number plus the specified number. &P-number Prints the page number minus the specified number. && Prints a single ampersand. &N Prints the total number of pages in the document. &Z Prints the file path. &G Inserts an image. Example The following code shows how formatting and VBA codes can be used to modify the header information and appearance. Sub Date_Time() ActiveSheet.PageSetup.CenterHeader = "&D &B&ITime:&I&B&T" End Sub     How to: Reference Cells and Ranges A common task when using Visual Basic is to specify a cell or range of cells and then do something with it, such as enter a formula or change the format. You can usually do this in one statement that identifies the range and also changes a property or applies a method. A Range object in Visual Basic can be either a single cell or a range of cells. The following topics show the most common ways to identify and work with Range objects.   How to: Refer to All the Cells on the Worksheet When you apply the Cells property to a worksheet without specifying an index number, the method returns a Range object that represents all the cells on the worksheet. The following Sub procedure clears the contents from all the cells on Sheet1 in the active workbook. Sub ClearSheet() Worksheets("Sheet1").Cells.ClearContents End Sub   How to: Refer to Cells and Ranges by Using A1 Notation You can refer to a cell or range of cells in the A1 reference style by using the Range property. The following subroutine changes the format of cells A1:D5 to bold. Sub FormatRange() Workbooks("Book1").Sheets("Sheet1").Range("A1:D5") _ .Font.Bold = True End Sub The following table illustrates some A1-style references using the Range property. Reference Meaning Range("A1") Cell A1 Range("A1:B5") Cells A1 through B5 Range("C5:D9,G9:H16") A multiple-area selection Range("A:A") Column A Range("1:1") Row 1 Range("A:C") Columns A through C Range("1:5") Rows 1 through 5 Range("1:1,3:3,8:8") Rows 1, 3, and 8 Range("A:A,C:C,F:F") Columns A, C, and F   How to: Refer to Cells by Using a Range Object If you set an object variable to a Range object, you can easily manipulate the range by using the variable name. The following procedure creates the object variable myRange and then assigns the variable to range A1:D5 on Sheet1 in the active workbook. Subsequent statements modify properties of the range by substituting the variable name for the Range object. Sub Random() Dim myRange As Range Set myRange = Worksheets("Sheet1").Range("A1:D5") myRange.Formula = "=RAND()" myRange.Font.Bold = True End Sub   How to: Refer to Cells by Using Index Numbers You can use the Cells property to refer to a single cell by using row and column index numbers. This property returns a Range object that represents a single cell. In the following example, Cells(6,1) returns cell A6 on Sheet1. The Value property is then set to 10. Sub EnterValue() Worksheets("Sheet1").Cells(6, 1).Value = 10 End Sub The Cells property works well for looping through a range of cells, because you can substitute variables for the index numbers, as shown in the following example. Sub CycleThrough() Dim Counter As Integer For Counter = 1 To 20 Worksheets("Sheet1").Cells(Counter, 3).Value = Counter Next Counter End Sub   How to: Refer to Cells by Using Shortcut Notation You can use either the A1 reference style or a named range within brackets as a shortcut for the Range property. You do not have to type the word "Range" or use quotation marks, as shown in the following examples. Sub ClearRange() Worksheets("Sheet1").[A1:B5].ClearContents End Sub Sub SetValue() [MyRange].Value = 30 End Sub   How to: Refer to Multiple Ranges Using the appropriate method, you can easily refer to multiple ranges. Use the Range and Union methods to refer to any group of ranges; use the Areas property to refer to the group of ranges selected on a worksheet. Using the Range Property You can refer to multiple ranges with the Range property by inserting commas between two or more references. The following example clears the contents of three ranges on Sheet1. Sub ClearRanges() Worksheets("Sheet1").Range("C5:D9,G9:H16,B14:D18"). _ ClearContents End Sub Named ranges make it easier to use the Range property to work with multiple ranges. The following example works when all three named ranges are on the same sheet. Sub ClearNamed() Range("MyRange, YourRange, HisRange").ClearContents End Sub Using the Union Method You can combine multiple ranges into one Range object by using the Union method. The following example creates a Range object called myMultipleRange, defines it as the ranges A1:B2 and C3:D4, and then formats the combined ranges as bold. Sub MultipleRange() Dim r1, r2, myMultipleRange As Range Set r1 = Sheets("Sheet1").Range("A1:B2") Set r2 = Sheets("Sheet1").Range("C3:D4") Set myMultipleRange = Union(r1, r2) myMultipleRange.Font.Bold = True End Sub Using the Areas Property You can use the Areas property to refer to the selected range or to the collection of ranges in a multiple-area selection. The following procedure counts the areas in the selection. If there is more than one area, a warning message is displayed. Sub FindMultiple() If Selection.Areas.Count > 1 Then MsgBox "Cannot do this to a multiple selection." End If End Sub   How to: Refer to Named Ranges Ranges are easier to identify by name than by A1 notation. To name a selected range, click the name box at the left end of the formula bar, type a name, and then press ENTER. Referring to a Named Range The following example refers to the range named "MyRange" in the workbook named "MyBook.xls." Sub FormatRange() Range("MyBook.xls!MyRange").Font.Italic = True End Sub The following example refers to the worksheet-specific range named "Sheet1!Sales" in the workbook named "Report.xls." Sub FormatSales() Range("[Report.xls]Sheet1!Sales").BorderAround Weight:=xlthin End Sub To select a named range, use the GoTo method, which activates the workbook and the worksheet and then selects the range. Sub ClearRange() Application.Goto Reference:="MyBook.xls!MyRange" Selection.ClearContents End Sub The following example shows how the same procedure would be written for the active workbook. Sub ClearRange() Application.Goto Reference:="MyRange" Selection.ClearContents End Sub Looping Through Cells in a Named Range The following example loops through each cell in a named range by using a For Each...Next loop. If the value of any cell in the range exceeds the value of limit, the cell color is changed to yellow. Sub ApplyColor() Const Limit As Integer = 25 For Each c In Range("MyRange") If c.Value > Limit Then c.Interior.ColorIndex = 27 End If Next c End Sub   How to: Refer to Rows and Columns Use the Rows property or the Columns property to work with entire rows or columns. These properties return a Range object that represents a range of cells. In the following example, Rows(1) returns row one on Sheet1. The Bold property of the Font object for the range is then set to True. Sub RowBold() Worksheets("Sheet1").Rows(1).Font.Bold = True End Sub The following table illustrates some row and column references using the Rows and Columns properties. Reference Meaning Rows(1) Row one Rows All the rows on the worksheet Columns(1) Column one Columns("A") Column one Columns All the columns on the worksheet To work with several rows or columns at the same time, create an object variable and use the Union method, combining multiple calls to the Rows or Columns property. The following example changes the format of rows one, three, and five on worksheet one in the active workbook to bold. Sub SeveralRows() Worksheets("Sheet1").Activate Dim myUnion As Range Set myUnion = Union(Rows(1), Rows(3), Rows(5)) myUnion.Font.Bold = True End Sub   Looping Through a Range of Cells When using Visual Basic, you often need to run the same block of statements on each cell in a range of cells. To do this, you combine a looping statement and one or more methods to identify each cell, one at a time, and run the operation. One way to loop through a range is to use the For...Next loop with the Cells property. Using the Cells property, you can substitute the loop counter (or other variables or expressions) for the cell index numbers. In the following example, the variable counter is substituted for the row index. The procedure loops through the range C1:C20, setting to 0 (zero) any number whose absolute value is less than 0.01. Sub RoundToZero1() For Counter = 1 To 20 Set curCell = Worksheets("Sheet1").Cells(Counter, 3) If Abs(curCell.Value) < 0.01 Then curCell.Value = 0 Next Counter End Sub Another easy way to loop through a range is to use a For Each...Next loop with the collection of cells specified in the Range property. Visual Basic automatically sets an object variable for the next cell each time the loop runs. The following procedure loops through the range A1:D10, setting to 0 (zero) any number whose absolute value is less than 0.01. Sub RoundToZero2() For Each c In Worksheets("Sheet1").Range("A1:D10").Cells If Abs(c.Value) < 0.01 Then c.Value = 0 Next End Sub If you do not know the boundaries of the range you want to loop through, you can use the CurrentRegion property to return the range that surrounds the active cell. For example, the following procedure, when run from a worksheet, loops through the range that surrounds the active cell, setting to 0 (zero) any number whose absolute value is less than 0.01. Sub RoundToZero3() For Each c In ActiveCell.CurrentRegion.Cells If Abs(c.Value) < 0.01 Then c.Value = 0 Next End Sub   Selecting and Activating Cells When you work with Microsoft Excel, you usually select a cell or cells and then perform an action, such as formatting the cells or entering values in them. In Visual Basic, it is usually not necessary to select cells before modifying them. For example, if you want to enter a formula in cell D6 using Visual Basic, you do not need to select the range D6. You just need to return the Range object and then set the Formula property to the formula you want, as shown in the following example. Sub EnterFormula() Worksheets("Sheet1").Range("D6").Formula = "=SUM(D2:D5)" End Sub For examples of using other methods to control cells without selecting them, see How to: Reference Cells and Ranges. Using the Select Method and the Selection Property The Select method activates sheets and objects on sheets; the Selection property returns an object that represents the current selection on the active sheet in the active workbook. Before you can use the Selection property successfully, you must activate a workbook, activate or select a sheet, and then select a range (or other object) using the Select method. The macro recorder will often create a macro that uses the Select method and the Selection property. The following Sub procedure was created using the macro recorder, and it illustrates how Select and Selection work together. Sub Macro1() Sheets("Sheet1").Select Range("A1").Select ActiveCell.FormulaR1C1 = "Name" Range("B1").Select ActiveCell.FormulaR1C1 = "Address" Range("A1:B1").Select Selection.Font.Bold = True End Sub The following example accomplishes the same task without activating or selecting the worksheet or cells. Sub Labels() With Worksheets("Sheet1") .Range("A1") = "Name" .Range("B1") = "Address" .Range("A1:B1").Font.Bold = True End With End Sub Selecting Cells on the Active Worksheet If you use the Select method to select cells, be aware that Select works only on the active worksheet. If you run your Sub procedure from the module, the Select method will fail unless your procedure activates the worksheet before using the Select method on a range of cells. For example, the following procedure copies a row from Sheet1 to Sheet2 in the active workbook. Sub CopyRow() Worksheets("Sheet1").Rows(1).Copy Worksheets("Sheet2").Select Worksheets("Sheet2").Rows(1).Select Worksheets("Sheet2").Paste End Sub Activating a Cell Within a Selection You can use the Activate method to activate a cell within a selection. There can be only one active cell, even when a range of cells is selected. The following procedure selects a range and then activates a cell within the range without changing the selection. Sub MakeActive() Worksheets("Sheet1").Activate Range("A1:D4").Select Range("B2").Activate End Sub   Working with 3-D Ranges If you are working with the same range on more than one sheet, use the Array function to specify two or more sheets to select. The following example formats the border of a 3-D range of cells. Sub FormatSheets() Sheets(Array("Sheet2", "Sheet3", "Sheet5")).Select Range("A1:H1").Select Selection.Borders(xlBottom).LineStyle = xlDouble End Sub The following example applies the FillAcrossSheets method to transfer the formats and any data from the range on Sheet2 to the corresponding ranges on all the worksheets in the active workbook. Sub FillAll() Worksheets("Sheet2").Range("A1:H1") _ .Borders(xlBottom).LineStyle = xlDouble Worksheets.FillAcrossSheets (Worksheets("Sheet2") _ .Range("A1:H1")) End Sub   Working with the Active Cell The ActiveCell property returns a Range object that represents the cell that is active. You can apply any of the properties or methods of a Range object to the active cell, as in the following example. Sub SetValue() Worksheets("Sheet1").Activate ActiveCell.Value = 35 End Sub Note You can work with the active cell only when the worksheet that it is on is the active sheet. Moving the Active Cell You can use the Activate method to designate which cell is the active cell. For example, the following procedure makes B5 the active cell and then formats it as bold. Sub SetActive() Worksheets("Sheet1").Activate Worksheets("Sheet1").Range("B5").Activate ActiveCell.Font.Bold = True End Sub   Cell Error Values You can insert a cell error value into a cell or test the value of a cell for an error value by using the CVErr function. The cell error values can be one of the following XlCVError constants. Constant Error number Cell error value xlErrDiv0 2007 #DIV/0! xlErrNA 2042 #N/A xlErrName 2029 #NAME? xlErrNull 2000 #NULL! xlErrNum 2036 #NUM! xlErrRef 2023 #REF! xlErrValue 2015 #VALUE! Example This example inserts the seven cell error values into cells A1:A7 on Sheet1. myArray = Array(xlErrDiv0, xlErrNA, xlErrName, xlErrNull, _ xlErrNum, xlErrRef, xlErrValue) For i = 1 To 7 Worksheets("Sheet1").Cells(i, 1).Value = CVErr(myArray(i - 1)) Next i This example displays a message if the active cell on Sheet1 contains a cell error value. You can use this example as a framework for a cell-error-value error handler. Worksheets("Sheet1").Activate If IsError(ActiveCell.Value) Then errval = ActiveCell.Value Select Case errval Case CVErr(xlErrDiv0) MsgBox "#DIV/0! error" Case CVErr(xlErrNA) MsgBox "#N/A error" Case CVErr(xlErrName) MsgBox "#NAME? error" Case CVErr(xlErrNull) MsgBox "#NULL! error" Case CVErr(xlErrNum) MsgBox "#NUM! error" Case CVErr(xlErrRef) MsgBox "#REF! error" Case CVErr(xlErrValue) MsgBox "#VALUE! error" Case Else MsgBox "This should never happen!!" End Select End If How to: Add Controls to a Document To add controls to a document, display the Control Toolbox, click the control you want to add, and then click on the document. Drag an adjustment handle of the control until the control's outline is the size and shape you want. How to: Add Controls to a User Form To add controls to a user form, find the control you want to add in the Toolbox, drag the control onto the form, and then drag an adjustment handle on the control until the control's outline is the size and shape you want. How to: Create a Custom Dialog Box Use the following procedure to create a custom dialog box: 1. Create a User Form On the Insert menu in the Visual Basic Editor, click UserForm. 2. Add Controls to a User Form Find the control you want to add in the Toolbox and drag the control onto the form. 3. Setting Control Properties Right-click a control in design mode and click Properties to display the Properties window. 4. Initializing Control Properties You can initialize controls in a procedure before you show a form, or you can add code to the Initialize event of the form. 5. Control and Dialog Box Events All controls have a predefined set of events. For example, a command button has a Click event that occurs when the user clicks the command button. You can write event procedures that run when the events occur. 6. Displaying a Custom Dialog Box Use the Show method to display a UserForm. 7. Using Control Values While Code Is Running Some properties can be set at run time. Changes made to the dialog box by the user are lost when the dialog box is closed.   How to: Create a User Form To create a custom dialog box, you must create a UserForm. To create a UserForm, click UserForm on the Insert menu in the Visual Basic Editor. Use the Properties window to change the name, behavior, and appearance of the form. For example, to change the caption on a form, set the Caption property.   ActiveX Controls For more information about a specific control, select an object from the following list. For information about events, select a control and click Events at the top of the topic. CheckBox ComboBox CommandButton Image Label ListBox OptionButton ScrollBar SpinButton TextBox ToggleButton   Using ActiveX Controls on Sheets This topic covers specific information about using ActiveX controls on worksheets and chart sheets. For general information on adding and working with controls, see Using ActiveX Controls on a Document and Creating a Custom Dialog Box. Keep the following points in mind when you are working with controls on sheets: In addition to the standard properties available for ActiveX controls, the following properties can be used with ActiveX controls in Microsoft Excel: BottomRightCell, LinkedCell, ListFillRange, Placement, PrintObject, TopLeftCell, and ZOrder. These properties can be set and returned using the ActiveX control name. The following example scrolls the workbook window so CommandButton1 is in the upper-left corner. Set t = Sheet1.CommandButton1.TopLeftCell With ActiveWindow .ScrollRow = t.Row .ScrollColumn = t.Column End With Some Microsoft Excel Visual Basic methods and properties are disabled when an ActiveX control is activated. For example, the Sort method cannot be used when a control is active, so the following code fails in a button click event procedure (because the control is still active after the user clicks it). Private Sub CommandButton1.Click Range("a1:a10").Sort Key1:=Range("a1") End Sub You can work around this problem by activating some other element on the sheet before you use the property or method that failed. For example, the following code sorts the range: Private Sub CommandButton1.Click Range("a1").Activate Range("a1:a10").Sort Key1:=Range("a1") CommandButton1.Activate End Sub Controls on a Microsoft Excel workbook embedded in a document in another application will not work if the user double-clicks the workbook to edit it. The controls will work if the user right-clicks the workbook and selects the Open command from the shortcut menu. When a Microsoft Excel workbook is saved using the Microsoft Excel 5.0/95 Workbook file format, ActiveX control information is lost. The Me keyword in an event procedure for an ActiveX control on a sheet refers to the sheet, not to the control. Adding Controls with Visual Basic In Microsoft Excel, ActiveX controls are represented by OLEObject objects in the OLEObjects collection (all OLEObject objects are also in the Shapes collection). To programmatically add an ActiveX control to a sheet, use the Add method of the OLEObjects collection. The following example adds a command button to worksheet 1. Worksheets(1).OLEObjects.Add "Forms.CommandButton.1", _ Left:=10, Top:=10, Height:=20, Width:=100 Using Control Properties with Visual Basic Most often, your Visual Basic code will refer to ActiveX controls by name. The following example changes the caption on the control named "CommandButton1." Sheet1.CommandButton1.Caption = "Run" Note that when you use a control name outside the class module for the sheet containing the control, you must qualify the control name with the sheet name. To change the control name you use in Visual Basic code, select the control and set the (Name) property in the Properties window. Because ActiveX controls are also represented by OLEObject objects in the OLEObjects collection, you can set control properties using the objects in the collection. The following example sets the left position of the control named "CommandButton1." Worksheets(1).OLEObjects("CommandButton1").Left = 10 Control properties that are not shown as properties of the OLEObject object can be set by returning the actual control object using the Object property. The following example sets the caption for CommandButton1. Worksheets(1).OLEObjects("CommandButton1"). _ Object.Caption = "run me" Because all OLE objects are also members of the Shapes collection, you can use the collection to set properties for several controls. The following example aligns the left edge of all controls on worksheet 1. For Each s In Worksheets(1).Shapes If s.Type = msoOLEControlObject Then s.Left = 10 Next Using Control Names with the Shapes and OLEObjects Collections An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other is not automatically changed to match. You use the code name of a control in the names of its event procedures. However, when you return a control from the Shapes or OLEObjects collection for a sheet, you must use the shape name, not the code name, to refer to the control by name. For example, assume that you add a check box to a sheet and that both the default shape name and the default code name are CheckBox1. If you then change the control code name by typing chkFinished next to (Name) in the Properties window, you must use chkFinished in event procedure names, but you still have to use CheckBox1 to return the control from the Shapes or OLEObject collection, as shown in the following example. Private Sub chkFinished_Click() ActiveSheet.OLEObjects("CheckBox1").Object.Value = 1 End Sub   Built-In Dialog Box Argument Lists Dialog box constant Argument list(s) xlDialogActivate window_text, pane_num xlDialogActiveCellFont font, font_style, size, strikethrough, superscript, subscript, outline, shadow, underline, color, normal, background, start_char, char_count xlDialogAddChartAutoformat name_text, desc_text xlDialogAddinManager operation_num, addinname_text, copy_logical xlDialogAlignment horiz_align, wrap, vert_align, orientation, add_indent xlDialogApplyNames name_array, ignore, use_rowcol, omit_col, omit_row, order_num, append_last xlDialogApplyStyle style_text xlDialogAppMove x_num, y_num xlDialogAppSize x_num, y_num xlDialogArrangeAll arrange_num, active_doc, sync_horiz, sync_vert xlDialogAssignToObject macro_ref xlDialogAssignToTool bar_id, position, macro_ref xlDialogAttachText attach_to_num, series_num, point_num xlDialogAttachToolbars xlDialogAutoCorrect correct_initial_caps, capitalize_days xlDialogAxes x_primary, y_primary, x_secondary, y_secondary xlDialogAxes x_primary, y_primary, z_primary xlDialogBorder outline, left, right, top, bottom, shade, outline_color, left_color, right_color, top_color, bottom_color xlDialogCalculation type_num, iter, max_num, max_change, update, precision, date_1904, calc_save, save_values, alt_exp, alt_form xlDialogCellProtection locked, hidden xlDialogChangeLink old_text, new_text, type_of_link xlDialogChartAddData ref, rowcol, titles, categories, replace, series xlDialogChartLocation xlDialogChartOptionsDataLabels xlDialogChartOptionsDataTable xlDialogChartSourceData xlDialogChartTrend type, ord_per, forecast, backcast, intercept, equation, r_squared, name xlDialogChartType xlDialogChartWizard long, ref, gallery_num, type_num, plot_by, categories, ser_titles, legend, title, x_title, y_title, z_title, number_cats, number_titles xlDialogCheckboxProperties value, link, accel_text, accel2_text, 3d_shading xlDialogClear type_num xlDialogColorPalette file_text xlDialogColumnWidth width_num, reference, standard, type_num, standard_num xlDialogCombination type_num xlDialogConditionalFormatting xlDialogConsolidate source_refs, function_num, top_row, left_col, create_links xlDialogCopyChart size_num xlDialogCopyPicture appearance_num, size_num, type_num xlDialogCreateNames top, left, bottom, right xlDialogCreatePublisher file_text, appearance, size, formats xlDialogCustomizeToolbar category xlDialogCustomViews xlDialogDataDelete xlDialogDataLabel show_option, auto_text, show_key xlDialogDataSeries rowcol, type_num, date_num, step_value, stop_value, trend xlDialogDataValidation xlDialogDefineName name_text, refers_to, macro_type, shortcut_text, hidden, category, local xlDialogDefineStyle style_text, number, font, alignment, border, pattern, protection xlDialogDefineStyle style_text, attribute_num, additional_def_args, ... xlDialogDeleteFormat format_text xlDialogDeleteName name_text xlDialogDemote row_col xlDialogDisplay formulas, gridlines, headings, zeros, color_num, reserved, outline, page_breaks, object_num xlDialogDisplay cell, formula, value, format, protection, names, precedents, dependents, note xlDialogEditboxProperties validation_num, multiline_logical, vscroll_logical, password_logical xlDialogEditColor color_num, red_value, green_value, blue_value xlDialogEditDelete shift_num xlDialogEditionOptions edition_type, edition_name, reference, option, appearance, size, formats xlDialogEditSeries series_num, name_ref, x_ref, y_ref, z_ref, plot_order xlDialogErrorbarX include, type, amount, minus xlDialogErrorbarY include, type, amount, minus xlDialogExternalDataProperties xlDialogExtract unique xlDialogFileDelete file_text xlDialogFileSharing xlDialogFillGroup type_num xlDialogFillWorkgroup type_num xlDialogFilter xlDialogFilterAdvanced operation, list_ref, criteria_ref, copy_ref, unique xlDialogFindFile xlDialogFont name_text, size_num xlDialogFontProperties font, font_style, size, strikethrough, superscript, subscript, outline, shadow, underline, color, normal, background, start_char, char_count xlDialogFormatAuto format_num, number, font, alignment, border, pattern, width xlDialogFormatChart layer_num, view, overlap, angle, gap_width, gap_depth, chart_depth, doughnut_size, axis_num, drop, hilo, up_down, series_line, labels, vary xlDialogFormatCharttype apply_to, group_num, dimension, type_num xlDialogFormatFont color, backgd, apply, name_text, size_num, bold, italic, underline, strike, outline, shadow, object_id, start_num, char_num xlDialogFormatFont name_text, size_num, bold, italic, underline, strike, color, outline, shadow xlDialogFormatFont name_text, size_num, bold, italic, underline, strike, color, outline, shadow, object_id_text, start_num, char_num xlDialogFormatLegend position_num xlDialogFormatMain type_num, view, overlap, gap_width, vary, drop, hilo, angle, gap_depth, chart_depth, up_down, series_line, labels, doughnut_size xlDialogFormatMove x_offset, y_offset, reference xlDialogFormatMove x_pos, y_pos xlDialogFormatMove explosion_num xlDialogFormatNumber format_text xlDialogFormatOverlay type_num, view, overlap, gap_width, vary, drop, hilo, angle, series_dist, series_num, up_down, series_line, labels, doughnut_size xlDialogFormatSize width, height xlDialogFormatSize x_off, y_off, reference xlDialogFormatText x_align, y_align, orient_num, auto_text, auto_size, show_key, show_value, add_indent xlDialogFormulaFind text, in_num, at_num, by_num, dir_num, match_case, match_byte xlDialogFormulaGoto reference, corner xlDialogFormulaReplace find_text, replace_text, look_at, look_by, active_cell, match_case, match_byte xlDialogFunctionWizard xlDialogGallery3dArea type_num xlDialogGallery3dBar type_num xlDialogGallery3dColumn type_num xlDialogGallery3dLine type_num xlDialogGallery3dPie type_num xlDialogGallery3dSurface type_num xlDialogGalleryArea type_num, delete_overlay xlDialogGalleryBar type_num, delete_overlay xlDialogGalleryColumn type_num, delete_overlay xlDialogGalleryCustom name_text xlDialogGalleryDoughnut type_num, delete_overlay xlDialogGalleryLine type_num, delete_overlay xlDialogGalleryPie type_num, delete_overlay xlDialogGalleryRadar type_num, delete_overlay xlDialogGalleryScatter type_num, delete_overlay xlDialogGoalSeek target_cell, target_value, variable_cell xlDialogGridlines x_major, x_minor, y_major, y_minor, z_major, z_minor, 2D_effect xlDialogImportTextFile xlDialogInsert shift_num xlDialogInsertHyperlink xlDialogInsertNameLabel xlDialogInsertObject object_class, file_name, link_logical, display_icon_logical, icon_file, icon_number, icon_label xlDialogInsertPicture file_name, filter_number xlDialogInsertTitle chart, y_primary, x_primary, y_secondary, x_secondary xlDialogLabelProperties accel_text, accel2_text, 3d_shading xlDialogListboxProperties range, link, drop_size, multi_select, 3d_shading xlDialogMacroOptions macro_name, description, menu_on, menu_text, shortcut_on, shortcut_key, function_category, status_bar_text, help_id, help_file xlDialogMailEditMailer to_recipients, cc_recipients, bcc_recipients, subject, enclosures, which_address xlDialogMailLogon name_text, password_text, download_logical xlDialogMailNextLetter xlDialogMainChart type_num, stack, 100, vary, overlap, drop, hilo, overlap%, cluster, angle xlDialogMainChartType type_num xlDialogMenuEditor xlDialogMove x_pos, y_pos, window_text xlDialogNew type_num, xy_series, add_logical xlDialogNewWebQuery xlDialogNote add_text, cell_ref, start_char, num_chars xlDialogObjectProperties placement_type, print_object xlDialogObjectProtection locked, lock_text xlDialogOpen file_text, update_links, read_only, format, prot_pwd, write_res_pwd, ignore_rorec, file_origin, custom_delimit, add_logical, editable, file_access, notify_logical, converter xlDialogOpenLinks document_text1, document_text2, ..., read_only, type_of_link xlDialogOpenMail subject, comments xlDialogOpenText file_name, file_origin, start_row, file_type, text_qualifier, consecutive_delim, tab, semicolon, comma, space, other, other_char, field_info xlDialogOptionsCalculation type_num, iter, max_num, max_change, update, precision, date_1904, calc_save, save_values xlDialogOptionsChart display_blanks, plot_visible, size_with_window xlDialogOptionsEdit incell_edit, drag_drop, alert, entermove, fixed, decimals, copy_objects, update_links, move_direction, autocomplete, animations xlDialogOptionsGeneral R1C1_mode, dde_on, sum_info, tips, recent_files, old_menus, user_info, font_name, font_size, default_location, alternate_location, sheet_num, enable_under xlDialogOptionsListsAdd string_array xlDialogOptionsListsAdd import_ref, by_row xlDialogOptionsME def_rtl_sheet, crsr_mvmt, show_ctrl_char, gui_lang xlDialogOptionsTransition menu_key, menu_key_action, nav_keys, trans_eval, trans_entry xlDialogOptionsView formula, status, notes, show_info, object_num, page_breaks, formulas, gridlines, color_num, headers, outline, zeros, hor_scroll, vert_scroll, sheet_tabs xlDialogOutline auto_styles, row_dir, col_dir, create_apply xlDialogOverlay type_num, stack, 100, vary, overlap, drop, hilo, overlap%, cluster, angle, series_num, auto xlDialogOverlayChartType type_num xlDialogPageSetup head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, orient, paper_size, scale, pg_num, pg_order, bw_cells, quality, head_margin, foot_margin, notes, draft xlDialogPageSetup head, foot, left, right, top, bot, size, h_cntr, v_cntr, orient, paper_size, scale, pg_num, bw_chart, quality, head_margin, foot_margin, draft xlDialogPageSetup head, foot, left, right, top, bot, orient, paper_size, scale, quality, head_margin, foot_margin, pg_num xlDialogParse parse_text, destination_ref xlDialogPasteNames xlDialogPasteSpecial paste_num, operation_num, skip_blanks, transpose xlDialogPasteSpecial rowcol, titles, categories, replace, series xlDialogPasteSpecial paste_num xlDialogPasteSpecial format_text, pastelink_logical, display_icon_logical, icon_file, icon_number, icon_label xlDialogPatterns apattern, afore, aback, newui xlDialogPatterns lauto, lstyle, lcolor, lwt, hwidth, hlength, htype xlDialogPatterns bauto, bstyle, bcolor, bwt, shadow, aauto, apattern, afore, aback, rounded, newui xlDialogPatterns bauto, bstyle, bcolor, bwt, shadow, aauto, apattern, afore, aback, invert, apply, newfill xlDialogPatterns lauto, lstyle, lcolor, lwt, tmajor, tminor, tlabel xlDialogPatterns lauto, lstyle, lcolor, lwt, apply, smooth xlDialogPatterns lauto, lstyle, lcolor, lwt, mauto, mstyle, mfore, mback, apply, smooth xlDialogPatterns type, picture_units, apply xlDialogPhonetic xlDialogPivotCalculatedField xlDialogPivotCalculatedItem xlDialogPivotClientServerSet xlDialogPivotFieldGroup start, end, by, periods xlDialogPivotFieldProperties name, pivot_field_name, new_name, orientation, function, formats xlDialogPivotFieldUngroup xlDialogPivotShowPages name, page_field xlDialogPivotSolveOrder xlDialogPivotTableOptions xlDialogPivotTableWizard type, source, destination, name, row_grand, col_grand, save_data, apply_auto_format, auto_page, reserved xlDialogPlacement placement_type xlDialogPrint range_num, from, to, copies, draft, preview, print_what, color, feed, quality, y_resolution, selection, printer_text, print_to_file, collate xlDialogPrinterSetup printer_text xlDialogPrintPreview xlDialogPromote rowcol xlDialogProperties title, subject, author, keywords, comments xlDialogProtectDocument contents, windows, password, objects, scenarios xlDialogProtectSharing xlDialogPublishAsWebPage xlDialogPushbuttonProperties default_logical, cancel_logical, dismiss_logical, help_logical, accel_text, accel_text2 xlDialogReplaceFont font_num, name_text, size_num, bold, italic, underline, strike, color, outline, shadow xlDialogRoutingSlip recipients, subject, message, route_num, return_logical, status_logical xlDialogRowHeight height_num, reference, standard_height, type_num xlDialogRun reference, step xlDialogSaveAs document_text, type_num, prot_pwd, backup, write_res_pwd, read_only_rec xlDialogSaveCopyAs document_text xlDialogSaveNewObject xlDialogSaveWorkbook document_text, type_num, prot_pwd, backup, write_res_pwd, read_only_rec xlDialogSaveWorkspace name_text xlDialogScale cross, cat_labels, cat_marks, between, max, reverse xlDialogScale min_num, max_num, major, minor, cross, logarithmic, reverse, max xlDialogScale cat_labels, cat_marks, reverse, between xlDialogScale series_labels, series_marks, reverse xlDialogScale min_num, max_num, major, minor, cross, logarithmic, reverse, min xlDialogScenarioAdd scen_name, value_array, changing_ref, scen_comment, locked, hidden xlDialogScenarioCells changing_ref xlDialogScenarioEdit scen_name, new_scenname, value_array, changing_ref, scen_comment, locked, hidden xlDialogScenarioMerge source_file xlDialogScenarioSummary result_ref, report_type xlDialogScrollbarProperties value, min, max, inc, page, link, 3d_shading xlDialogSelectSpecial type_num, value_type, levels xlDialogSendMail recipients, subject, return_receipt xlDialogSeriesAxes axis_num xlDialogSeriesOptions xlDialogSeriesOrder chart_num, old_series_num, new_series_num xlDialogSeriesShape xlDialogSeriesX x_ref xlDialogSeriesY name_ref, y_ref xlDialogSetBackgroundPicture xlDialogSetPrintTitles titles_for_cols_ref, titles_for_rows_ref xlDialogSetUpdateStatus link_text, status, type_of_link xlDialogShowDetail rowcol, rowcol_num, expand, show_field xlDialogShowToolbar bar_id, visible, dock, x_pos, y_pos, width, protect, tool_tips, large_buttons, color_buttons xlDialogSize width, height, window_text xlDialogSort orientation, key1, order1, key2, order2, key3, order3, header, custom, case xlDialogSort orientation, key1, order1, type, custom xlDialogSortSpecial sort_by, method, key1, order1, key2, order2, key3, order3, header, order, case xlDialogSplit col_split, row_split xlDialogStandardFont name_text, size_num, bold, italic, underline, strike, color, outline, shadow xlDialogStandardWidth standard_num xlDialogStyle bold, italic xlDialogSubscribeTo file_text, format_num xlDialogSubtotalCreate at_change_in, function_num, total, replace, pagebreaks, summary_below xlDialogSummaryInfo title, subject, author, keywords, comments xlDialogTable row_ref, column_ref xlDialogTabOrder xlDialogTextToColumns destination_ref, data_type, text_delim, consecutive_delim, tab, semicolon, comma, space, other, other_char, field_info xlDialogUnhide window_text xlDialogUpdateLink link_text, type_of_link xlDialogVbaInsertFile filename_text xlDialogVbaMakeAddIn xlDialogVbaProcedureDefinition xlDialogView3d elevation, perspective, rotation, axes, height%, autoscale xlDialogWebOptionsEncoding xlDialogWebOptionsFiles xlDialogWebOptionsFonts xlDialogWebOptionsGeneral xlDialogWebOptionsPictures xlDialogWindowMove x_pos, y_pos, window_text xlDialogWindowSize width, height, window_text xlDialogWorkbookAdd name_array, dest_book, position_num xlDialogWorkbookCopy name_array, dest_book, position_num xlDialogWorkbookInsert type_num xlDialogWorkbookMove name_array, dest_book, position_num xlDialogWorkbookName oldname_text, newname_text xlDialogWorkbookNew xlDialogWorkbookOptions sheet_name, bound_logical, new_name xlDialogWorkbookProtect structure, windows, password xlDialogWorkbookTabSplit ratio_num xlDialogWorkbookUnhide sheet_text xlDialogWorkgroup name_array xlDialogWorkspace fixed, decimals, r1c1, scroll, status, formula, menu_key, remote, entermove, underlines, tools, notes, nav_keys, menu_key_action, drag_drop, show_info xlDialogZoom magnification   Using Control Values While Code Is Running Some control properties can be set and returned while Visual Basic code is running. The following example sets the Text property of a text box to "Hello." TextBox1.Text = "Hello" The data entered on a form by a user is lost when the form is closed. If you return the values of controls on a form after the form has been unloaded, you get the initial values for the controls rather than the values the user entered. If you want to save the data entered on a form, you can save the information to module-level variables while the form is still running. The following example displays a form and saves the form data. ' Code in module to declare public variables. Public strRegion As String Public intSalesPersonID As Integer Public blnCancelled As Boolean