How to define variable name and value?

[Administrator]

I would like to know how I can define a variable name and give a value to it. For example define variable "TimeDial" and value of 3. Then use variable "TimeDial" in a formula such as Myformula = TimeDial * 3. I know I can use cell referece but I partucularly want to use variable name and value pair.

I also want to use my variable and value cross the different sheets. Is that possible? I saw the following example in your help file:
formula "= sheet1!A1 + sheet2!C3".
But I want to replace cell references with variables such as:
formula "= sheet1!TimeDial + sheet2!TapValue" which TimeDial and TapValue are my variable names.

Finally I like to know the maximum number of sheets per grid? In Excel the maximum number of sheets in workbook is 255.

[Administrator]

Are you trying to use Essential Calculate, or the formula support in our GridControl that is part of Essential Grid? The reason I ask is that it may be simpler to do this using Essential Grid. We ship a couple of samples showing how you can set up named ranges (which can be used as the variables that you described).
\Essential Studio\3.2.1.0\Windows\Grid.Windows\Samples\Quick Start\NamedRangesForFormulas
\Essential Studio\3.2.1.0\Windows\Grid.Windows\Samples\In Depth\RetirementSimulation

[Byron Tate]

>Are you trying to use Essential Calculate, or the formula support in our GridControl that is part of Essential Grid? The reason I ask is that it may be simpler to do this using Essential Grid. We ship a couple of samples showing how you can set up named ranges (which can be used as the variables that you described).
>\Essential Studio\3.2.1.0\Windows\Grid.Windows\Samples\Quick Start\NamedRangesForFormulas
>\Essential Studio\3.2.1.0\Windows\Grid.Windows\Samples\In Depth\RetirementSimulation
>
This question is related to what I want to do. I have read through the tutorials and docs for calculate
package. I have a setup with several GridDataBoundGrids in a TabBarSplitterControl, that will all be
implementing the ICalcDataGrid interface , just like the
example in folder GridDataBoundGridCalculator of the samples.

I am designing functioning for this multi-grid setup. I see that variables can be defined using QuickCalc,
which seems to be a wrapper around the calc engine. I want users to be able to define
variables sets like this:

A = 3 + GRIDA!B3 + sin(45)
B = [A] + GRIDB!C4

where GRIDA and GRIDB are the registered sheet names for those grids:

engine.RegisterGridAsSheet("GRIDA", this.gridDataBoundGrid1, sheetfamilyID);
engine.RegisterGridAsSheet("GRIDB", this.gridDataBoundGrid2, sheetfamilyID);

I want to end up with variable expressions that reference other variables as well as
grid cells, then be able add a cell function to display the result like this:

grid[nRow,nCol].Text = "= [B]";

Is this possible? Or is the functionality of QuickCalc not available from the grid world?

[Administrator]

The CalcEngine class supports NamedRanges similar to the NamedRanges found in the GridFormulaEngine used with the GridControl.

In the sample you mentioned, at the bottom of the Load event handler in DataGridWorkBookForm.cs, you can define a variable A to be the formula "A1 + A2" with code like this:

engine.AddNamedRange("A", "A1+A2");

Then you can run the sample, and in some cell (other than A1 or A2), you can type a formula like "=A" or "=2*A+1", and see the A replaced by the computation of A1+A2.

[Byron Tate]

Thanks Clay. This seems to be working. NameRanges are capable of a lot more than naming ranges!

[Byron Tate]

After getting the above examples working, I realized that the NameRange expressions all have specific rows referenced. The hlookup function is great for getting the value of another cell, but there is a problem with this:

If I create 2 function expressions, one of which is dependent on the the other, and store them as a NamedRange, I can only use it for whatever row number I have put as the 3rd argument to the hlookup function.

If I want to apply these 2 expressions to the same columns in the other 50,000 rows I have in my DataTable, I would have to have 50,000 separate expressions for each of the 2 expressions, one for each row.

My question: Is there a ''current row'' concept in the CalcEngine I don''t see? A function to return the current row would be great.

I have attached the GridDataBoundGridCalculator solution, with changes I have made. In SingleDataGridForm.cs, see the #region WHAT I WOULD LIKE TO BE ABLE TO DO for the section of code I would like to be able to do.

[Administrator]

Your upload did not make it. You can retry or send it to [e-mail] and mention this thread in the subject line.

There is support for a ''current row'' reference in our GridFormulaEngine (you use row 0, so "= A0 + B0" would always represent the sum of the first two cells, no matter what row it is in). But, currently, this support is not available in CalcEngine. Then main reason is that ICalcData has no concept of the current row. You could try to tweak the implementation of ICalcData that you are using to recognize row 0 as special, but you would still have to somehow know what row you are currently on.

[Byron Tate]

Thanks. Looks like using GridFormulaEngine is the way to go. I don''t need the generality of ICalcData interface since we will always use functioning in connection with a GridDataBoundGrid. I notice that all the features we need are available: NamedRanges, Adding functions, best of all, ''Current Cell'' support! I was getting nervous after telling my bosses that this functioning pkg could do all this.

[Hernando Gisinger]

Thanks. Looks like using GridFormulaEngine is the way to go. I don''t need the generality of ICalcData interface since we will always use functioning in connection with a GridDataBoundGrid. I notice that all the features we need are available: NamedRanges, Adding functions, best of all, ''Current Cell'' support! I was getting nervous after telling my bosses that this functioning pkg could do all this.

Thanks