4D v17 R4 is on the way with a cool new 4D View Pro feature: named ranges and formulas. If you need to use formulas with references to cell(s) or constant(s), this feature can be a great help!
With these new commands, your formulas are more readable and easily accessible with 4D View Pro’s automatic completion.
You can now create two types of names by programming: one referring to a range of cells, and another referring to a formula.
named ranges
A named range is a human-readable label for a range of cell(s). You can easily apply a readable name with code to the cells you’re interested in with VP ADD RANGE NAME.
For example, in this document:
You can create a named range for each “sales” cell:
// name the B2 cell as Pears_Sales
VP ADD RANGE NAME (VP Cell ("ViewProArea";1;1);"Sales_Pears")
// name the B3 cell as Oranges_Sales
VP ADD RANGE NAME (VP Cell ("ViewProArea";1;2);"Sales_Oranges")
// name the B4 cell as Cherries_Sales
VP ADD RANGE NAME (VP Cell ("ViewProArea";1;3);"Sales_Cherries")
// name the B5 cell as Grapes_Sales
VP ADD RANGE NAME (VP Cell ("ViewProArea";1;4);"Sales_Grapes")
Then, you can use the named ranges in your formulas via automatic completion. They will appear automatically when you type the first letter of the name:
named formulas
You can use VP ADD FORMULA NAME to replace calculations with readable names in your spreadsheets:
// Create a Sales total
VP ADD FORMULA NAME("ViewProArea";"SUM($B$2:$B$5)";"Sales_Total")
The same VP ADD FORMULA NAME command can be used to create a constant and make the calculations in your spreadsheet easier:
// Create a constant
VP ADD FORMULA NAME("ViewProArea";"=1.1";"Tax")
The spreadsheet shows a simple, readable formula:
and more
As we mentioned earlier, these names can be quite useful and as a bonus, you can directly use these names in your code with the VP Name command:
VP SET BOOLEAN VALUE(VP Name("ViewProArea";"Sales_Grapes");285;"$#,###.00")
The two new commands allow you to manage your existing names:
- With the VP Get formula by name command, verify if a name already exists and get the formula assigned to it.
- With VP Get names command, get a list of all names created for a given scope.