Tips

You may want to incorporate these suggestions when using Excel formulas within queries.

1010data macro code can include Excel formulas that create dynamic code dependent on variables and data entered on the input worksheet. Keep these points in mind as you define these formulas:

  • Define Excel names for input cells to be referenced in formulas that evaluate to dynamic macro code (code dependent on Excel formulas and other cells).
  • Use Excel’s TEXT() function to properly display the value of dates.
  • If a line of dynamic macro code will be used in more than one q-sheet, define this as a formula in a centralized location, such as a dedicated worksheet or the input worksheet. Define a name for each cell that contains such a formula.
  • Use the & operator to concatenate string (text) values.
  • Use 2 double quotes in a row ("") to tell Excel to ignore the special meaning of a single double quote (").

A number of these recommendations are demonstrated in the following example.

Example

  1. For a date range selection, define input cells named From and To.
  2. In a cell named DateSelector, enter a formula such as:
    ="<sel value=""between(date; "&TEXT(From,"yyyymmdd")&"; "&TEXT(To,"yyyymmdd")&")""/>"
  3. At the desired line within your macro code, enter:
    =DateSelector

Given dates From="1/1/2007" and To="2/1/2007", this formula will evaluate to:

<sel value="between(date; 20070101; 20070201)"/>
Note: It is advisable to first run your macro within the web interface to ensure desired results are obtained, then copy the macro code into your Excel workbook, replacing the lines of code that are to be dynamic with the proper formulas or names.