RangeDef command

Syntax

The syntax of all 3 commands is the same. There are 3 forms of the RangeDef command:

1. Simple nominal account range, e.g. =AP("RangeDef,7010,701Z,a,-1,0,ab")

This form prints the account descriptions for accounts between 7010 to 701Z which have a balance in either the current year or last year.

2. Named range, e.g. =AP("RangeDef,[*TO],,a,-1,0,ab")

This form prints the account descriptions for accounts in the named range TO which have a balance in either the current or last year.

3. Complex nominal account range, e.g. =AP("RangeDef,[(7010~7090)],,a,-1,0,ab")

This form prints the account descriptions for accounts between 7010 to 7090 which have a balance in either the current year or last year and match the pattern 7010~7090.

It is also possible to use =^^RangeDef instead of =RangeDef which is an older syntax, but is not recommended.

Parameters

• 1, 2 Account range The first and second parameters specify the accounts range and whether subaccounts are included. This is different for the three forms above.
  • In the first form ,they give the starting and end code, 7010 and 701Z in the above example. Only main accounts are listed; subaccounts are ignored. If subaccounts are required, the third form should be used.
  • In the second form, the first parameter specifies the named range code. This must start "[*" and end "]". The second parameter is missing to list just main accounts, or "s" to specify that subaccounts are required, e.g.  =AP("RangeDef,[*TO],s,a,-1,0,ab")
  • In the third form, the first parameter specifies the nominal range. This must start "[(" and end ")]". The second parameter is missing to list just main accounts, or "s" to specify that subaccounts are required, e.g.  =AP("RangeDef,[(7010..701Z)],s,a,-1,0,ab")
• 3 Year code The third parameter is irrelevant for a RangeDef command but for a RangeCode command says which period’s balances are required, i.e. “a” for the current year, “b” for comparatives and so on.
• 4 Order The fourth parameter is usually set to -1 as shown above. This means that the accounts are listed in nominal code order. If you wish to list the accounts alphabetically according to their descriptions, set it to 0.
• 5 Zero balances The fifth parameter is usually set to 0 as shown above. This indicates that rows for which all the balances given in parameter 6 (Years to check) are zero should be not included. If you wish to see these all zero rows, then set it to -1.
• 6 Years to check The sixth parameter indicates which balances are going to be printed on the same line. (Each RangeDef or RangeCode command is processed in isolation. So it doesn't know which other commands are on the same line.)  “ab” above indicates that there are commands to print the current and comparative years. This is required so that the RangeDef command knows when to show the account description, i.e. it is shown if either the current or comparative year has a nonzero balance. It is also used by RangeCode commands in the same way so that they print a balance if either the current or comparative year has a nonzero balance, ensuring that descriptions and balances printed by the RangeDef and RangeCode commands all line up with each other. 
• 7 Debit/credit only The seventh parameter is "deb" to list only debits and "cred" to list only credits. ("Dr" and "Cr" work as well but are not recommended as they do not work on the Nz command).This can be useful if you want to show all your bank balances as Current Assets and your bank overdrafts separately as Current Liabilities.

Notes

• Generally the parameters on a RangeDef command (or a RangeDefCode command) match those on a RangeCode command. For instance you might have:
  • =AP("RangeDef,7010,701Z,a,-1,0,ab") in column B to list account descriptions
  • =AP("RangeCode,7010,701Z,a,-1,0,ab") in column C to list current year balances.
  • =AP("RangeCode,7010,701Z,b,-1,0,ab") in column D to list last year balances.

The only parameter that changes is the one that specifies whether to list current or last year balances. If the RangeDef command used different parameters from the RangeCode command, the account descriptions would not match up with the account balances.

• The Year code can also be set to % for the totals of the year's audit journals and $ for the current year balances before the audit journals. But these do not work properly because you can not also include a $ or % in the Years to Check parameter. So the values for the audit journals or balances before audit journals may not match the descriptions. The only workaround is to set the Zero balances parameter to include the zero rows as follows:
  • =AP("RangeDef,7010,701Z,a,-1,-1,a") in column B to list account descriptions
  • =AP("RangeCode,7010,701Z,$,-1,-1,a") in column C to list current year balances before audit journals.
  • =AP("RangeCode,7010,701Z,a,-1,-1,a") in column D to list current year balances.

and then apply a Data Regulate to suppress all the unwanted zero rows.