using an add-in to store constants

[obiwann]

Can I use an add in to store a constant. I want to use that addin so all my workbooks can reference to certain constants.

Moderator's Note: Modified the thread title to correct a typo --6SJ

[MrShorty]

I'm not proficient in using named constants, so I'm not certain how well it would work to define a named constant to be used in any open workbook.

The strategy I use for frequently used constants looks something like this:

1) I have an add-in file that contains multiple user-defined VBA functions (UDF's).
2) For constants that I want available across all workbooks, I store a UDF for this purpose (these are the simplest UDF that one can write) in this add-in. For example, I have a UDF for the gas constant that I use that will look something like this

Please Login or Register  to view this content.

which I can call from any open workbook, just like the PI() function =Rgas()

[obiwann]

Do i put that code in the addin file or the workbook that i want to use the constant. What I am trying to do is have a place to store my constants (named cell) that can be referenced by several workbooks that use the same constant. When ever I have to update that constant, the change can be made in one place.

[MrShorty]

I'm a little confused. Do you have the constants stored in a workbook and want to use named cells to refer to them? As I said, I'm not very conversant with named ranges -- especially how to reference them across workbooks like that. If that is the approach you want to take, you will probably store them in your "personal.xlsx" file and someone else can help you reference them from other workbooks.

Using a UDF like I proposed, you store the UDF in an add-in file. Once the add-in is "installed" (through the manage add-ins dialog), the UDF's are available to all open workbooks and can be used like Excel's built in functions (like the =PI() function). As in my example, I have found it easiest to store the value of the constant in the function code rather than in a spreadsheet cell.

[obiwann]

I like your recommendation, just one question though. Do I enter this code into the addin and does it go into a module or a class module. Can you please write an example for the following: I have 5 workbooks that use a constant named "volume" which = 39.678. I want to store this constant in an add in. That way when this number needs to updated every month i only have to do it in the addin,

[MrShorty]

I have never used a class module, so I can't see that it would "need" to go in a class module.

So, the "constant function" goes in a regular module in a workbook that you save as an add-in. Once the add-in is saved, you install the add-in from the manage add-ins dialog (you will probably have to use the Browse button to locate the add-in the first time).

The code itself should be as simple as the one I posted above:

Please Login or Register  to view this content.

Is there a specific step in this process that you are having difficulty with? I'm not in a position right now to create the add-in for you, but if you explain exactly where in this process you are stuck, we should be able to help you.

[shg]

I have 5 workbooks that use a constant named "volume" which = 39.678. I want to store this constant in an add in. That way when this number needs to updated every month i only have to do it in the addin,

Then all the workbooks that use some prior value of 'volume' will change when reopened.

That's a variable, not a constant.

[obiwann]

> Ok, here is what I have done. I added this code to my add-in:
> Function volume() As Double
> volume = 39.677
> End Function
>
> On my new open workbook I type in =volume and the cell returns a value
> #NAME?
>
> What am I doing wrong?

[MrShorty]

As with the built in PI() function, the parentheses are required, even though no arguments are being passed to the function.

Did you install and activate the add-in? http://office.microsoft.com/en-us/ex...658.aspx?CTT=1 Does the add-in file appear in your list of available add-ins? Does the function appear in the function wizard under the "user-defined" category?

Beyond that, the name error means that Excel is somehow not recognizing the function name. An error in typing the function or an error in installing the add-in are the most common errors I've come across for this.

[obiwann]

Do you mean I have to type in =volume()

[MrShorty]

Yes. (additional text to bring message to 10+ characters)

[obiwann]

Thank you it works! Your help is much appreciated.