Creating an Add-In
Basharat Javaid
saved within the workbook.
I removed the modules form the workbook A and put them in an empty workbook,
B say, (with one blank sheet) and saved the workbook B as bookB.xla.
I opened the workbook A, added in the bookB.xla (Tools, Add-Ins ...). The
cells containing the functions show #Name? error.
The functions in bookB.xla do show up in the "User Defined" Function
category. I even tried inserting the functions with the Insert, Function
menu and entered the required arguments - but still I get #Name? error.
What's the problem and how do I resolve it?
--
Basharat Javaid.
Vasant Nanavati
I was unable to reproduce your problem using a simplistic function. Perhaps
the problem lies with the function itself and the references within it. You
may want to post the relevant code.
Regards,
Vasant.
Basharat Javaid <bja...@san.rr.com> wrote in message
news:eFi4tkHOBHA.320@tkmsftngp03...
Basharat Javaid
The problem is not just with one or two functions - all functions (I have
hundreds of them) are producing the #Name? error.
Here are some of the simplest functions I have:
' compute fractional year between two dates which are no more than 1 year
apart.
Function YearFraction(PYB, PYE) .
YearFraction = Year(PYE) - Year(PYB - 1) + (Month(PYE) - Month(PYB - 1)) /
12 + (Day(PYE + 1) - Day(PYB)) / 365 ' all on one line
End Function
' annualize salary if someone worked less than the full year
Function AnnualComp(PYB, PYE, DOH, EarnedComp, DOT)
Period = Year(PYE) - Year(PYB - 1) + (Month(PYE) - Month(PYB - 1)) / 12 +
(Day(PYE + 1) - Day(PYB)) / 365
Period1 = Year(PYE) - Year(DOH - 1) + (Month(PYE) - Month(DOH - 1)) / 12 +
(Day(PYE + 1) - Day(DOH)) / 365
If DOH > PYB Then Period = Period1 ' if hired during the year
If Val(DOT) > 0 And DOT <= PYE Then ' if terminated during the year
AnnualComp = 0
Else
AnnualComp = EarnedComp / Period
End If
End Function
They work fine when the modules containing the functions are within the
workbook.
Basharat.
"Vasant Nanavati" <vas...@aol.com> wrote in message
news:uQAbsWJOBHA.1020@tkmsftngp04...
Tom Ogilvy
the addin. See if that solves the problem.
What version of Excel are you using?
Also, if the formulas were built when the code was still in workbookA, then
you might need to reenter them.
You can do this using replace
Edit => Replace
replace what =
replace with "ZZ"
Then save the workbook
Edit => Replace
Replace What ZZ
Replace with =
You might try the above before trying to set a reference.
Regards,
Tom Ogilvy
Basharat Javaid <bja...@san.rr.com> wrote in message
news:OtVgxiLOBHA.1044@tkmsftngp05...
Basharat Javaid
Creating a reference worked.
Here what I noticed.
1. If I add-in the .xla file from Add-ins dialog box, the .xla shows up in
the Project window with all the modules visible and editable (if xla is not
locked & password protected)!? And it also shows up in the available
references list box.
2. One does not need to add-in the .xla to make it work. Creating the
reference alone does the trick and the add-in doesn't show up in Project
window . However, it shows up in the References list in the Project window.
So if 2. alone works, why use the add-in process?
I am using Excel 2000, and yes the formulas were built in workbook A - isn't
that the best way, first test the function in the workbook and then move the
function module to the xla file?
Basharat
"Tom Ogilvy" <twog...@email.msn.com> wrote in message
news:OIXw1zLOBHA.1872@tkmsftngp03...
Norman Harker
Installing the Add-In ensures that it is loaded each time Excel Opens.
One point to watch when changing an Add-In in VBE is that you save the
Add-In before you close Excel next time. Best to do it frequently before you
crash out :( Otherwise changes to the Add-In are not saved and you get no
warning as with other changed files. A bit of a bugger and I think it
actually counts as a bug as I can't see any good reason for it.
hth
"Basharat Javaid" <bja...@san.rr.com> wrote in message
news:eGEhaRPOBHA.1860@tkmsftngp03...
Tom Ogilvy
seem to have a memory of where the source of the function was. When you
remove them and put them in an xla, then you get the name problem. That is
based on a few problems I have seen presented here - I haven't really done
much personally in this area.
When you open a workbook that references other workbooks, those workbooks
are automatically opened as well. The disadvantage is that you have to
create the reference. When working properly, you shouldn't have to manually
create a reference. The functions in the addin should be available. I
would suspect if you create a new workbook and try to use the formulas from
the addin (given that it is open), you shouldn't have a problem without
creating a reference.
So your theory of testing is sound - but I would test in a workbook which
will eventually become the addin. Test the functions in the sheets of that
workbook. When they work, then you can delete all but one blank worksheet
and make the workbook an addin. Then start using the functions in your
production environment.
Regards,
Tom Ogilvy
Basharat Javaid <bja...@san.rr.com> wrote in message
news:eGEhaRPOBHA.1860@tkmsftngp03...
Basharat Javaid
Re: your last para - testing & deleting worksheets, except one. Here is what
I did.
1. Saved my main workbook A as XYZ_sheets.xls, deleted all the function
modules and re-saved XYZ_sheets.xls.
2. Went back to book A, inserted a blank sheet, deleted all other sheets
and all modules "except" the function modules. So now I have a workbook with
a blank sheet and function modules. I saved this as XYZ_Modules.xls and then
as XYZ_Modules.xla.
3. I re-opened XYZ_sheets.xls and added in XYZ_Modules.xla with the
Add-In - but the function formulas did not work in the XYZ_sheets without
creating the reference!
So I presume when I deleted the function modules in step 1, the worksheet
cells containing my user defined functions would have shown #Name? error
immediately i.e. even before I saved it as XYZ_sheets.xls.
I guess the correct procedure may be to:
A. Delete the sheets first (except the blank one) from book A and save it
as XYZ_modules.xla.
B. Re-open book A and Add-in XYZ_Modules.xla.
C. Delete the function modules from book A (not from the xla that shows
up) then save it as XYZ_sheets.xls.
I may have duplicate function problem at step B but that may disappear after
step C. I'll try that & report back.
Regards,
Basharat.
"Tom Ogilvy" <twog...@email.msn.com> wrote in message
news:uUbupSTOBHA.1664@tkmsftngp03...