Using Add In Function library in Excel 1

[sabascal]

Hi all,

I created a small add-in with a library of functions.
Code is tested and works fine if I use it in one of my add-in sheet.

Now if I change the extension of the file to xla, change the property of the file to add-in = true, and drop the file in :
C:\Program Files\Microsoft Office\Office\XlStart

Everything works fine in my add-in , except that when I call my functions from a new workbook, it returning :#VALUE!

My functions are declared in a module of my add-in.


Example of functions:
--------------------
Function QROI(IRR As Double, Depreciation As Double, CostOfFund As Double, OH As Double, Leverage As Double, Taxes As Double) As Double
QROI = (IRR / 100 - Depreciation / 100 - CostOfFund / 100 * Leverage / 100 - OH / 100) * (1 - Taxes / 100)
On Error Resume Next
End Function
-------------

Any idea ?
Thanks

 

[combo]

Have you tried to create an add-in rather via 'save as' and select 'excel add-in' as file type?

combo

 

[sabascal]

Yes combo,
my file has an .xla extension and has is add-in property set to true.

 

[combo]

I've asked because it was not obvious in your previous post.

If you need the add-in to behave as an add-in, you should install it rather than open. Add-in, When installed, is opened with excel, wherever it is. It can also be opened like a regular workbook, in that case you have to refer to function with workbook name.
Only after installing the add-in excel knows it is an add-in. And XLStart is not the best place for keeping it.
You may also need to re-enter cells with the function, you should have no problem with installed add-in.

combo

 

[sabascal]

Thanks Combo

I dropped my add-in in
C:\Program Files\Microsoft Office\Office\XlStart
Because I want it to be installed each time I launch excel.
Is there a best place to put it?

I tested my function in workbooks and in my add-in.
It works fine in a workbook, don't work in my add-in.

any idea?

 

[combo]

Hi,
What you need is installed add-in. A way to install add-in is via tools>addins.. etc. Installing add-in tells excel to open it each time you start excel, you do not need XLStart directory here and you do not need to install file every time you open excel. The default directory for user add-ins you can find by selecting 'add-in' as file type when saving file, excel will jump here.
Probably your functions refer to non-existing file (test version?), check this in the formulas. Normally, with add-in installed, formula should look like =QROI(.....), without add-in path and file name.

combo

 

[zpap]

Hi,
1/ I do never change a .xls into .xla extension. As Combo said, I always make a file/saveas, select filetype xla, and
of use the standard directory for addin (automaticaly preselected by system), of choose a specific folder (for sharing purpose with others people.
2/Addin must be loaded before use: 3 ways: see

http://msdn.microsoft.com/library/d...en-us/modcore/html/deovrloadingexceladdin.asp

I tried your addin:
I created and saved as i said
Then I opened a new workbook
Then tools/addins/browse and select my new addin
then encoded the new function in a cell .. and it works !
regards
Luc