Thread: Unrecognized functions in *.XLA

In an attempt to centralize my common VBA functions for several files, I
saved the function modules in a *.XLA file in my XLSTART directory. I then
went back to the original spreadsheet and deleted the function modules. Now,
even though the addin is shown as active and the functions are even
acknowleged in the 'insert function' toolbar, I only get #NAME? errors for
the addin functions show on the original spread sheet. If I open up a new
blank spreadsheet the functions work ok . Can anybody tell me what I need to
do to get the original spreadsheet to recocnize the add in functions?

Try this:

1. Edit the cell and reenter it. Sometimes works.
2. Check there is no path description in the function (e.g. it doesn't show
C:\My Documents\My Excel File.xls!FunctionName).
3. In the vbe. Try entering Application.CalculateFull in the immediate
window.

Robin Hammond
www.enhanceddatasystems.com

"rbnorth" <rbnorth@discussions.microsoft.com> wrote in message
news:FB9C15F1-7498-4F5F-993D-3D15F7E47CBD@microsoft.com...
> In an attempt to centralize my common VBA functions for several files, I
> saved the function modules in a *.XLA file in my XLSTART directory. I then
> went back to the original spreadsheet and deleted the function modules.
> Now,
> even though the addin is shown as active and the functions are even
> acknowleged in the 'insert function' toolbar, I only get #NAME? errors for
> the addin functions show on the original spread sheet. If I open up a new
> blank spreadsheet the functions work ok . Can anybody tell me what I need
> to
> do to get the original spreadsheet to recocnize the add in functions?

a trick:

add a reference to your addin to the calling workbook's
references. (in VBE via tools/references)

It helps to give your addin's VBproject a descriptive name
like RBNfunctions

Sub AddRef()
Dim wb As Workbook
Dim vbRef As Object 'VBIDE.Reference

Debug.Assert ThisWorkbook.IsAddin

For Each wb In Workbooks
For Each vbRef In wb.VBProject.References
If vbRef.FullPath = ThisWorkbook.FullName Then Exit For
Next
If vbRef Is Nothing Then
wb.VBProject.References.AddFromFile ThisWorkbook.FullName
End If
Next

End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


rbnorth wrote :

> In an attempt to centralize my common VBA functions for several
> files, I saved the function modules in a *.XLA file in my XLSTART
> directory. I then went back to the original spreadsheet and deleted
> the function modules. Now, even though the addin is shown as active
> and the functions are even acknowleged in the 'insert function'
> toolbar, I only get #NAME? errors for the addin functions show on the
> original spread sheet. If I open up a new blank spreadsheet the
> functions work ok . Can anybody tell me what I need to do to get the
> original spreadsheet to recocnize the add in functions?

Thanks both of you but neither set of suggestions helped. Im afraid Im not
familiar enough with the structure of the vbe to get Sub AddRef() to run.
Even with Macro security turned low it gave me an access denial. After
messing with it a bit its clear that the *.xla is not getting fully
incoporated into the workbook where older functions have already been
defined. I can add the full path to the function and it will run AND the path
reference dissappears, but if I copy that cell to another location, I get the
"NAME? error at the new location.

"keepITcool" wrote:

> a trick:
>
> add a reference to your addin to the calling workbook's
> references. (in VBE via tools/references)
>
> It helps to give your addin's VBproject a descriptive name
> like RBNfunctions
>
> Sub AddRef()
> Dim wb As Workbook
> Dim vbRef As Object 'VBIDE.Reference
>
> Debug.Assert ThisWorkbook.IsAddin
>
> For Each wb In Workbooks
> For Each vbRef In wb.VBProject.References
> If vbRef.FullPath = ThisWorkbook.FullName Then Exit For
> Next
> If vbRef Is Nothing Then
> wb.VBProject.References.AddFromFile ThisWorkbook.FullName
> End If
> Next
>
> End Sub
>
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> rbnorth wrote :
>
> > In an attempt to centralize my common VBA functions for several
> > files, I saved the function modules in a *.XLA file in my XLSTART
> > directory. I then went back to the original spreadsheet and deleted
> > the function modules. Now, even though the addin is shown as active
> > and the functions are even acknowleged in the 'insert function'
> > toolbar, I only get #NAME? errors for the addin functions show on the
> > original spread sheet. If I open up a new blank spreadsheet the
> > functions work ok . Can anybody tell me what I need to do to get the
> > original spreadsheet to recocnize the add in functions?
>