Symbol indirection

[Farhan Ahmed]

Is it possible to define constant symbols in Excel that a user can use in place of not-easy-to-remember numbers and strings?

For example, suppose a UDF requires a user to enter 5087 as the item code; I want to define 'FOOBAR' as a symbol that if the user enters in the formula gets translated into 5087 before it reaches my function.

I know I can create a map of string -> integer values in my code and do the translation myself but I wanted to check if Excel provides such a functionality. Also, if I do it myself, the user will have to enter a string as a parameter, which I am hoping to avoid to not require the use of quotes.

Thanks,

Farhan

--

[Patrick O'Beirne]

Just define a name FOOBAR=5087

> --
> You received this message because you are subscribed to the Google
> Groups "Excel-DNA" group.
> To post to this group, send email to exce...@googlegroups.com.
> To unsubscribe from this group, send email to
> exceldna+u...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/exceldna?hl=en.

[Naju Mancheril]

Following up on Patrick's response:

The Name Manager can be used to define names in Excel (in Excel 2010, this is on the Formulas pane at the top).

You can define FOOBAR as 400. So =FOOBAR in a cell will evaluate to 400.

The more common approach is to use named cells. Just type your tunable parameters into cells on some worksheet, and then name the cell as FOOBAR (you can use the Name Manager to do this as well).

Named cells are easier to update/use with sensitivity analysis: just change the contents of the named cell. If also has the advantage that you can enter a formula into your named cell. So the "constant" can be a computed value.

[Farhan Ahmed]

Thanks Patrick and Naju. 

Name Manager looks to be promising. Have you used it through the ExcelDNA abstraction? I'm looking for a simple get/set example.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

--

Farhan Ahmed

far...@ycharts.com | ycharts.com

P: 425-802-3818

[Patrick O'Beirne]

On 16/07/2012 20:13, Farhan Ahmed wrote:
> Thanks Patrick and Naju.
>
> Name Manager looks to be promising. Have you used it through the
> ExcelDNA abstraction? I'm looking for a simple get/set example.

ActiveWorkbook.Names.Add "Foobar",1243

Will set (create or update) a name's value

ActiveWorkbook.Names("Foobar").value
gets the value. Also see RefersTo and RefersToRange for more flexibility.

[Farhan Ahmed]

Cool, thanks.

I must be doing something wrong since I can't get it to work. Here's my code snippet which I've put at the top of a UDF (that's another question, see later):

            Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
            MessageBox.Show(app.ActiveWorkbook.Names.Count.ToString());
            app.ActiveWorkbook.Names.Add("FOO", 1234);

The message box correctly shows the count as 0, suggesting that I can correctly get references to the Application and Workbook instances. The last line throws an exception though (I am assuming because the call returns from the UDF at this point, though the debugger doesn't break). Anything obviously wrong that I am doing here?

Also, I want to put this code in the AutoOpen() so that the names are defined for the user from the start. ActiveSheet is not yet defined at the time the AutoOpen() is called. Is ActiveWorkbook guaranteed to exist by the time AutoOpen() is called or is there a timing issue lurking here?

Thanks.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To post to this group, send email to exce...@googlegroups.com.

To unsubscribe from this group, send email to exceldna+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

[Farhan Ahmed]

Alright, figured it out - the function had to be decorated as 'IsMacroType=true'. 

Would love help with the AutoOpen() question though.

[Govert van Drimmelen]

Hi Farhan,

For your AutoOpen, I guess you could subscribe to the Workbook-related
events on the Application object (http://msdn.microsoft.com/en-us/
library/microsoft.office.interop.excel.application_events). Then when
the NewWorkbook event fires, you can set up your names.

But this is getting mighty complicated, compared to just defining a
bunch of functions that return those constants. I'd suggest instead of
the Names you define functions like this in your add-in:

public static double ycFOO() { return 1234; }

Then your user can enter =FancyFunction(ycFoo(), ycBarOptionX(), $A$3)

which is not too bad, except for the extra brackets. (You'll have to
do some parameter validation, since using =ycFoo without the brackets
will return the registration id for the function.)

Picking a standard prefix for your constant (and function) names makes
them much easier to discover when typing in a formula.

-Govert

On Jul 17, 9:40 pm, Farhan Ahmed <far...@ycharts.com> wrote:
> Alright, figured it out - the function had to be decorated as
> 'IsMacroType=true'.
>
> Would love help with the AutoOpen() question though.
>
>
>
>
>
>
>
>
>
> On Tue, Jul 17, 2012 at 2:28 PM, Farhan Ahmed <far...@ycharts.com> wrote:
> > Cool, thanks.
>
> > I must be doing something wrong since I can't get it to work. Here's my
> > code snippet which I've put at the top of a UDF (that's another question,
> > see later):
>
> >             Excel.Application app =
> >> (Excel.Application)ExcelDnaUtil.Application;
> >>             MessageBox.Show(app.ActiveWorkbook.Names.Count.ToString());
> >>             app.ActiveWorkbook.Names.Add("FOO", 1234);
>
> > The message box correctly shows the count as 0, suggesting that I can
> > correctly get references to the Application and Workbook instances. The
> > last line throws an exception though (I am assuming because the call
> > returns from the UDF at this point, though the debugger doesn't break).
> > Anything obviously wrong that I am doing here?
>
> > Also, I want to put this code in the AutoOpen() so that the names are
> > defined for the user from the start. ActiveSheet is not yet defined at the
> > time the AutoOpen() is called. Is ActiveWorkbook guaranteed to exist by the
> > time AutoOpen() is called or is there a timing issue lurking here?
>
> > Thanks.
>

> > On Mon, Jul 16, 2012 at 4:41 PM, Patrick O'Beirne <obeirne....@gmail.com>wrote:
>
> >> On 16/07/2012 20:13, Farhan Ahmed wrote:
>
> >>> Thanks Patrick and Naju.
>
> >>> Name Manager looks to be promising. Have you used it through the
> >>> ExcelDNA abstraction? I'm looking for a simple get/set example.
>
> >> ActiveWorkbook.Names.Add "Foobar",1243
>
> >> Will set (create or update) a name's value
>

> >> ActiveWorkbook.Names("Foobar")**.value

> >> gets the value. Also see RefersTo and RefersToRange for more flexibility.
>
> >> --
> >> You received this message because you are subscribed to the Google Groups
> >> "Excel-DNA" group.
> >> To post to this group, send email to exce...@googlegroups.com.

> >> To unsubscribe from this group, send email to exceldna+unsubscribe@**
> >> googlegroups.com <exceldna%2Bunsu...@googlegroups.com>.
> >> For more options, visit this group athttp://groups.google.com/**
> >> group/exceldna?hl=en <http://groups.google.com/group/exceldna?hl=en>.

[Farhan Ahmed]

Thanks Govert - I'll check out the workbook related event as well as consider your suggestion of using trivial UDFs.

For now, I've implemented a simple timer that fires every second till I can get a reference to the active workbook and then I add the names and disable the timer. Seems to be working, though it's certainly not clean. 

To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.