Solved

My spreadsheet does not recognize a VBA function I have created

Posted on 2014-10-09
4
251 Views
Last Modified: 2014-10-09
I have a spreadsheet that no longer recognizes a VBA function I have created. The function worked the last time I used the spreadsheet, but now it just returns #NAME?.

Let me know if you need me to attach the spreadsheet and any other needed files.

Thanks,

Jeff
0
Comment
Question by:Starbuck67
  • 2
  • 2
4 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40371787
Couple of possibilities come to mind:
1) The Function isn't saved in this workbook.
2) The function name isn't exactly the same as defined in VBA.

Of course, you can verify these by going into VBA and checking.  If your function is in your Personal Macro Workbook, you'll need to copy it to the one you wish to use it in.

-Glenn
0
 

Author Comment

by:Starbuck67
ID: 40371818
Hi Glenn,

I should have just posted here to begin with. Instead I spent a couple of hours searching the web for the same description I posted here.... :(

I had thought that the personal.xslb was available to any workbooks I was using. Personal. xslb loads every time I open a workbook. Also, I have created a number of macros that also show up in the VBA project area as modules and they are available to any workbook I am using.

I did copy the module with the function in it to the workbook that contains the use of that function and it works fine now.

Would be interested in hearing your thoughts, though.

Thanks,

Jeff
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40371861
VBA subroutines in the Personal Macro Workbook are available in an Excel instance, but not user-defined functions (UDFs).  Sad to say, but that's just a limitation we live with.  

However, there is resolution.  Create a blank workbook with the UDF(s) in the code and save it as an Excel Add-In file (.xlam).  Then, activate that add-in (Menu: File - Options - Add-Ins.  "Manage" section on bottom - "Excel Add-ins" - Go button).  Your function will be available.

-Glenn
0
 

Author Comment

by:Starbuck67
ID: 40371899
I saw some comments about .xlam files in my online research, but didn't look into them. Thanks for the explanation regarding those. I'll give it a try.

Jeff
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can email be forwarded using contact only on Exchange 2007 8 47
Backup Data/Portable NAS storage 4 65
Need job search tool for follow-ups 6 80
Dentrix G4 1 47
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
A list of useful business intelligence software.
Using Adobe Premiere Pro, the viewer will learn how to set up a sequence with proper settings, importing pictures, rendering, and exporting the finished product.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now