Re: Converting A1:C1 into ExcelReference

[Govert van Drimmelen]

Hi Marmik,

You can use the xlfTextref function:

[ExcelFunction(IsMacroType=true)]
public static object GetValueFromAddress(string address)
{
ExcelReference xlRef =
(ExcelReference)XlCall.Excel(XlCall.xlfTextref, address, true);
return xlRef.GetValue();
}

The last 'true' indicates you are using A1-style references. If you
don't pass a sheet name, you'll have to pass at least a '!', so this
will work:
=GetValueFromAddress("!A1")
=GetValueFromAddress("Sheet2!A1:B3")

You need to be a little careful with this - there is still an
unexplained issue, discussed with workaround here -
https://groups.google.com/group/exceldna/browse_frm/thread/411099d80d801be8
- where Excel crashes in the xlfTextref call. BUt it doesn't seem like
this will affect you.

Remember that by doing this kind of dereferencing from strings you are
making it hard for Excel to keep its dependency tree right, so your
functions won't update at the right times etc. It would make much more
sense in your example to pass those ranges in directly, as in
=RDI(A1:B2, C1:D2)

That way the function will be called again by Excel if the value in A1
changes. This is the way Excel normally works, but of course there may
be times to take another approach.

Regards,
Govert


On Jul 10, 11:17 pm, Nikee <ocean.ind...@gmail.com> wrote:
> I am getting range as Text in my function; now I want to convert them into
> ExcelReference and read data from that range. How can i do this ?
>
> ExcelFunction Signature = RDI("FIELD=A1:B2, DURATION = C1:D2")
>
> Now inside ExcelFunction I want to read values from A1:B2 and C1:D2; these
> range can be anything; i have just put sample. All the values that are in
> A1:B2 and C1:D2 are strings only...
>
> Thanks
> Marmik

[Govert van Drimmelen]

Hi Marmik,

As a cautionary example - this macro will crash Excel because of an
ExcelReference that does not have a valid SheetId. If you replace the
string "R2C3" by "!R2C3", then everything is OK.

<DnaLibrary RuntimeVersion="v4.0" Name="Excel Crash Test"
Language="C#">
<![CDATA[
using System;
using ExcelDna.Integration;

public class MyAddIn
{
[ExcelCommand(MenuName = "Bug", MenuText = "Crash Excel")]
public static void AddWorksheet()
{
ExcelReference cell = (ExcelReference)XlCall.Excel(
XlCall.xlfTextref, "R2C3", false);

cell.SetValue(123.45);
}
}
]]>
</DnaLibrary>

-Govert

On Jul 10, 11:55 pm, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi Marmik,
>
> You can use the xlfTextref function:
>
>    [ExcelFunction(IsMacroType=true)]
>    public static object GetValueFromAddress(string address)
>    {
>         ExcelReference xlRef =
> (ExcelReference)XlCall.Excel(XlCall.xlfTextref, address, true);
>         return xlRef.GetValue();
>    }
>
> The last 'true' indicates you are using A1-style references. If you
> don't pass a sheet name, you'll have to pass at least a '!', so this
> will work:
>    =GetValueFromAddress("!A1")
>    =GetValueFromAddress("Sheet2!A1:B3")
>
> You need to be a little careful with this - there is still an

> unexplained issue, discussed with workaround here -https://groups.google.com/group/exceldna/browse_frm/thread/411099d80d...