Updating cells outside of rage of calling function

Add-in Express™ Support Service
That's what is more important than anything else

Updating cells outside of rage of calling function
Accessing cells in worksheet 
Posts 1 - 10 of 13
First | Prev. | 1 2 | Next | Last 
steven price




Posts: 17
 Joined: 2010-11-04
Hi ,


We want to have a worksheet function which is called in a single cell but which can then write its results to a range of cells below.

for instance the function call is in cell A1 but it updates data in the range A2:b10.

We want to achieve this without the use of an array functions and we were thinking we should be able to access the other cells in the current sheet via the Excel.Application object.

Is this possible and do you have an example of this behaviour ?

thanks

Steve
Posted 22 Jun, 2011 19:07:52 Top
Andrei Smolin


Add-in Express team


Posts: 18827
 Joined: 2006-05-11
Hi Steven,

Please check http://www.add-in-express.com/docs/net-excel-udf-tips.php#modify-multiple-cells That trick is not available for an Excel Automation add-in. Sorry, we have no sample so far.

As to using Excel.Application, check the section "Using the Excel Object Model in an XLL" on the same page.


Andrei Smolin
Add-in Express Team Leader
Posted 23 Jun, 2011 01:06:16 Top
Steven Price


Guest


Andrei

Do you have a simnple exampel piece of code that demonstrates this for us , it wodul help a lot

Thanks

Steve
Posted 28 Jun, 2011 04:56:16 Top
Andrei Smolin


Add-in Express team


Posts: 18827
 Joined: 2006-05-11
Hi Steve, 

public static object MyFunction(object arg)
{
    AddinExpress.MSO.ADXExcelRef xlCell = new ADXExcelRef(0, 0, 0,0,"Sheet1", true);
    xlCell.SetValue("My value");
    return "OK";
}



Andrei Smolin
Add-in Express Team Leader
Posted 28 Jun, 2011 09:23:09 Top
Steven Price


Guest


Andrei

Ho I tried the above but althigh th first line works the second does not set the value of the cell value

Steve
Posted 28 Jun, 2011 10:50:46 Top
Andrei Smolin


Add-in Express team


Posts: 18827
 Joined: 2006-05-11
Hi Steve,

Do you have the latest Add-in Express? Also, check if you test this on a workbook that have a sheet named "Sheet1".


Andrei Smolin
Add-in Express Team Leader
Posted 28 Jun, 2011 10:56:43 Top
Steven Price


Guest


Andrei

We downloaded our version last week.

The workbook does have a Sheet1 (we removed it and got the expected error)

We can sucesfully use GetValue on the returned xlCell object but when we use SetValue it returnes false.

We are calling this from the function below which is in an ADX XLL module

Steve

public static object RBK_Report(string ResultsSet,DateTime ValueDate)
{
// test update of cell
AddinExpress.MSO.ADXExcelRef xlCell = new ADXExcelRef(0, 0, 0, 0, "Sheet1", true);
xlCell.SetValue("A Value");

string resultSetName = ResultsSet.ToUpper(CultureInfo.CurrentUICulture);
DateTime vdate = ValueDate.Date;
List<VWResultsPV> resPV = DBInterface.GetResults(resultSetName, vdate);
if (resPV.Count == 0)
return new object[] { "No result defined for resultSet \"" + resultSetName + "\" and value date \"" + vdate.ToShortDateString() + "\"." };
else
{
object[,] res = new object[resPV.Count + 1, 3];
res[0, 0] = "Description";
res[0, 1] = "Currency";
res[0, 2] = "Values";
for (int i = 0; i < resPV.Count; i++)
{
int index = i + 1;
res[index, 0] = resPV[i].OutputType;
res[index, 1] = resPV[i].ResultCcy;
res[index, 2] = resPV[i].PV;
}
return res;
}

}
Posted 28 Jun, 2011 11:15:28 Top
Andrei Smolin


Add-in Express team


Posts: 18827
 Joined: 2006-05-11
Do you call RBK_Report from a cell other than A1 on Sheet1?


Andrei Smolin
Add-in Express Team Leader
Posted 28 Jun, 2011 11:18:16 Top
Steven Price


Guest


Yes, we are trying to update cells that our outside of the range of the calling function. So for thsi example pretend that we call RBK_Report from cell B1 and want to update cell A1
Posted 01 Jul, 2011 10:21:36 Top
Andrei Smolin


Add-in Express team


Posts: 18827
 Joined: 2006-05-11
Hi Steven,

I tested this on Excel 2010 32-bit + Add-in Express 2010 for Office and .NET 6.4.3056.


Andrei Smolin
Add-in Express Team Leader
Posted 01 Jul, 2011 11:02:40 Top

Posts 1 - 10 of 13
First | Prev. | 1 2 | Next | Last